I have a table that tracks inventory at our locations. Every 2 weeks we measure and report the inventory so that every 2 weeks there is a new record for the inventory.
I'm trying to create a formula where the beginning inventory of one period (record) is always the ending inventory of the period (record) previous to the current. I have formula queries that return the result from the table itself and also lookup fields from a parent record, but I can't seem to make a formula that is not recursive / references itself, because by definition ending inventory formula is (beginning inventory - sales + receipts = ending inventory)
How are you creating the new inventory? Some kind of import or weekly process? You're not going to get around the recursion issue easily - your best solution is when you create the new inventory is to import/save the beginning inventory in the new record by copying the ending inventory from the prior.
It's only a single numeric field. I could copy it, but in 2024 that seems like a lot of manual entry. This is a database not a spreadsheet.
You don't have to do it manually - you can automate the import or populate it during the creation when the inventory itself is created.
Hey Chayce I do have that already. I created a formula URL that takes the inventory to the parent table and then pushes it into a numeric field upon creation of the child record. However, if something changes, or there is a correction in the middle of the sequence, then all of the following records have to be manually updated.
Understood - I'm suggesting however that you use Pipelines or some other import then to more / copy that data. So in your example - you could have a pipeline that searches hourly for any records that need to be updated, such as comparing the number you're expecting versus what is actually stored, and then have the pipeline update the value for you. You could run it daily if it doesn't need to run hourly or whatever cadence you need. The idea is you would automate the process that all or part of your button is currently doing and push the load to the Pipeline to make sure it stays in sync.
As Chayce said, probably capturing the previous period inventory and saving the value on a numeric field on the new inventory period record would be the best solution.
You can do this with a pipeline and a couple of summary and lookup fields, something like this: