Number of Unique "PartNumbers" In a Field

Topic Labels: Formulas
1557 4
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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

4 Replies 4
10 - Mercury
10 - Mercury

Can you show us an example of the table where you want this new number to reside and what the linked field looks like?

I hope this screenshot will further explain what I am trying to describe. Another question would be on how to add two entries of the same part number in a linked field.Screenshot 2021-02-17 135450Screenshot 2021-02-17 135906

10 - Mercury
10 - Mercury

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?