Data structure Inventory management FIFO

[VictorB]

Hi, 

I'm building an app to do Inventory Management with the First In First Out method. 
I'm not sure of the data structure is best to use. Right now I have this structure :

Table Article ; Table Inventory In ; Table Inventory Out ; Table Current Inventory
(With date, Quantity, and Price)

The point is to get the right Price Value in the Table Inventory Out, using the First In First Out method.

For example: 

Problem A) I'm looking for the blues cells
Problem B) In the case the row Quantity Out is using more than one row of the table Inventory In, I'm not sure what's best to do. 

My thoughts are :

Problem A) using a List of ID from the table Inventory In with a SORT() by date and then updating the list with value from the Table Inventory Out. 

Problem B) Made an action with a bot to create a new row in Table Inventory Out if the quantity Out is using many rows of the table Inventory In.

Am I on the right way ?
And do you have any proposals to realize my thoughts in AppSheet ?

Any suggestions and or references would be greatly appreciated. 

[SkrOYC]

I haven't done any FIFO inventory management yet but I've thought about it and I would have a system where I add requirements when moving out things and use a loop to exactly translate that requirement to the items based on the availability of the inventory.

Also, I'd use one table for the inventory, just identify which row is in and which is out, not two tables

[VictorB]

Hi, thanks for the lead. 
I'm also trying to build a system with a loop, but for now I can't get it work.

---

@SkrOYC wrote:

I would have a system where I add requirements when moving out things and use a loop to exactly translate that requirement to the items based on the availability of the inventory.

---

Can you explain that a bit more ? 

[SkrOYC]

Requirements/Movements would be a table (although with a better name) where you submit the movements.

This fires actions that adds Out movements to the Inventory table.

For this, the action should check how many items there were on the last available product so that it decides whether to add the movement directly (if it's less than the available amount) or add the max available for that one and redo the same again until finished.

You could add ref columns in between to make a parent-child between Movements and Inventory rows

[Rifad]

Do you have any examples for this ? 

[TeeSee1]

I thought this would be an interesting subject to work on and created a POC app that looks like this.

Some comments

• Movements table records both acquisition of inventory ([type]="GR") and consumption ([type]="GI")
  • A GR row records the date when that inventory is acquired, unit price and qty. It also has a VC, [Remaining Qty], that calculates the remaining qty by subtracting the sum of related GR records' qty from the initially acquired qty.
  • [Valuation]: Different calculation logic depending on the [type]. 

    IFS(
     [type]="GR",[unit price]*[qty],
     [type]="GI",[qty]*[_THISROW].[related mv].[unit price]
    )​

• A Sales row records item and qty sold that triggers a sequence of actions that create GI movement(s) that together fulfill the sales qty. Each GI row has a reference to the Sales record as well as a ref to a GR record from which inventory is pulled.
  • [Qty to be assigned]: a VC that calculates the qty to be fulfilled by subtracting the sum of the related GR qty created up to the previous loop iteration from the original sales qty. This is used to determine the qty of the next GI to be created using the following expression

    MIN(LIST(
     [Qty to be Assigned],
     INDEX(
      SELECT(
       movements[remaining qty], 
       [id] = MINROW("Movements","mv date",AND([Remaining Qty]>0, [item]=[_THISROW].[item]))
      ),1
     )
     )
    )​

  • It is also used to determine when to stop the loop
  • The GR record to pull inventory from is determined by the following expression

    MINROW("Movements","mv date",
     AND([Remaining Qty]>0, [item]=[_THISROW].[item],[type]="GR")
    )​

  • [cogs]: Calculates the sum of [valuation] of the generated GI rows.
• Inventory Table: Sums up the total qty of each item available to calculate the max allowable sales qty to be entered. Not directly related to the FIFO process...

This was quickly put together and I am sure a lot of refinement is required for production use (i.e. too many VCs that might impact performance when the data volume increases. Multiple users trying to pull inventory from the same item.).

This is just one implementation attempt and if others can share their implementations, it would be quite educational for many of us!

You are welcome to see/play with the app, FIFO, if you are interested.

https://www.appsheet.com/portfolio/3401559