cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FredLEGUEN
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
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
Regular Visitor

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
Regular Visitor

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
Regular Visitor

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
secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (7,853)