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} & '%)'
)
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} & '%)'
)
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}&'%)',
')'
)
)
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}&'%)',
')'
)
)
Thank you so much, @momentsgoneby80 !
Your solution works flawlessly. I. had tried to do what you were suggesting, but I clearly didn’t know how to get there. Following your code made things very clear to me. I am learning these things one step at a time…
Thank you again!
