How to Patch multi-records

(0) Share

Report

Posted on by FredLEGUEN

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

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

FredLEGUEN 29 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
FredLEGUEN 29 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

And the gallery is linked to the database

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 ?

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
FredLEGUEN 29 on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Ami K 15,689 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No