cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Midhun_Madhu
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

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
Pstork1
Dual Super User
Dual Super User

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.

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

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.

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

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.

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (4,215)