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.

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

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,506)