Discussions

Expand all | Collapse all

Inventory On Hand

  • 1.  Inventory On Hand

    Posted 01-29-2020 14:33
    Hello
                       I am trying to create an inventory control database that will correct the on hand amount with each transaction.  I am tracking my Raw Materials and have a table for the item and specific lot number, and a table for all transactions.  I would like to have my on hand amount automatically adjust every time i complete a transaction, thank you.

    ------------------------------
    James Vierkant
    ------------------------------


  • 2.  RE: Inventory On Hand

    Posted 01-29-2020 15:00
    I'm very anxious to see if you get a response.  I'm trying to do something similar and can't figure it out.  I think it may need an automation so I've been experimenting with them but no luck so far.  Good luck!

    ------------------------------
    Mike
    ------------------------------



  • 3.  RE: Inventory On Hand

    Posted 01-29-2020 15:25
    This is typically done with relationships and summary fields

    One Item has many Order Item Lines  (ie the order lines)

    One Item has many Purchase Order Receipt Lines (somehow you also need to increase inventory, typically via a Purchase Order Header with Many Purchase Order Lines)

    One Item has Many manual adjustments (for say cycle counts or damage / loss adjustments.

    Then you create a summary field of the total qty on Invoice Order Lines.

    Then you create a summary field of the total received on Purchase Orders.

    Then you create a summary field of the total manual adjustments.

    Then on the item record you create a formula to calculate the current inventory balance.
    [Total received] - [Total on orders] + [Total manual adjustments]

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------