I would like to create a Form in another application which shows all of the possible options in a Multi-Select field. To do so, I need to pass to that application an array containing all of the unique values in the multi-select field.
The ArrayUnique function works for a rollup on a per-record basis, but I need to summarize the field values in an entire colum. How can I do this?
Easiest way is to create a new table with a single record, link all the records in the target table to the single record, and perform a rollup from there. And the easiest way to create that many-to-one link is to set the primary field of the single record to something like ‘.’, define a linked record field in the original table that points to the new table, and copy-and-paste a ‘.’ into all cells in the newly defined linked record field column. Airtable will grumble for a moment and then convert the periods into links. Now you can create a rollup field in the new, single-record table that will roll up values from the entire column.
However, under certain circumstances ARRAYUNIQUE() may not behave the way one expects it to. As the linked post (and the demonstration base it references) explains, one can sometimes recapture the anticipated behavior by nesting aggregation functions.
Easiest way is to create a new table with a single record, link all the records in the target table to the single record, and perform a rollup from there. And the easiest way to create that many-to-one link is to set the primary field of the single record to something like ‘.’, define a linked record field in the original table that points to the new table, and copy-and-paste a ‘.’ into all cells in the newly defined linked record field column. Airtable will grumble for a moment and then convert the periods into links. Now you can create a rollup field in the new, single-record table that will roll up values from the entire column.
However, under certain circumstances ARRAYUNIQUE() may not behave the way one expects it to. As the linked post (and the demonstration base it references) explains, one can sometimes recapture the anticipated behavior by nesting aggregation functions.
Thank you Mr. Hall. This solution works - except there is no ability in Airtable to create a Default value for a Link Field. So is there any way to avoid having to manually enter the ‘.’ in each new record?
Thank you Mr. Hall. This solution works - except there is no ability in Airtable to create a Default value for a Link Field. So is there any way to avoid having to manually enter the ‘.’ in each new record?
I would have sworn I had replied to this a couple of days ago… but then I discovered a half-finished reply moldering offline. Sorry for the delay…
Short answer: No, somehow, you’ll have to make sure each new record added to the table is linked to the summation table. How that happens — and how much pain it imposes — depends on your overall work flow.
From your description, I couldn’t tell if this was a one-off process, an every-now-and-then process, or an after-every-record process. If it is the latter (which seems unlikely), I’d probably name the single record in the calculation table something like ‘ :white_check_mark: ’ (the Heavy White Check Mark emoji). Then, during data entry, you would click once on the plus sign in the linked record field and a second time to select the record itself. That’s a two-mouse-click expenditure of effort to create the link, which seems reasonable — and the check mark that appears in the linked record field offers a nice visual confirmation the link has been made.
For less-frequent intervals, you might prefer the cut-and-paste method I described, which has the benefit of being able to modify multiple records at once.
There is also the option of using a third-party middleware offering such as Zapier to perform trigger-based updating of the link field. Personally, that strikes me as overkill, but should you decide to go that route, in the [Documentation] table of my Wardrobe Manager base there is a PDF attachment entitled “Wardrobe Manager Zapier Guide” that provides step-by-step instructions on setting up a very similar Zapier Zap; only the field to be updated would need to be changed.
I would have sworn I had replied to this a couple of days ago… but then I discovered a half-finished reply moldering offline. Sorry for the delay…
Short answer: No, somehow, you’ll have to make sure each new record added to the table is linked to the summation table. How that happens — and how much pain it imposes — depends on your overall work flow.
From your description, I couldn’t tell if this was a one-off process, an every-now-and-then process, or an after-every-record process. If it is the latter (which seems unlikely), I’d probably name the single record in the calculation table something like ‘ :white_check_mark: ’ (the Heavy White Check Mark emoji). Then, during data entry, you would click once on the plus sign in the linked record field and a second time to select the record itself. That’s a two-mouse-click expenditure of effort to create the link, which seems reasonable — and the check mark that appears in the linked record field offers a nice visual confirmation the link has been made.
For less-frequent intervals, you might prefer the cut-and-paste method I described, which has the benefit of being able to modify multiple records at once.
There is also the option of using a third-party middleware offering such as Zapier to perform trigger-based updating of the link field. Personally, that strikes me as overkill, but should you decide to go that route, in the [Documentation] table of my Wardrobe Manager base there is a PDF attachment entitled “Wardrobe Manager Zapier Guide” that provides step-by-step instructions on setting up a very similar Zapier Zap; only the field to be updated would need to be changed.
Very helpful - thank you!
+3Hey all, just been looking at this issue - wondering if there are any more recent solutions to the SUM column issue laid out here.
But if not, and creating a single row on another table linked to all rows that need summing and adding an ID such as ‘.’ is still the best/only way - then I just wanted to add that you can use automations to remove the manual part of the process.
E.G
When new row created
add ‘.’ to field.
:slightly_smiling_face:
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
