cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Limit quantity alert from a Collection

Hello everyone!

 

I have a SP List of Products where there is a column for the status (if Sold, in Stock or Available) and another for the responsible seller.
In my App, I have a collection that counts the quantity of products with a certain status for each seller, for example,

 

Sellers nameSoldAvailableStock
Seller 110319
Seller 211411

 

My group has a rule on the limit quantity for each seller, such as not being able to have more than 30 products in Stock, 20 as Available and 50 in Sold. Because of that, I have two questions.

 

1. There is a Form in the App where the seller can register a new product, I would like that if he had already reached the limit quantity for that status, the system would prevent it from registering. As an example, Seller 2 already has 30 products as in Stock (according to the collection) so if he tries to register a new product with Stock status the forms will send an alert on the screen and will not let him register.

 

2. It is a situation similar to the first question but now it is about changing the status of an existing product. It has a screen with EditForm just to change the status of a product. If the seller tries to change the product to a status that it has already reached the limit, I would like an alert to appear on the screen and prevent it from changing. As an example, Seller 1 already has 20 products as Available (according to the collection) but he chose a product that was like "Stock" and changes his status to "Available" and exceeds the limit, I would like a system that prevent that.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Hi @santpaiva ,

 

Could you tell me:

What is the data structure of your list?

 

Due to lack of information, I made a list and made some assumptions.

1\ This is my list ‘LIST61’. Title is the name of the product. ‘Sellers name’ is a single line of text column. Status is a Choice column. It has three options(Sold/Available/Stock). Due to the large amount of data, the following is a screenshot of part of the data.

v-xiaochen-msft_0-1611818715212.png

 

 

I did a test for you:

 

1\ Add a test collection

ClearCollect(mycollection,{Sellersname:"Seller 1",Sold:10,Available:3,Stock:19})

 

2\ Add a gallery control ‘Gallery1’ and set its Items property to:

LIST61

 

3\ Add an edit form ‘Form1’ and set its Item property to:

Gallery1.Selected

 

//‘DataCardValue1’ is the Title’s card. ‘DataCardValue2’ is the Sellers name card. ‘DataCardValue3’ is the Status card.

 

For the first requirement:

4\ Add a submit icon control and set its onselect property to:

Switch(DataCardValue3.Selected.Value,"Sold",If(!(DataCardValue1.Text in LIST61.Title) && DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Sold,SubmitForm(Form1),Notify("Error",NotificationType.Error)),"Available",If(!(DataCardValue1.Text in LIST61.Title) && DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Available,SubmitForm(Form1),Notify("Error",NotificationType.Error)),"Stock",If(!(DataCardValue1.Text in LIST61.Title) && DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Stock,SubmitForm(Form1),Notify("Error",NotificationType.Error)))

 

For the second requirement:

5\ Add another submit icon and set its onselect property to:

Switch(DataCardValue3.Selected.Value,"Sold",If(DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Sold,SubmitForm(Form1),Notify("Error",NotificationType.Error)),"Available",If(DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Available,SubmitForm(Form1),Notify("Error",NotificationType.Error)),"Stock",If(DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Stock,SubmitForm(Form1),Notify("Error",NotificationType.Error)))

 

It should be noted that because the formula is too long, in order to facilitate understanding, I used two submission icons to demonstrate the two requirements separately.

You could try to set a variable to switch the visible properties of the two icons respectively.

You could also try to convert two formulas into one formula.

 

Best Regards,

Wearsky

If my post helps, then please consider Accept it as the solution to help others. Thanks.

View solution in original post

1 REPLY 1
Microsoft
Microsoft

Hi @santpaiva ,

 

Could you tell me:

What is the data structure of your list?

 

Due to lack of information, I made a list and made some assumptions.

1\ This is my list ‘LIST61’. Title is the name of the product. ‘Sellers name’ is a single line of text column. Status is a Choice column. It has three options(Sold/Available/Stock). Due to the large amount of data, the following is a screenshot of part of the data.

v-xiaochen-msft_0-1611818715212.png

 

 

I did a test for you:

 

1\ Add a test collection

ClearCollect(mycollection,{Sellersname:"Seller 1",Sold:10,Available:3,Stock:19})

 

2\ Add a gallery control ‘Gallery1’ and set its Items property to:

LIST61

 

3\ Add an edit form ‘Form1’ and set its Item property to:

Gallery1.Selected

 

//‘DataCardValue1’ is the Title’s card. ‘DataCardValue2’ is the Sellers name card. ‘DataCardValue3’ is the Status card.

 

For the first requirement:

4\ Add a submit icon control and set its onselect property to:

Switch(DataCardValue3.Selected.Value,"Sold",If(!(DataCardValue1.Text in LIST61.Title) && DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Sold,SubmitForm(Form1),Notify("Error",NotificationType.Error)),"Available",If(!(DataCardValue1.Text in LIST61.Title) && DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Available,SubmitForm(Form1),Notify("Error",NotificationType.Error)),"Stock",If(!(DataCardValue1.Text in LIST61.Title) && DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Stock,SubmitForm(Form1),Notify("Error",NotificationType.Error)))

 

For the second requirement:

5\ Add another submit icon and set its onselect property to:

Switch(DataCardValue3.Selected.Value,"Sold",If(DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Sold,SubmitForm(Form1),Notify("Error",NotificationType.Error)),"Available",If(DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Available,SubmitForm(Form1),Notify("Error",NotificationType.Error)),"Stock",If(DataCardValue2.Text in LIST61.'Sellers name' && CountRows(Filter(LIST61,'Sellers name'=DataCardValue2.Text,Status.Value=DataCardValue3.Selected.Value)) < LookUp(mycollection, Sellersname=DataCardValue2.Text).Stock,SubmitForm(Form1),Notify("Error",NotificationType.Error)))

 

It should be noted that because the formula is too long, in order to facilitate understanding, I used two submission icons to demonstrate the two requirements separately.

You could try to set a variable to switch the visible properties of the two icons respectively.

You could also try to convert two formulas into one formula.

 

Best Regards,

Wearsky

If my post helps, then please consider Accept it as the solution to help others. Thanks.

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (81,224)