Adding certain statuses

[Mike_Lopez]

Hello Community! 

I need some guidance... I have a few columns with fields that are Single Select and one of the options is "COMPLETED". I am looking for a way to create a formula for it to add "how many COMPLETED fields" I have for that record... For example if it was Excel I would try an "IF" formula or "COUNT" to add how many columns have the COMPLETED field, but here I just can't figure it out. Anyone know how to go about? 

[Nathan_Gupta]

Hi there! Off the bat, I'm not familiar with any ways to look across at all fields dynamically in a single row / "record". You could have one formula field at the very end that is one large If statement and checks all the fields you hardcode into it, but that's super clunky. You could also script, but that's also potentially overkill.

If each single select field is the exact same, you may want to consider using multiple tables and/or List views, but hard to tell what's best without seeing your use case.

Wishing you luck!

[Mike_Lopez]

Thanks for the reply Nathan! 

So to give a bit of context, let's call each record "CLIENT" and each column a task or step that needs to be completed. So let's say I have 5 columns with 5 steps to complete and each one says "In progress" or other similar statuses. What I want is to have a column that counts how many "completed" statuses there are ej. 1,2,3,4,5 and that way I can filter on that column and know which clients are almost out the door. 

Hope this helps and any help or thoughts are welcome! 🙂

[Nathan_Gupta · Accepted solution]

Hi Mike,

Your use case makes a lot of sense! I've definitely come across this desire before. A few work-arounds--none perfect (though the last one is elegant), but they should accomplish your overall goal.

So let's say your table starts off looking something like this:

Off the bat, I can see three avenues you might consider: 

1. Use formula fields to "count" statuses of interest.


First, you might create a *helper* formula field that essentially concatenates each of your individual status fields. (You'll have to hardcode the individual fields here.)

CONCATENATE({Client Placed Order}, " ", {Printed Shipping Label}, " ", {Packaged Box}, " ", {Invoice Sent to Client}, " ", {Client Paid}, " ", {Order Shipped})​

Then you might create a formula field that counts the number of times a word appears in that array. 

(LEN({All Statuses Array}) - LEN(SUBSTITUTE( {All Statuses Array},"Completed","")))/LEN("Completed")

The annoying thing with this approach is 1) it's not dynamic if you add in more fields, and 2) all your individual statuses need to be relatively similar (e.g. for the "Client Paid" field, I couldn't use "Paid". I had to use "Completed" so it would show up in the count). To alleviate the second concern, you might consider numbering your options (e.g. A. Pending, B. To Do, C. In Progress, D. Completed), so even if you do deviate from the "Completed" to "Paid" or "Shipped" nomenclature, you can still do a search for "D. " in your formula field. Note, I created the helper field because it just makes the second field's formula significantly cleaner. Definitely not necessary though.

2. Use a multi-select field with a formula field.


First you might create a multi-select field where each individual status field is accounted for in the new field.

 

Then you'll create a formula field which counts the number of statuses selected. Note: this is a hacky approach where we are counting commas (there isn't a clear count formula method for multiple select fields as far as I know)

IF(
    LEN(
        Status
        )=0,
        0,
        LEN(
            {Status}&','
            )-LEN(
                SUBSTITUTE(
                    {Status},
                    ',',
                    ''
                    )
                )
    )​

In this approach, we're removing a lot of the nuance in what the status could be. In effect, we're making everything binary. But, perhaps simplicity is best 🙂

3. Splitting this into 2 tables: "Clients" & "Interactions / Actions"

Now, this is the fun one (on the opposite end of simplicity). Although we're using Airtable to its fundamentals, we may be trying to hammer a nail with a cinderblock. 



Here, we sectioned off all the individual statuses as a separate table, and linked them to each client. This is perhaps your most scalable & extensible approach, with the opportunity for automation to be installed and auto-generate the interactions & corresponding links to clients. And even the "status" field in the Interactions table doesn't have to be a status field, it could be a generic "Description" textbox. This approach would also allow for richer information to be stored with each "interaction", e.g. let's say you wanted the individual people responsible for "boxing up a shipment" & "processing payment" to be tagged, this would allow for that. Or, if you wanted to save a copy of the invoice directly in your airtable, you could easily add a field for attachments in the "Interactions" table. However, if you're a spreadsheet lover (I'm a spreadsheet lover), then this is not as pretty as looking at a simple consolidated table with rows and columns giving you the individual states. 

Hope this helps 🙂

[Mike_Lopez]

WOW!! @Nathan_Gupta this is super awesome!! You are such an expert! Thanks so much!! I will try all of them and see how it goes but it's definitely what I was looking for and more!! 🙂

[Nathan_Gupta]

Happy to help 🙂