Solved: Building a ref list from another table - Page 2

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