Skip to main content
Question

Table-to-table relationships for Inventory ERP

  • January 31, 2022
  • 0 replies
  • 224 views
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
------------------------------
This topic has been closed for replies.

EdwardHefter
Forum|alt.badge.img+3
  • EdwardHefter
  • Quickbase Alumni
  • February 1, 2022
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
------------------------------
Forum|alt.badge.img+15
  • DonLarson
  • Quickbase Alumni
  • February 10, 2022
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
------------------------------
Terms & ConditionsAccessibility statement