How do I force a formula action to run even when calling it inside a Concatenate or ListCombine?

1

I have a fairly specific goal:

From a button in table 1: create and activate a row in a table 2, and add a reference to that row in a column of table 1. If I’m working with only ONE reference: this works fine:

ModifyRows(
  thisRow,
  Associated Topics,
  Topics.AddRow().Activate()
)

But the problem is that this completely overwrites the Associated Topics field even if there already exists a topic there. Since this is to be a many-to-many relationship, I’d prefer to append to the list rather than overwrite the field.

But in my testing, I’m not able to use the AddRow() action from within either a Concatenate() or ListCombine(). If I try to, the action doesn’t launch and simply returns what I believe is a reference to some Action object (Action ⚡). Wrapping the action in RunActions() also doesn’t seem to help.

Does anyone have experience here? Thanks!

2

Hi @lamb and Welcome to the Community :partying_face: !

I’m fairly “aways from keyboard” but how about storing the reference to the brand new created row within a simple text field (like it can be done when setting up a simple Add Row button and select the optional Results column in the menu) and then use that field to add the row to the multi-select lookup field ? :blush:

This is what I did in the sample you’ll find below using this Action formula :

RunActions(
  ModifyRows(
    thisRow,
    Table.[Added rows], 
    Activate(AddRow([Table 2]))),
  ModifyRows(
    thisRow,
    Table.[Table 2],
    ListCombine(
      thisRow.[Table 2].Filter(CurrentValue.IsNotBlank()),
      thisRow.[Added rows]
    )
  )
)

So, the “1st” action modify the value in the text field [Added rows] to add the reference to the brand new row the button creates (and is opened at the same time).
And the 2nd action modify the lookup field to add the reference of the brand new row to the list rows already selected :blush: .

The field [Added rows] can be hidden :blush: .

I hope this helps :innocent:

2 Likes
3

You’re the best. (And thanks for the welcome!)

I was actually super close to having this solution but tried to do it in the form of a Text or Lookup control, both of which gave me the same problem where SetControlValue() caused the inner Action to be read as an object, rather than a function returning a reference.

One adjustment I made to your formula though is, rather than using an entire helper column, which is O(n) storage cost (one field per row), I created a helper “temp_var” table and set the values in there instead, which is O(1) storage cost (one field for ALL rows) and can be shared between tables who share this use case.

I can post the example when I’m home from work. I just use the current user as a key for the value to prevent situations where two people are trying to use buttons at the same time.

I wonder why the action executes in ModifyRows() but not SetControlValue().

Anyways, thanks again @Pch !

4

My pleasure @lamb :grin: !

I was just an idea :wink: !

And I’m very glad to know it helped you moving forward :raised_hands: !

5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.