Updating multiple records at once is successful using PATCH with forAll EXCEPT a few records .. maybe using substitute will help?

[VeraRizvi]

Hello,

I am having an issue using the patch and forAll function together in a Gallery.

The formula I am using is correct and updates all the records except 2 records.

ForAll(Filter(EngResrcGallery.AllItems,Rate >= 0),
            Patch('[dbo].[EngineeringResources]',
                     LookUp(EngResrcList, EngResource = EngResLabel.Text),
                      {Rate:Value(rateInput.Text)}
             )
);

Here is some background info.

'[dbo].[EngineeringResources]' is table in my SQL Database

OnStart of the app I create a collection "EngResrcList" which is basically '[dbo].[EngineeringResources]' table...
I am using that collection for easy lookups to avoid any delegation problem.

Gallery is using "EngResrcList" as the datasource.

So, when a user clicks on button to update the gallery with new rates,  the forAll and patch formula runs and updates all records except 2.

The two records that don't get updated, my guess is that because there is a comma in the primaryKey.. but I dont get any errors.

'[dbo].[EngineeringResources]' table has two columns and "EngRecouse" is the primary key

EngResource                               Rate
Clerical                                        12.34
Project Leader, A                              0
Admin                                        12.34
Project Support, A                          0

Solution:
     I have tried using Substitute function and that didnt work either and gave me an error
            ForAll(Filter(EngResrcGallery.AllItems,Rate >= 0),
                      Patch('[dbo].[EngineeringResources]',
                                LookUp(EngResrcList, Substitute(EngResource, ",", "") = EngResLabel.Text),
                                 {Rate:Value(rateInput.Text)}
                      )
              );

the error I get is inner exception (Server respons: 1 key was expected, 2 were provided) for these two records while all other records get updated just fine. ..


 

[yashag2255 · Accepted solution]

Hi @VeraRizvi 

I tried to replicate the same table on a SQL server and did a debug on the Inner exception that you are getting and this turns out to be a limitation at this point of time as the comma in the value is treated as a separator between the two values and even though it is passed as a single string but SQL is treating that as two different values provided against a single key. After performing various use cases and function, I was getting inner exception everytime. 

I am afraid this is beyond the capabilities of PowerApps at the moment. If you need this to be added in the capabilities, please raise an idea on the below link or upvote if it exists already.

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

Regarding the invalid argument error this is occurring because EngResource is a text column and [@'[dbo].[EngineeringResources]'].EngResource returns a single column table of all the values in EngineeringResources table. Since they both are different data types, hence this is not compatible.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[VeraRizvi]

Hi @yashag2255 

Thank you for the clarification!

I solved my problem by adding an extra column "ID" which is just integers.. so now i can update all records usng the same formula i was using before but instead of looking up the "EngREsource text" now i am looking up ID column .. I have also set ID column to be the primary key .. so now there is no issue of splitting a single value into two .. no inner exception. That was my work around with minimum changes needed to acheive my desired result.

Also I would like to know, is there a way to debug in PowerApps .. I want to see what values are being passed to variables .. Can I put a break in middle of a process/action and see where things go wrong and use that to fix any issues?