Setting up a FIFO inventory

Hi Yannick -

 

I'm not sure I understand everything you ask, but I can help with bits.

 

If the Articles table already have a reference field to Inventory and you are in Inventory wanting to see the oldest Output you can try something like:

 

let x := 'Articles output'.'Date of sale'

let sortX := sort(x)

first(sortX)

 

Assuming that the records are already linked the first line finds all Date of sale for that inventory record. Then Ninox sorts all the data in acsending order. Then it grabs the first one.

 

Maybe this will be enough to get you started. Smarter people can probably get you a whole solution.

Hi Yannick

 

I've mocked up a solution. It uses two tables, Inventory and Transactions. Transactions is used for both stock coming in, and for stock going out. It has fields to hold the date, quantity, price and, for stock coming in a Stock on Hand field. I also have two table references between these two tables. The first, Transactions, is used as a usual one to many relationship for all the transactions relating to an inventory item, and for this one Composition should be set to Yes. The second table reference, TransactionsSOH, only holds those transactions where there is stock on hand. The Composition has to be set to No for this one.

 

 

 

As this is a mock-up I have put the two scripts you need into buttons but you may well want to insert them into your workflow. The first is for stock coming in.

 

StockOnHand := Quantity;
InventorySOH := Inventory;
'In/Out' := "In"

 

This copies the quantity to StockOnHand, and the table reference to InventorySOH

 

This is the script for stock going out

 

let a := Quantity;
let v := 0;
for b in Inventory.TransactionsSOH do
if a > 0 then
let c := min(a, b.StockOnHand);
a := a - c;
b.(StockOnHand := b.StockOnHand - c);
v := v + c * b.Price * 100;
if b.StockOnHand = 0 then
b.(InventorySOH := 0)
end
end
end;
Price := round(v / Quantity) / 100;
'In/Out' := "Out"

 

For the transacton going out it loops through all the available stock on hand reducing the stock and at the same time calculating the average price of the stock used.

 

Finally, here is a screenshot of it working

 

 

Regards John

John,

would you still happen to have the data model for your mock-up solution, it would be useful in a project I am creating and I’d like to see if it will fit within my existing DB structure. 
thank you.