easy even if your not a developer my butt

[Anonymous]

Hey all

Im trying to automate some basic inventory controls for the beverage department of a hotel. We use outlook for our email, and as one of the more tech savy individuals (among a group of luddites) Ive been digging deeper into the OneDrive/365 dynamics/whatever-other-name-microsoft-thinks-up Suite and it looks like the powerapps thing could do what I want if I can just get it to bend to my will .

What I want is very basic, but I havent been able to see how to make it work by following the tutorials and videos available.

I have an excel spreedsheet, formated as a table that lists Product name, category (vodka, bourbon, etc.), vendor, unit price, and total on hand for 4 different revenue centers plus the store room.

How do I get the app to alter data in the excel table.

For instance I want to set a REV Center Drop down to select where the booze is going, then a Category Drop down  where you choose vodka, then choose ketel, then input how many you take. I need that inputed value to subtract from the storeroom total on hand and add to the related rev center on hand total.

It says its easy to do this for non developers, but I think im further from developer that the technical writers envision people who take these things on usually are because I just dont see how to do it.

[JamesonRH]

First, make sure you don't have a column in your table for "REV Center". Each REV Center would need its own column in the table. Then create two dropdown controls, one for category, and one for product name. Then, create a textinput control for your storeroom. In this input, you will type in how many you are removing from the store room.  Then, create a textinput control for EACH of your REV centers. This will allow you to type in how many you want to ADD to each rev center.

You would need to create an editform control and select the columns "Category", "Product Name," "Store Room," and "REV Center1," REV CEnter2," etc. Then, set the forms items property to 

First( Filter (datasource, Category = nameofyourcategorydropdown.selected.value))

This will cause the form to populate the data from your spreadsheet based on the category you select from your drop down. Then click on the textbox in the form for Product Name and set its default to 

nameofyourdropdownforproductname.selected.value

This will cause the "Product Name" section in the form to populate with whatever you selected from that drop down. Then click on the textbox in the form for storeroom, and set its default to 

Filter(datasource, Category = nameofyourcategorydropdown.selected.value); ThisItem.StoreRoom - nameoftextinput.text (this is the text input where you would type in how many you are taking)

Not 100% sure that will work as I've never used a filter function AND a subtraction at the same time, but it should. Keep in mind, the number reflected in the form will show the quantity in your storeroom - the number you enter in the text input. If you work with people who know not to enter a larger number than you have available, this should be fine. If you don't, you'll need to add a conditional formula on the submit form button to only submit the form if the number in the storeroom card is greater than or equal to 0.  Then, for the EACH of the REV Center sections on the form, select its textbox and set its default to

 ThisItem.REVCentername + nameoftextinput.text (This is the text input where you would type in how many you want to add to this REV Center).

Your users will have to remember to make sure the amount they add to all REV centers combined should be equal to the amount you subtract from the store room, unless someone else can come up with a formula for that. That's a little beyond my skill level.

That should be it for the form. Now you just need to add a button and set it's "OnSelect" value to

SubmitForm(Nameofform)

When you click the button to submit the form, if it was successful, you should see the changes reflected in the form. Keep in mind, the numbers are going to be a preview of the numbers after the change. For example, if you currently have 5 bottles in your store room, and you enter 1 in the "input" textbox, the quantity is going to change to 4 before you submit the form. Submitting the form just confirms this is the change you would like to make.

I hope that helps. It should work. The only part I'm iffy on is the filtering and subtracting. If anything, I hope I've given you a place to start.