About first-in, first-out inventory management

[suzukixxx]

I've been wondering if it's possible to implement FIFO using APPSHEET for the past month or so.
The only thing I want to accomplish is to display the number of days in stock.

This app has an "inventory master" and simply records records in the "incoming table" and "outgoing table" on a daily basis. The inventory quantity is calculated by the total of these three tables.

For example, suppose product "A" was registered two weeks ago, and the initial stock is 100 units.
Assume that you received 100 pieces of "A" one week ago and yesterday.
There are currently 300 pieces in stock.
Assuming that we shipped 150 pieces of "A" today, there are only 150 pieces left in stock.
Now, the number of days in stock for these 150 items cannot be calculated using the app.
Actually, if we use the first-in, first-out method, the inventory on the 7th day will be 50 units, the inventory on the 1st day will be 100 units, and the initial inventory will be 0 units.
My request is to display the "Number of days in stock" as "7 days" in this state. That's all.
The method of separating IDs or the method of having humans identify and issue goods each time from the oldest incoming history is unrealistic (and cannot be operated).
I would like to be able to find out the number of days in stock by somehow entering records only in the incoming and outgoing tables.

Three ideas came to mind:
①Create a theoretical inventory column (not VC) in the inventory table,
・When receiving goods, the number of goods received is entered in the theoretical inventory column.
・When "A" is shipped, use the orderby function to arrange the receipts of "A" in order of age, and subtract the theoretical inventory amount from the oldest order.
・Stop when the number to be subtracted becomes zero
I think it would be possible if we could create some kind of automation.
However, this system breaks down when records are corrected during operation.
②Simply arrange the number of items in and out of stock for "A" in chronological order, find the difference, and calculate the number of days in stock based on the oldest number of days for which the difference is zero.
③ Identify the ID of the current oldest inventory using VC in the issue table

We are not considering operational cover, such as having to manually identify the ID at the time of delivery. In that case, paper operations would be more efficient. I would like to find a way to calculate it automatically.

[AleksiAlkio]

So.. you would count that each time guys in the warehouse picks the oldest items first? And for that reason you would not need to identify the ID?

[suzukixxx]

Thank you for your reply. The materials we are trying to manage are textiles, and if there are any old ones, the manufacturing staff will use the oldest ones first. I think it's almost impossible to get it wrong. However, if you use this app to manage materials that are difficult to distinguish, such as copy paper, I think you will need to devise ways to manage them. However, currently, the only way to identify the age of a material is the ID assigned to the material at the time of stocking, and manufacturing personnel do not check the age of the material by looking at the ID.

[AleksiAlkio]

It should be doable. The best way is probably with a webhook as it has more possibilities. Not sure, but it might need an additional table like a child table for inputs. Each time the output is generated, it adds a child record when the batch was used.