cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted

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
Highlighted
Dual Super User III
Dual Super User III

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

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

Highlighted

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

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

Highlighted

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

Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (8,757)