Add/Remove an item from multi-select cell via Button

1

I have a multi-select column. Let’s say the options are Apples, Bananas, Cherries.

I have a row that is currently set to [Apples,Bananas].

I want a button column that adds “Cherries” to whatever was already there.

I know how to use buttons to say “Cherries” is the only thing selected (Modify row with value Cherries), but how do I add? Some kind of thisrow.fruit.currentvalue + “Cherries” or something like that? Some kind of array.push function? …

Similarly, how about a button that deletes “Bananas” but leaves whatever else was in there?

2 Likes
2

Some more context is that my real-world use case is planning equipment needed for events. So my multi-select column is actually a Lookup, modelling a many-to-many relationship between equipment items and events.

Do I need to add a join table that manages the relationships between these two tables (equipment and events)?

Part of my motivation for trying to do it just in 2 tables (using multi-select lookup) is table size. I have a few hundred events per year, each with maybe 100 pieces of equipment allocated to them, so we’re talking 100k rows in that join table before long.

1 Like
3

Try something like thisRow.ModifyRows(Fruit, ListCombine(thisRow.Fruit, "Cherries"))

A separate join table would be a good idea if you wanted to store some extra info on that relation, e.g. join events to pieces of equipment, and on each such link set a different rent price. But if you’re not doing this and not planning on doing this, no need for a join table there.

1 Like
4

Works like a charm. Took me a minute to find the enable raw formula button (had been stuck filling in the “update values” field) :slight_smile:

Haven’t yet figured out deleting (maybe I can iterate over the list with FormulaMap or something, looking for entries I want to nuke, still poking through the docs for that).

Thanks!

1 Like
5

This will be something like TableName.Filter(...).DeleteRows(). Not sure if you need formula map there.

Or do you mean deleting an entry from that list? Then you’ll have again to ModifyRows but use Splice() plus Find():

thisRow.ModifyRows(Fruit, thisRow.Fruit.Splice(Find("Cherries"), 1))

2 Likes
6

Yeah deleting an item from the list.

I also just got that working via:

thisRow.modifyRows(fruit, thisRow.fruit.Filter( currentValue != "Cherries" ))

to only leave the row with things that:

  • it already had in it and
  • are not cherries

I find this more intuitive than the Find and Splice, but those are great to know about too.

Thanks for the help - I’ll be sure to share the doc I’m building once it’s done, as my specific use case can be adapted to a lot of different “allocate this to this” scenarios.

3 Likes
7

Ok so one issue with the adding.

If a row contains [Apples,Bananas] and we do listCombine with Cherries, it’s perfect: [Apples, Bananas,Cherries]

But if a row is blank, and we do listCombine with Cherries, we get: [Blank,Cherries]

And those blanks are messing up some other formulas because they’re a different data type than the column is expecting (column expects looked-up rows from another table and chokes on the [Blank] nulls).

Do I just need to incorporate some kind of conditional (IF(thisRow.Fruit)) and branch into two copies of the rest of my formula? Or is there a more elegant way to handle it at the moment of listCombining?

1 Like
8

Yeah, I don’t have any better ideas now than to use:

If(
  thisRow.fruit.isBlank(),
  List("Cherries"),
  thisRow.fruit.ListCombine("Cherries")
)
1 Like
9

Ok thanks! Unfortunately my real-world use scenario involves an elaborate spaghetti of filtering and comparisons, but at least I can copy and paste. I’ve also been using line breaks and indentation lately which have been helping me read my own code a bit more easily (comments would be nice too, but probably hard to implement)

10

If it feels like a spaghetti, consider splitting it up into separate columns. They don’t have to serve any other purpose.

11

Oh, figured it out! [Blank] was coming from explicitly specifying the existing contents.

Original that included specifying existing contents:

thisRow.modifyRows(
  [Fruit],
  ListCombine(thisRow.Fruit, "Cherries")
)

Instead, this also works, and doesn’t force the Blank:

thisRow.modifyRows(
  [Fruit],
  ListCombine("Cherries")
)
2 Likes
12

WOW, didn’t know that it would work! Nice discovery!

UPD: wait, I just tried it, and it doesn’t work for me. It replaces the selection with just Cherries.

13

Ah nuts it doesn’t seem to be working for me now either. I must have been mistaken.

Ok. Back to conditionals.

14

@Paul_Danyliuk thanks so much for your help on this. If you’re still game to explore it, I’ve started a thread with the next-level-up challenge, because I can’t get enough nested lists.

15

Thanks for the help on this topic! I also needed combine multiple select list columns and remove the blanks. I was able to do it in a single button by taking both formulas above using RunActions. It works great!
The formula in the button would look like this:

RunActions(
thisRow.ModifyRows(
thisRow.fruit,
ListCombine(
thisRow.[Column 1],
thisRow.[Column 2],
thisRow.[Column 3]
)),
thisRow.modifyRows(
thisRow.Items,
thisRow.Items.Filter(currentValue != “Blank” ))
)