Re: Formula limit? airtable shortens my formula?

East_Lima_Realt · ‎Jun 23, 2022

is there a formula limit in airtable? i wish to compute for the payment rental reminder. could be every 2 months or every 3 months. but upon saving, airtable is shortening it to half of what i type. what’s happening?

IF(AND(FIND(“Bank Transfer Regular”, {Payment Term}), Status= “Active”),SWITCH(AND(FIND(“2 months advance”, {Payment Term}),{Contract Start}< DATEADD({Contract Start}, 2, ‘month’)), DATETIME_FORMAT(DATEADD({Contract Start}, 2, ‘month’),‘MM-DD-YYYY’)),
SWITCH(AND(FIND(“2 months advance”, {Payment Term}),TODAY()> DATEADD({Contract Start}, 2, ‘month’), (TODAY()< DATEADD({Contract Start}, 4, ‘month’))), DATETIME_FORMAT(DATEADD({Contract Start}, 4, ‘month’),‘MM-DD-YYYY’)),SWITCH(AND(FIND(“2 months advance”, {Payment Term}),TODAY()> DATEADD({Contract Start}, 4, ‘month’), (TODAY()< DATEADD({Contract Start}, 6, ‘month’))), DATETIME_FORMAT(DATEADD({Contract Start}, 6, ‘month’),‘MM-DD-YYYY’)), SWITCH(AND(FIND(“2 months advance”, {Payment Term}),TODAY()> DATEADD({Contract Start}, 6, ‘month’), (TODAY()< DATEADD({Contract Start}, 8, ‘month’))), DATETIME_FORMAT(DATEADD({Contract Start}, 8, ‘month’),‘MM-DD-YYYY’)), SWITCH(AND(FIND(“2 months advance”, {Payment Term}),TODAY()> DATEADD({Contract Start}, 8, ‘month’), (TODAY()< DATEADD({Contract Start}, 10, ‘month’))), DATETIME_FORMAT(DATEADD({Contract Start}, 10, ‘month’),‘MM-DD-YYYY’)))

Ben_Young1 · ‎Jun 24, 2022

Hey @East_Lima_Realty!

I’ve written a much longer formula than the one you provided and haven’t encountered the same issue.

I do, however, think I see what the problem is.
It seems that you’re trying to create a nested IF formula.

Here’s the exact same formula you posted but with a bit of formatting:

IF(
    AND(
        FIND(
            “Bank Transfer Regular”,
            {Payment Term}
        ),
        Status = “Active”
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            {Contract Start} < DATEADD({Contract Start}, 2, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start},2,‘month’),
            ‘MM-DD-YYYY’
        )
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 2, ‘month’),
            TODAY() < DATEADD({Contract Start}, 4, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start},4,‘month’),
            ‘MM-DD-YYYY’
        )
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 4, ‘month’),
            TODAY() < DATEADD({Contract Start}, 6, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start},6,‘month’),
            ‘MM-DD-YYYY’)
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 6, ‘month’),
            TODAY()< DATEADD({Contract Start}, 8, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start}, 8, ‘month’),
            ‘MM-DD-YYYY’
        )
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 8, ‘month’),
            TODAY()< DATEADD({Contract Start}, 10, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start}, 10, ‘month’),‘MM-DD-YYYY’
        )
    )
)

The IF formula syntax is: IF(statement, a, b).
There’s a few errors, but the main reason for the formula failure is that you wrote the formula in this syntax: IF(condition, a, b, c, d, e).

Here’s a version of your formula that fits within the formula syntax.
I have also refactored the formula body to be a bit more efficient in its flow.

IF(
    AND(
        FIND(
            “Bank Transfer Regular”,
            {Payment Term}
        ),
        FIND(
            "2 months advance",
            {Payment Term}
        ),
        {Status} = “Active”
    ),
    IF(
        {Contract Start} < DATEADD({Contract Start}, 2, 'month'),
        DATETIME_FORMAT(
            DATEADD({Contract Start}),
            'MM-DD-YYYY'
        ),
        IF(
            AND(
                TODAY() > DATEADD({Contract Start}, 2, 'month'),
                TODAY() < DATEADD({Contract Start}, 4, 'month')
            ),
            DATETIME_FORMAT(
                DATEADD({Contract Start}, 4, 'month'),
                'MM-DD-YYYY'
            ),
            IF(
                AND(
                    TODAY() > DATEADD({Contract Start}, 4, 'month'),
                    TODAY() < DATEADD({Contract Start}, 6, 'month')
                ),
                DATETIME_FORMAT(
                    DATEADD({Contract Start}, 6, 'month'),
                    'MM-DD-YYYY'
                ),
                IF(
                    AND(
                        TODAY() > DATEADD({Contract Start}, 6, 'month'),
                        TODAY() < DATEADD({Contract Start}, 8, 'month')
                    ),
                    DATETIME_FORMAT(
                        DATEADD({Contract Start}, 8, 'month'),
                        'MM-DD-YYYY'
                    ),
                    IF(
                        AND(
                            TODAY() > DATEADD({Contract Start}, 8, 'month'),
                            TODAY() < DATEADD({Contract Start}, 10, 'month')
                        ),
                        DATETIME_FORMAT(
                            DATEADD({Contract Start}, 10, 'month'),
                            'MM-DD-YYYY'
                        )
                    )
                )
            )
        )
    )
)

You should be able to just copy and paste that into your formula field to see how it behaves.
I do recommend that you check to make sure that the formatting on your field names is correct, or else it will toss you an error.

Let me know if I’m misunderstanding something or if something breaks with the formula.
Definitely feel free to let me know if you have any questions!

Edited for clarity

kuovonne · ‎Jun 24, 2022

The version of the formula that I see has curly quotes in places that need straight quotes.

I haven’t actually read the formula, and it seems to have a lot going on. (It looks inside-out to me.) I just wanted to give this warning.

Ben_Young1 · ‎Jun 24, 2022

Yeah… the formula is relatively dense.
I spent a bit too long last night pulling it apart and cleaning it.

@East_Lima_Realty - Kuovonne is correct. I tend to get lazy in my formulas and code when it comes to my quotes; keep an eye out for them if you try to use the formula.

Alexey_Gusev · ‎Jun 24, 2022

IF(AND(
      FIND("Bank Transfer Regular",{Payment Term}),
      FIND("2 months advance",{Payment Term}),
      {Status} = "Active",
      {Contract Start},
      IS_AFTER(TODAY(),{Contract Start})
    ),

DATETIME_FORMAT(
DATEADD({Contract Start},2+2*INT(DATETIME_DIFF(TODAY(),{Contract Start},'months')/2),'months'),'MM-DD-YYYY')
)