Re: Correct Nested IF showing blank instead of result

Expert_Credit_S · ‎Jul 23, 2019

I’m building something for my client wherein they choose a single-select and it will show results for 6 total fields based off that selection:

i.e. If x = selection1, then 1 = A, 2 = B, 3 = C, 4 = D, 5 = E, 6 = F
If x = selection 2, then 1 = B, 2 = C, 3 = D, 4 = E, 5 = F, 6 = G
If x = selection 3, then 1 = C, 2 = D, 3 = E, 4 = F, 5 = G, 6 = H

I’ve included screenshots and my formulas below and I cannot figure out WHY “Round 3” shows blank when I’m like 100% positive that the formula is correct. Please help!

Formula 1 for “Round 1”:
IF(T({Choose Item Type & Round}) = “charge-offDOLA”, “remove this account due to the fact that there are different dates last active reported. How can there be different dates when this is what stipulates when the account naturally falls off? You can’t report different months and different years!”,
IF(T({Choose Item Type & Round}) = “charge-offDOLP”, “date last paid shows mutliple dates and this makes it look like I paid when I didn’t and didn’t when I paid. How is that possible? How can there be years between bureaus? You need to remove this account immediately for inaccuracy!”,
IF(T({Choose Item Type & Round}) = “charge-offMONTHPAY”, “you cannot report a monthly payment on a closed account. There should not be anything listed here after this account was closed. Remove for violation.”,
IF(T({Choose Item Type & Round}) = “charge-offPAYHIST”, “remove for inaccurate payment history! How can 1 bureau report that I was on time while another reports that I was late and the 3rd reports nothing at all? I was either late or on time. There are mutiple dates listed like this.”,
IF(T({Choose Item Type & Round}) = “charge-offBALANCE”, “I don’t owe the balance listed and unless you have paperwork that can be furnished to prove otherwise. Remove for inaccuracy”,
IF(T({Choose Item Type & Round}) = “charge-off1STDELINQ”, “1st date of delinquency is inaccurate and this account needs to be removed because of it. This date throws off the entire history of this account as well as date last active. Remove immediately.”,
IF(T({Choose Item Type & Round}) = “charge-offHIGHBAL”, “high balance is lower than the balance [or if that is not true] the high balance on this account is inconsistent from bureau to bureau and there can only be 1 high balance on this account! Unless you can provide documentation directly from the creditor stipulating that the information you are reporting is correct including the exact dollar amount, this must be removed for inaccuracy.”,
IF(T({Choose Item Type & Round}) = “charge-offOPEND”, “open date reflects multiple inaccuracies and this account must be removed: mutliple dates reported as date opened and incorrect date reported by your organization.”,
IF(T({Choose Item Type & Round}) = BLANK(),BLANK())))))))))

Formula for "Round 2:

IF(T({Round 1}) = “remove this account due to the fact that there are different dates last active reported. How can there be different dates when this is what stipulates when the account naturally falls off? You can’t report different months and different years!”, "date last paid shows mutliple dates and this makes it look like I paid when I didn’t and didn’t when I paid. How is that possible? How can there be years between bureaus? You need to remove this account immediately for inaccuracy! ",
IF(T({Round 1}) = “date last paid shows mutliple dates and this makes it look like I paid when I didn’t and didn’t when I paid. How is that possible? How can there be years between bureaus? You need to remove this account immediately for inaccuracy!”, “you cannot report a monthly payment on a closed account. There should not be anything listed here after this account was closed. Remove for violation.”,
IF(T({Round 1}) = “you cannot report a monthly payment on a closed account. There should not be anything listed here after this account was closed. Remove for violation.”, “remove for inaccurate payment history! How can 1 bureau report that I was on time while another reports that I was late and the 3rd reports nothing at all? I was either late or on time. There are mutiple dates listed like this.”,
IF(T({Round 1}) = “remove for inaccurate payment history! How can 1 bureau report that I was on time while another reports that I was late and the 3rd reports nothing at all? I was either late or on time. There are mutiple dates listed like this.”, “I don’t owe the balance listed and unless you have paperwork that can be furnished to prove otherwise. Remove for inaccuracy”,
IF(T({Round 1}) = “I don’t owe the balance listed and unless you have paperwork that can be furnished to prove otherwise. Remove for inaccuracy”, “1st date of delinquency is inaccurate and this account needs to be removed because of it. This date throws off the entire history of this account as well as date last active. Remove immediately.”,
IF(T({Round 1}) = “1st date of delinquency is inaccurate and this account needs to be removed because of it. This date throws off the entire history of this account as well as date last active. Remove immediately.”, “high balance is lower than the balance [or if that is not true] the high balance on this account is inconsistent from bureau to bureau and there can only be 1 high balance on this account! Unless you can provide documentation directly from the creditor stipulating that the information you are reporting is correct including the exact dollar amount, this must be removed for inaccuracy.”,
IF(T({Round 1}) = “high balance is lower than the balance [or if that is not true] the high balance on this account is inconsistent from bureau to bureau and there can only be 1 high balance on this account! Unless you can provide documentation directly from the creditor stipulating that the information you are reporting is correct including the exact dollar amount, this must be removed for inaccuracy.”, “open date reflects multiple inaccuracies and this account must be removed: mutliple dates reported as date opened and incorrect date reported by your organization.”,
IF(T({Round 1}) = “open date reflects multiple inaccuracies and this account must be removed: mutliple dates reported as date opened and incorrect date reported by your organization.”, “I don’t believe that you did an independent investigation and this account needs to be removed unless you can prove that you did not just parrot what the creditor told you. If you only utilized an electronic response, then this account was not legally investigated and must be removed.”,
IF(T({Round 1}) = BLANK(),BLANK())))))))))

Formula for “Round 3” *** this is where the problem is:

IF(T({Round 2}) = “date last paid shows mutliple dates and this makes it look like I paid when I didn’t and didn’t when I paid. How is that possible? How can there be years between bureaus? You need to remove this account immediately for inaccuracy!”, “you cannot report a monthly payment on a closed account. There should not be anything listed here after this account was closed. Remove for violation.”,
IF(T({Round 2}) = “you cannot report a monthly payment on a closed account. There should not be anything listed here after this account was closed. Remove for violation.”, “remove for inaccurate payment history! How can 1 bureau report that I was on time while another reports that I was late and the 3rd reports nothing at all? I was either late or on time. There are mutiple dates listed like this.”,
IF(T({Round 2}) = “remove for inaccurate payment history! How can 1 bureau report that I was on time while another reports that I was late and the 3rd reports nothing at all? I was either late or on time. There are mutiple dates listed like this.”, “I don’t owe the balance listed and unless you have paperwork that can be furnished to prove otherwise. Remove for inaccuracy”,
IF(T({Round 2}) = “I don’t owe the balance listed and unless you have paperwork that can be furnished to prove otherwise. Remove for inaccuracy”, “1st date of delinquency is inaccurate and this account needs to be removed because of it. This date throws off the entire history of this account as well as date last active. Remove immediately.”,
IF(T({Round 2}) = “1st date of delinquency is inaccurate and this account needs to be removed because of it. This date throws off the entire history of this account as well as date last active. Remove immediately.”, “high balance is lower than the balance [or if that is not true] the high balance on this account is inconsistent from bureau to bureau and there can only be 1 high balance on this account! Unless you can provide documentation directly from the creditor stipulating that the information you are reporting is correct including the exact dollar amount, this must be removed for inaccuracy.”,
IF(T({Round 2}) = “high balance is lower than the balance [or if that is not true] the high balance on this account is inconsistent from bureau to bureau and there can only be 1 high balance on this account! Unless you can provide documentation directly from the creditor stipulating that the information you are reporting is correct including the exact dollar amount, this must be removed for inaccuracy.”, “open date reflects multiple inaccuracies and this account must be removed: mutliple dates reported as date opened and incorrect date reported by your organization.”,
IF(T({Round 2}) = “open date reflects multiple inaccuracies and this account must be removed: mutliple dates reported as date opened and incorrect date reported by your organization.”, “I don’t believe that you did an independent investigation and this account needs to be removed unless you can prove that you did not just parrot what the creditor told you. If you only utilized an electronic response, then this account was not legally investigated and must be removed.”,
IF(T({Round 2}) = “I don’t believe that you did an independent investigation and this account needs to be removed unless you can prove that you did not just parrot what the creditor told you. If you only utilized an electronic response, then this account was not legally investigated and must be removed.”, “Please send me everything that you used during this investigation to determine the accuracy of this account. Additionally, I would like to see a detailed report that provides the names, dates and method of verification. If this cannot be provided within 15 days, this account is falsely reported and must be removed for violation.”,
IF(T({Round 2}) = BLANK(),BLANK())))))))))

Justin_Barrett · ‎Jul 23, 2019

First off, these formulas are WAY more complex than they really need to be, which may be contributing to the problem you’re having with troubleshooting the system. My first recommendation is to take those long pieces of text and put each one into its own long text field in a single record in a new table (I’ll call this table [Responses]). To keep things really simple, you could even name those fields closer to what you had in your initial example. For example, you could put the first response in the {Round 1} formula:

remove this account due to the fact that there are different dates last active reported. How can there be different dates when this is what stipulates when the account naturally falls off? You can’t report different months and different years!

…into a field named {A}, the first {Round 2} response into a field named {B}, and so on. With that table built, link all of these records in [Table 1] to that single record in [Responses], then add lookup fields to pull them into [Table 1], using the same field names. Now you can use them as reference in all of your formulas. Need to change the text? Change them in the [Responses] table, and everything updates automatically.

Not only that, but you can further simplify it by using SWITCH instead of nested IF functions. SWITCH is ideal for this situation: you’re checking a single field’s contents, and switching the output based on what you find. You also don’t need to wrap the single-select field in T(), as it’s already full text. Finally, there’s no need to check for BLANK() at the end. Airtable automatically makes a field blank if all the prior tests fail.

With those changes applied, {Round 1} becomes this:

SWITCH(
    {Choose Item Type & Round},
    "charge-offDOLA", A,
    "charge-offDOLP", B,
    "charge-offMONTHPAY", C,
    "charge-offPAYHIST", D,
    "charge-offBALANCE", E,
    "charge-off1STDELINQ", F,
    "charge-offHIGHBAL", G,
    "charge-offOPEND", H
)

The {Round 2} formula becomes similarly easy, because it can use those same text fields for comparison:

SWITCH(
    {Round 1},
    A, B,
    B, C,
    C, D,
    D, E,
    E, F,
    F, G,
    G, H,
    H, I
)

…and so on through the rest of the rounds.

Expert_Credit_S · ‎Jul 24, 2019

thank you very much. ALL of my formulas in all of the bases I’ve built use the nested if and I’ve considered using switch but couldn’t get it right.

I am going to go thru this piece by piece as you’ve listed and rebuild. thank you for taking the time to answer this.

Expert_Credit_S · ‎Jul 24, 2019

How do you add a lookup field AND add the formula field? Maybe this is common sense but I am not seeing it. Obviously, they are 2 different fields but is it as simple using 2 fields or is there a way to add a lookup field into a formula? Please advise. thanks!

Justin_Barrett · ‎Jul 24, 2019

The basic format for SWITCH is this:

The first item is the thing you want to check. It can be a field, or it can be a simple formula that operates on a field, but it must result in a static value (usually a string or number).

The other items in the function are in pairs. The first item in each pair is one possible option that could be found, and the second item in the pair is the result to return if that thing is found.

Using my suggestion for your {Round 1} as an example:

SWITCH(
    {Choose Item Type & Round},
    "charge-offDOLA", A,
    "charge-offDOLP", B,
    "charge-offMONTHPAY", C,
    "charge-offPAYHIST", D,
    "charge-offBALANCE", E,
    "charge-off1STDELINQ", F,
    "charge-offHIGHBAL", G,
    "charge-offOPEND", H
)

This SWITCH function is looking at what’s in {Choose Item Type & Round}. If it finds “charge-offDOLA”, then it will return what’s in {A}. If it finds “charge-offDOLP”, it will return what’s in {B}, and so on. I like to format SWITCH functions across multiple lines because it makes the pairs easier to visualize.

I just happened to be typing this while you added this question, so I’ll address it here.

Sorry for not being more clear with my description. I’ll backtrack a bit to (I hope) make it more clear.

In the [Responses] table, you’ll have a single record. For the primary field, just label it “Responses” for clarity. Your next field for that record will be named {A}, and will contain that first response paragraph. You’ll make similar fields for the second response in {B}, the third in {C}, etc.

Back in [Table 1] (which I suggest renaming, but I’ll use that for now), you’ll add a link field that points to [Responses]. In all the records in [Table 1], that link field will point to that lone “Responses” record. To make this easy, make the link in the top-most record, then click the square in the corner and drag-fill down the column until all records have the same link (similar to drag-filling cells in Excel).

Now make a lookup field named {A}. Set it to refer to the [Responses] link you just made, and tell it that you want to pull the value from the {A} field in [Responses]. Make another lookup field that grabs what’s in {B}, and so on.

Now in your formulas in [Table 1], you simply use A, B, etc. to refer to the contents of those lookup fields.

Does that clarify things?

Expert_Credit_S · ‎Jul 24, 2019

thank you very much. I’ve gotten all my “responses” into another table and will now figure out the lookup. :slightly_smiling_face: thank you for the super detailed explanation

Expert_Credit_S · ‎Jul 24, 2019

OK, so I thought that everything was all good until I got to entering the formula for round 2. I’m including a video so that you can see what I’m talking about:

the formula works fine for round 1 but when I do the same thing a - b for round 2 I get the formula error

Justin_Barrett · ‎Jul 24, 2019

Okay, there are a couple things amiss from what I see in the video. First off, the [Responses] table should have all the responses in consecutive fields in a single record, kind of like this (truncated for brevity):

The idea behind this table is that it stores every response you need, one per field, in just one record, and then the lookups from the other table pull all of that in and lets you compare and assign as needed.

The other issue is actually an error in my recollection of a restriction with the SWITCH function. The thing you’re looking for—the first item in each two-item pair—is what has to be a static value. It can’t come from a formula, lookup, etc. So while SWITCH will work for {Round 1}, you’ll have to go with nested IF functions for the other rounds. However, you can still take advantage of the looked-up field contents to keep your formulas much shorter. Here’s how the {Round 2} formula would look:

IF(
    {Round 1} = A, B,
    IF(
        {Round 1} = B, C,
        IF(
            {Round 1} = C, D,
            IF(
                {Round 1} = D, E,
                IF(
                    {Round 1} = E, F,
                    IF(
                        {Round 1} = F, G,
                        IF(
                            {Round 1} = G, H,
                            IF(
                                {Round 1} = H, I
                            )
                        )
                    )
                )
            )
        )
    )
)

The other rounds would use the same formula, but with the alphabet references shifted ahead by one letter for each round.

Expert_Credit_S · ‎Jul 24, 2019

thank you. I DID have all of them listed in one record going across but when I did that, it only showed results for the first 4 (a-d). I then went and looked at the results it provided and noticed that it was doing it because it was all in 1 line like that so I had to adjust. I don’t get it…I will go back and put them in a row again and see what happens :slightly_smiling_face:
thank you!

Expert_Credit_S · ‎Jul 24, 2019

everything disappears if there’s only 1 record: