Need Help with nested IF statements combining 6 fields into one

Markus_Wernig · ‎Mar 13, 2021

I can’t seem to figure this one out. I have 3 Writer fields with 3 corresponding Share fields.

So far I am only able to combine the WRITER fields with the following formula:

{WRITER 1}
& IF(
AND(
{WRITER 1}, OR({WRITER 2}, {WRITER 3}
)
), ', ’
)
& {WRITER 2}
& IF(
AND(
{WRITER 2}, {WRITER 3}),
', ')
& {WRITER 3}

But I’d like to combine WRITER and corresponding SHARE fields and then combine everything in the ALL NAMES COMBINED formula field.

The final combinations should read:

John (BMI: 20457, 50%), Michael (BMI: 20691, 50%)
John (BMI: 20457, 50%), Michael (BMI: 20691, 25%), Charles (PRS: 30918, 25%)
Charles (PRS: 30918, 100%)

Yes, the extra “)”, commas, and percentage signs have to be added in the formula, but my question is, can all this be done in one swoop in the ALL NAMES COMBINED formula field, or do I have to create 3 “PRE-COMBINED” fields for the 3 Writers and their percentages and then combine these fields with my original formula?

It would be great if it could be done in one formula, as I already have way too many fields in my table.

Thank you in advance for pointing me in the right direction. I really do appreciate it!

momentsgoneby80 · ‎Mar 13, 2021

Hi @Markus_Wernig!
If you create a new record with no values in any of the 6 different fields your formula reference, you will see that your formula outputs “, %), %)”.

The source or the first comma and first "%) are located on line #6 in your code.
The source or the second comma and second "%) are located on line #9 in your code.

Airtable reads line 6 as

Insert value from {WRITER 2} followed by a comma, followed by {WRITER 2 SHARE}, followed by "%)

If there are no value entered in the fields {WRITER 2} and {WRITER 2 SHARE} the output is

, %)

Same output for line 9.

To fix the problem you need to tell Airtable to only write out these outputs when certain conditions are met. This should hopefully work for you and if not, at least give you an idea how to achieve what you are after.

IF({Writer 1},
	{Writer 1}&''&
	IF({Writer 1 Share},
		', '&{Writer 1 Share}&'%)',
		')'
	)
)&''&
IF(
	OR(
		{Writer 2},{Writer 3}
	),
	", "
)&''&
IF({Writer 2},
	{Writer 2}&''&
	IF({Writer 2 Share},
		', '&{Writer 2 Share}&'%)',
		')'
	)
)&''&
IF({Writer 3},
	", "
)&''&
IF({Writer 3},
	{Writer 3}&''&
	IF({Writer 3 Share},
		', '&{Writer 3 Share}&'%)',
		')'
	)
)

See Solution in Thread

Markus_Wernig · ‎Mar 13, 2021

I have made some progress with my attempt at combining these 6 fields into one formula field, but I can’t get rid of one or two extra % signs that always pop up.

What am I not seeing? Thank you!

Here’s how far I got with my formula:

IF(AND(
	{WRITER 1},{WRITER 1 SHARE}),
	{WRITER 1} &', '& {WRITER 1 SHARE} &'%)')
&IF(AND(
	{WRITER 1 SHARE},OR({WRITER 2 SHARE},{WRITER 3 SHARE})),", ")
	& {WRITER 2} &', '& {WRITER 2 SHARE} & '%)'
&IF(AND(
	{WRITER 2 SHARE},{WRITER 3 SHARE}),", "
	&{WRITER 3} &', '& {WRITER 3 SHARE} & '%)',
	{WRITER 3} &', '& {WRITER 3 SHARE} & '%)'
)

momentsgoneby80 · ‎Mar 13, 2021