Hey all. New to appsheet here, but lots of e...

[Marc_Dillon]

Hey all.

New to appsheet here, but lots of experience with google spreadsheets and scripting in them.

This is seeming to be a hard thing to search for so I will ask it here. I have an inventory spreadsheet, each row an item, 3 property columns (id,name,amount). I have a form for a user to fill out where they pick an inventory item from an Enum list, and input an amount (negative or positive). Is there some easy built in way that I am missing to use the data from the filled out form, to add or subtract to the inventory? Thanks!

Edit: An example. Say item 1 has an amount of 5. A user uses 1 of this item, so they fill out the form, selecting item 1 from the list, and inputting -1. I want the amount in the inventory sheet for item 1 to be automatically update (subtract 1 from it). I do not want the user to have the directly edit the inventory amount (that is easy app-wise, but hard on the user).

[Eddie_Wang_AppS]

To clarify, is that a separate google form data you’re using or an Appsheet-based form?

[Marc_Dillon]

an appsheet form

[Marc_Dillon]

Thank you for your help @tony and others. I just figured out how to do what I needed. It seems so simple now, but so complicated before.

[Alex_Meraru]

@Marc_Dillon which is…?

[Marc_Dillon]

Yah, you’re right. I should describe my solution.

In my case I have a bunch of inventory items that I want to keep a running total on. Some days they get used up, some days we get a delivery of more.

A usage of inventory items usually isn’t just one, it comes in a group of items used that needed to be connected.

I have three tables.

1. A table of inventory items, this table started with just name of items. 2. A table of inventory adjustments, which has a label, and a list of references to: 3. A table of adjustment details. The details reference the inventory items, and back to the adjustment.

So a single adjustment can have any number of inventory item changes.

Then in the inventory table, there is an automatically created “related …” virtual column. Manually create another virtual column, type Number, app formula: SUM(SELECT([Related…][Adjustment qty], true)). And that column adds up all total adjustments per inventory item.

Seeing as how I just figured it out, I’m sure that description was not the best. Let me know if you need more help.

[Kane_Group]

@Marc_Dillon Could you please explain the above in a bit more detail, would be very much appreciated as trying to do similar. I don’t need to keep a history. Just In, out, Current Stock where Current Stock = Current Stock+(In-Out)

[Marc_Dillon]

@Kane_Group Which part needs more explanation? Actually you do need to keep a history, because that’s how the current stock is calculated; by keeping a sum of all history.

[Eddie_Wang_AppS]

Hmm… I’m relatively new to the AppSheet platform, so take everything I say loosely. I might not be understanding your question as well.

That said, I have a personal app (different use case) that I think has the same functional need. This is how I’m doing it: 1) Connect spreadsheet to app 2) Go to Data => Columns => change the relevant fields to enumerated list and whatever else you would need. 3) Then, in the app, it should pretty much generate the form you need whenever you try and alter the data.

help.appsheet.com - Editing Your Columns Editing Your Columns help.appsheet.com

[Marc_Dillon]

Thanks for the reply, but I don’t think that helps. I’ve added an edit to the original post:

Edit: An example. Say item 1 has an amount of 5. A user uses 1 of this item, so they fill out the form, selecting item 1 from the list, and inputting -1. I want the amount in the inventory sheet for item 1 to be automatically update (subtract 1 from it). I do not want the user to have the directly edit the inventory amount (that is easy app-wise, but hard on the user).

[tony1]

@Marc_Dillon I think this app is close to what you want: appsheet.com - Dynamic inventory with qty - Scan items with quantities to keep a running inventory count

You can search over example apps here: https://www.appsheet.com/Support?q=inventory&hPP=10&idx=help&p=0&dFR[doc_type][0]=Apps&is_v=1 Dynamic inventory with qty - Scan items with quantities to keep a running inventory count appsheet.com

[Alex_Meraru]

Hi, I am new to this but won’t it work if you have a “goods_in table”, a “goods_out table” and your “stock_table” to be:

(what’s next is not the way to do it but it’s the way I know to explain it)

SUM(goods_in)-SUM(goods_out) ?

I hope it’s not a foolish answer

[Marc_Dillon]

So it seems the answer is no, not really a super easy built in way to keep a dynamic inventory.

Yes I figured I could keep a sum of all “purchases” and “sales”, but then I thought I would somehow have to do some sort of messy vertical lookup formula for each product. That’s spreadsheet thinking though.

The app Tony posted seems to use another virtual column called “Related Purchases” and “related sales”, which are lists of type Ref, and an app formula of “REF_ROWS(“Sales”, “Product Barcode”)” . And the “total sales” virtual column is defined by “SUM(SELECT([Related Sales][Quantity], true))” .

So it seems that for every product inventory, there is a virtual column keeping a list of sales and purchases of that product, then a virtual column summing those lists. Is that the gist of it?

[Alex_Meraru]

I might just got you an quick and easy “half of an answer”. If you group your view by product and set Group aggregate to SUM[qty] it’s being done in one go.

If there is a way to change the user input from a positive entry to a negative

record, done.

but that will be a view only information

[tony1]

@Marc_Dillon Those “Related …” columns are automatically generated by AppSheet when you have a reference from one table to another. So in this case, each Sale record has a reference to a particular Product record.

[tony1]

help.appsheet.com - References Between Tables References Between Tables help.appsheet.com