Discussions

 View Only
  • 1.  Running inventory

    Posted 01-26-2024 15:45
      |   view attached

    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
    ------------------------------


  • 2.  RE: Running inventory

    Posted 01-26-2024 16:16

    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
    ------------------------------



  • 3.  RE: Running inventory

    Posted 20 days 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
    ------------------------------



  • 4.  RE: Running inventory

    Posted 20 days 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
    ------------------------------



  • 5.  RE: Running inventory

    Posted 17 days ago

    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.



    ------------------------------
    Brian Hunot
    ------------------------------



  • 6.  RE: Running inventory

    Posted 17 days ago

    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. 



    ------------------------------
    Chayce Duncan
    ------------------------------



  • 7.  RE: Running inventory

    Posted 01-28-2024 21:22

    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
    ------------------------------