ModifyRows for adding (not replacing) a value to a column of type "allow multiple selections"

I have figured out how to use ModifyRows() for a button, to set the cell value of a certain column.

The column type is a lookup with “allow multiple selections = on”.

But when I use the button with ModifyRows() , it replaces the previous cell value, rather than adding a new value to the cell, among the previous (multiple) value(s) in the cell.

I’ve tried to concatenate the new value to the previous ones using ModifyRows(thisRow, blueColumn, ListCombine(blueColumn, newValue)), but it gets in to some recursive loop that stops.

Any tips on this one?

Hi @Johan_Nyman, can you take a look at Ways to use a button to append/add to a multi-select column to see if Listcombine helps you.

3 Likes

Hey! Most likely it’s not a recursive loop but you’re referencing the whole column. Try:

thisRow.ModifyRows(blueColumn, ListCombine(thisRow.blueColumn, newValue))

(note thisRow inside ListCombine)

3 Likes

The combined help of both of you solved that problem step. Thanks a lot @mallika and @Paul_Danyliuk!

This is what I got to work well:

ModifyRows(thisRow, articleReferenceTable.blueColumn, ListCombine( thisRow.blueColumn, newValue ).Filter(CurrentValue!="").Unique() )

Now the only remaining problem is, that the newValue should be replaced by a popup for creating a new record, with Activate(AddRow()). The result of that new record should be placed as a referenced element among the multi-values in thisRow.blueColumn.

But here the modal window refuses to open to let me create a new record.

This is what I tried for the button:

ModifyRows(thisRow, articleReferenceTable.blueColumn, ListCombine( thisRow.blueColumn, Activate(AddRow([Detail view of articleReferenceTable])) ).Filter(CurrentValue!="").Unique() )

This is an articleReferenceTable. The blueColumn in the table references to elements/rows of the same table. The idea is that an article may reference to one or several other articles, which are also rows in the same table. and the blueColumn is used to store all other articles that the article in thisRow references.

The logic is, that in the current row record of the detail view, one searches among existing articles to link the article to. If the article we wish to link to doesn’t previously exist, then the button should open a modal to enter the new record, and when it is saved, it is placed among the other elements in the blueColumn cell of thisRow in the current record, from which the button was pushed.

I managed to get the Activate(AddRow()) to work correctly per se, and get it to replace the previous value(s) of the blueColumn. But now as I want it not to replace, but to append the returned, newly created row, then an “Action error” element appears.

1 Like

Activate() doesn’t return a row, that’s why you cannot pass it to the outer formula unlike AddRow() that unconventionally returns one.

You need to do this in two steps. First add the element to the list, then activate:

RunActions(
  thisRow.ModifyRows(aRT.blueColumn, ListCombine(thisRow.blueColumn, AddRow(...))),
  thisRow.blueColumn.Last().Activate()
)

(assuming that the added row will be added to the end of the list)

In my practice I found that working with single value cells is much more convenient than with multi-selects, especially when you need to add/remove elements from those lists. So if there’s a reasonable way to reorganize the way how you store article relations (e.g. with a separate join table where each separate row would link one article to another), that might be a better solution overall. You can always look up related articles with a formula, but managing the links will be much easier.

2 Likes

Thanks a lot! I have used single value cells in separate join tables quite a lot, but now recently tried multi-value as a shortcut. Maybe time to go back to separate join tables. I’ll try your two-step approach first, and then evaluate and decide. Thanks again for a quick reply!

Multi-values are great when all you need to do programmatically with them is either write the whole list in (e.g. a result of Filter()), and read from it, but not mutate the list. Adding (via ListCombine()) is okay and a justifiable approach when making a separate join table is more effort. But removing from the list is usually more work than tracking the relations separately.

Just for the fun of it, here’s list modification taken to extreme: To-do list, but it's just one table and one row 🤯 Definitely not an example to follow.

2 Likes

This was all very good info!

  • manipulating (adding to) a multi value selection list
  • using Activate() and separating actions with RunActions(,)
  • choosing to work with separate join tables for easier manipulation, instead of multi-values.

I have hence solved my problems. For now :slight_smile: Thanks again to both of you.

1 Like

Hi Paul

I tried running similar code. It adds the ‘Action’ in the child list - instead of the new record created by addrow. What am i missing?

1 Like

Please share the doc with me actinate@gmail.com with edit rights so that I can take a look instead of guessing.

I ran into the same issue as @Puneet_Agarwal2. It adds action in the child list instead of the new record.

Would really appreciate help on what I might be doing incorrectly:

RunActions(
  thisRow.ModifyRows(thisRow.[Benchmarked Title], ListCombine(thisRow.[Benchmarked Title], AddRow([Benchmarks Master],[Benchmarks Master].[Benchmarked Title],"Add Benchmark"))),
  thisRow.[Benchmarked Title].Last().Activate()
)

Table 1, where the button is triggered: [Core Title]
Column A, where I want to append the newly created reference: this.row.[Benchmark Title]

Table 2, where the row is created that should be added as a reference: [Benchmarks Master]
Column X (stores title of the new row): [Benchmarks Master].[Benchmarked Title]

Ok, so did get this to work, but here is the formula that worked for me in case others run into this challenge:

RunActions(
  AddRow([Benchmarks Master],[Benchmarks Master].[Benchmarked Title],""),
  
thisRow.ModifyRows(thisRow.[Benchmarked Title],ListCombine(thisRow.[Benchmarked Title],[Benchmarks Master].Last()).Filter(CurrentValue.IsNotBlank())),

thisRow.[Benchmarked Title].Last().Activate()
  
  )
1 Like

Hi @Paul_Danyliuk – do you have any strategies when this scenario isn’t avoidable? I have a scenario where we have a set of glossary terms related to our business. Those terms get referenced in numerous Standard Operating Procedures (SOPs) and Policy documents. To do this, dbDefinedTerms has a column, whereUsed, which is a multi-select of the available documentIDs in the system. Then each SOP has a canvas column that displays a filtered view of all of the defined terms associated with it. Those associations are manually assigned, the exception being when an SOP gets updated. That row dbMasterRepository gets “copied” as a new row and increments the version number of the document (the display column), effectively losing the associations to the defined terms. I’m trying to figure out a way to increment the SOP version number and then append that new documentID number to the list whereUsed.