Hi @clairehunter, great question! 

Hmm, I wonder if it might be easiest to add a formula field (directly in Airtable) for the order record? One which returns a value of “Invoiced” only when all it’s linked item records are set as “Complete”. Then you could have your “Closed Orders” view only show an order record if the formula field’s value is “Invoiced”.

Then if you need to carry out subsequent actions once an order appears in that “Closed Orders” view you’d set up a Zap that uses the New Record (Airtable) trigger. That trigger has a “Limit to View” option so it could be set to only trigger when an order record appears in that “Closed Orders” view and add the actions carry out any necessary actions. 

Do you think that sort of approach could work?

Hello @SamB 

Thank you for your response. I do believe this would work. We have a unique identifier which is set as the master key. It will look something like this: “eoihsf0u2039uiwe9fw09sfcuhsk012”. Then there will be an invoice number. I believe the formula idea would work. Would you be able to help me set it up? I understand it would be something along the lines of: “IF (all master keys under with the same invoice number are set Status to “Complete” then change all pertaining rows’ Status to “Invoiced”.) I am unsure about how I would write this out. I will be working on trying to figure it out in the meantime. Thanks for your help!

Hello @SamB 

I definitely think that something like this would work. I tried to send a message earlier, however, it did not post. I was wondering if you would be able to help me with creating this formula. I know it would be a basis of: IF(All master keys with the same invoice number are set to Status = “Complete”, THEN move all to Status = “Invoiced”). I will be working on trying to create this in a formula. Would you know how to write something like this out? Thanks so much for your help!

@SamB 

Hi! I tried to send this message twice before. So, I apologize for the delayed response. Would you be able to help me write this formula? I will be trying to figure it out in the meantime. Thanks so much for your help!

Hi @clairehunter! 

Sorry for the delayed response from me here, it seems the previous messages were caught by a spam filter for some reason but have been approved now! 

I’m not an expert in Airtable by any means so their own Support Community might be a better place to get some help on the formula and set up. That said, I did some quick playing around in Airtable and assuming you’ve got two separate tables, one for the individual purchased items and another for the orders you’d need to do the following:

• Have the two tables linked together using a the Link to another record field in each table.
• In your Orders table, add a Rollup field and set it to roll up the statuses of the items (e.g. Complete or Incomplete). If all items are marked as complete it will output “Complete”. If at least one item is not complete it will output “Incomplete, Complete” and if all are unpaid it’ll be “Incomplete”.
• Then, (also in the Orders table) add a Formula field and use the following formula: IF({Status Rollup (from Purchased Items)}="Complete","Invoiced","Not invoiced")
• That formula should look at the list of item status in the rollup field, check if it is just “Complete” and will output the status of “Invoiced”. If any items in the order are still outstanding then it will output the status as “Not invoiced”

Here’s an example of how the Orders table would look:


Then in Airtable you’d configure your Closed Orders view to only show orders that have a status of Invoiced. And in your Zap, when setting up the trigger you’d select that specific Closed Orders view from the Limit to View field:

Which would allow the Zap to only trigger on new orders added into that Closed Orders view in Airtable.

Hope that helps to get you pointed in the right direction. Please do keep us updated on how you get on with this, keen to ensure you’re all set! 

Hello @SamB 

Thanks so much for the input! This is exactly what I am looking for. However, I only have one base (table) with multiple views. All of the information is within that one table. There are two fields I need to play off of. 1) “Master Key” which is unique to each given item in a given order. Ex. “oefh0293urijebfos9uqNoefh”. 2) Invoice Number. This field will be the same on multiple lines sometimes. For example: 3 lines with unique master keys have the same Invoice Number. I would like once all of the status’ are set to “Completed” move them to a new view or change all of their status’ to “Invoiced”. I appreciate your help so much. Thank you! I will try and post this on an Airtable forum.

I’m so sorry about the delay in replying here, @clairehunter! 

Unfortunately for the approach I suggested previously you’d need to use linked records from different tables within a single Airtable base. I don’t know of a way to handle it all within the same table but perhaps someone in Airtable’s community might know of a way.

Were you able to get an solution after posting in their support forum? 

Hello @SamB No worries! I did make a post in Airtable’s forum with no response. I will keep trying to figure it out. Thanks so much for your help!

Thanks for letting me know, @clairehunter. Hopefully someone on their side will have a solution for you soon.

If we can help with anything else in the meantime just let us know!