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
------------------------------