Remove Record on CDS 500+ records

[Anonymous]

How do I remove a record in CDS when I have over 500 records and Remove/RemoveIf do not support delegation?

I have no problem delegating the Filter function to query the record. I just can't remove the record. I don't see the common delegation warning (blue underlines/Dot) when I write the RemoveIf. PowerApps seems to accept the code. When I select the button it returns to 'ScreenHome' screen and the record is not removed. It did remove records prior to the data source exceeding 500 records.

Delegation

Remove/RemoveIf

Quote regarding delegation from the Remove/RemoveIf Documentation...

"When used with a data source, these functions can't be delegated. Only the first portion of the data source will be retrieved and then the function applied. The result may not represent the complete story. A blue dot will appear at authoring time to remind you of this limitation and to suggest switching to delegable alternatives where possible"

I feel like I'm missing something silly. But I currently don't see how to remove records if these functions are limited. 

-Thanks

[mr-dang · Accepted solution]

Since the ForAll() function came out, I've used it for more precise actions.

Normally you would use:

Remove(datasource,Filter(datasource,conditions))

RemoveIf(datasource,conditions)

This would remove the items you want in one single action and access of the datasource. But as you noted, it's limited to what can be delegated.

ForAll kind of inverts the steps. First you specify what records you want to remove, then the ForAll function performs the remove action for each record in your set. This requires more communication with the datasource, but ForAll performs it simultaneously, so it's not as slow as you may think (at least for CDS).

ForAll(Filter(datasource,conditions),
    Remove(datasource,First(Filter(RenameColumns(datasource,"RecordId","RID"),RID=RecordId)))
)

• Red: Here, you can specify the exact things you want to remove. You can give the argument directly, or you can collect it to a temporary collection beforehand. Assuming your conditions are delegable, this should work.
• Blue: I renamed the columns since I'm referencing a column of the same name and I want to avoid ambiguity. I am unaware of how you can reference the record itself (i.e. ThisItem) for ForAll. That would make the formula easier to write.
• Green: Here, I am telling the Remove() function to remove the record in the connected datasource that has the same unique identifier as the argument in Red. CDS uses "RecordId" and "PrimaryId" as unique identifiers of each record.

Note: This formula is unnecessarily longer and needlessly complicated compared to the regular Remove() function. It is only meant as a temporary solution.

[Anonymous]

Thanks, with a few tweaks it's working great. 

It was giving me greif for trying to use the same data source for the remove and ForAll. That's why I made the collection and I used a previously defined context (CurrentPatientNumber) to help with ID.

This problem was driving me nuts, you are awesome. 

ClearCollect('Collection_Remove',Filter('Community Outreach Information',CurrentPatientNumber in PatientNumber));ForAll(Filter(Collection_Remove, CurrentPatientNumber in PatientNumber), Remove('Community Outreach Information',First(Filter('Community Outreach Information',CurrentPatientNumber in PatientNumber))))

[pyee]

Has anyone gotten this method to work with a SQL Server table via Data Gateway?

I'm getting an error that the Remove function doesn't like one of its arguments...

ForAll(Filter('[dbo].[GasContract_Acreage]', Id = gal_ACREAGE_DEDICATIONS.Selected.Id), Remove('[dbo].[GasContract_Acreage]', First(Filter(RenameColumns('[dbo].[GasContract_Acreage]', "Id", "RID"), RID = Id))))