Forum Discussion

JamesVierkant's avatar
JamesVierkant
Qrew Member
6 years ago

Inventory On Hand

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
------------------------------
App builders
Relationships
  • MikeRorstad's avatar
    MikeRorstad
    Qrew Cadet
    6 years ago
    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
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    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
    ------------------------------