Better treatment of lists / arrays / multi-value fields in formulas (formulae?)

Feature Requests
1

I’ve worked a lot with multi-value fields (references to tables, multi-value selections) in Seatable recently, and particulary with formulas to transform and analyze these. Here are a few suggestions I came across.

Simple example to illustrate my points: Lets say I have a multi-value lookup from one table, pulling a name and a number into my current table. So the basic content of the formula is {lookup} (or {lookup.Name}, “Name” being the primary / linked column, and {Lookup.Amount}, “Amount” being an additional column of type “number”. You get the idea.

Here are my suggestions:

Caveat: Please do not suggest a script like in this feature request. As far as I can tell, scripts are not dynamic, but have to be run each time, and then physically break up the multi-value fields into other columns. Unless

  • Suggestion 1: Create some way to add functions to seatable, which can then be accessed in a formula, just like the built-in ones. The function could then manipulate and output data on the fly, when a cell is displayed. Make them read-only, just like the built in functions, so there are no security issues.
  • Suggestion 2: At least, make the handling consistent. For example,
    {Lookup.Name} & " (" & {Lookup.Amount} & ")"
    creates the output
    Bob, Joe, Rebecca (12)
    However, when I format the “Amount” column in the linked table as text (something I don’t want to do), the output of the same formula changes to Bob, Joe, Rebecca (12, 42, 19), which is more in line with what I’d expect. I’d actually consider this a bug.
  • Suggestion 3: Give more control over the way multiple values are iterated and displayed via formulas. Details:
    • I found more cases where an output like Bob (12), Joe (42), Rebecca (19) would be more useful than the way it’s currently done.
    • Also, having more control over the list separator (Comma+Space) and any additional characters before and after would be nice. At the moment, I use another formula column which replaces ", " with something else, but that affects ", " within values, too.
    • In general, I think some syntax with bracketing could influence the way these lists are iterated, and improve a few things I have mentioned. In my example,
      ({Lookup.Name}) & " (" & ({Lookup.Amount}) & ")"
      could mean “iterate separately, and then concatenate results” (your way), while
      ({Lookup.Name} & " (" & {Lookup.Amount} & ")")
      could create my way
  • Suggestion 4: As a quick win, you might enhance some of the functionality that’s already there in the formulas. Some way to make the list unique would be fine, and / or only count unique entries.
  • Suggestion 5: As a final thought, I have always been impressed what you can do with lists an lambda expressions. Maybe that could be another way to make all this happen quickly, since there are a lot of lambda libraries available.

Epilogue: Don’t take this as a demand, or even criticism. Seatable is a brilliant piece of software, and I’d like to see it develop in a way were I can finally suggest it to my employer to be used company-wide.

3 Likes
2

To meet this need, you can first create a link formula column with function rollup to summarize the linked rows and then use the result in another formula column.

3

Thanks Daniel, I do it like this already (which just led me to another suggestion)

However, in the particular suggestion you mentioned, I actually consider the different treatment of numbers (only first one shown) and strings (all shown) an actual bug, because I can’t think of any reason for this behaviour, nor can I find it in the documentation.

4

In this case, lookup function does not designed to work for multi-value fields, so either behaviour is okay. In real use cases, we expect people to use rollup function for multi-value fields, and use lookup function for single value fields.