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

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

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.

Super User II
Super User II

 

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

Super User II
Super User II

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

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (74,880)