Anyone ever made a inventory management system that handled multi level assemblies? Looking to build something that will help me track assemblies with various quantities or parts inside them, various warehouse locations and hopefully also link back to zapier for integration with a website.
I would like to be using a full ERP system but they estimate months to have it implemented so looking for something in the mean time.
Database would only have around 70 finished parts made up from 200 or so parts total.
If anyone has a template would be amazing!
Thanks
Page 1 / 1
Hey @Brad91,
1. Unfortunately I do not have a template of my one which I could share with you! 2. You might want to check available templates here. 3. If you’d like to get a second opinion on how long building a full ERP could take, please feel free to grab a slot using this link. I’d love to go through your specific needs together and let you know my insights.
I have such a system in my company. It’s fairly complex, but at it’s core we have a Sub-Assemblies table which has 3 key fields: Parent sku (link), child sku (link), and child sku quantity (number). All skus are listed on the same SKU table regardless of if they are built, or base-level (purchased), with a naming convention used to tell them apart. Each time a parent sku is added to a sales order, the child skus are added to a bill of materials and deducted from inventory.
Like I said it gets pretty complex in our case, but it works well and I am happy to clarify or answer more specific questions.
I have such a system in my company. It’s fairly complex, but at it’s core we have a Sub-Assemblies table which has 3 key fields: Parent sku (link), child sku (link), and child sku quantity (number). All skus are listed on the same SKU table regardless of if they are built, or base-level (purchased), with a naming convention used to tell them apart. Each time a parent sku is added to a sales order, the child skus are added to a bill of materials and deducted from inventory.
Like I said it gets pretty complex in our case, but it works well and I am happy to clarify or answer more specific questions.
Any chance you would have a blank copy you might share?
how do you get the information into the tables? I export a list from solidworks into excel but would rather not retype every line manually. I think importing the bom with children correctly would be the hardest part. Especially because i may use the same part in several assemblies i dont want to double up the information in the tables
Hi @Brad91,
I believe you need a nested BOM system. You most likely won’t find a template online since it’s more complex and if someone’s built one on Airtable it was because it needed to be very custom to their use case. Depending on the scale of your operation, a custom built Airtable solution could be more than just a bridge to a full ERP system like Odoo or Katana. A custom Airtable inventory system would just take a week or 2 to build. If interested in what’s possible let me know.
Assuming your data looks like this:
Then you’d have a table for ‘Movement’ that you’d populate with the receipes whenever you logged an assembly:
This assumes that you’re creating the sub-assemblies before you construct the primary ones though. If you want the sub-assemblies to also be created at the same time as the primary then that gets trickier and depends on your business flow. e.g. do you want it to only do that if there are no sub-assemblies in stock? Does it always happen? etc
