General Caster Concat Nested If Statement

[MollyMagic (Molly Hendricks)]

Hello fellow Monday pros!

I need your help. I’m trying to write a nested If statement using General Caster so that I can populate a text column in my board.

The Purpose
To auto fill a text column with the dates a person was absent from training ONLY if their attendance column (status) is Absent.

The Formula
IF({9/25}=“Absent”,1,0)+IF({9/26}=“Absent”,1,0)+IF({9/27}=“Absent”,1,0)+IF({9/28}=“Absent”,1,0)+IF({9/29}=“Absent”,1,0)+IF({10/2}=“Absent”,1,0)+IF({10/3}=“Absent”,1,0)+IF({10/4}=“Absent”,1,0)+IF({10/5}=“Absent”,1,0)+IF({10/6}=“Absent”,1,0)+IF({10/9}=“Absent”,1,0)+IF({10/10}=“Absent”,1,0)+IF({10/11}=“Absent”,1,0)+IF({10/12}=“Absent”,1,0)+IF({10/13}=“Absent”,1,0)

What ‘should’ happen
When selecting ‘Absent’ from any of the status columns, it should fill in the text column with the date they were absent.
(Example:)

The problem
Although it works on the Formula column of my board, it does not work in the General Caster recipe.

The recipe I’m using is:

[FrancisElliott]

Hi Molly,

I see a few potential issues with your formula:

1. Looks like a stray ampersand (&) in there at some point. Does that have a purpose?
2. Column references in General Caster use different syntax. Rather than {column name} it need’s to be {item's column name} i.e. your column references should be {item's 9/25} etc.
3. You appear to be using ' rather than " for many of the strings. I’m not sure if General Caster accepts single quotes at all, but better to make them all double quotes (") just to be safe.

Additionally, I’d really recommend formatting your formulas for readability.

Here is an updated formula. Not tested, but it does fix the initial potential issues I spotted. Hope it helps!

CONCATENATE(
    IF(
        {item's 9/25}="Absent",
        "Absent 9/25; ",
        ""
    ),
    IF(
        {item's 9/26}="Absent",
        "Absent 9/26; ",
        ""
    ),
    IF(
        {item's 9/27}="Absent",
        "Absent 9/27; ",
        ""
    ),
    IF(
        {item's 9/28}="Absent",
        "Absent 9/28; ",
        ""
    ),
    IF(
        {item's 9/29}="Absent",
        "Absent 9/29; ",
        ""
    ),
    IF(
        {item's 9/30}="Absent",
        "Absent 9/30; ",
        ""
    ),
    IF(
        {item's 10/1}="Absent",
        "Absent 10/1; ",
        ""
    ),
    IF(
        {item's 10/2}="Absent",
        "Absent 10/2; ",
        ""
    ),
    IF(
        {item's 10/3}="Absent",
        "Absent 10/3; ",
        ""
    ),
    IF(
        {item's 10/4}="Absent",
        "Absent 10/4; ",
        ""
    ),
    IF(
        {item's 10/5}="Absent",
        "Absent 10/5; ",
        ""
    ),
    IF(
        {item's 10/6}="Absent",
        "Absent 10/6; ",
        ""
    )
)

[MollyMagic (Molly Hendricks)]

Thanks @FrancisElliott . I tried it and it’s giving me an error. I’m sure it’s something small I’m missing like a comma or something. Just can’t figure it out. When I actually select the column in General Caster, it shows as “BPO ultipro ID’s 9/25” instead of “Item’s 9/25”.

Here’s what I tried using your code:
CONCATENATE(IF({BPO Ultipro ID’s 9/25}=“Absent”, “Absent 9/25; “,”“),IF({BPO Ultipro ID’s 9/26}=“Absent”, “Absent 9/26; “,””),IF({BPO Ultipro ID’s 9/27}=“Absent”, “Absent 9/27; “,”“),IF({BPO Ultipro ID’s 9/28}=“Absent”, “Absent 9/28; “,””),IF({BPO Ultipro ID’s 9/29}=“Absent”, “Absent 9/29; “,”“),IF({BPO Ultipro ID’s 10/2}=“Absent”, “Absent 10/2; “,””),IF({BPO Ultipro ID’s 10/3}=“Absent”, “Absent 10/3; “,”“),IF({BPO Ultipro ID’s 10/4}=“Absent”, “Absent 10/4; “,””),IF({BPO Ultipro ID’s 10/5}=“Absent”, “Absent 10/5; “,”“),IF({BPO Ultipro ID’s 10/6}=“Absent”, “Absent 10/6; “,””), IF({BPO Ultipro ID’s 10/9}=“Absent”, “Absent 10/9; “,”“), IF({BPO Ultipro ID’s 10/10}=“Absent”, “Absent 10/10; “,””), IF({BPO Ultipro ID’s 10/11}=“Absent”, “Absent 10/11; “,”“), IF({BPO Ultipro ID’s 10/12}=“Absent”, “Absent 10/12; “,””), IF({BPO Ultipro ID’s 10/13}=“Absent”, “Absent 10/13; “,”"))

Here’s the error I’m getting in General Caster:

[FrancisElliott]

Hi Molly,

Here is your reformatted formula:

CONCATENATE(
	IF(
		{BPO Ultipro ID's 9/25}="Absent",
		"Absent 9/25; ",
		""
	),
	IF(
		{BPO Ultipro ID's 9/26}="Absent",
		"Absent 9/26; ",
		""
	),
	IF(
		{BPO Ultipro ID's 9/27}="Absent",
		"Absent 9/27; ",
		""
	),	
	IF(
		{BPO Ultipro ID's 9/28}="Absent",
		"Absent 9/28; ",
		""
	),
	IF(
		{BPO Ultipro ID's 9/29}="Absent",
		"Absent 9/29; ",
		""
	),
	IF(	
		{BPO Ultipro ID's 10/2}="Absent",
		"Absent 10/2; ",
		""
	),
	IF(
		{BPO Ultipro ID's 10/3}="Absent",
		"Absent 10/3; ",
		""
	),
	IF(
		{BPO Ultipro ID's 10/4}="Absent",
		"Absent 10/4; ",
		""
	),
	IF(
		{BPO Ultipro ID's 10/5}="Absent",
		"Absent 10/5; ",
		""
	),
	IF(
		{BPO Ultipro ID's 10/6}="Absent",
		"Absent 10/6; ",
		""
	),
	IF(
		{BPO Ultipro ID's 10/9}="Absent",
		"Absent 10/9; ",
		""
	),
	IF(
		{BPO Ultipro ID's 10/10}="Absent",
		"Absent 10/10; ",
		""
	),
	IF(
		{BPO Ultipro ID's 10/11}="Absent",
		"Absent 10/11; ",
		""
	),
	IF(
		{BPO Ultipro ID's 10/12}="Absent",
		"Absent 10/12; ",
		""
	),
	IF(
		{BPO Ultipro ID's 10/13}="Absent",
		"Absent 10/13; ",
		""
	)
)

I ran a small test using two columns and the following formula:

CONCATENATE(
    IF(
        {Order's 9/25}="Absent",
        "Absent 9/25; ",
        ""
    ),
    IF(
        {Order's 9/26}="Absent",
        "Absent 9/26; ",
        ""
    )
)

Which ran perfectly.

So here are some troubleshooting steps to try:

1. Have you made all of the columns in your formula? Are they all named correctly?
2. Try adding just one IF check, and then two, and then three etc. Is there a a point at which it stops working?

Hope that helps!

[MollyMagic (Molly Hendricks)]

Thank you so much! I’m definitely going to try that. I appreciate your help.