cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Validating data source and collection

Hey everyone!

I'm trying to solve following issue:
I have a data source consisting of a table with rows and values (right now a excel file in onedrive). This table holds two columns: "Item Numbers" and "Available Quantity".

In my powerapp the user can select a "Item Number" from the data source and insert a number in a numberinput, which the user wants to move. It is required by the user to move a quantity of the item number multiple times in the same screen. Therefore i have created a collection which sums up the moved quantity for the specific "Item Number".

However, i would like a data validation towards my data source, that has a value of "Available Quantity" per "Item Number". It should not be allowed for the user to select/move a higher value than the "Available Quantity" in the summed collection.

Currently i can restrict the number input to not exceed the selected Itemnumber's available quantity by the following syntax:

 

If(Value(NumberInput1.Text) > ItemNumberCombo.Selected.'Available Quantity', DisplayMode.Disabled,Edit)

 

But this doesn't prevent the user from adding an additional quantity that exceeds the available quantity , when going back to the screen from another screen as its stored in the collection. I don't wish to edit in the data source like subtracting the moved quantity in available quantity.

I therefore look forward to hear your inputs on how to validate a summed quantity per Itemnumber in a collection towards Data source table that has the available quantity per itemnumber.
Is there a solution to compare two identical item numbers and their quantity - one from a data source table and one from a powerapps collection?

Thanks a lot in advance!

2 REPLIES 2
v-siky-msft
Community Support
Community Support

Hi @Anonymous ,

 

I think, you need a table to store the moved quantity or available quantity. Collection is temporary, it is not suggested.

If you don't want to modify the data source config, I suggest you create a new table to store the moved quantity or available quantity.

The new table also contains two column: "Item Numbers_1" and "Available Quantity_1".

Once move the quantity, also modify the available quantity of new table, set the following to Move button.

 

Patch('New Table', LookUp( 'New Table', 'Item Numbers_1' = ItemNumberCombo.Selected.'Item Numbers'), {'Available Quantity_1': ItemNumberCombo.Selected.'Available Quantity' - Value(NumberInput1.Text) } )

 

Also set the following formula to DisplayMode to restrict the number input 

If(Value(NumberInput1.Text) > LookUp( 'New Table', 'Item Numbers_1' = ItemNumberCombo.Selected.'Item Numbers'). 'Available Quantity_1', DisplayMode.Disabled, Edit)

Best regards,

Sik

Anonymous
Not applicable

Hi @v-siky-msft 

Thank you very much for your answer!
Your suggestion does work from preventing moving a higher quantity than what's available over multiple times. However, it can't record multiple moves of quantity, as it's using the original quantity from "Available Quantity" and subtracting the value from "NumberInput1.Text" each move and therefore doesn't take the updated value in the New Table into the equation.

Do you know how to fix this?

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,157)