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 project has many products.I'm just seeking thoughts on if this is the right setup option for our uses. We may have instances where we receive in parts that are for inventory and will eventually be assigned to a Project. For example I could receive thousands of screws (Products) that will eventually need to be allocated to a certain Project. I'm wondering if I could make a Project called just "Parts for Inventory" and then as the parts are allocated, copy the Project, and reassign the screws to their applicable Project and out of "Parts for Inventory". What's the easiest way of doing this? I could manually subtract the quantity from Parts for Inventory and make a new Product record for the project, but is there an easier way? Let me know if this makes no sense :) Any insight would be much appreciated! Annie ------------------------------Annie Ryden------------------------------

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

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

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

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

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

Table-to-table relationships for Inventory ERP

11 Replies

EdwardHefter
Qrew Cadet
4 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
  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
    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
    ------------------------------
DonLarson
Qrew Elite
4 years ago
Annie,

Inventory gets complicated. Here is a model I have used

There is a fair amount going on, but not by the standards of Off the Shelf Inventory Packages.

This tracks the Manufacturer and the Vendors.

Titanium Screws are made by a handful of Manufacturers but sold by many dealers at very different price points and with different part numbers.

The Transaction Type Table is where I have Into Inventory, Out of Inventory and Inventory Adjustments which happen in the Inventory Transaction Table.

I have not explained everything, but hopefully this will help.

------------------------------
Don Larson
------------------------------
- AnnieRyden
  Qrew Member
  4 years ago
  Hi Don!
  
  This is immensely helpful, thank you so much! This was exactly what I needed to help get me started!
  
  We manufacture large industrial/electrical equipment and I'm trying to transfer the process from the person who was previously in the position. I have dozens of spreadsheets manually tracking exactly this, and these are exactly the inputs that I need to track.
  
  So far I've just set up a Projects Table with a child Products Table. Is there any more information I could provide for some more guidance? I really appreciate your message! This makes my project feel much less daunting already.
  
  ------------------------------
  Annie Ryden
  ------------------------------

Forum Discussion

Table-to-table relationships for Inventory ERP

11 Replies

Related Content

How to Link Tables in a Complex Many-to-Many Relationship?

Moving Fields from One Table to Another

Help connecting Table to Table Relationships

How to relate two tables with the key to a third table?

Way to Duplicate Tables or Make A Template for a Table?