Trying to apply If,then Xor logic onto a concatenated output. Please help

[Clay_Hurst]

Here is the code I am trying to save but keep getting errors. Any ideas?

IF(OR({Do you have all the Information needed}=“No”,{Do you have all the Material you need}=“No”),CONCATENATE({My Name},“Can’t perform”{Production Goal for the day/Week:})

[Justin_Barrett · Accepted solution]

You’re missing a comma after “Can’t perform”, and a closing parenthesis for the IF function.

A couple more tips:

1. You can use the & operator as an abbreviated way to concatenate multiple pieces.
2. Remember to add spaces inside literal strings that will be concatenated with field data.
3. Building formulas in a text editor and using indentation to help ensure that you have matching parenthesis pairs makes the process a lot easier.

In your case these tweaks would look like this:

IF(
    OR(
        {Do you have all the Information needed}="No",
        {Do you have all the Material you need}="No"
    ),
    {My Name} & " can’t perform " & {Production Goal for the day/Week:}
)

[Clay_Hurst]

Good tips my brother. I appreciate it.

[Clay_Hurst]

Justin,
What am I missing here: I can’t get the formula to save:

IF(
AND(
{Do you have all the Information needed}=“No”,
{Do you have all the Material you need}=“No”
),
{My Name} & " Needs Information and Material to perform " & {Production Goal for the day/Week:}

IF (
{Do you have all the Information needed}=“Yes”,
{Do you have all the Material you need}=“No”
),
{My Name} & " Needs Material to perform " & {Production Goal for the day/Week:}

IF (
{Do you have all the Information needed}=“No”,
{Do you have all the Material you need}=“Yes”
),
{My Name} & " Needs Information to perform " & {Production Goal for the day/Week:}
)))

[Justin_Barrett]

1. You’re missing a comma after each of the first two messages you’re concatenating, after the closing curly brace, and before the next IF begins.
2. Your second and third IF functions are missing a surrounding AND for the two comparisons you’re making in each.

Here’s the correction:

IF(
    AND(
        {Do you have all the Information needed}="No",
        {Do you have all the Material you need}="No"
    ),
    {My Name} & " Needs Information and Material to perform " & {Production Goal for the day/Week:},
    IF (
        AND(
            {Do you have all the Information needed}="Yes",
            {Do you have all the Material you need}="No"
        ),        
        {My Name} & " Needs Material to perform " & {Production Goal for the day/Week:},
        IF (
            AND(
                {Do you have all the Information needed}="No",
                {Do you have all the Material you need}="Yes"
            ),
            {My Name} & " Needs Information to perform " & {Production Goal for the day/Week:}
        )
    )
)

Seeing so much repetition in the comparisons you’re making, I also came up with this slightly shorter version:

IF(
    OR(
        {Do you have all the Information needed}="No",
        {Do you have all the Material you need}="No"
    ),
    {My Name} & " Needs " & SWITCH(
        {Do you have all the Information needed} & {Do you have all the Material you need},
        "NoNo", "Information and Material",
        "YesNo", "Material",
        "NoYes", "Information"
    ) & " to perform " & {Production Goal for the day/Week:}
)

[Clay_Hurst]

I like that slightly shorter version. More efficient and thus more elegant. Very inventive too by the way. You are a bad @$$. Thank you sir.

[Clay_Hurst]

Justin,
I modified it a little bit by adding a 3rd condition. However, I do have a question, the below SWITCH did not print to the concatenated sentence. This was the result, “John Doe 3rd Needs to perform Installation of Prefab Headboards.” (He actually needed material)

Here’s the code:

IF(
OR(
{Do you have all the Tooling you need}=“No”,
{Do you have all the Information needed}=“No”,
{Do you have all the Material you need}=“No”

),
{My Name} & " Needs " & SWITCH(
    {Do you have all the Information needed} & {Do you have all the Material you need},
    "NoNoNo", "Tools, Information and Material",
    "YesYesNo", "Material",
    "YesNoYes", "Information",
	"NoYesNo", "Tools and Material",
	"YesNoNo", "Material and Information",
	"NoYesYes", "Tools",
	"NoNoYes", "Tools and Information"
) & " to perform " & {Production Goal for the day/Week:}

)

[Justin_Barrett]

The SWITCH function works based on what you feed it in the first argument. In my case, I fed it a concatenated version of the two fields you were originally using for your comparisons. While you added the third field to your OR check at the beginning, you didn’t add it to the start of the concatenation that SWITCH checks. In that case, it only mashed together the original two. None of those results are in your list, so it defaults to the equivalent of BLANK(). To work correctly, the line after the SWITCH opener should read:

{Do you have all the Tooling you need} & {Do you have all the Information needed} & {Do you have all the Material you need},

[Clay_Hurst]

Thank you much!
Man I should have caught that as I understood what you did when I read the code, but like a newborn learning to crawl… What language are we writing anyway? Is this specific to AirTable? Thank you in advance sir.

[Justin_Barrett]

I don’t think it has a name. Even though the syntax and structure are similar to many other programming languages, the implementation is fairly Airtable-specific in some respects.