Answered

Using ForAll and LookUp to Patch SharePoint list

(0) Share

Report

Posted on by Ryan_B

446

I have a collection of data that I will be uploading to a SharePoint list. I want to first check to see if the first two columns will match an existing row in that list. If it does then I want it to do nothing, otherwise it will patch the new record. I feel like the solution here is to use a combination of ForAll, LookUp, and Patch, but for the life of me I cannot get it to work.

ForAll(
 colFinal,
 If(
 LookUp(Acronyms, ThisRecord.Acronym = Acronym && ThisRecord.Definition = Definition, true),
 "",
 Patch(
 'Acronym Submissions', 
 Defaults('Acronym Submissions'),
 {
 Acronym: ThisRecord.Acronym,
 Definition: ThisRecord.Definition
 }
 )
 )
);

The error I'm getting when using the above code is that it's expecting a Text value instead of a record in the Patch function. After doing some research I found others had the same issue, but they were performing the LookUp on a single piece of information within the record rather than the entire record itself. I have a feeling that I have the order wrong or the entire concept wrong, as the problem lies with the ThisRecord portion in the Patch.

I'm not sure what I'm doing wrong as I feel like I've done something similar to this before.

Categories:

Connecting to Data

All responses (3)

Answers (1)

Ryan_B 446 on at

Like (1)

Report

Re: Using ForAll and LookUp to Patch SharePoint list

@RandyHayes

I see now. You're right, I'm trying to treat it like a ForLoop which makes sense to me. This was a much smoother approach and works flawlessly. Thank you for your help and expertise as always!!
Verified answer

RandyHayes 76,287 Super User 2024 Season 1 on at

Like (2)

Report
Re: Using ForAll and LookUp to Patch SharePoint list

@Ryan_B

Well, to start, your formula has the ForAll backward. You are trying to use it like a ForLoop in some development language - which PowerApps is not. It is a function that returns a table of records based on your iteration table and record schema.

It is more efficient to use the function as intended and will provide better performance.

Your formula should be the following:

Patch('Acronym Submissions', Filter( ForAll(colFinal As _item, If(!LookUp(Acronyms, _item.Acronym = Acronym && _item.Definition = Definition, true), { Acronym: _item.Acronym, Definition: _item.Definition } ) ), !IsBlank(Acronym) ) );

Patch is smart...first, it takes a table (which is what ForAll was designed to create - a table) and will use that to perform the patching from the entire table - not instantiating patch over and over like when you use the ForAll backward.

Also, it is smart because if you include the primary key in your records, it will then update that record. If the primary key is missing, then it will create a record.

The filter in the above is to ensure that nothing that no blank records are attempted to be patched.

The above formula should provide what you need.

I hope this is helpful for you.
Ryan_B 446 on at

Like (0)

Report
Re: Using ForAll and LookUp to Patch SharePoint list

Update:

I altered the code slightly to what's found below by adding an IsBlank and the LookUp returning the record instead of true. No errors this time but something still isn't right. I attempted to add a duplicate record and a new one, but both went through.
ForAll( colFinal, If( IsBlank( LookUp(Acronyms, ThisRecord.Acronym = Acronym && ThisRecord.Definition = Definition, ThisRecord)), Patch( 'Acronym Submissions', Defaults('Acronym Submissions'), { Acronym: ThisRecord.Acronym, Definition: ThisRecord.Definition } ) ) );