Forum Discussion

BrianHunot1's avatar
BrianHunot1
Qrew Cadet
11 months ago

Running inventory

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)



------------------------------
Brian Hunot
------------------------------
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    11 months ago

    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. 



    ------------------------------
    Chayce Duncan
    ------------------------------
    • BrianHunot1's avatar
      BrianHunot1
      Qrew Cadet
      10 months ago

      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.



      ------------------------------
      Brian Hunot
      ------------------------------
      • ChayceDuncan's avatar
        ChayceDuncan
        Qrew Captain
        10 months ago

        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. 



        ------------------------------
        Chayce Duncan
        ------------------------------
  • AlbertoTablada2's avatar
    AlbertoTablada2
    Qrew Trainee
    11 months ago

    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:

    1. Create Summary field to obtain Max Record ID of child records.
    2. Create Lookup field to this field that was just created in step 1.
    3. Create Summary field to obtain Max Record ID, but this time with filter where Record ID# must be less than [Lookup from step 2]
    4. Create Lookup field to field created on step 3.
    5. Summary field to obtain Max Ending Inventory, with filter where Record ID# must be equal to [Lookup from step 4]
    6. Create new data entry numeric field to capture Previous Ending Inventory value.
    7. Then create pipeline to run for example when an inventory record gets updated and the "Previous Ending Inventory" is empty, then have the pipeline query the parent record, and obtain the value from "Max Ending Inventory" and save this value on "Previous Ending Inventory" of the inventory record that triggered the pipeline.


    ------------------------------
    Alberto Tablada
    Lead Software Engineer
    BlueWell Group
    Florencia
    +50688083124
    ------------------------------