Hi all
I have been spinning my wheels for a few days now with my code below.
The objective here is to sync records between an external 'source' system and Dataverse. The basic steps are:
- Retrieve a list of products from an API using Powerautomate (This is populated straight into a collection called 'colSourceItems'
- Build a collection of records already in Dataverse using ShowColumns called 'colSourceItemsDataverse'
- Evaluation 1: Determine which records in Dataverse need to be Updated, or Inactivated by comparing states and date updated last in source system (This seems to work perfectly from the testing that I have performed)
- Evaluation 2: Determine which records from the Source system DO NOT exist in Dataverse, and hence must be NEW items to add (This is the part that is NOT working and is the first code snippet below)
- The final steps in the code then process each type from both evaluations above and patch the changes to Dataverse
- Using the UpdateContexts I then pass the results of what is NEW, UPDATED, INACTIVATED in a notification (This part not showing in code snippets)
I have also posted full part of the code relevant to this issue in the second code snippet.
Issue Summary: Evaluation 2 does NOT result in showing me the records in the source system that are NOT in Dataverse. I have played around with several versions of the expression into 'IsBlank (LookUp' etc etc without success. I have analysed record by record, and show the collections in Galleries side by side and the expression is definitely not showing what I see in the galleries (I.e. I can see I have record ID's in the source where they are NOT in Dataverse).
Now @RandyHayes I know you are going to hammer me about using collections in this way, but from the testing I have done, this scales the performance when dealing with thousands of records (noting delegation constraints). But obviously still happy to hear this and increase my understanding of the platform as I am still very novice.
Any help would be greatly appreciated.
Evaluation 2 Code Snippet:
//Evaluate Records in Source NOT in Dataverse to determine Items to Add
ForAll(
Filter(
colSourceItems,
AccountCode = varSourceAccountRevenueCode
),
If(
!(ThisRecord.ItemID in colSourceItemsDataverse.cr07e_itemid),
Collect(
colSourceItemsToAdd,
{
ItemID: ItemID,
Code: Code,
Name: Name,
Description: Description,
AccountCode: AccountCode,
UnitPrice: UnitPrice,
UpdatedDateUTC: UpdatedDateUTC
}
)
)
);
Full Code Snippet:
Clear(colSourceItemsToUpdate);
Clear(colSourceItemsToUnchange);
Clear(colSourceItemsToInactivate);
Clear(colSourceItemsToAdd);
//Get Product List from Source
ClearCollect(
colSourceItems,
GETItems.Run()
);
//Collect Source Products currently in Dataverse
ClearCollect(
colSourceItemsDataverse,
ShowColumns(
colSourceItemsDataverse,
"cr07e_itemssourceid",
"cr07e_itemid",
"cr07e_name",
"cr07e_code",
"cr07e_accountcode",
"cr07e_description",
"cr07e_unitprice",
"cr07e_updateddateutc",
"cr07e_updateddateutcnew",
"statecode"
)
);
//Evaluate Records in Dataverse to determine Items to Inactivate / Update / Unchange
ForAll(
colSourceItemsDataverse,
If(
And(
ThisRecord.Status = 'Status (colSourceItemsDataverse)'.Active,
ThisRecord.ItemID in colSourceItems.ItemID
),
If(
ThisRecord.cr07e_updateddateutcnew < DateTimeValue(
LookUp(
colSourceItems,
cr07e_itemid = ItemID, UpdatedDateUTC
)
),
Collect(
colSourceItemsToUpdate,
{
ItemsSourceID: ThisRecord.cr07e_itemssourceid,
ItemID: ThisRecord.cr07e_itemid,
Code: Code,
Name: Name,
Description: Description,
AccountCode: AccountCode,
UnitPrice: UnitPrice,
UpdatedDateUTC: UpdatedDateUTC
}
),
Collect(
colSourceItemsToUnchange,
{
ItemsSourceID: ThisRecord.cr07e_itemssourceid,
ItemID: ThisRecord.ItemID,
Code: Code
}
)
),
If(
ThisRecord.Status = 'Status (colSourceItemsDataverse)'.Active,
Collect(
colSourceItemsToInactivate,
{
ItemsSourceID: ThisRecord.cr07e_itemssourceid,
ItemID: ThisRecord.ItemID,
Code: Code
}
)
)
)
);
//Evaluate Records in Source NOT in Dataverse to determine Items to Add
ForAll(
Filter(
colSourceItems,
AccountCode = varSourceAccountRevenueCode
),
If(
!(ThisRecord.ItemID in colSourceItemsDataverse.cr07e_itemid),
Collect(
colSourceItemsToAdd,
{
ItemID: ItemID,
Code: Code,
Name: Name,
Description: Description,
AccountCode: AccountCode,
UnitPrice: UnitPrice,
UpdatedDateUTC: UpdatedDateUTC
}
)
)
);
//Process Items to Update
If(
!IsEmpty(colSourceItemsToUpdate),
UpdateIf(
colSourceItemsDataverse,
ThisRecord.cr07e_itemssourceid in colSourceItemsToUpdate.ItemsSourceID,
{
cr07e_itemid: LookUp(
colSourceItemsToUpdate,
ItemID = cr07e_itemid,
ItemID),
cr07e_code: LookUp(
colSourceItemsToUpdate,
ItemID = cr07e_itemid,
Code),
cr07e_name: LookUp(
colSourceItemsToUpdate,
ItemID = cr07e_itemid,
Name),
cr07e_description: LookUp(
colSourceItemsToUpdate,
ItemID = cr07e_itemid,
Description),
cr07e_accountcode: LookUp(
colSourceItemsToUpdate,
ItemID = cr07e_itemid,
AccountCode),
cr07e_unitprice: LookUp(
colSourceItemsToUpdate,
ItemID = cr07e_itemid,
UnitPrice),
cr07e_updateddateutcnew: DateTimeValue(
LookUp(
colSourceItems,
ItemID = cr07e_itemid,
UpdatedDateUTC
)
),
statecode: 'Status (colSourceItemsDataverse)'.Active
}
)
);
//Process Items to Inactivate
If(
!IsEmpty(
colSourceItemsToInactivate
),
UpdateIf(
colSourceItemsDataverse,
ThisRecord.cr07e_itemssourceid in colSourceItemsToInactivate.ItemsSourceID,
{
cr07e_updateddateutcnew: DateTimeValue(
LookUp(
colSourceItems,
cr07e_itemid = ItemID,
UpdatedDateUTC
)
),
statecode: 'Status (colSourceItemsDataverse)'.Inactive
}
)
);
//Process Items to Add
If(
!IsEmpty(
colSourceItemsToAdd
),
ForAll(
colSourceItemsToAdd,
Collect(
colSourceItemsDataverse,
{
cr07e_itemid: ItemID,
cr07e_code: Code,
cr07e_name: Name,
cr07e_description: Description,
cr07e_accountcode: Value(AccountCode),
cr07e_unitprice: UnitPrice,
cr07e_updateddateutcnew: DateTimeValue(UpdatedDateUTC)
}
)
)
);
Patch(
ItemsSources,
colSourceItemsDataverse
);
Refresh(ItemsSources);
UpdateContext(
{
varSourceProductSyncResult:
"New: " & CountRows(
colSourceItemsToAdd
) & Char(10) &
"Updated: " & CountRows(
colSourceItemsToUpdate
) & Char(10) &
"Inactivated: " & CountRows(
colSourceItemsToInactivate
)
}
);
