Setting up a FIFO inventory
Hello,
I'm trying to setup a FIFO base inventory where the articles come in with a certain price at a certain date and get taken out at a certain price at a certain date. With this I'm trying to also have a table that shows my actual inventory and it's value so here would be the three tables:
Articles input:
N°/Name/Date of purchase/Quantity of units/Price per unit/Total price
Articles output:
N°/Name/ Date of sale/Quantity of units/Selling price/Total price
Inventory:
Article N°/Article Name/Input or output/Date of transaction/Quantity of units/Price per units/Total value
Here are my questions:
- How do I tell Ninox to use the oldest data first when an output is recorded? And once this is done, how do I mark the units already used to be sure they're not used anymore in the calculations?
- How do I tell Ninox to use the various correct prices corresponding to the outputed articles?
Thank you for your help.
5 replies
-
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. -
Hi Andy
I can't see it ATM but it wouldn't be too difficult to re-create if you would like me to? I can't today but will do so in the next couple of days if you would like.
Regards John
Content aside
- 6 mths agoLast active
- 5Replies
- 513Views
-
2
Following