Trying to patch a column value based on another column using IF and Lookup

(0) Share

Report

Posted on by IMathur

291

Hi Experts,

I am trying to patch a value of the column 'VewFilter' (single line text) on the basis of a Customer Column ( multiple person) using below formula: I want if the user email id of the person who is logged-in is present in Customer Column, then ViewFilter should be filled with true and otherwise false. This value will change with every user who tries to log-in the app.

If(LookUp(CompletedProjects,User().Email in Customer.Email), Patch(CompletedProjects, CProjects_Gallery1.Selected, {ViewFilter:true}),Patch(CompletedProjects,CProjects_Gallery1.Selected, {ViewFilter:false}))

above formula was used on select of submit button outside of the form

giving the below error

If there is anyother way to implement this criteria also, please help

Categories:

Using Apps Using Formulas

I have the same question (0)

All responses (6)

Answers (0)

Joel CustomerEffective 3,224 on at

Like (0)

Report
Re: Trying to patch a column value based on another column using IF and Lookup

try something like this:
Patch(CompletedProjects, CProjects_Gallery1.Selected, {ViewFilter:If(LookUp(CompletedProjects,User().Email in Customer.Email,true,false)})

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report
Re: Trying to patch a column value based on another column using IF and Lookup

Hi @IMathur :
Could you tell me:
What is your data source?Is it a SharePoint list?
What is Customer's data type?Is it Person or Group?
I assmue Customer's data type is "Person or Group".
Firstly,let me explain why you encounted this problem.
1\The first parameter of the If function should be a Boolean value,but the result of "LookUp(CompletedProjects,User().Email in Customer.Email)" is a record.
2\This formula("LookUp(CompletedProjects,User().Email in Customer.Email)") is not delegable.
Secondly,please try this code:

If(!(IsBlank(LookUp(CompletedProjects,"i:0#.f|membership|"& User().Email = Customer.Claims))), Patch(CompletedProjects, CProjects_Gallery1.Selected, {ViewFilter:true}),Patch(CompletedProjects,CProjects_Gallery1.Selected, {ViewFilter:false}))

In addition, this formula cannot be delegated, so the delegation warning cannot be eliminated.
Best Regards,
Bof

Was this reply helpful? Yes No
IMathur 291 on at

Like (0)

Report

Re: Trying to patch a column value based on another column using IF and Lookup

Hi @v-bofeng-msft ,

Thanks a lot for the reply!
I want to know, can this formula be implemented in the 'OnStart' property of App, reason being i am using the view filter column to visible the true records to the user who is logged -in and can't see items whose 'viewfilter' value is false, and this will change whenever a new user logs in. So i want this value to be updated as soon as the user start the app.

Also this is the only way i can think for user visibility management of the items in the sharepoint list, if you feel there is a better way to implement this, please tell that..

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report

Re: Trying to patch a column value based on another column using IF and Lookup

Hi @IMathur :
Yes,this formula can be implemented in the 'OnStart' property of the App.
I suggest you replace “CProjects_Gallery1.Selected”, because Gallery selects the first record by default when loading the app.
Best Regards,
Bof

Was this reply helpful? Yes No
IMathur 291 on at

Like (0)

Report

Re: Trying to patch a column value based on another column using IF and Lookup

Hi @v-bofeng-msft ,

I tried the formula in App> Onstart> If(!IsBlank(LookUp(datasourcelist,"i:0#.f|membership|"& Customer.Email= User().Email).Customer), Patch(datasourcelist, {ViewFilter:true}),Patch(datasourcelist, {ViewFilter:false}))

It is not giving any error and it is not running/working as well. please help..

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report
Re: Trying to patch a column value based on another column using IF and Lookup

Hi @IMathur :
Patch(datasourcelist, {ViewFilter:true}) has a syntax error. You should specify a baserecord to update.I think this link will help you a lot:
Syntax
Such as:

Patch(datasourcelist, First(datasourcelist),{ViewFilter:true})

or

Patch(datasourcelist, Lookup(datasourcelist,XX=XX),{ViewFilter:true})

Best Regards,
Bof

Was this reply helpful? Yes No