cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dstortz
Frequent Visitor

Patching Data Source Based on Listbox Selection

I'm new to PowerApps and trying to create an app for asset tracking that allows users to check out assets and check them back in once finished.  I started with the sample asset tracking app and starting modifying it.  I have the check out screen set up with an drop down to select the quantity of the asset being checked out.  When assets are checked in, I want the available quantity to by updated  and increased by the number that were previously checked out and having trouble determining how to do this. 

 

My data is Excel based, the Products workbook has the available quantity and product details while the Assets has the product checked out, quantity checked out, and by who.  My check in screen has a drop down with the names of the people with items checked out, then a list box with the items they have checked out.  I want to be able to select from the list box, the item(s) to check in, then update the available quantity for check out.  I'm not sure how to go about patching the data based on the selection from the list box, then add the quantity from Assets to the available in Products.  Any help would be much appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @dstortz ,

 

For you first question, why not leave quantity columns in both tables? Then it would be easy to show the quantity of checkout or checkin in the two Forms.

 

With the quantity field in Assets table, the best way to update Products table would be OnSuccess of the CheckIn Form, formula could be like:

Patch(Products,LookUp(Products,ProductId=ListBox1.Selected.ProductId),{Available:LookUp(Products,ProductId=ListBox.Selected.ProductId).Available+CheckInForm.LastSubmit.Quantity})

So only if the form is submitted successfully, it would patch check in quantity to Products table.

 

For the second question, the collection "ProductsCol" is the sample data table for the template app, you could replace it with your own Products table as the data source.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

 

 

View solution in original post

3 REPLIES 3
v-jefferni
Community Support
Community Support

Hi @dstortz ,

 

Could you please share more details about your app? Would you like to refresh the Products table's available field value?

 

If so, since you have the check out quantity stored in Assets table, when user selected the item in the List Box, you could update the Products table available field value. So below are the steps:

 

1.  Set the dropdown Items to:

Distinct(Assets,ReservedByName)

2. Set the ListBox Items to below and set its Value property on right hand pane to what you would like to display, toggle off the Select Multiple property as well:

Filter(Assets,ReservedByName = NameDropdown.Selected.Result)

 3. On a button, you could apply below formula to update the available quantity in Products table:

Patch(Products,LookUp(Products,ProductId=ListBox1.Selected.ProductId),{Available:LookUp(Products,ProductId=ListBox.Selected.ProductId).Available+ListBox.Selected.'QuantityCheckOut'})

 

Hope this helps, please have a try.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Thanks for the help!  I think #3 above will be close to what I need to do.  Some more background on my app: 

Reserve Screen.PNG

 

 

 

 

 

 

 

 

 

 

 

The Reserve Screen has Name, email, and quantity.  These values are columns in the Assets source, I also have a status column that sets to 'Checked Out' when an item is checked out.  The fact that I have the quantity in Assets has been the tricky part for me.  If I move quantity to Products, #3 you posted should work, is there a way to use quantity in the Assets source to add to available in Products?

CheckIn Screen.PNG

 

 

 

 

 

 

 

 

 

 

 

My CheckIn screen uses the boxes as you describe in #1 & #2. and works ok so far.  Is it best to do the Patch on the the CheckIn button or OnSuccess for the Form on the CheckIn screen?  The sample app I started with does a patch for check out on the OnSucess of the form on the Reserve screen with the following syntax:

 

Patch(ProductsCol,First(Filter(ProductsCol,ProductId=ItemSelected.ProductId)),
{Available:If(ItemSelected.Available>0,ItemSelected.Available-Dropdown5.Selected.Value,ItemSelected.Available)});
Navigate(ConfirmationScreen,ScreenTransition.None)

 

I realize this also references collections rather than the Products data source, this is how the sample app was setup, I don't reference this collection anywhere else in the app, so I'll probably eliminate the collections ultimately or I'll need to add a step to update Products with the collection data.

Hi @dstortz ,

 

For you first question, why not leave quantity columns in both tables? Then it would be easy to show the quantity of checkout or checkin in the two Forms.

 

With the quantity field in Assets table, the best way to update Products table would be OnSuccess of the CheckIn Form, formula could be like:

Patch(Products,LookUp(Products,ProductId=ListBox1.Selected.ProductId),{Available:LookUp(Products,ProductId=ListBox.Selected.ProductId).Available+CheckInForm.LastSubmit.Quantity})

So only if the form is submitted successfully, it would patch check in quantity to Products table.

 

For the second question, the collection "ProductsCol" is the sample data table for the template app, you could replace it with your own Products table as the data source.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,929)