Is there a better method to concatenate multiple Rollup Fields and discard blanks?

[Karlstens]

Hello,

I’ve been trying out a couple of ideas for table linking via Linked Fields and Automation. One particular idea lead me to creating multiple Rollup Fields, that then all need to be returned into a linked field via an Automation.

There’s a few ways to do this. One method is scripting the entire solution within an Automation - but I’m exploring non-script methods - but my troubles are with the array construction that the Automation then copies.

Here’s one example table - please excuse my spaceship terminology :grinning_face_with_sweat: :rocket:

At the moment, I’m using the below formula, but feel that it’s just a bit too much? Is there not a simpler way to write this?

CONCATENATE(
  IF({Chassis (Frigate Class)}		,{Chassis (Frigate Class)} 		& ", " ,""),
  IF({Chassis (Destroyer Class)}	,{Chassis (Destroyer Class)} 	& ", " ,""),
  IF({Chassis (Capital Class)}		,{Chassis (Capital Class)} ,"")
 )

An Array function that could be written as this would be amazing :sparkles:

MagicArray({Chassis (Frigate Class)},{Chassis (Destroyer Class)},{Chassis (Capital Class)} )

I’m hoping there is, but can’t seem to find it in the manual. :cry: Perhaps at least, there’s an easier method where I use Concatenate, and then remove duplicate/triple/quadruple commas with Regex replace? … but at least my current stacked If statement method works.

Alternatively to formula fields, it would be great if we could bypass the Calculation Formula Field completely, and somehow enter the dynamic fields directly into an Automation.

But this method fails as soon as one of the returns is blank and there’s a double comma - hence me exploring formulas.

Keen to hear thoughts!

EDIT:
I’ll need to sleep on it some more, but I think I’m leaning more in favour to actually scrap the formula and rollup columns, and simply execute a script off of a Lookup column.

But one reason I’m still interested in this problem, is that it seems like there’s an almost do-able solution to this problem without the need to go full-blown-JS (which don’t get me wrong, I’m now a sucker for this punishment :rofl: )

let inputConfig = input.config();

let table = base.getTable("Sheild Technology");
let record = await table.selectRecordAsync(inputConfig.recordId, {
    fields: [
      "Name",
      "Chassis (Frigate)",
      "Chassis (Destroyer)",
      "Chassis (Capital)"
    ]
});

console.log(record?.getCellValue("Chassis (Frigate)"));
console.log(record?.getCellValue("Chassis (Destroyer)"));
console.log(record?.getCellValue("Chassis (Capital)"));

let chassisArray = Array.from(new Set([
  ...record?.getCellValue("Chassis (Frigate)") ?? [],
  ...record?.getCellValue("Chassis (Destroyer)") ?? [],
  ...record?.getCellValue("Chassis (Capital)"??)  []
]));

console.info(chassisArray)

let chassisData = chassisArray.filter( element => element != null ).map( element => ({
	"id" : element
}));

await table.updateRecordAsync(inputConfig.recordId, {
    "Chassis": chassisData
})

[kuovonne]

Karlstens:

Perhaps at least, there’s an easier method where I use Concatenate, and then remove duplicate/triple/quadruple commas with Regex replace? … but at least my current stacked If statement method works.

A formula won’t be able to remove duplicates.

Your formula could be simplified slightly by removing the third parameter of the IF functions. You can also use REGEX to remove any final trailing comma. (I showed this technique on the Sept 27 episode of BuiltOnAir.)

REGEX_REPLACE(
  CONCATENATE(
    IF({Chassis (Frigate Class)}, {Chassis (Frigate Class)} & ", "),
    IF({Chassis (Destroyer Class)}, {Chassis (Destroyer Class)} & ", "),
    IF({Chassis (Capital Class)}, {Chassis (Capital Class)})
  ),
  ", $", ""
 )

[kuovonne]

Karlstens:

Keen to hear thoughts!

If there is a chance of having duplicates, I would go with a full scripting option. The scripting will let you remove duplicates, but a formula won’t. A script can also reduce the number of helper fields in your table.

I’m also wondering if your script works? (Parts of it look strange to me.) If it does work, what are your rollup formulas? Are they simply values or ARRAYUNIQUE(values) or something else?

[Karlstens]

My script almost works - it’s not robust enough against all situations, but in time I’ll fix it - hopefully this evening whilst I cook a risotto.

Thanks for clarifying that building an array within a formula field is simply not possible other than using many If statements. I might reach-out to Airtable Support and send them this thread as a Feature Request - however I also suspect that perhaps they’ve omitted this functionality by design - as it forces anyone needing to do anything with arrays to use an automated script, ie; Pro account.

I’ll see if I can patch my script now. I tested how easy it duplicates against all other “Technology” tables, and it more or less copies over without issue. :slightly_smiling_face:

Knowing that the ultimate advantage of a script is reducing fields, I’ll attempt to code up the Lookup function into my script - it’s something I’ve never done before as I’ve always used Lookup columns, but think I’ve got the skills to do it so will give it a go.

To answer your question, I was on the fence whether to use Rollup or Lookup, but decided to stick with Lookup since scripting.

EDIT: Eep, so I had a crack at coding the function of a Lookup Field, and oh my does it get difficult when there’s multiple record id’s to look up. :dizzy_face: I think I might just keep the Lookup fields in play for now.

[ScottWorld]

@Karlstens

I know that you weren’t asking about using an external 3rd-party tool to do this, and it might be completely overkill to use an external service for this.

However, I thought I should at least mention that Make.com can easily handle this with one simple formula that will combine fields into an array, but will also exclude all the blanks along with their commas.

That’s because Make has 19 array functions and an array aggregator, for a total of 20 array tools to work with.

But you only need to use 2 of those array tools: the “add” function and the “remove” function.

So, to set this up in Make.com, you just need to combine your fields into a new “empty array” using the “add” function, and then you need to remove any blank values (“null values”) from the array that you just created using the “remove” function.

See screenshot below for how this would look in Make, and here’s the sample formula that I used in the screenshot: {{remove(add(emptyarray; 1.1; 1.2; 1.3); null)}}

[kuovonne]

Karlstens:

Thanks for clarifying that building an array within a formula field is simply not possible other than using many If statements.

To clarify, these formula fields will not build an array. They create a text string that is a comma separated list. There is a difference. Airtable’s create record and update record actions are able to receive a comma separated list and split it back into an array. But that does not make the text an array.