Forum Discussion

AnnieRyden's avatar
AnnieRyden
Qrew Member
4 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
AnnieRyden's avatar
AnnieRyden
Qrew Member
4 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
4 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
    4 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
    ------------------------------
    • EdwardHefter's avatar
      EdwardHefter
      Qrew Cadet
      4 years ago
      I don't know everything that you would want the pipeline to do, but from what you've described, the way I would trigger it is to have a checkbox that is called something like "Ready to Order." When the record is updated, if that checkbox is checked, the pipeline will run. Part of that pipeline will be to UNcheck the box, so you can use it for a trigger again if you need to.

      ------------------------------
      Edward Hefter
      www.Sutubra.com
      ------------------------------
      • AnnieRyden's avatar
        AnnieRyden
        Qrew Member
        4 years ago
        Oh, okay! Thank you!

        That's so helpful and I love the idea of just checking the box to "place an order". Could I have uncheck the box to do something else too? Like remove the purchase order items? 

        If I uncheck the box but want to reorder again I'm struggling to think how I could attach the order to a different project number.

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