Skip to main content

Hello,



I am trying to create a field that concatenates two rollup fields that have the array(unique) formula in each of them. But the concatenated result eliminates the comma.





You can see in the 10th row, the comma between “Refugee Support” and “Water access, sanitation, and hygiene” is missing. Here’s what I used:



IF(OR({BACKEND_Subject Areas of Proposals as Lead}=BLANK(),{BACKEND_Subject Areas of Proposals as Partner}=BLANK()),



CONCATENATE(ARRAYUNIQUE({BACKEND_Subject Areas of Proposals as Lead},{BACKEND_Subject Areas of Proposals as Partner})),



CONCATENATE(ARRAYUNIQUE({BACKEND_Subject Areas of Proposals as Lead}& ", " &{BACKEND_Subject Areas of Proposals as Partner})))



For context: Each row is an organization that have submitted a proposal to us. I would like the field “Subject Areas of Proposal(s)” to be a tidy array of subject areas tied to that organization’s proposals, which they selected from a standard taxonomy list (Linked Record in the forms). Sometimes the organization is the lead in the proposal (which is one Rollup field with array(unique)), sometimes they are the partner (which is the other Rollup field with array(unique)). The “Subject Areas of Proposal(s)” field is my attempt to have all Subject Areas associated with that organization in one space, showing unique values only (so if the organization submitted 4 environment proposals, it wouldn’t say environment four times).

Couple things:





  • The ARRAY...() functions don’t work in Formula fields to begin with, they only work for Rollup field aggregations




  • CONCATENATE()'s arguments need to be written like


    CONCATENATE("text 1", ",", "text 2", ",", "text 3", "," ...), not


    CONCATENATE("text 1" & "text 2" & "text 3" ....), nor


    CONCATENATE("text 1", "text 2", "text 3" ....)





so your formula should look closer to:



TRIM(


   CONCATENATE(


      {BACKEND_Subject Areas of Proposals as Lead},


      IF(


         AND(


            {BACKEND_Subject Areas of Proposals as Lead},


            {BACKEND_Subject Areas of Proposals as Partner}


         ),


         ", "


      ),


      {BACKEND_Subject Areas of Proposals as Partner}


   )


)

Couple things:





  • The ARRAY...() functions don’t work in Formula fields to begin with, they only work for Rollup field aggregations




  • CONCATENATE()'s arguments need to be written like


    CONCATENATE("text 1", ",", "text 2", ",", "text 3", "," ...), not


    CONCATENATE("text 1" & "text 2" & "text 3" ....), nor


    CONCATENATE("text 1", "text 2", "text 3" ....)





so your formula should look closer to:



TRIM(


   CONCATENATE(


      {BACKEND_Subject Areas of Proposals as Lead},


      IF(


         AND(


            {BACKEND_Subject Areas of Proposals as Lead},


            {BACKEND_Subject Areas of Proposals as Partner}


         ),


         ", "


      ),


      {BACKEND_Subject Areas of Proposals as Partner}


   )


)

Thanks for the comment.



I had originally tried to just concatenate the two fields I titled with BACKEND but because these are Rollups with array(unique), the resulting concatenation in the formula field omits the comma. It seems to still be the case with the formula you suggested omitting the ARRAY…()



Moreover, in my original formula I had included the OR() so that the comma would not appear if one of the fields is empty.



If I use a formula field to concatenate straight from the lookup field from which the two Rollup fields (with BACKEND suffix) are rolling up, I get repetitions (e.g. Environment, Environment, Refugee Support, Environment).



The end goal is that the formula field will just show a line of unique subject area labels with a space and comma where needed.



Any help is appreciated!

Thanks for the comment.



I had originally tried to just concatenate the two fields I titled with BACKEND but because these are Rollups with array(unique), the resulting concatenation in the formula field omits the comma. It seems to still be the case with the formula you suggested omitting the ARRAY…()



Moreover, in my original formula I had included the OR() so that the comma would not appear if one of the fields is empty.



If I use a formula field to concatenate straight from the lookup field from which the two Rollup fields (with BACKEND suffix) are rolling up, I get repetitions (e.g. Environment, Environment, Refugee Support, Environment).



The end goal is that the formula field will just show a line of unique subject area labels with a space and comma where needed.



Any help is appreciated!




I’m doing the reverse with AND(): if both fields have a value, add the comma. I do this because it creates a less redundant formula and it is typically more efficient to ask if a field is filled verses if its empty.



Since you Rollup aggregations result in arrays (not strings), you need to force the output as a string so Airtable can properly determine if the field is “filled” (or if its “empty”). Either adjust the formula slightly like so:



TRIM(


   CONCATENATE(


      {BACKEND_Subject Areas of Proposals as Lead},


      IF(


         AND(


            {BACKEND_Subject Areas of Proposals as Lead}&"",


            {BACKEND_Subject Areas of Proposals as Partner}&""


         ),


         ", "


      ),


      {BACKEND_Subject Areas of Proposals as Partner}


   )


)


or change both your Rollups’ aggregations to: ARRAYJOIN(ARRAYUNIQUE(values))

Reply


Terms & Conditions