Simple inventory app, updating one table with a value from another

[tomstorey]

Hi all.

Im banging my head against a wall trying to figure this out, and the basic form of the problem seems to be a fairly common question from what I can find searching around, but I cant seem to make heads or tails of the solutions.

I am working on a simple inventory management app, and theres a particular workflow that I would like to follow:

I have 3 tables:

1. Sites - a list of locations that can have an inventory of stock
2. Inventory - a list of items that are held at a site and their quantities
3. Log - a list of changes to each inventory item

I want to be able to select one of 3 actions per inventory item:

1. Add - I should increase the quantity of the inventory item by x
2. Take - I should decrease the quantity of the inventory item by x
3. Stocktake - I should set the quantity of the inventory item to x

The action is chosen from a list and stored along with a quantity for that action in the Log table.

All of the basic functionality such as creating sites, creating inventory items at a site, and adding log entries against an inventory item is functional.

So in particular, given the action specified in the form, I would ideally like to see the quantity of the widget change as follows:

• After the Add action, the quantity increases from 10 to 15
• Then, after the Take action, the quantity should decrease from 15 to 13
• Then, after the Stocktake action, the quantity should be set to 9

The bit I am struggling with is how to make the 3 actions work. I have been searching for hours and there is a lot of talk of using Actions to accomplish this, but I just cant wrap my head around how to make it work. I suppose in some ways I am looking for the elegant solution where the quantity column of an inventory row is adjusted based on the action (Add increments, Take decrements, Stocktake sets), as opposed to doing a SUM() over a collection of rows in the Log table for example (I dont know if that would work too well with my Stocktake option).

Asking for some help from someone here who can help me with this. I have plenty of experience programming in C, Python, and even assembly for various CPUs, but Im struggling with this - maybe because I am approaching it too much from a regular programmers perspective..?

I have attached a couple of screenshots of the screens I have implemented so far so you can hopefully see the basic set of data I am working with and how it links together.

Thanks!

---

List of sites:

Looking at a site and its inventory:

Looking at a particular inventory item:

The form you use to perform an action on an item (i.e. when you click Manage):

[WillowMobileSys]

I believe you want to use the INPUT() function which provides the ability of passing a value between certain actions to perform updates. Click on the link for details on how to set it up.

[RedVox]

What if you added a field to the logs that calculated the new total so the current total is just calculated directly in each log. The widget record could have a calculated value for the current total based on a calculation using LOOKUP( MAXROW(“Child Table” to return the current total from the most recent log record.

[tomstorey]

Oooook, things are a bit clearer today after sleeping on it, and I think I have figured it out using parts of both suggestions above.

In my Log table I added a virtual column (“New qty”) with the following formula:

IF([Action] = "Add", [Inventory].[Quantity] + [Quantity], IF([Action] = "Take", [Inventory].[Quantity] - [Quantity], [Quantity]))

This stores the value of the Quantity column from the Inventory table adjusted according to the action that was performed.

I then have two actions:

1. On the Log table which calls an action against the Inventory table, supplying New qty as an input
2. On the Inventory table which takes the New qty input and updates the Quantity column

Then, in the Log_Form view, I adjust the Form Saved event action to call the action created against the Log table.

So far it seems to be working perfectly! The New qty column in the Log table is not very useful to keep visible, however, since its value is calculated by a formula so it changes based on the Inventory items quantity column. There might be a way around that, but its not something that I need to see anyway.

Ive attached screenshots with the contents of the actions since I think that will be easier to digest than a wall of text describing each of them.

Thanks both for the suggestions!

---

Log table action:

Inventory table action: