0

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

null
    • Fred
    • 3 yrs ago
    • Reported - view

    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.

    • John_Halls
    • 3 yrs ago
    • Reported - view

    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.

     

    Screenshot 2021-11-06 at 08.53.20

     

    Screenshot 2021-11-06 at 08.55.09

     

    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

     

    Screenshot 2021-11-06 at 09.16.10

     

    Regards John

    • ahansonuk
    • 6 mths ago
    • Reported - view

    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. 

    • John_Halls
    • 6 mths ago
    • Reported - view

    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

      • ahansonuk
      • 6 mths ago
      • Reported - view

       

      thank you for your help it is much appreciated, the script would work nicely within my project so if you could recreate it it would be very helpful. 
      it is the relationships that I really need and how the will fit within the database I have created. 
      regards Andy. 

Content aside

  • 6 mths agoLast active
  • 5Replies
  • 513Views
  • 2 Following