Using Patch and FirstN

[jrtpts]

I am trying to patch a specific number of records in a CDS entity. For example, let's say I want to change a whole number field in 100 records from 0 to 5. There are more than 100 records with 0 in that field, and the 100 I want to change aren't necessarily unique to the other records with 0 in that field. But for my purposes, I need to patch precisely 100 records. Here's the formula I'm using:

ClearCollect(colNewRecords, FirstN(Filter('CDS Entity', asdf_recordgroup = 0), 100));
ForAll(colNewRecords, Patch('CDS Entity', LookUp('CDS Entity', asdf_recordguid = colNewRecords[@asdf_recordguid]), {'Record Group': 5}))

Originally, I placed the FirstN in the ForAll function's source, which caused an error with the '=' later in my formula, marked invalid argument type. I could make the error go away by rearranging the syntax to use 'in' instead of '=', but then the formula doesn't do anything when it runs. Placing the FirstN in the collection made that error go away, but the formula still doesn't do anything when it runs.

If I remove FirstN altogether, this all runs just fine, except it patches all records with 0 in the field, instead of the specific number I need patched

I haven't found any documentation or threads which suggest that FirstN and Patch aren't compatible - is this a syntax issue or does FirstN/LastN somehow muck up the data in a collection such that Patch can't use it?

[eka24]

Try;

ClearCollect(colNewRecords, FirstN(Filter('CDS Entity', asdf_recordgroup = 0), 100));
ForAll(colNewRecords, Patch('CDS Entity', {'Record Group': 5}))

Or

Update('CDS Entity',
FirstN(First (Filter('CDS Entity', asdf_recordgroup = 0)), 100),
{ 'Record Group': 5 } )

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[jrtpts]

Thanks for your response! 

I tried this, and the function ran, but instead of patching the 100 records in the collection, it created copies of those 100 records in the entity with the 'Record Group' value changed.

The LookUp function seems necessary to match the collection records to the records in the larger CDS entity, but in my research I've learned that it's also a very troublesome function...

[eka24]

Clarification:

Do you want to change the records in the Collection or in the Entity?

If the collection:

UpdateIf(CDS Entity, true, { 'Record Group': 5 } )

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[jrtpts]

The change needs to occur in the entity, not the collection.

[v-siky-msft]

Hi @jrtpts ,

The Codes and Syntax are all good in my test, the only possibility of the issue I think could be the delegation issue.

The colNewRecords[@asdf_recordguid] code is not delegable in PowerApps, If the target records exceed the delegation threshold,  so nothing happens.

Could you please try to rename the GUID field and try again to see if the issue is fixed?

Please try this:

ClearCollect(colNewRecords, FirstN(Filter(RenameColumns('CDS Entity',"asdf_recordguid","IDColumn"), asdf_recordgroup = 0), 100));
ForAll(colNewRecords, Patch('CDS Entity', LookUp('CDS Entity', asdf_recordguid = IDColumn), {'Record Group': 5}))

Hope this helps.

Sik

[jrtpts · Accepted solution]

I figured it out!

ClearCollect(colNewRecords, FirstN(Filter('CDS Entity', asdf_recordgroup = 0), 100));
ForAll(colNewRecords, Patch('CDS Entity', LookUp(FirstN(Filter('CDS Entity', asdf_recordgroup = 0), 100)), asdf_recordguid = colNewRecords[@asdf_recordguid]), {'Record Group': 5}))

Basically, I just limited the LookUp function to colNewRecords's definition. I think I needed to limit the LookUp function to the same set of records captured in the colNewRecords collection. I think I remember seeing somewhere that this ForAll(Patch(LookUp())) arrangement only works if you keep the various table references the exact same size.

@v-siky-msft , I think you're right that this wouldn't work with any sets over the delegation limit, but for my use case, I'll never need to patch more records than the limit allows. If I ever do, it'll be for a bulk import outside the canvas app.