Hi All,
I have a requirement where I need to create a custom entity, similar to the expense journal entity, and add an ID field called SID from an isv that we have. Now this SID and the Activity Number field in the ledgerjournaltrans_Project table have a relation. The SID was created via data method and I used postload() to get the SID in the entity based on the activity number. And in this entity a requirement is that when we fill out the SID in the excel entity template, the activity number gets populated as well.
My question is how to accomplish this? I’m assuming via maptoEntityDataSource() method? I guess my concern was that ActivityNumber is a required field, and in the excel template you need to enter a value in ActivityNumber before publishing. Will maptoEntityDataSource do that? Have only been using postload() so this is a bit new for me.
Thanks!
I’m confused. The title says that it’s about export and you mentioned some code in that respect, but the description “when we fill out the SID in the excel entity template” sounds like a complete different topic: editing the data in Excel (using the Data Connector add-in). Please whether it’s the case and fix the title if so.
Hi Martin,
I fixed the title. I was wanting guidance on how to autopopulate field in excel Data Connector add-in. The requirement is that when we fill out the SID in the data connector add in, the activity number gets populated as well.
Right now I see this:
The idea is to simplify the Activity number lookup so the user can pick it via the SID number. Any suggestions? I was maptoEntityDataSource could do that?
Okay, so you want a data entity that supports OData (which is used by the Excel add-in as well), where you’ll provide SID and then you want something to happen on import. I understand you want to use SID to find ActivityNumber, but it’s not clear to me what you want to do with it.
Remember that I don’t know your data entity and your other customizations, such as which table contains the SID field and how its related to LedgerJournalTrans_Project.ActivityNumber.
Yes, that’s right — we’re trying to make things easier for users in the Excel add-in by letting them enter just the SID, and then have the matching Activity Number filled in automatically.
To explain better:
- The SID isn’t a real field in the table — it’s a data method added to the
PREFABExpenseJournalLineEntity.
- That entity is linked to
LedgerJournalTrans_Project, and we match records using the ActivityNumber.
- On export, we already populate the SID using the Activity Number in the
postLoad() method (shown below).
- Now we want to do the opposite on import — when a user enters an SID, we want to find the correct Activity Number and fill it in automatically.
Here’s how we set the SID on export:
[ExtensionOf(tableStr(PREFABExpenseJournalLineEntity))]
final class ExpenseJournalLineEntity_Extension
{
public void postLoad()
{
next postLoad();
LedgerJournalTrans_Project ledgerJournalTransProject;
if (this.ActivityNumber)
{
select firstonly ledgerJournalTransProject
where ledgerJournalTransProject.ActivityNumber == this.ActivityNumber;
if (ledgerJournalTransProject)
{
this.SID = MyLedgerJournalHelper::computeWBS(ledgerJournalTransProject);
}
}
}
}
And this is how SID is being calculated in the data method:
class MyLedgerJournalHelper
{
public static ARPTaskId_Wbs computeWBS(LedgerJournalTrans_Project ledgerJournalTransProject)
{
ARPTaskId_Wbs wbs;
[wbs, ledgerJournalTransProject.ActivityNumber] = ARPTasktools::TaskWBSId_edit(
ledgerJournalTransProject.projId,
'',
false,
wbs,
ledgerJournalTransProject.ActivityNumber
);
return wbs;
}
}
Now I’m looking for the best way to handle this on import — so when SID is provided, we can automatically look up and set the Activity Number. Would love any suggestions! Just a bit unsure on how to handle this.
You surely can write code in the entity to take the value of the virtual entity and do something with it. Your description (automatically look up and set the Activity Number) suggests that you want to you want to use some (unknown) logic to find ActivityNumber from SID and assign it to a field of an existing data entity data source. If so, your code can look like this:
public void mapEntityToDataSource(
DataEntityRuntimeContext _entityCtx,
DataEntityDataSourceRuntimeContext _dataSourceCtx)
{
super(_entityCtx, _dataSourceCtx);
switch (_dataSourceCtx.name())
{
case dataEntityDataSourceStr(YourEntity, DataSourceWithActivityNum):
// Run your logic to find the activity number from SID
str activityNumber = YourLogic::sidToActivityNumber(this.SID);
// Assign the value
YourTable yourTable = _dataSourceCtx.getBuffer()
yourTable.ActivityNumber = activityNumber;
break;
}
}
That makes sense, i’ll try this out. Thank you.
Just wanted to double check that the mapEntityToDataSource also works for when publishing new data via the excel add in?
Yes, the logic for mapping entity fields to data source fields is necessary (and therefore executed) on every import, regardless of the source.
In practice, there are just two scenarios - you either use data management (including its APIs) or OData. They differ, e.g. postGetStaging() applies just to data management.
OData can be called directly, but it’s also used by the Excel add-in, by Power Platform and so on.
Hi Martin,
I made a post on this already but didn’t know if this was the better place to ask:
I did get the logic to work, however when I create a new line in Excel and enter the ID, I get this message:
“Changes made to read-only data rows will be reverted.”
After that, both the ID and ActivityNumber fields go blank.
However, when I enter the same values again, the lookup works perfectly, and the ActivityNumber populates as expected.
Additionally:
- If I clear
ActivityNumber on an existing line (where ID is already set), the autofill also works fine.
- This issue only occurs on the first attempt when creating a new line.
Is there a known reason why this happens only for new rows on the first publish attempt? Is there something I need to adjust or another way to ensure the logic triggers correctly on first insert?
For reference, the thread for the last point is ActivityNumber Not Auto-Populating on Initial Publish for New Excel Add-in Rows. Let’s not duplicate the discussion here.
