Lookup values from excel table and write to with patch

[stefanlive]

I have created a table that has 6 columns..

I want to search for a row in excel that meets two conditions.. then write to the column in that row..

I have used the following syntax

Patch(thelessonsbooked, LookUp(thelessonsbooked, learnernumber = thecgnum.Text && lessonnumber = feedbacknumber.Text), {learnernumber: thecgnum.Text, Completed: "SAD"});

thelessonbooked is the excel table

learnernumber is column1 a text field

feedbacknumber is column2 again a text field  and the data from these comes from selecting values in two linked gallaries. i.e the user click on a name and from that it lists a number of lessons that person attended.

So I want to write the word SAD in the column called "Completed"

if i do this it works but writes it to the first record with that learner number

Patch(thelessonsbooked, LookUp(thelessonsbooked, learnernumber = thecgnum.Text), {learnernumber: thecgnum.Text, Completed: "SAD"});

trouble is that learnernumber could have various lessons they are doing so I need to make sure it looksup the correct one.. although powerapps says the syntax is right it does not right anything to that cell on excel.

What am I doing wrong

[v-yutliu-msft]

Hi @stefanlive ,

Could you describe more clearly about "learnernumber could have various lessons they are doing"?

I'm confused about your problem about the formula.

What's more, the reason why "if i do this it works but writes it to the first record with that learner number" is that:

LookUp function only returns the first record that meet the requirment.

If you want to update multiple records at the same time, you need to use ForAll function.

For example:

ClearCollect(collection1,Filter(thelessonsbooked, learnernumber = thecgnum.Text && lessonnumber = feedbacknumber.Text))
ForAll(collection1,Patch(thelessonsbooked, LookUp(thelessonsbooked,ID=collection1[@ID]), 
{learnernumber: thecgnum.Text, Completed: "SAD"})
       )

You could refer this:

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

Best regards,

[stefanlive · Accepted solution]

I created a work around by creating a unique field which I could reference rather than try and find two values and this works well.