Solved: Building a ref list from another table

Gman1 · 03-22-2020 07:26 PM

Hi All,

Hoping to get some assistance. I have two tables. One with Apartments and another with Incidents. The incidents table can have multiple apartments allocated to it (using EnumList) which shows up as “100A, 101A, 102A” in the table.

select(Cases[JobNumber],(contains(Cases[Apartment],[_THISROW].[Apartment]),true))
*The above formula is in the Apartments table and is trying to build a list of “JobNumber” to use as a reference, by selecting all Cases where the apartment number appears in the Apartment column of the cases table.

The formula however actually just says ‘if it finds the apartment in the table, then return all cases’.

What should happen is this:

Case 111 in “Cases” table has apartments “100,101” assigned to it
Case 112 in “Cases” table has apartments “100,102” assigned to it
Case 113 in “Cases” table has apartments “101” assigned to it
Apartment 100 in the “Apartments” table should have a reference list showing only cases 111 and 112 in it

Can someone help?

Steve

@Kirk_Masden, bingo!

Try:

select(
  Cases[JobNumber],
  in([_THISROW].[Apartment], [Apartment]),
  true
)

View solution in original post

Kirk_Masden

Hi @Gman! Welcome to the community!

I see that @Steve is replying. He’s the expert but I was about to suggest that you might need to use IN() instead of CONTAINS():

Steve

@Kirk_Masden, bingo!

Try:

select(
  Cases[JobNumber],
  in([_THISROW].[Apartment], [Apartment]),
  true
)

Kirk_Masden

Thanks @Steve! By the way, “IN” is such a short word that it doesn’t seem to work in a search in documentation. So, when I want to find your article about IN() I search for “indocumenation” on the community. That takes me to the link.

Gman1

Hi Steve (and thanks others for the help too!),

Should the code be (with the { } and , 😞
Select(
cases[jobnumber],
in([_THISROW].[apartment], {Apartment}),
true
)

If so, I’m not getting results. Would the IN expression be able to tell that apartment 100A is in the cell/enumlist “100A, 101A”? I was using contains as it acted as a search to see if these cells contained the text 100A. Though the above might be returning no result because 100A doesn’t = 100A,101A.

I know I’m going to need to sort out this whole retrieving multiple values out of an enumlist, as I expect to use this layout a fair bit.

Kirk_Masden

Hi again @Gman! The difference between () and {} is that {100A, 101A} is used if you have a list that you have manually typed in. So, for example, [Apartment] in the expression @Steve wrote would be the list of all of the apartment numbers in the table. If you wrote [Apartment]-{100A, 101A} would be a list of the apartment except 100A and 101A, since they have been subtracted:

The simplest way to construct a list of constant values is by enclosing it in curly braces and listing the constant values separated by commas. {1, 2, 3}: constructs a list of constant Number values. {“Apple”, “Banana”, “Cherry”}: constructs a list of constant Text values.
The LIST() function constructs a list from constants, column values, and expressions. LIST(1, 2, 3): constructs a list of constant Number values and is equivalent to {1, 2, 3}. LIST(“Apple”, “Banana”, “Cherry”): constructs a list of constant Text values and is equivalent to {“Apple”, “Banana”, “Cherry”}. LIST([HomePhone], [CellPhone], [WorkPhone]): constructs a list from the specified column values of the row.

Steve

Whoops! My expression had a typo: a period that should have been a comma. I’ve corrected it above.

You should not be using curly braces ({ and }).

Have your read through and do you understand the IN() and CONTAINS() docs Kirk provided above?

Steve

Unfortunately, that’s just how Intercom–the service that hosts the documentation–seems to work. I don’t know that there’s anything to be done about it. @Peter, do you know if Intercom can allow searches of only two characters, like in or if?

Myself, I just keep a tab open to the expressions collection and search using my browser’s search (Ctrl-F) for functions like in() or if().

Kirk_Masden

That works quite nicely!! I hadn’t notice that before. Thanks!

Gman1

Hi Steve and Kirk,

First of all, thanks for your great explanations and clarifications. Will help me lots moving forward with just those details.

I have read through the docs and I mostly kind of understand it (I’m an expert at complex Excel formulas and basic VBA, but this is all almost familiar but still alien for me).

You’re right, I’ve done it without the { } and it’s returning the correct results, however it changes the Virtual Column “List” with type “Ref”, back to “List” with type “Text” when I use that formula.

So I have the correct results from the formula, but can’t get the reference to work as it only displays the result as “text”.

Steve

A Ref value is the key column value of a row in the table. My guess is that the JobNumber column of the Cases table is not that table’s key column. What column of the Cases table is the key column? Alternatively, why do you want references?