Table design idea (Tracking of fabrication and delivery)

[Kevin_Moreau]

Hello all,

I started a base and before I get too far I thought I might ask the community to help me start on the right track. I would like to track the fabrication of a lot of piping assemblies. Here is what I have in mind :

1. One table for the list of pipe assemblies to track the drawing status (for comments, for construction)
2. One table to track the fabrication status (drawing for construction received, in progress, completed, shipped to paint shop)

• For this step I would like to have an attachment for the invoice from the fab shop and the delivery bill from the fab shop to the paint shop

3. One table to track the paint of each assemblies (received from fab shop, in progress, completed, shipped to work site)

• For this step I would like to have an attachment for the invoice from the paint shop and the delivery bill from the paint shop to the work site

4. One table to track the receiving of each assembly to the work site (received from paint shop, installation in progress, completed, invoiced to client)

• For this step I would like to have an attachment for the material / installation bill and the invoice to the client

Am I on the right track? is it possible for the assembly number to be unique for the entire base?

Ultimately, before I create my invoice I would like to be able to combine all the attachment together in order to be able to invoice our client based on what was delivered to site. How could I do that?

Here is an example:

I have 50 drawings
10 of them have the “for construction” status and are sent to the fab shop in order to start the fabrication
Then, 5 of the assemblies are completed and are shipped to the paint shop
Then 4 of the completed assemblies are painted and we request to ship them to site to start the installation
4 assemblies are now on site and can now be invoiced

• Edit : I also would like to create an automation if an attachment is missing, send a reminder. For example if an assembly is shipped from the fab shop to the paint shop. I should have a delivery bill and I want to be reminded that the attachment is missing until I upload it to the table. (would it be possible to group all the missing document in one email? let say : assembly 1, 2 and 3 have been delivered but the delivery bill is missing)

[Andy_Lin1 · Accepted solution]

I think the best way forward might actually be with a single table for the pipe assemblies themselves, and then have separate tables for things like the job, supplier, client, and possibly invoicing/budgeting.
Here’s why:

• You’ve identified separate stages of production, but the input and final output of all these stages is considered a single product.
• Each assembly seems to go through the same production process.
• You wanting a unique identifier for each assembly as it goes through each production step.

Since each pipe assembly record is going through the same journey, if you were to create this database with just one table for the production process, if would save you the effort and hassle of creating a record at each step of production, and it would also save you some database space (you’d only have to create 1 record vs 4).

So, the way you’d set up each individual step is with a single-select field. You’d have options like

• Drawing - Comments
• Drawing - Construction
• Fab - Received
• Fab - In Progress
• …

And then you would set up 4 views, each with a filter that selects for a particular set of options from that single select field (e.g. Draw, Fab, Paint…)

As for what you’re looking to get out of your database:

1. Unique Assembly Number – this is built into the design, since there is now only one record per assembly
2. Combine attachments for invoicing – this is also built in,* though you may need a formula field to calculate the total amount of the invoices (I also have no idea how you’re getting the numbers from the attachments, but you could just add a Currency field for each invoice amount).
   *I’m guessing that you probably have more than one assembly per job. In that case, you’ll want a second table for jobs and link the assembly records there. You could then roll up the invoice amounts from the assembly records that are linked to that job record to get a grand total for the client.
3. Email reminder automation – right now, there is a template for a “Weekly Digest”, which you can modify to your schedule (it doesn’t have to be weekly) and your search parameters (e.g. if “Production Stage” is “Paint - Received” and attachment field “Delivery Bill” is empty).
   

[Kevin_Moreau]

Thank you for the tips, it help me build my base the right way!