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

Delegation issue is restricting Look Up to work, Please help!

Hi Everyone,
I have created a Power App canvas app that will LookUp and update an SQL table. Please find my code below:

 

 

 

ForAll(
       Gallery1.AllItems, 
       If(Checkbox1.Value=true && !IsBlank(TextInput1),
       Patch(
             '[dbo].[mytable]',
             LookUp(
                    '[dbo].[mytable]',
                    IDvalue = Value(Label1.Text)), 
             {VAL : TextInput1_3.Text},{VAL2 : 1}, 
             {LAST_MODIFIED_BY:User().FullName},{DATE:Now()}
                    )
              )
       );

 

 

 

When I created this there was a delegation warning at the place 

 

 

IDvalue = Value(Label1.Text))

 

 

It was fine since the data-set was small, but now the data-set has grown and has more than 2200 columns in the table and the write back to SQL is not working.  I am thinking if I could remove the delegation warning form the above IDvalue field, I may be able to delegate the lookup to SQL and the query may work correctly.

Any help or workaround would be really appreciated.

Many thanks, 

Midhun  

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Delegation issue is restricting Look Up to work, Please help!

Is the textbox on the screen or in a data card in the gallery?  If its in the gallery then you would need to set the variable every time you do a new record, so you would be back to the same issue you had originally.  I assume its in the gallery and the value is different for every record.  If that's the case I don't think there is a way to get around the delegation issue, because you'll have to calculate the value for every record and that is what is causing the delegation issue.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

6 REPLIES 6
Dual Super User
Dual Super User

Re: Delegation issue is restricting Look Up to work, Please help!

The easiest way to fix this would be to store 

Value(Label1.Text)

as a variable and then use the variable in your function.  Embedded functions are not considered set values at run time so they can't be delegated to the server for resolution.  Using a variable should clear up your problem. 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Midhun_Madhu
Level: Powered On

Re: Delegation issue is restricting Look Up to work, Please help!

Hi @Pstork1 ,

Thanks a lot for the prompt reply. 

I was trying to do that now. I am a beginner to Power Apps and is learning as I go. 

Please see my attempt below (probably very stupid approach as the the variable is set outside ForAll). 

 

 

If(
        Checkbox1.Value = true && !IsBlank(TextInput1), 
        Set(sk,  Value(Label1.Text))
   );
ForAll(
       Gallery1.AllItems, 
       If(Checkbox1.Value = true && !IsBlank(TextInput1),
       Patch(
             '[dbo].[mytable]',
             LookUp(
                    '[dbo].[mytable]',
                    IDvalue = sk), 
             {VAL : TextInput1_3.Text},{VAL2 : 1}, 
             {LAST_MODIFIED_BY:User().FullName},{DATE:Now()}
                    )
              )
       );

 

 

 Would please help or show me how I can convert the Label1.Text to a variable (or store it to a variable), I would really appreciate that.

Many thanks,

Midhun

Dual Super User
Dual Super User

Re: Delegation issue is restricting Look Up to work, Please help!

you can't convert the TextBox to a label inside the command. Instead put the following in the on Change event of the textbox

Set(filtervalue,Value(TextInput1_3.Text))

Then use the variable wherever you now use the Value(TextInput1_3.Text) now

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Midhun_Madhu
Level: Powered On

Re: Delegation issue is restricting Look Up to work, Please help!

Hi @Pstork1 ,

Thanks a lot!
I just tried this and it works fine for one entry at a time. 
But when there are multiple entries to be wrote back only the first one is going in to SQL.

Do you know if this variable can be used for multiple selections.

Many thanks,

Midhun

Dual Super User
Dual Super User

Re: Delegation issue is restricting Look Up to work, Please help!

Is the textbox on the screen or in a data card in the gallery?  If its in the gallery then you would need to set the variable every time you do a new record, so you would be back to the same issue you had originally.  I assume its in the gallery and the value is different for every record.  If that's the case I don't think there is a way to get around the delegation issue, because you'll have to calculate the value for every record and that is what is causing the delegation issue.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Midhun_Madhu
Level: Powered On

Re: Delegation issue is restricting Look Up to work, Please help!

Hi @Pstork1 ,


Thanks a lot for all the help. 
You were right we won't be able to work with variables as the value is inside a Gallery.

I found a work around by writing back to an empty temporary table in SQL. Once the values are written back I called a Stored procedure that will then do the look up and update the table I want.

This is working fine now. 

 

I really appreciate all the help and advice. Many thanks,

 

Midhun

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (4,251)