Actions and Getting Values from a Differant View

[Jonathan_S]

Good Afternoon All,

I have a Parts View in my app that contains stock etc. I have attached an action to this view that opens up a form where a user can select a part, and add stock. (Preferable to auto select the part when navigating from a selected Row )

I had to make a separate form for this/Table which I will use as a history Table right away.
This History Table has a referance to the parts table for the user to select a part.

I’m trying to figure out how I would Take the Values of the form (History Table) and add the stock to the selected part in the parts table.

This is what Ive got,

[Koichi_Tsuji]

Why don t you make a parent/child relationship between parts table and history table?

I understood your history table will save and store the records in terms of how many new stock arrives to the site for each parts.

In that case, on your history table and parts column should ref to parts table, then it will generate the parent/child relationship.

Then when you open up detail view for every single parts, then associated history of stock records will be nested as child table.

Then for instance, you place a physical column to parts table which store the value of initial stock number. Then also create new virtual column to this table, which will dynamically calculate the current stock level. Expression should be [initialStock] + sum (select(history[newStockNumber],[partsID]=[_thisrow].[partsID]))

It will dynamically adding the number of new stocks when it arrives. You can minus the stock as well, by creating another table, the stockOut kinda of table, and do the same trick to run the calculation to get the current stock level.

[Jonathan_S]

Sorry, to much to fast, Could you explain a little more, I read it multiple times

[Koichi_Tsuji]

Okey, sorry, if I made you complicated.

First you have parts table, which stands as absolutely MASTER table to store the unique list of the parts you have.
Then if I were you, I will generate the child table to record the stock.

I m not sure if you are happy with just ADDING the stock level, but i assume sometime you need to decrease the number of the stock when it is consumed.

So for the child table to manage the in/out for the stock, I will place the column of

PartID : Ref type to lookup the parts table
In or out : Enum type if IN the stock is added, if OUT the stock is subtracted.
Amount : record the number of stock in or out

Then on the parts master table, you place the physical column to record the INITIAL number of the stock you have for each items. This is hard coded.

Then you create the virtual column to calculate the current stock level.

[initialStock] + sum (select(history[newStockNumber],and([partsID]=[_thisrow].[partsID],[In or Out]=“In”))) - sum (select(history[newStockNumber],and([partsID]=[_thisrow].[partsID],[In or Out]=Out")))

This is super simple math. For each parts items, you have initial stock level as numeric number. Then when you add the new child row which is record of IN/OUT of your stock, then virtual column will dynamically calculate. Initial value to sum of the set or row which is flagged with IN. In means the stock level is going up. So add.
Then when the flag is “out” then subtract.
Above expression is combining those and dynamically calculate the current stock level for each item all the time.

[Jonathan_S]

Ok, Let me get a run at this, I may just get started on it in the morning

[Koichi_Tsuji]

Yes, better to start with fresh mind.
Hope it will solves your problems.