cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
FredLEGUEN
Level: Powered On

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
Super User
Super User

Re: How to Patch multi-records

@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.

View solution in original post

5 REPLIES 5
FredLEGUEN
Level: Powered On

Re: How to Patch multi-records

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}
   )
)

Super User
Super User

Re: How to Patch multi-records

@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.
FredLEGUEN
Level: Powered On

Re: How to Patch multi-records

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 ?

Super User
Super User

Re: How to Patch multi-records

@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.

View solution in original post

FredLEGUEN
Level: Powered On

Re: How to Patch multi-records

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).

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 146 members 4,737 guests
Please welcome our newest community members: