Validating data source and collection

[Anonymous]

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!

[v-siky-msft]

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]

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?