Data structure Inventory management FIFO

VictorB · 02-07-2023 12:58 AM

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

LuchoPrz

Hola Victor,

Mi propuesta sería crear dos tablas, una para llevar las existencias actualizadas del inventario por ID de producto donde tengas un campo con tu saldo inicial, otro campo de entradas y un tercer campo de salidas (campos que se van actualizando cada vez que generes una entrada o salida respectivamente y te actualice el campo de existencia), y crear una tabla detalle donde lleves el control por ID de producto donde tengas un campo Movimiento en el que indicas si es entrada o salida y la cantidad guardada sea positiva o negativa dependiendo del movimiento y se inserte conforme se hagan entradas o salidas desde los módulos correspondientes (adicional campos como fecha, si manejas sucursales, etc., para que puedas tener reportes generales y si requieres saber el detalle no tengas que ingresar formulas muy complejas que te hagan perder rendimiento

no sé si me expliqué...
si te queda alguna duda con gusto envía tu duda...
saludos!!!

aijac1314

Hi @VictorB

I came across a video that might be helpful for you in building an FIFO Warehouse Management System (WMS) using AppSheet. It provides a step-by-step guide on setting up workflows and managing inventory using the FIFO method. You can check it out here: https://youtu.be/LfJThQA4_sM.

Hope this helps with your project!