Forum Discussion

AnnieRyden's avatar
AnnieRyden
Qrew Member
3 years ago

Table-to-table relationships for Inventory ERP

Hello,  I'm working on creating a new ERP system for our Supply Chain Inventory/Procurement purposes. I've set up the App to have a parent table: "Projects" with a child table "Products". So, one p...
Relationships
EdwardHefter's avatar
EdwardHefter
Qrew Cadet
3 years ago
The way I've traditionally seen this done is that the items coming in from Purchase Orders (or Transfer Orders or whatever signals someone else to send material to you) go either to a project or into inventory. If they go to a project, it is pretty straightforward and it sounds like you are set up for that. If they go into inventory, you start to run into a lot of decisions to make, most of them dealing with accounting practices instead of “real world” tracking of items.

The simplest is to just keep track of how many of each item are in inventory, and the Purchase Orders make the quantity go up and transferring inventory to different Projects make the quantity go down. From an accounting perspective (since the same item may cost something different depending on how many you buy or when you buy, like steel and wood products!), you may need to keep track of which Purchase Order an item came in on and which specific item gets transferred to a Project. Sure, one WidgetXYZ is exactly the same as a different WidgetXYZ, but if they had different costs when they came in, there will be a different cost assigned to the Project.

There are other ways to keep track of inventory (inventory cost more than inventory), like FIFO or LIFO which can get complicated, too.

For just generally keeping track of how many items you have, though, having a bucket called inventory should work. If you want to just have a Project called Inventory, that should work too, if there is a way in your system to transfer items from one Project to another.

------------------------------
Edward Hefter
www.Sutubra.com
------------------------------
  • AnnieRyden's avatar
    AnnieRyden
    Qrew Member
    3 years ago
    Hi Edward!! 

    Thanks so much for your reply. I really appreciate it. We need to track purchase orders and purchase order lines as well so I think this is the right path. I had found this post from Mark Schnier yesterday and am trying to figure out how to apply this concept to our business:

    Response from mark shnier:

    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]

     

    From <https://community.quickbase.com/communities/community-home/digestviewer/viewthread?GroupId=103&MessageKey=cfbc3e8c-0cdb-4abe-a1b4-6078ff22d4ed&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer

    Would this mean that I need another table for purchase order lines? In our case one project has many products and many purchase order receipt lines. So I'm thinking my setup should be a parent table: "projects" with two child tables "products" and "purchase order receipt lines". 

    I'm just wondering how I could create product lines without having to manually create associated purchase order receipt lines now.

    ------------------------------
    Annie Ryden
    ------------------------------
    • EdwardHefter's avatar
      EdwardHefter
      Qrew Cadet
      3 years ago
      Depending on how complicated you need to get, you can avoid having another table for PO lines. I set up a system that has the Projects with the Products line, and one field on the Product line is the PO number. There are other fields for the expected date, the actual date it arrived, the serial number (we need to track it), etc. But... this approach doesn't easily allow for Product going in to inventory. You could do it by having an "Inventory" Project, but then you would need a way for the user to move Product from one Project to another.

      Having another table for POs and their PO lines gives you much more flexibility. You can have one PO that brings in Product for multiple Projects. You can do RMAs. You can track whether a PO has been received completely. You can do cost tracking. 

      Are you familiar with Pipelines? You can have one that creates the PO skeleton (parent table and PO lines, but no vendor, prices, expected delivery dates, etc.) whenever Product is added to a Project. There are probably a few other ways to do it, depending on what you want to accomplish.

      ------------------------------
      Edward Hefter
      www.Sutubra.com
      ------------------------------
      • AnnieRyden's avatar
        AnnieRyden
        Qrew Member
        3 years ago
        Hi Edward! Sorry for my delay! 

        I wasn't aware of pipelines but I'm trying to figure them out now. I was thinking that if the "Status" input was switched to "Ready to Order" or just that I could have that be my trigger. Now I'm not sure if that's possible though. 

        What would you use as a trigger? I was also thinking that I could just have the trigger be if the "Purchase Order #" input was populated. That could get messy though. What if we want to change a PO number later or had some reason to enter a purchase order # without wanting it to run the pipeline? Do you have any thoughts on how you'd set it up?
        I really appreciate your help!
        Annie ​​

        ------------------------------
        Annie Ryden
        ------------------------------