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

[lvdp]

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

[tchin-nin]

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]

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]

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

[Drrickryp]

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

[tchin-nin]

@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]

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 · Accepted solution]

Hi @lvdp,

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

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 

[tchin-nin]

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]

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

Thank you. 

@Anonymous