Hey There Community!
I’m very quickly learning Airtable thanks to it’s many resources including these forums so I’m hoping somebody will have the answer to what I’m looking for. I am trying to track what I thought seemed like a pretty simple process. And I may have gone a little further than I had planned so here goes:
I have an “Order Items” Table with a Status Field, Payment Method, Delivery Method and Due Date. Multiple Records in this field are connected to Records in another Table for “Orders”. Each Recird in “Orders” sometimes contains multiple Order Items.
So my goal is to allow There to be a Formula for Order Status in the “Orders” Table that explains the orders process based on a few factors. These Factors Are:
The Status of all items that belong to the order, (Single Select, Order Items Table)
The Payment Method Chosen, (Single Select, Orders Table)
The Delivery Method Chosen, (Single Select, Orders Table)
A Checkbox that confirms an order has been Completed (Orders Table)
and a Due Date (Orders Table)
I am trying to eliminate the need to change the status of an order independent from the status of the items in the order. I am hoping to have an output for these scenarios to help make things easier on the eyes of my coworkers as Airtable can be a little overwhelming for those who aren’t already in the the data entry world at all or very savvy with computers. We also have multiple people involved in different steps of the process so I’m trying to ease the communication as well.
Here’s my best explanation of the Statuses I want to output.
If ANY Item Status’ in an order are Pending , Order Status should = “Pending”
If Any Item Status’ in an order are “Pending Materials”, Order Status = “Pending Materials”
If all items are either “in Progress” or “Complete”, Order = “In Progress”
Once All Items are Complete, Order Status = “Ready for Payment”
If Payment Method is Empty or “Not Paid”
If Payment Method is either “E-Transfer” or “Paid” or “Cash”
Order Status is "Ready for {Insert Delivery Method} "…
I’ve tried a few Nested IF Formulas but I can’t get the complexity I want no matter how many different formulas I try! Thanks in advance folks!
…This may be reaching for too much here, but I’m also trying to find a way to add a notification for orders that have been set to “Complete” after the Due Date. I don’t want all orders that have a due date before today because eventually that would be all orders…but only the ones that were changed to “complete” after the due date… I’d like orders that have been fufilled late to get some extra recognition…
