How can I clean up my inventory log periodically?

[Lachi]

Hello, I would like some help for my inventory management app. I managed to build it by learning from the template and making some changes to fulfill my needs for my business. The app is working fine, but I do have concerns.

My app has items table and Inventory log table, you add items on items and you change the stock on Inventory log. So there's a virtual column [Stock] on Items that sum up all the changes made on the log. Everytime there's a change, there will be a new row on the log table. There will be so many rows on the table in the future.

My concern is, is there anything I can do to the app, so I can periodically clean up my Inventory log without affecting the stock?

I thought about adding new columns (not virtuals) on the table Items such [Inicial Stock] , [Log](capture all changes), and [Current Stock] ([Inicial Stock]+[Log]). So I can put the current stock(the numbers data) on [Inicial Stock] and be able to clean up the rows on Inventory log, while keeping the latest data and have a fresh Start.

But when I did everything, [Log] doesn't compute as the virtual column did, It didn't return me anything, the [Current Stock] didn't work as well.

I don't know what to do now...please help...thank you~(sorry for my english)

[TeeSee1 · Accepted solution]

Here is what I would do.

Keep the [Initial Stock] as a standard column.

Make the [Current Stock] a virtual column which is calculated by [Initial Stock] + Sum of the logs. (Or you could make the [Log] a virtual column, keep it as a separate column and use it in the calculation of the [Current Stock] if you like.)

[Lachi]

Thank you~ I managed to do it 🙂 although I still don't understand why can't normal column do the calculations when the virtual columns work just fine...

[Marc_Dillon]

App Formulas in real columns only evaluate when that record is edited. Virtual Columns re-evaluate upon every sync.