Hello, I have a field called “Quantity” that needs to count how many parts are being replaced for each machine. I have a field marked “PartNumber” where I can link the parts from a PartsList (table of a partnumber for each record along with a description). Is there any way to calculate that I have 2 of “M10506” and only 1 of “M34563”? We are coming from paper copies of Service Reports and I have no idea how to do this. I would gladly compensate anyone who would spend more time with me to help iron out this database. Thank you for the help. -Nathan
Hi Nathan. It’s difficult to probably give you a comprehensive, clear idea of how this base should be designed in a message board reply, with the limited amount I’ve seen so far. There are great Airtable consultants here who would be glad to help you. However, I will point you in the direction that I would go.
First, I would have three tables in the base (as far as addressing the current issue). A table for service reports, one for service report items, and one for part descriptions (Parts List). Of course I see you have a couple of other tables which look good too.
A service report is linked to multiple service report items. A service report item is linked to a specific part and has at least a field for quantity (duh). The parts list probably has quantity available, unit price, etc. So now, back in the service item table, you can “lookup” the unit cost of the part, multiply by the quantity and get a total cost for that part(s). In the service report table, you can use a rollup field to get a part list subtotal for the service report. All of this will flow up into your page designer app based on your service report table.
Hope this helps some!
Hi, I do have a basic price for each part. I just need to know how many belts were used for maintenance and how many cameras replaced. So an output of “Quantity of specific Part Number” and then “part numbers” along with a description for each on the Service Report.
The customer needs to see what we have replaced before they sign off, the cost is sent later via an invoice.
2 of M15660 “Head Camera”
1 of M23455 “Recip Blade”
1 of M44566 “14mm Belt”
If I knew how to design scripts I would go that route. Does this seem like a more advanced problem that a formula cannot accomplish?