Filter a collection by Item NOT FOUND IN collection column not working within ForAll

[Nebulas]

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:

1. Retrieve a list of products from an API using Powerautomate (This is populated straight into a collection called 'colSourceItems'
2. Build a collection of records already in Dataverse using ShowColumns called 'colSourceItemsDataverse'
3. 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)
4. 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)
5. The final steps in the code then process each type from both evaluations above and patch the changes to Dataverse
6. 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
        )
        }
);

[WarrenBelz · Accepted solution]

Hi @Nebulas ,

Firstly try this

ForAll(
   Filter(
      colSourceItems,
      AccountCode = varSourceAccountRevenueCode && 
      !(ItemID in colSourceItemsDataverse.cr07e_itemid)
   ) As aItems,
   Collect(
      colSourceItemsToAdd,
      {
         ItemID: aItems.ItemID,
         Code: aItems.Code,
         Name: aItems.Name,
         Description: aItems.Description,
         AccountCode: aItems.AccountCode,
         UnitPrice: aItems.UnitPrice,
         UpdatedDateUTC: aItems.UpdatedDateUTC
      }
   )
);

As well as the ambiguity issue with the identical field names, it is good to keep the filter together.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Nebulas]

Hi @WarrenBelz 

Thanks for your super fast response. This set me on the correct path.

Turns out that my issue was some dirty test data from one of my previous versions of the code that erroneously repeated the same ItemID/Name across about 60 records. This was overstating the number of 'unique' records that were going to be 'Updated'. This over statement led me to change my Not In expression. Once I reverted back to my original expression ( !(ItemID in colSourceItemsDataverse) and compared against your version it was actually getting the same and correct results. Now that I have deleted the dirty data, the sync routine now works perfect again.

That being said, it looks like there is merit in using your variation with the 'As aItem' as this will help me better distinguish between field names/datasources especially when nested in ForAll's / LookUps etc that can get very confusing.

Would this be accurate?

Thanks again for you help!

[WarrenBelz]

Hi @Nebulas ,

Yes - the As statement is a disambiguation tool - the issue with the original Patch was the identical field names on both sides. It is also good to keep the filter in one statement.

[Nebulas]

Hi @WarrenBelz 

Okay I understand, and this is really going to help me clean up and better differentiate the items within my collections.

So in Summary I have three main collections at play here:

1. Source systems full active list of items (colSourceItems)
2. Dataverse target system full list of items (colSourceItemsDataverse)
3. Output collections for how to process each type (I.e. colSourceItemsToUpdate, ...ToUnChange, ...ToAdd, ...ToInactivate

Collections 1 & 3 above would benefit from using 'As aItem' to disambiguate (May have just invented a word).

Collection 2 above is my 'Staging Collection' where I apply each of the outputs from collection 3. I deliberately use identical field names so that the patch statement is simple (I.e. Able to patch the entire 'colSourceItemsDataverse' collection into the Dataverse table because all of the field names match).

Thanks again, this has been a valuable learning exercise that will help me going forward.

[WarrenBelz]

Hi @Nebulas ,

No problems  . . . and disambiguation is apparently a word.