cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

How to Patch multi-records

Hi,

 

I have build a PowerApps solution to update the status of a column in a Sql Server database.

On a Power BI report, my users filter items and when they have selected the data they want, they use the PowerApps canvas to update what they have selected.

To communicate between Power BI and PowerApps, I have used the great functionnality PowerBIItegration.Data.

 

Here is my problem.

In order to update only the records selected, I have used the Patch function because I can include a test. But maybe there is another function to update my table.

And after many research, I have found how to write the code to update one field of my table and I did that

Patch('[dbo].[ImportTable]',
First(Filter(
'[dbo].[ImportTable]',
'DOC ZIPPE' = OT.Text)),
{StatusColumn: true })

 

Sure, it works but only for the first record (logic, the Filter function is included in a First function)

I have tried to adapt the code with ForAll but without success.

 

How can I write the code to update many records?

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions

@FredLEGUEN 

Ah, the picture sheds the light!

What you really want to iterate over in your ForAll statement is the Gallery items.

 

Consider using this formula instead:

ForAll(GalleryOT.AllItems,
   Patch('[dbo].[ImportData]', 
      LookUp(
      '[dbo].[ImportData]', 
      'DOC ZIPPE' = OT.Text
   ),
   {StatusColumn: true}
   )
)

That should give you what you want assuming that the 'DOC ZIPPE' is a text column.  

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

View solution in original post

6 REPLIES 6
Regular Visitor

Here is my new code with LOOKUP this time but I still update one record, not all selected

😒😤😤😤


ForAll(PowerBIIntegration.Data,
   Patch('[dbo].[ImportData]',
      LookUp(
      '[dbo].[ImportData]',
      'DOC ZIPPE' = OT.Text
   ),
   {StatusColumn: true}
   )
)

@FredLEGUEN 

What is OT.Text  in your Formula?

You should be referencing a value that will be in your PowerBIIntegration.Data list that will provide a unique record to be returned in your Lookup.

 

For example, if the PowerBIIntegration.Data has a column called DocID, then your formula would be similar to this:

ForAll(PowerBIIntegration.Data,
   Patch('[dbo].[ImportData]', 
      LookUp(
      '[dbo].[ImportData]', 
      'DOC ZIPPE' = DocID
   ),
   {StatusColumn: true}
   )
)

See if that is what you are running into.  If you are basing OT.Text on a label on your screen, then you will only be getting one record patched.

 

I hope this is helpful for you.

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

Thanks @RandyHayes for your answer,

You describe perfectly the problem but I don't get your solution.

 

OT is the name of my Label where I display the contain of the column 'DOC ZIPPE' of my database.2019-06-12_16-18-24.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And the gallery is linked to the database

 

2019-06-12_16-19-26.png

 

 

 

 

 

 

 

 

 

 

 

 

 

The contain of my column 'DOC ZIPPE' is already unique (it's my column Key in fact).

This is why I have written OT.Text because each row in the gallery is unique.

So, in my formala, I have created a test between the column of my table 'DOC ZIPPE' and the value of the gallery list OT.TEXT

 

I hope these informations could help to correct my formula. Because DocID, I don't understand where it can come from ?

@FredLEGUEN 

Ah, the picture sheds the light!

What you really want to iterate over in your ForAll statement is the Gallery items.

 

Consider using this formula instead:

ForAll(GalleryOT.AllItems,
   Patch('[dbo].[ImportData]', 
      LookUp(
      '[dbo].[ImportData]', 
      'DOC ZIPPE' = OT.Text
   ),
   {StatusColumn: true}
   )
)

That should give you what you want assuming that the 'DOC ZIPPE' is a text column.  

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

View solution in original post

Thanks you so much @RandyHayes 

That's exactly how to solve the problem.

 

I knew I was close to the solution but the documentation is really poor about PowerApps (the application is too new).

Just what I was looking for after struggling for a couple of hours. Thank you for the solution @RandyHayes  and for @FredLEGUEN  for raising the question.

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Solution Authors
Top Kudoed Authors
Users online (15,199)