Hi - wondering if there is a more efficient way to track order status for individual items on a BOM. I have set up one table that tracks the current status/timeline of built to order bicycles and another table that lists all the components on the order (BOM). The BOM table contains the part description, vendor, expected arrival date etc and is connected to the order tracking table through a lookup. I have a formula looking at the individual BOM ETA’s to calculate the ETA for the build. I also have views that group the inbound parts by vendor so that our receiving person can sort through the various parts in a shipment and connect them with the respective projects.
Order Tracking Table:
- Order #1
- Order #2
…
- Order #X
Parts Tracking Table / BOM:
Order#1: Part#1 description | Part #1 Vendor | Part#1 ETA
Order#1: Part#2 description | Part #2 Vendor | Part#2 ETA
…
Order#1: Part#Y description | Part #Y Vendor | Part#Y ETA
Order#2: Part#1 description | Part #1 Vendor | Part#1 ETA
Order#2: Part#2 description | Part #2 Vendor | Part#2 ETA
…
Order#2: Part#Y description | Part #Y Vendor | Part#Y ETA
Order#X: Part#1 description | Part #1 Vendor | Part#1 ETA
Order#X: Part#2 description | Part #2 Vendor | Part#2 ETA
…
Order#X: Part#Y description | Part #Y Vendor | Part#Y ETA
The problem I am looking at is that the BOM table has exploded with data. Each project has 50-60 unique components and after a few months of processing builds, it has become daunting. I had originally debated creating a new table for each project BOM but figured the number of tabs would get crazy so I put all BOMs in one table and group by project name.
I have also looked at building the BOM horizontally instead of vertically, which keeps all the data confined to a single row and makes life more manageable from some perspectives, but would have required me to duplicate vendor name, ETA, current status for each type of part, which besides being tedious made the number of columns crazy.
Thanks for any suggestions
