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

Patch only when Col A is blank or Col A = name and Choice B = X

I have an "Orders" List in Sharepoint with 3 columns

TextInput1 - Requestor Email

Choice list - Status (Options: Pending, In progress, Done)  -
Calc Col - StatusCalc (text)

 

I need to patch the list only if the Requestor has never submitted an order or if a previously submitted order for them is already  "Done"

 

So far I have:

Set(ExistingRequestor, LookUp('Orders', EmailAddress= TextInput1.Text));

If
(IsBlank(ExistingRequestor),

         Patch(blah).....,
         UpdateContext({ShowPopup:true})
)

 

This runs fine if the requestor has never submitted a request to the list, and shows a popup if they have just fine.

What I can't seem to get right is the syntax in the If statement to allow the requestor to submit a second requst if their previous request is Done (not pending or in progress).

 

Help Please....

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @lvdp,

I have made a test on my side, please take a try with the following workaround:4.JPG

Set the OnSelect property of the "Submit" button to following formula:

If(
IsBlank(LookUp('20180925_case10',EmailAddress=DataCardValue6.Text))||!IsBlank(LookUp('20180925_case10',EmailAddress=DataCardValue6.Text&&Status.Value="Done")),
Patch('20180925_case10',Defaults('20180925_case10'),EditForm1.Updates),
Notify("You have submitted an order which has not be Done",NotificationType.Error)
)

Note: The '20180925_case10' represents the SP list data source within my app.

On your side, you should type the following formula:

If(
IsBlank(LookUp('Orders',EmailAddress=TextInput1.Text))||!IsBlank(LookUp('Orders',EmailAddress=TextInput1.Text.Text&&Status.Value="Done")),
Patch(blah)....,
UpdateContext({ShowPopup:true})
)

 

The Choice type column could not delegated within SP list data source, in order to avoid the Delegation issue, please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following formula:

ClearCollect(OrderCollection,'Orders')

Set the OnSelect property of the "Submit" button to following:

If(
  IsBlank(LookUp(OrderCollection,EmailAddress=TextInput1.Text))||!IsBlank(LookUp(OrderCollection,EmailAddress=TextInput1.Text.Text&&Status.Value="Done")),
  Patch(blah)....,
  UpdateContext({ShowPopup:true})
);
Refresh('Orders');
ClearCollect(OrderCollection,'Orders')

or

If(
  IsEmpty(Filter(Filter(OrderCollection,EmailAddress=TextInput1.Text),Status.Value="Pending" || Status.Value="In progress")),
  Patch(blah)....,
  UpdateContext({ShowPopup:true})
);
Refresh('Orders');
ClearCollect(OrderCollection,'Orders')

 

Best regards,

Kris 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
tchin-nin
Memorable Member
Memorable Member

Hi,

I'm not sure you should use a variable because you'll have to recalculate it's value everytime the TextInput is changed.

Is the Requestor the current app user (in this case you can user User().Email) ? Or can user requests thing for other people ? 

 

Anyway, you can do something like this : 

If(IsEmpty(Filter('Orders',EmailAddress=TextInput1.Text && (Status.value="Pending" || Status.value="In progress"))),

         Patch(blah).....,
         UpdateContext({ShowPopup:true}))

 

Théo

lvdp
Frequent Visitor

Thanks Theo,

 

The current user can request an order on behalf of someone else.

 

Your filter expression gives me a delegation warning - any other ideas?

tchin-nin
Memorable Member
Memorable Member

Hi,

It looks like Choices column types are not delegable.
An option would be to replace the status by a Single line of text, and prevent user to fill/update this column but rather doing it programmatically (With Flow for example).
Then the filter on a single line of text can be delegated to SharePoint.

Théo

@tchin-nin,

 

Another reason to avoid Choice and Lookup columns in Sharepoint!  Aside from being difficult to add and edit items, they aren't delegatable. 

@lvdp

To prevent delegation you could also filter first by the requestor. It will probably return an array that contains less than 500 items, so you avoid delegation issues on the Choices filtering .

 

@Drrickryp the ability to delegate choices and lookup is coming !!

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Delegation-Include-LOOKUP-column-types-in-existi...

Big news 🙂

 

Théo

tchin-nin
Memorable Member
Memorable Member

Hi @lvdp

 

Can you try this ? 

 

If(IsEmpty(Filter(Filter('Orders',EmailAddress=TextInput1.Text),Status.value="Pending" || Status.value="In progress")),

         Patch(blah).....,
         UpdateContext({ShowPopup:true}))

 

You first filter will retrieve all orders made by the email address filled in the TextInput, so I guess less than 500 items.

Then your second filter will perform the Status filter on the Choices column. You'll still have your delegation warning, but less than 500 items retrieves by the first filter so it should work properly.

 

Théo

 

v-xida-msft
Community Support
Community Support

Hi @lvdp,

I have made a test on my side, please take a try with the following workaround:4.JPG

Set the OnSelect property of the "Submit" button to following formula:

If(
IsBlank(LookUp('20180925_case10',EmailAddress=DataCardValue6.Text))||!IsBlank(LookUp('20180925_case10',EmailAddress=DataCardValue6.Text&&Status.Value="Done")),
Patch('20180925_case10',Defaults('20180925_case10'),EditForm1.Updates),
Notify("You have submitted an order which has not be Done",NotificationType.Error)
)

Note: The '20180925_case10' represents the SP list data source within my app.

On your side, you should type the following formula:

If(
IsBlank(LookUp('Orders',EmailAddress=TextInput1.Text))||!IsBlank(LookUp('Orders',EmailAddress=TextInput1.Text.Text&&Status.Value="Done")),
Patch(blah)....,
UpdateContext({ShowPopup:true})
)

 

The Choice type column could not delegated within SP list data source, in order to avoid the Delegation issue, please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following formula:

ClearCollect(OrderCollection,'Orders')

Set the OnSelect property of the "Submit" button to following:

If(
  IsBlank(LookUp(OrderCollection,EmailAddress=TextInput1.Text))||!IsBlank(LookUp(OrderCollection,EmailAddress=TextInput1.Text.Text&&Status.Value="Done")),
  Patch(blah)....,
  UpdateContext({ShowPopup:true})
);
Refresh('Orders');
ClearCollect(OrderCollection,'Orders')

or

If(
  IsEmpty(Filter(Filter(OrderCollection,EmailAddress=TextInput1.Text),Status.Value="Pending" || Status.Value="In progress")),
  Patch(blah)....,
  UpdateContext({ShowPopup:true})
);
Refresh('Orders');
ClearCollect(OrderCollection,'Orders')

 

Best regards,

Kris 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @v-xida-msft

 

Collecting the 'Orders' datasource into a local collection won't change the delegation issue. Yes it will remove the delegation warning, but if the 'Orders' list has more than 500 items, the collection will contains only the 500 first items, so he will still have data issues due to delegation.

 

Théo

 

Anonymous
Not applicable

hi @lvdp - can you please advise if the above responses have resolved your issue? 

 

Thank you. 

 

@Anonymous

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,216)