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

SQL Table update based on ID column

Hello,

I'm creating a collection (FinalTemp) in PowerApps, this is created based on a database I have with a key column (Id_lps), In the collection I have fields which can be used to modify the values and which will be then fed back to the SQL database ([dbo].[Ranes_Marc_Lps]).

This process is supposed to happen for every item in the gallery and the logic is it should check for the Id_lps in the database to be the same as in the gallery item.

My current code is:
(HiddenID is a label which shows the Id_lps of the gallery item)

 

ForAll(
           FinalTemp,
                   UpdateIf('[dbo].[Ranes_Marc_Lps]', Value(HiddenID.Text) = Id_lps,
                                   {
                                    LPRanQty: Value(ConfQtyTXT.Text),
                                    Processed: If(ConfCheck.Value = true,1,0),
                                    ModifiedBy: User().FullName,
                                    ModifiedOn: Now()
                                   }
         )
)

 

 

It works perfectly when there is only 1 item, however if there are more items it only updates the first item (as if it were ignoring the "ForAll")

I also tried changing the 'item' part of the UpdateIf to Id_lps = Id_lps hoping it'd match the Id from the DB to the id in the gallery but it writes the information of the first item in the gallery into every line

Does anyone have any idea of what I'm doing wrong?
Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
LABORSAL
Level: Powered On

Re: SQL Table update based on ID column

I just found the solution for both the issues!

 

If instead of trying to use the same column names from the original data I change the column name of the ID and reference it it both works and removes the delegation problem.

 

ClearCollect(UpdateCollect, RenameColumns(FinalTemp,"Id_lps","IDCol")); //Generate a new collection which changes the name of the Id_lps column
ForAll(UpdateCollect,
         Patch('[dbo].[Ranes_Marc_Lps]', LookUp('[dbo].[Ranes_Marc_Lps]', Id_lps = IDCol), //Make a reference to the new column instead of the collection
               {
                LPRanQty: LPRanQty,
                Processed: Processed,
                ModifiedBy: User().FullName,
                ModifiedOn: Now()
               }
              )
      )
6 REPLIES 6
KroonOfficeSol
Level 10

Re: SQL Table update based on ID column

@LABORSAL

 

Sure this only works on just one item, this is because you reference a textbox on the screen. And I don't see you chance this textbox value in the forall() formula.

 

If I understand you create a collection with multiple records and want to post this records to the database. Then you should reference the fields in your collection and not the controls on your screen. Do something like this:

 

ForAll(
           FinalTemp,
                   Patch('[dbo].[Ranes_Marc_Lps]', If(IsBlank(Id), Defaults('[dbo].[Ranes_Marc_Lps]'), LookUp('[dbo].[Ranes_Marc_Lps]',  id_lps = Id)),
                                   {
                                    LPRanQty: first(FinalTemp).ConfQtyTXT,
                                    Processed: If(first(FinalTemp).ConfCheck = true,1,0),
                                    ModifiedBy: User().FullName,
                                    ModifiedOn: Now()
                                   }
         )
)

Hope this gets you on the right track.

 

Paul

 

 

LABORSAL
Level: Powered On

Re: SQL Table update based on ID column

Thanks @KroonOfficeSol!

 

I found a way to reference the table correctly (one of the biggest issues the formula had) and after following your suggestion to use the collection values instead of the control values (and changing how everything works so collection always gets updated) it seems to be working, a final problem I have is 

 

ForAll(FinalTemp,
         Patch('[dbo].[Ranes_Marc_Lps]', LookUp('[dbo].[Ranes_Marc_Lps]', Id_lps = FinalTemp[@Id_lps]), //<-- Changed the FinalTemp[@Id_lps] which helped cicle trhough both collection and database 
               {
                LPRanQty: LPRanQty, // <-- Updated collection value instead of element and referenced it
                Processed: Processed, //<-- Same as above
                ModifiedBy: User().FullName,
                ModifiedOn: Now()
               }
              )
      )

I'm now facing a new issue where it's being delegated, and as such it *might* not find the values if they are not <= 2k

 

Is there any way to look for the record in the db so it does not do this? I guess a possibility would be to write everything into a new table and make the record always Defaults("New Database") but it'd be a lot better if it worked with the existing database Smiley Very Happy

KroonOfficeSol
Level 10

Re: SQL Table update based on ID column

There shouldn't be delegation problem here. What you should try is
1 create a test gallery put in only the lookup part to check if the delegation warning is still there.
2 create a button and put here the patch() part off your formula and replace the values which in your formula comes from your collection. Try if the patch works.

If both are oke you're good to go, unless you want to store more then 2000 records in your collection,:-)

Also check if the values you try to patch are right, check for text instead off values.

Hope this helps.

Paul

Community Support Team
Community Support Team

Re: SQL Table update based on ID column

Hi @LABORSAL,

Based on the formula that you provided, I don't think you have faced a Delegation issue with your formula.

More details about the delegable functions and delegable predicates of SQL Server data source, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list#list-of-data-sources-an...

If you don't face a Delegation issue with your formula, it would delegate the processing of data to the your SQL data source, so you would not have the Non-delegable limits.

More details about the Delegation in PowerApps, please check the following article:

Delegation

In addition, you could also take a try with the following formula to achieve your needs:

ForAll(
        Gallery1.AllItems,   /* <-- Gallery1 represents the Gallery control within your app */
        Patch(
'[dbo].[Ranes_Marc_Lps]',
LookUp('[dbo].[Ranes_Marc_Lps]', Id_lps=Value(HiddenID.Text)), { LPRanQty: Value(ConfQtyTXT.Text), Processed: If(ConfCheck.Value = true,1,0), ModifiedBy: User().FullName, ModifiedOn: Now() } ) )

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LABORSAL
Level: Powered On

Re: SQL Table update based on ID column

Hello @v-xida-msft@KroonOfficeSol

 

Thanks for your suggestions so far, I already managed to make the table update, but as you can see in the pictures below I get the blue squigly line and msg of non-delegation.

 

I imagine powerapps decides to import the data and validate it against the gallery and that's why it's not delegating it.

 

First Try (The working code)First Try.jpg

 

Second Try (Suggestion by @v-xida-msft)

Second Try.jpg

 

To validate my theory I changed what the Id_lps can be in the formula and it no longer shows the blue squigly line nor the non-delegation warning.

 

 

Third (and non-working) tryThird Try.jpg

 

 

I believe a possible solution would be to set a variable to the ID text it should check for each of the records and validate against the variable, however neither Set() nor UpdateContext() are usable insde the ForAll =(

LABORSAL
Level: Powered On

Re: SQL Table update based on ID column

I just found the solution for both the issues!

 

If instead of trying to use the same column names from the original data I change the column name of the ID and reference it it both works and removes the delegation problem.

 

ClearCollect(UpdateCollect, RenameColumns(FinalTemp,"Id_lps","IDCol")); //Generate a new collection which changes the name of the Id_lps column
ForAll(UpdateCollect,
         Patch('[dbo].[Ranes_Marc_Lps]', LookUp('[dbo].[Ranes_Marc_Lps]', Id_lps = IDCol), //Make a reference to the new column instead of the collection
               {
                LPRanQty: LPRanQty,
                Processed: Processed,
                ModifiedBy: User().FullName,
                ModifiedOn: Now()
               }
              )
      )