About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces Zapier Canvas Zapier Chatbots

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

  • Home
  • Get Help
  • How Do I
  • How to identify and move Airtable orders to "Closed Orders" based on invoice status?

How to identify and move Airtable orders to "Closed Orders" based on invoice status?

  • 1 December 2023
  • 9 replies
  • 31 views
C
Badge

Hello!

I have created an order management app through Airtable that tracks our open orders through their entire process. We import to Airtable through a Zapier Zap from an Excel file that was exported out of our POS system. We have a master key that comes out of our POS system and it is unique to each item in every single open order (each row is an item, not an order). Once the item status has been set to "Complete" I have it set up in Airtable to move to a "Completed Items" Kanban view.

We need a way for Airtable, Zapier, whichever to recognize when the item has been invoiced (paid for, left the building). When an order is invoiced it will not show up in our "Open Customer Orders" Excel export from the POS system any longer. (Makes sense, of course.) There will be no master key to map to.

My boss is wondering if there is any way to tell Airtable that this order has been "Invoiced" due to the fact that ALL Master keys under a given invoice number are set to complete. Once all master keys with the same invoice number are set to the status "Completed" it should move them to a holding space (like a "Closed Orders" grid view in Airtable which I have already created. It could work either way and we are open to suggestions. Looking for a solution to make this work. I apologize for the confusing explanation. I hope it explains alright. If you have any questions / need further information, please let me know. Thanks so much for the help, in advance!

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

9 replies

SamB
Rank
Userlevel 7
Badge +11

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! 😁

C
Badge

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!

SamB
Rank
Userlevel 7
Badge +11

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? 🤞

C
Badge

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.

SamB
Rank
Userlevel 7
Badge +11

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:
ffcf7fefe9aefe116356b41c8cabc922.png

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:
2f13c82cd6667d6ae7eda8f4c5fa2572.png
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! 🙂

C
Badge

@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!

C
Badge

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!

C
Badge

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!

SamB
Rank
Userlevel 7
Badge +11

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?

Terms & Conditions