Discussions

Expand all | Collapse all

How to Track Inventory (Should be Simple but I'm lost)

  • 1.  How to Track Inventory (Should be Simple but I'm lost)

    Posted 18 days ago
    Problem: I want to create a "Inventory" table that automatically reduces each equipment quantity as I deploy my equipment at different stores.

    Current Setup: I have a stores table. The stores table has fields for different equipment that is deployed at that store.
    ex. Monitor, Laptop

    Each store may have a different quantity of equipment deployed.
    ex. [store 1000, monitor Qty 4, Laptop Qty 3], [Store 1001, monitor Qty 2, Laptop  Qty1]

    Whenever a store has equipment deployed, I  update the quantity to that number.

    What I tried to do: 
    - created a table called "equipment"
    - Added Fields [Equipment Name, Equipment Qty on Hand, Equipment Qty Deployed]
    ..now I'm a little confused, do I need to create a "Store" field as well and upload every store number that is on the Stores table and then link them? 

    what is the best way to get the summary of each equipment deployed across all stores, subtract it from my inventory on hand and give me the current quantity available number?



    ------------------------------
    Arbab
    ------------------------------


  • 2.  RE: How to Track Inventory (Should be Simple but I'm lost)

    Posted 18 days ago
    If understand what you are wanting, yes you would want the tables related. There are at least three ways you can do this. You can relate them and then manually add the store to each equipment, you can add a stores field as you stated and then relate them, or you can create a spreadsheet and include a unique ID and then relate them.

    Take a look at this post and see if it gives you some guidance:
    tables, IDs, and relationships

    ------------------------------
    Adam Keever
    ------------------------------



  • 3.  RE: How to Track Inventory (Should be Simple but I'm lost)

    Posted 18 days ago
    I don't think you understand.

    Here is a screen shot to better explain:

    Here is my Inventory Table:

    As you can see, the stores table has many different equipment.

    I want to take the sum of each piece of equipment across all stores and provide them on the equipment table and it should look like this:

    Equipment 1 - 50
    Equipment 2 - 10  

    etc.


    ------------------------------
    Arbab Rizvi
    ------------------------------



  • 4.  RE: How to Track Inventory (Should be Simple but I'm lost)

    Posted 18 days ago
    Yes, that is where the summary field function would be used in the relationship:

    You would select your filed for your equipment to generate the summary in the parent table.


    ------------------------------
    Adam Keever
    ------------------------------



  • 5.  RE: How to Track Inventory (Should be Simple but I'm lost)

    Posted 15 days ago
    Arbab,
    I would suggest having a stores table.  That would have a child table of assigned equipment as opposed to had coding the various kinds of equipment.

    There woulds be a table of equipment masters, one for each unique type of equipment, for example Xerox 123 model printer.

    The relationships would be

    1 store < Assigned equipment > Equipment master.

    ie the Assigned Equipment is the many to many join table.

    The Equipment mater would also have a relationship to Equipment purchases or if you prefer to simplify it, to a table of equipment adjustments.  The adjustment type would be say a receipt of new equipment, or an inventory adjustment if an equipment is taken out of service.  Ideally the Assigned equipment table would have serial numbers so you would actually know which serial was at which store.

    The inventory levels in the main warehouse would be a formula which would add the net inventory adjustments to the total of the assigned equipment. 

    My main recommendation is NOT to hard code the types of equipment into specific fields on the store as you will just make way too much work to set up summary fields for each type of equipment as  these will of change over time.

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



  • 6.  RE: How to Track Inventory (Should be Simple but I'm lost)

    Posted 14 days ago
    Arbab,

    I agree with Mark.  You want to get the many to many relationship between the stores and an Item Master table.

    However, think through where this is going.   There are a number of "standard" processes for Inventory that can start to get very complicated such as:

    Reorder Quantities
    Inventory Count Adjustments
    Costing models

    If you are going to need these it will get much more complicated quickly.  As long as it is just quantities at the store, Quick Base can handle it easily.





    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------