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:
Can someone help?
Solved! Go to Solution.
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.
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.
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.
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?
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()
.
That works quite nicely!! I hadnโt notice that before. Thanks!
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โ.
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?
See also:
Thatโs IT! Thank you!!! You were right, JobNumber wasnโt the key column. Once I changed it over, the formula works a treat and the Apartments Sheet is referencing multiple jobs correctly.
Another hurdle jumped and lesson learned. Really appreciate it
User | Count |
---|---|
40 | |
34 | |
28 | |
23 | |
17 |