Setting a Lookup Column via an Action (Button)

[Brendan_Woithe]

I’ve seen this problem come up in a few posts, but have not seen the answer.
Its simple enough - and comes down to not quite understanding how to treat data that is a reference.

I cannot for the life of me make a formula for the button that is able to insert “Option 3” as a lookup into a new row in Table 2.

It should be easy to find option 3 using the “Add this row” column checkbox. Alas, it isn’t.

Everything I’ve come up with ends up inserting “Option 3” as text into the Option column in Table 2 instead of inserting it as a reference.

And this then breaks other bits of my workflow later on.

Can anyone suggest a formula that would work in this case - or am I going about things the wrong way?

Cheers! B.

[Brendan_Woithe]

This shows what I thought would work…

But it ends up inserting TEXT rather than the lookup (so a reference to a row - which is what i need!)

Cheers.

[Ander]

@Brendan_Woithe

Please include a dummy doc next time. It makes it much quicker and easier to jump in and understand where you’re at. For example, I’m not sure why you’re having this problem, because I’m unable to review the parts of your doc not visible in your screenshots.

Here’s one approach:
[Table 1].Filter([add this row]=true).FormulaMap( AddRow([Table 2], [Table 2].option,CurrentValue))

[Brendan_Woithe]

Here’s my example doc. Apologies for not including it… I just try use screenshots so they’re preserved over time (as opposed to sharing docs which will disappear if my account is ever closed / accidentally cleaned up etc)

Your approach is interesting…I’m still unsure as to what isn’t working with my approach. I’d love to hear why my approach isn’t right.

[Federico_Stefanato]

Hi @Brendan_Woithe,

The main issue here is related with what Lookup() - or Filter() - formulas actually retrieve.

They in fact return “one or multiple rows”, therefore a generic datatype.

In order to have the single row reference, you just add the intuitively obvious, but necessary First() selector after your filter.
So, [Table1].Lookup([Add this row], true).First() should work as expected.

Let me know!

[Asaf_Dafna]

Two general “tips” (not directly related to your question):

1. It’s recommended to use Filter() instead of Lookup(), in your case [Table 1].Filter([add this row]=true).First() instead of [Table1].Lookup([Add this row], true).First().
   See this thread (answers in post #6 & #7): Filter() vs Lookup() — what’s the difference really?

2. Since your filter is based on a checkbox column, you can use [Table 1].Filter([add this row]).First() since the value of the [add this row] column is already true or false.

[Brendan_Woithe]

Oh I think I’m starting to get my head around this stuff - thank you all. Its also interesting that the formulamap idea from @Ander also works great. I’m guessing it is slightly less efficient given its doing multiple executions? (Is that something I should even be bothered by?)
And as to the filter vs lookup info - much appreciated. I’m not sure why I’ve had so much trouble getting filter() working for me, where as I’m generally ok with filter. Yet from reading that thread, there should be no difficulties at all. I’m bookmarking this so I can reference it when I jump back into my document tomorrow!