SQL Update Patch with ForAll with keyed lookup

[akharns]

I have a Gallery with info that has been collected via a SQL view. The view contains the key fields for looking up the specific record in the source SQL table. I want to give the user the option of updating a field in that record. The gallery rows contain the current and new values of that field, with the new value defaulted to the current value. The user will scroll down and enter values in the New value field for the desired gallery rows. Then the user will press an Update Button above the Gallery. On Select for the Update Button, PowerApps should select the rows where new values were entered and for each one, retrieve the target row using the two key fields and update the value in that row.

I am thinking that, since I have the key values which uniquely identify the row to be updated, I don't need to use filter or lookup for specifying the update row (2nd Patch parameter). I should be able to directly link to the target record in the source table.

Source Data (record to be updated)
Table A
Key fields are Fld1 and Fld2
Field to be updated is UpdValue

Gallery named myGallery, with Rows containing
txtFld1 from a.Fld1
txtFld2 from a.Fld2
txtCurValue from a.UpdValue
txtNewValue - input value

On Select action for the Update Button
ForALL(Filter(myGallery.AllItems,txtCurValue.Text<>txtNewValue.Text),
Patch(
'A',
Fld1=txtFld1.Text && Fld2=txtFld2.Text,
{UpdValue:txtNewValue.Text})
)

[EddieE]

@akharns 

A quicker way is to use a Patch( ... ForAll ( .. ) ) rather than a ForAll( .. Patch(..)). See here for more explanation from @RandyHayes 

For your situation, you need to make sure that the Gallery contains all the fields/columns from you SQL view (not a cutdown version) and then you should be able to do this on the Update button

Patch(
    'A',
    ForAll(
        Filter(
            myGallery.AllItems,
            txtCurValue.Text <> txtNewValue.Text
        ),
        {
            Fld1: Fld1,
            Fld2: Fld2,
            UpdValue: txtNewValue.Text
        }
    )
)

I'm used to using SharePoint and not SQL but this should work. The reason for including Fld1 and Fld2 in your record update is so that PowerApps knows which record to work on. That link explains this a little more.

[akharns]

Thank you, @EddieE .  I'm still trying to getting it to work.  The example in https://docs.microsoft.com/en-us/power-apps/maker/canvas-apps/functions/function-patch uses ForAll like my original example, at the outer level.  From what I can tell from the documentation, Patch requires three parameters and your example uses ForAll as the 2nd and final parameter, as does the solution by @RandyHayes .  PowerApps is telling me I have a patch parameter problem.  Perhaps there's some magic that pulls the 2nd and 3rd Patch parameter from the ForAll but at this point, what I have (which I think corresponds to your examples) is syntactically incorrect.

[EddieE]

@akharns 

Could you please post the code you are getting the error on, I may be able to spot the error.

[RandyHayes · Accepted solution]

@akharns 

Probably one of the worst examples that they have in the docs!!  That ForAll is not used properly in that example and will lead to horrible performance issues.

In your current OnSelect formula, your syntax does not appear to be correct.

Your formula should be the following:

Patch(A,
    ForAll(
        Filter(myGallery.AllItems, 
            txtCurValue.Text <> txtNewValue.Text &&
            Fld1 = txtFld1.Text && 
            Fld2=txtFld2.Text
        ),
        {ID: ID,
         UpdValue:txtNewValue.Text
        }
    )
)

What is important to include is the ID (primary key) of your record if you wish to actually update a record.  If you do not provide that, then your Patch will create a new record.

As @EddieE mentioned...share any Formula that you are getting an error on.

I hope this is helpful for you.

[akharns]

Thank you, @RandyHayes and @EddieE .  I think we're getting close.

Here's what I have right now:

//Patch(
//'A',
ForAll(
Filter(
SiteGallery.AllItems,
txtUpdateCur.Text<>txtUpdateNew.Text && SD_ID=txtSD_Id.Text
),
{
SD_ID:txtSD_Id.Text,
SiteGenderRestrictionCode:txtUpdateNew.Text
}
)
//)

This has the Patch commented out, and it shows the syntax as being correct.  With all uncommented, it says there are invalid arguments.  When I hover over the ForAll, it says it's a table and expects it to be a record.

[RandyHayes]

@akharns 

What is the column type for SD_ID in your table?  I assume that is the primary key?

When the patch complains about expecting a record and not a table, that means that there is an issue with the schema of the table returned from the ForAll.  So, check that the types are correct.

[EddieE]

@akharns 
I’m fairly sure you don’t need this inside your filter

SD_ID=txtSD_Id.Text

Then inside your record / {…} change this

SD_ID:txtSD_Id.Text

to this

SD_ID: SD_ID

As @RandyHayes  says, there’s a data type mismatch somewhere but I this these changes should sort it out

[akharns]

SD_ID is numeric.  I changed it to SD_ID=Value(txtSD_Id.Text) and it resolved the syntax problem.  It's still not updating, but we made it past that hurdle.

[akharns]

Actually, it did update.  Yahoo!   It just didn't refresh my gallery.  I suppose that will need to be specified?