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

[Midhun_Madhu]

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  

[Pstork1]

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. 

[Midhun_Madhu]

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

[Pstork1]

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

[Midhun_Madhu]

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

[Pstork1 · Accepted solution]

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.

[Midhun_Madhu]

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