Update Table Data Using Form Submission Field

[Kieran_Dixon]

Hi all

I’m trying to put together a simple inventory app.

I have a ‘Products’ table, an ‘Orders’ table, and a ‘Shipments’ table.

The way the inventory templates present current stock is to take a starting stock, add all orders in and then subtract all shipments out.

This seems very inefficient to me, especially as the database grows.

It makes sense to me that when an order is received the stock number in the ‘Products’ table would go up (by the entered amount) and when shipments are sent the stock number in the ‘Products’ table would go down (by the entered amount).

I’ve been trying to do this (and searching for a solution in the community) but I can’t figure it out.

When a form is filled in to add a row to the ‘Shipments’ table (for a single product), I’d like the quantity for that product in the ‘Products’ table to decrease by the amount entered in the form.

I’ve tried a few things with actions and reference actions but I can’t get the data to flow from the form submission to the ‘Products’ table (as each submission is not a fixed quantity).

Any help would be appreciated (including telling me that I’m structuring things incorrectly).

Thanks a bunch

[karimmc]

Hi Kieran,

I think this will indeed save you storage, however you will lose history on your product entries and exits, that is that the other tables are meant for

If you don’t mind losing your history you could do it this way, for that you can do the following:

Set two columns on your products table: product_quantity and entry_quantity.

In the product_quantity column you are going to store the actual inventory and in the entry_quantity you’ll have the user setting that quantity everytime. Then you’ll need to set up a bot in which every time that the entry_quantity is edited it will change the product_quantity value to [product_quantity]+[entry_quantity], and after that it will have to clear the entry_quantity column so that it is blank on the next entry.

This will have some limitations, one is what I mentioned before about losing you entry history, other could be that the bot could be messed if there are two users editing that field at the same time, among other things. I would strongly recommend going for the other solution if you can handle the storage in your database.

Regards,

Karim.

Pd: I hope I understood clearly your issue.

[Kieran_Dixon]

Hi Karim,

Thanks for the thoughtful response.

You’ve understood the problem well.

My issue with the additional tables isn’t that they will exist (I’m happy to have a detailed history of product flows). Instead, it’s about the calculation of the current stock.

I guess I just don’t understand why I would want that to be a (potentially taxing) calculation when instead it could be a fixed value.

I thought it would be relatively simple to take a piece of data when a row is added to a table and have that data affect a column in another table. However, it seems it’s quite complicated.

Please let me know if my clarification leads to any further thoughts.

Thanks again!

[karimmc]

Complementing Marc´s response: You could use INPUT() to get the value of the quantity of added product and set the [current_quantity] column formula to be [_THISROWBEFORE].[current_quantity]+[new_quantity]

Not sure if I’m using this expression correctly in here but the idea is a combination of the value of the current quantity of the product added to the value received by the INPUT()

[Marc_Dillon]

Kieran_Dixon:

I’ve tried a few things with actions and reference actions but I can’t get the data to flow from the form submission to the ‘Products’ table (as each submission is not a fixed quantity).

Maybe this could be the missing piece?

Using INPUT() to update another table using a value from this row Tips & Tricks 💡

This “Tip” is a little report, with a sample app, on my own attempt to familiarize myself with the new INPUT() function. As @praveen has indicated, the advent of this function gives us the power of an “UPDATE EXISTING ROW(s) using values from this row” action: Here’s a sample app I made to test this out: https://www.appsheet.com/samples?appGuidString=502cc394-2717-4a69-8246-5fc26a1aa9cf This extremely simple app has two tables: one with multiple records and one with only one record. The i…

In general though, inventory app questions like these are probably the most often asked question here. I’d urge you to keep searching and reading this community.

[Kieran_Dixon]

Hi Marc,

Thanks for your help with this.

I read up on the INPUT function and it doesn’t seem to get me where I want to go.

To clarify further:

I want to add rows to the ‘Orders’ and ‘Shipments’ tables using a form view.

When a form submission occurs, I want the data from this submission to be used to complete a calculation in another table.

From INPUT’s description it seems as though it should do what I’m after, but when implementing I’m unable to make the connection between the tables.

Please do let me know if my further explanation brings any possible solution to mind.