Grouped data creates new options PLEASE HELP

Our fields are grouped by Order Number (reflecting the same project) The drop down menu has the status of materials. I’m looking for a way to mark (a new field) when all items matching the order number read Received. (In the example, I threw in the cancel status - because the real world never runs so smooth)

The end result I’m looking to accomplish is once statuses are marked Received the “new field” generates a reminder to send a form. (Hoping to accomplish this with zapier and jotform) Which leads me to believe another field is required to push this process to zapier?

Thank you in advance.

Example:

Order Number Vendor Name Material Status Processed
456789123 Abc Vendor Received
456789123 123 Materials On order
456789123 Yeah! Supplies Cancel

If everything is in on one table, then the Airtable cells really only know about the data in the rest of their row. You might be better off extracting Orders/Projects to their own table, so that you have both granular control over materials, as well as high-level controls over Orders. Then you can link those tables together so information is shared automatically (such as which orders have entirely received materials). See example below.

Otherwise, if this is too big of an overhaul, you could use the scripting block to make changes to the table when you push a button. That would likely require the help of a JavaScript developer (checkout the ‘work offered’ section if that’s the route you go).

Materials (table)

Name (single line text)
Material1
Material2
Matierial3

Project (linked record)
456789123
456789123
456789123

Status single select
Received
Received
On order

Projects (table)

Order Number (single line text)
456789123

Materials (linked record)
material1, material2, material3

Materials Status (lookup)
Received,Received,On order

Order Status (formula)
Not entirely received

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.