IF formula question

[Wayne_Merlino]

Hi Airtable Community,

I am having an issue with a formula that I was hoping to get some help on. Here is the issue:

We do our invoicing on Airtable. We have a few different types / categories of invoices - they are as follows:

Quotes: This is what we use when we have to submit a price 1st before any approvals to begin have been granted.

Suppleental: This is like a change order - we use this when the job has changed and or scope was added.

Estimate: This is used  when we have submitted a price but it has not been approved - what we call "settled"

Invoice: This is what we use when we have submitted an estimate and it has been approved. 

We have the primary field be a concatenation field that takes the name  of the customer, the type of work it is, and then looks to the "(MIT) Settled Date" field and if it is empty, uses the word "ESTIMATE" as the last word of the primary name. The thinking here is that when there is no date in the "(MIT) Settled Date" field, it has not been approved - therefore, per above definitions, it is not an Invoice yet but still an Estimate. This works fine. My problem is with the "Quotes" and "Supplementals".

The way that we define a "Quote" or a "Supplemental" is a checkbox. If it is checked, the formula says to make the name the concatenation version with the "Quote" or "Supplemental" ending on the name, depending on what is picked. Our workflow goes from quote to estimate to invoice - although sometimes we can go from quote to invoice. Supplementals are just that - a supplemental part - a way to add additional work to a record without having to modify the original record. Sort of like a related but separate charge. 

What I want the primary field to do is to create a name based on where it is at in the life of the record. If it is a quote, I want the last word on the name to say "Quote". If it is a supplemental (unsettled per above) then I want the last word in the name to be "Supplemental Estimate". If it is an estimate, I want the last word in the name to say "Estimate". I only want the last word in the name to say "Invoice" IF there is a date in the "(MIT) Settled Date" field. That is how we are defining that it is in fact an Invoice. 

Here is my formula:

IF({Quote Only?}=1,CONCATENATE({Customer Name}," - ",{Invoice Type}," - ","QUOTE ONLY"),IF({Is this a Supplemental?}=1,CONCATENATE({Customer Name}," - ",{Invoice Type}," - ","SUPPLEMENTAL"," ESTIMATE"),

IF({(MIT) Settled Date:}=(""),CONCATENATE({Customer Name}," - ",{Invoice Type}," - ","ESTIMATE"),(CONCATENATE({Customer Name}," - ",{Invoice Type}," - ","INVOICE")))))

What i am having problems with are the supplemental ones. The date being added in the "(MIT) Settled Date" field does not seem to be overriding the first conditions and it stays as a "Supplemental Estimate". It does not change the name to be "Supplemental Invoice" as the others do. 

Can anyone help with with this please? By the way - I am using a temporary field to mimic what I want the primary field to be - basically doing this until I get it right so I don't mess anything up right now. So the name of the field that is mimicking the primary field is: "12-26-23 test WM". Once I get the formula right I will copy it to the primary field then delete this field

[Ben_Young1]

Hey @Wayne_Merlino! 

I'm admittedly a bit confused, but give this a shot and let me know where you end up:

TRIM(
    IF(
        {Customer Name},
        TRIM({Customer Name}) &
            IF(
                OR(
                    {Invoice Type},
                    {Quote Only?},
                    {Is this a Supplemental?},
                    {(MIT) Settled Date:}
                ),
                ' - '
            )
    )
    &
    IF(
        {Invoice Type},
        TRIM({Invoice Type}) &
            IF(
                OR(
                    {Quote Only?},
                    {Is this a Supplemental?},
                    {(MIT) Settled Date:}
                ),
                ' - '
            )
    )
    &
    IF(
        {Quote Only?},
        'QUOTE ONLY',
        IF(
            AND(
                {Is this a Supplemental?},
                {(MIT) Settled Date:}
            ),
            'SUPPLEMENTAL INVOICE',
            IF(
                {Is this a Supplemental?},
                'SUPPLEMENTAL ESTIMATE'
            )
        )
    )
)

Here's the plain language logic:

1. If Quote Only? returns true, always return 'QUOTE ONLY', otherwise...
2. If both Is this a Supplemental? and (MIT) Settled Date: return true, always return 'SUPPLEMENTAL INVOICE', otherwise...
3. If Is this a Supplemental? returns true, return 'SUPPLEMENTAL ESTIMATE'.
4. If none of those conditions are met, then only the Customer Name and/or Invoice Type fields are returned and spaced with a hyphen if both are not empty.

[Wayne_Merlino]

Hey Ben,

I think this could work - I just added it into the table and it works for the Quote and Supplemental, but there are no names beyond estimate type - meaning "Estimate" or "Invoice" when I add a date into the settled field. Is there a way to have the word "Estimate" added to the names as the baseline starting point? 

[Ben_Young1 · Accepted solution]

Okay. Give this a shot?

TRIM(
    IF(
        {Customer Name},
        TRIM({Customer Name}) &
            IF(
                OR(
                    {Invoice Type},
                    {Quote Only?},
                    {Is this a Supplemental?},
                    {(MIT) Settled Date:}
                ),
                ' - '
            )
    )
    &
    IF(
        {Invoice Type},
        TRIM({Invoice Type}) &
            IF(
                OR(
                    {Quote Only?},
                    {Is this a Supplemental?},
                    {(MIT) Settled Date:}
                ),
                ' - '
            )
    )
    &
    IF(
        {Quote Only?},
        'QUOTE ONLY ',
        IF(
            {Is this a Supplemental?},
            'SUPPLEMENTAL '
        )
    )
    &
    IF(
        {(MIT) Settled Date:},
        'INVOICE',
        'ESTIMATE'
    )
)

[Wayne_Merlino]

Fantastic! Totally worked. Thank you very much - I never would have gotten there without you.