How can I remove records from a collection if they exist in a table?

[Anonymous]

Hi All,

I'm trying something like: -

ForAll(collection

    If(CountRows,table, tableRecID = collectionRecID)<>0,

        RemoveIf(collection, collectionRecID = tableRecID)

    )

)

But PowerPlatform throws the error: -

"This function cannot operate on the same data source that is used in ForAll"

The context is a little complex but basically I have created a Canvas app to check people in and out of events. The network is limited so to prevent that from slowing the registration process I create a registration collection from a registration table to track and display the registration status (person checked In / Out) and use a timer to write items from the registration collection to the registration table, then (THE CODE ABOVE:) remove the registration record from the collection if it has been written to the table.

Any feedback on whether this is the right approach and if so how to solve this or if there is another or best practice approach would be massively appreciated.

Many thanks in advance

[Drrickryp]

Hi @Anonymous 

The easiest way to remove items from a collection when they no longer exist in a datasource is to simply scrap the collection and do a ClearCollect() from the modified datasource to recover it.

[Anonymous]

Hi @Drrickryp , many thanks for your reply but I want to remove records from the collection when they exist in the datasource (not "when they no longer exist in a datasource").

I'm need to ensure absolute integrity that a record committed to the collection will be written to the Dataverse table.

[Drrickryp]

ClearCollect(
    colB,
    datasource
);
ClearCollect(
    colA,
    yourcollection
);
ClearCollect(
    colC,
    Filter(
        colB,
        !(ID in colA.ID)
    )
)

In the example above, colC will contain unique names in the collection not in the datasource.

[Anonymous · Accepted solution]

Thanks again @Drrickryp  but I don't think that addresses my problem below. I do seem to have a found a solution however...

Problem:
I cannot write directly to the Dataverse table due to inconsistent available network bandwidth which slows the user registration experience.

I therefore need a foolproof way to ensure that once data is written to the collection it is always written to the Dataverse table.

Approach:
Write registrations to a tracking collection and have screen elements refer to that collection.

Write from the tracking collection to a commit collection and then to a Dataverse attendance table using a timer control only removing records from the tracking collection if verified that they have been written to the Dataverse attendance table.

Solution:
I seem to have solved the problem by introducing the commit collection to the logic. I've simplified the OnTimerEnd code below to better convey the logic: -

//If there are records in the commit collection then try later, otherwise ...
If(CountRows(col_commit) = 0,

    //Copy the registration collection to the commit collection
    ClearCollect(col_commit,col_tracking);

    //Loop through the commit collection
    ForAll(col_commit,

        //write registration data from the commit collection to the attendance table 
        Patch(tbl_attendance, Defaults(tbl_attendance),{uniqueTableField1: uniqueCollectionField1,tableField2: collectionField2});

        //Remove the record from registration collection if it has been written to the attendance table
        If(CountIf(tbl_attendance, uniqueTableField = uniqueCollectionField) <> 0, Remove(col_tracking,col_commit));

    )

    //Clear the commit data ready for the next sync
    ClearCollect(col_commit,Blank())

)

This approach leaves records in the tracking collection if not confirmed written to the attendance table via the commit collection so app users can continue to register people whilst data syncs to the Dataverse attendance table as and when able.

Whilst data is written from the commit collection to the attendance table, application users can continue to register people with registration records being added to the tracking collection.

On the next timer event any records in the tracking sollection not yet written to the Dataverse table are once more passed to the commit collection (provided the commit collection has completed its last sync and is consequently empty. It will keep trying at every timer interval).

I wonder if there is a better approach and welcome any builds on this from the community but this seems to work well enough for me just now.

[Drrickryp]

Nice. I’ll give you the kudos this time.