Formula to return data based on multiple fields?

[Dakota_Bushnell]

Hello fellow Airtablers!

I’m in a bit of a bind trying to put together a formula for what I’m trying to do here.

I have 3 different single select fields, which based on if they meet certain criteria, will populate another formula field that’ll show “Complete” or “Incomplete”. That formula is working wonderfully. However, I’m trying to come up with a formula that’ll state the reason as to Why it’s showing as incomplete.

I can sort of get it to work for one field, but not multiple.

Here is the formula I have for the field that populates with “Complete” or “Incomplete”:

IF(AND(Status = "Done", {Billing Status} = "Payment Received", {E-file Status} = "Accepted", {Billed}!=BLANK()), "✔ Complete", "❌ Incomplete")

Here’s an image of what it looks like:

As you can see, it’s marked as Incomplete, because the billing status is not marked as “Payment Received”.

I’m attempting to create a column after the {Final Status} column that will show why it’s marked “Incomplete”. Is this possible?

Any help or suggestions are greatly appreciated. I’m still a bit new to Airtable and am trying to learn as much as I can.

Thanks in advance!

[Dakota_Bushnell]

I’ve been playing around with it and "Sort of" got it to work. Granted I’m sure my formula’s quite sloppy.

Here’s my current formula:

IF({Status} != "Done", {Status}) & IF({Billing Status} != "Payment Received", {Billing Status}) & IF({Billed} = BLANK(), "Missing Billed Amount") & IF({E-file Status} != "Accepted", {E-file Status})

I’ve got it to pull from those fields if they don’t meet certain criteria, but now how do I separate them properly?

[Jeremy_Oglesby]

Since you are using concatenation (&), you can just concatenate arbitrary text in-between with each conditional.

For example:

IF({Status} != "Done", {Status} & " || ") &
IF({Billing Status} != "Payment Received", {Billing Status} & " || ") &
IF({Billed} = BLANK(), "Missing Billed Amount || ") &
IF({E-file Status} != "Accepted", {E-file Status})

You can use any other separating characters you want if you don’t like the pipe characters.

[Dakota_Bushnell]

I did think of that as well, and tried doing the ", " to separate them.

Maybe I’m just a neat freak or a bit of a perfectionist, but it ends up looking sloppy.

For example, what if it only meets the criteria for one of the IF statements; It’d look something like:

“Done,” with nothing after it.

Same would go for the “||” pipes; “Done ||”

Is there a way to only include separators if there is more than one criteria met?
Does that make sense?

[Jeremy_Oglesby]

Yep… just takes a good bit of logic is all :slightly_smiling_face:

[Dakota_Bushnell]

Mind pointing me in the right direction? I’m trying my best to learn and practice what I do end up learning.

[Jeremy_Oglesby]

IF({Status} != "Done", {Status}) &
IF(
  OR(
    {Billing Status} != "Payment Received",
    {Billed} = BLANK(),
    {E-file Status} != "Accepted"
  ),
  " || "
) &
IF({Billing Status} != "Payment Received", {Billing Status}) &
>>Insert IF(OR()) here to print separator<<
IF({Billed} = BLANK(), "Missing Billed Amount") &
>>Insert IF(OR()) here to print separator<<
IF({E-file Status} != "Accepted", {E-file Status})

I’d suggest getting yourself a good text editor for writing your complex Airtable formulas – writing them in Airtable’s built-in formula editor is less than pleasant. I use Visual Studio Code myself.

It looks like this:

[Dakota_Bushnell]

I’m having issues getting the code to work.

Are the “>>Insert IF(OR))” sections preventing me, do you think?

I’m trying to understand the logic of it all.

[Dakota_Bushnell]

I did also download Visual Studio Code, but I’ll have to play around with that later to get a better understanding of how it works.

[Jeremy_Oglesby]

Yes, sorry @Dakota_Bushnell, I was not very clear there.

I wrote the first bit of IF(OR()) logic for you after the first field check.

The spots between the >> ... << are where I intended for you to try your hand at writing an IF(OR()) statement to do something similar, for learning purposes.