cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate III
Advocate III

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
Highlighted
Community Support
Community Support

Hi @ajazz ,

 

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

Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,997)