cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kriggo15
Post Patron
Post Patron

ForAll Gallery Patch to SQL

I have a gallery (Gallery1) that has a list of tables.
I have another gallery (Gallery3_1) that has all of the columns related to the chosen table: Filter('[tbl].Columns]', Gallery1.Selected.DatasetID = DatasetID).

In the column gallery (Gallery3_1) I have several text inputs for users to enter information. I want to be able to patch all of that information back to the Columns table but my code isn't running properly. It isn't writing anything back when I enter data into the input fields. Where am I going wrong?

ForAll(
    Gallery3_1.AllItems,
    Patch(
        '[tbl].[Columns]',
        LookUp(
            '[tbl].[Columns]',
            ColumnID = ThisRecord.ColumnID
        ),
        {Nullable: TextInput4_15.Text,
         Length: TextInput4_15.Text}
    )
)

 ForAll Gallery.png

1 ACCEPTED SOLUTION

Accepted Solutions

@kriggo15 

So, I believe your issue comes with the ColumnID as the reason you are referring to ThisRecord (which I contend, should work).

You can go to the old tried and true method and rename columns:

ForAll(
    RenameColumns(Gallery3_1.AllItems, "ColumnID", "iColumnID"),
    UpdateIf(
        '[tbl].[Columns]',
        ColumnID = iColumnID,
        {Nullable: TextInput4_15.Text,
         Length: TextInput4_15.Text}
    )
)

You can also try to use the new As feature, but the above has always been the workaround in the past.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

5 REPLIES 5
RandyHayes
Super User III
Super User III

@kriggo15 

Consider altering your formula a little to this:

ForAll(
    Gallery3_1.AllItems,
    UpdateIf(
        '[tbl].[Columns]',
        ColumnID = ThisRecord.ColumnID,
        {Nullable: TextInput4_15.Text,
         Length: TextInput4_15.Text}
    )
)

As UpdateIf is a little more reliable in these cases.

 

Questions to ask:

1) Does my Columns table have a Primary Key??  PowerApps will not update tables that do not.

2) Be careful with ThisRecord, it has a tendency to reference some things that it shouldn't (or shouldn't appear to be).

3) The referenced TextInput controls are all in Gallery3_1

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Thanks @RandyHayes,. Your code works and I unfortunately just found the issue you linked out to where it began to overwrite each record in my SQL table. Answers to your questions below:

 

  1. Yes, there is an ID field as the primary key.
  2. Yeah, encountered the issue you referenced. Originally I thought it should be ID = ThisItem.ID, but "ThisItem" is not a valid name. What should I use instead of ThisRecord?
  3. Yes, they are all part of Gallery3_1

@kriggo15 

So, I believe your issue comes with the ColumnID as the reason you are referring to ThisRecord (which I contend, should work).

You can go to the old tried and true method and rename columns:

ForAll(
    RenameColumns(Gallery3_1.AllItems, "ColumnID", "iColumnID"),
    UpdateIf(
        '[tbl].[Columns]',
        ColumnID = iColumnID,
        {Nullable: TextInput4_15.Text,
         Length: TextInput4_15.Text}
    )
)

You can also try to use the new As feature, but the above has always been the workaround in the past.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

@RandyHayes, I'm doing some additional testing as it appeared to be working yesterday but now I'm seeing irregularities. Is UpdateIf buggy?

When I use my initial test table, the columns update as expected. After button press, it is disabled and it takes about 5-10 seconds to update 9 columns. If I switch to a different table and make updates, then submit, the button is only disabled for 1-2 seconds and nothing gets updated. It's like it doesn't see any changes and just cancels. Here's my code (Note: I want to submit null back to the table if the input is blank, hence the if statement):

ForAll(
    RenameColumns(Gallery3_1.AllItems, "ColumnID", "iColumnID"),
    UpdateIf(
        '[tbl].[Columns]',
        ColumnID = iColumnID,
        {
            Column1: If(IsBlank(TextInput4_9.Text), Blank(), TextInput4_9.Text),
            Column2: If(IsBlank(TextInput4_10.Text), Blank(), TextInput4_10.Text),
            Column3: If(IsBlank(TextInput4_11.Text), Blank(), TextInput4_11.Text),
            Column4: If(IsBlank(TextInput4_12.Text), Blank(), TextInput4_12.Text),
            Column5: If(IsBlank(TextInput4_13.Text), Blank(), TextInput4_13.Text),
            Column6: If(IsBlank(TextInput4_14.Text), Blank(), TextInput4_14.Text),
            Column7: If(IsBlank(TextInput4_15.Text), Blank(), TextInput4_15.Text),
            Column8: If(IsBlank(TextInput4_16.Text), Blank(), TextInput4_16.Text),
            Column9: If(IsBlank(TextInput4_17.Text), Blank(), TextInput4_17.Text)
        }
    )
)

 

@kriggo15 

Can you explain more about "If I switch to a different table"?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (60,774)