cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JimMeldrum1
Helper II
Helper II

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

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

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

5 REPLIES 5
Drrickryp
Super User
Super User

Hi @JimMeldrum1 

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.

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
Super User
Super User

 

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.

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.

View solution in original post

Drrickryp
Super User
Super User

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,407)