Filtering using multiple lookups from a table

1

Let’s say that I have 2 tables. One table is populated by codes and references from the second table, and the other has an ID field, a lookup from first table (codes), and a third field with text quotes

Second field could include multiple selections, either a code or a list of codes.

I’ll paste an example at the end

If I want to filter using one value from the table using a formula, it works. For instance

bulletedlist(Lookup([Text quotes],Code,[code 1]).Quote)

However, if I try to filter using compound expressions, like

bulletedlist(Lookup([Text quotes],Code,OR([code 1],[code 2])).Quote)

The result is blank

Other way of expressing the problem: the second field of first table is populated by this formula:

[Text quotes].Filter(Code=thisRow).Quote

It is not blank if code field on Text Quotes table is filled with just one code and not a multiple selection

My question is: how can I write a formula which returns results refered to multiple selections?

Thanks in advance!

2

I suppose you only need a little tweak to your last formula:

[Text quotes].Filter(Code.Contains(thisRow)).Quote.BulletedList()
3

Thanks. It doesn’t work because code field can contain more than one value and multiple selections doesn’t match with any single row

4

Hi. Can anybody guess why it doesn’t work?

1 Like
5

@Juan_Luis_Chulilla

Check out the green column. Is that what you want?

2 Likes
6

@Ander

  • ANSWER by Juan Luis Chulilla
    • THANKS!!!. Answer in orange inside the example section
    • Now, I would like to extend the principle and I’m finding a new obstacle. Let’s see this formula embedded in text:

[Text quotes].Filter(Code.Contains([@code 1])).Quote.BulletedList()

  • it works and it returns an element. However, this other one doesn’t work

[Text quotes].Filter(Code.Contains([@code 1] OR [@code 2])).Quote.BulletedList()

  • My aditional question is: “How can I launch a temporary formula like a sql query if I want to compound it using an expression?”
1 Like
7

@Juan_Luis_Chulilla

The yellow column has this formula: [Text quotes].Filter(Code.Contains([code 1]) or Code.Contains([code 2])).Quote.BulletedList()

Does that do what you want?

1 Like
8

Thanks again @Ander !!!

dude you are a Coda formula ninja!

I have to go deep into codaese. I would assume that Contains would admit an expression, but you have shown that the expression has to be built with Filter. A little bit more verbose, but nothing serious.

My doubts are solved and the solution is quite, quite useful for a range of needs. Such Code Field can be used as tag for other fields that contains, say, text or images. A temporary formula inside a section can be used for recover the pieces of content that you have tagged with “codes”, using logical operators.

1 Like
9

@Juan_Luis_Chulilla

Yes, I use it heavily for these types of purposes. It’s awesome! :smiley:

1 Like
10

For what is worth, I would like to point out another detail of these kind of formulas.

I have tried to extend the example and show the data in other way. I have created a new table called Non-lookup and lookup from coding into text quotes. I want to list sources in which codes has been used

First column is a raw copy from coding index. Second column is a lookup from coding.CodeName. Third column is this formula:

[Text quotes].Filter(Code.Contains(thisRow.[N-L Code])).Source.BulletedList()

It doesn’t work. However, the formula of fourth row works:

[Text quotes].Filter(Code.Contains(thisRow.[Lookup Code])).Source.BulletedList()

First conclusion: if we want to look using Contains, both fields have to be of the same type. For instance, you cannot look for a string into a list of references, as third column formula do. You need to look for a reference into a list of references, unless the list of references is a lookup from a field of other table and you are looking for the strings from the looked-up table. Am I right, @Ander ?

Second conclusion: if you use Contains of a reference into a lists of references, you have to refer exactly to the field in which the reference is inserted, no matter if the field is the index of the table. For instance:

[Text quotes].Filter(Code.Contains(thisRow.[Lookup Code])).Source.BulletedList()
1 Like
11

@Juan_Luis_Chulilla

I’m unclear on what you’re saying in these two conclusions. Could you please state them a different way, or better yet, put an example of each in your doc?

12

OK, let me rephrase it

I have the last table, Non-lookup and lookup from coding into text quotes, built from Coding and from Text quotes

In its first column, I have a raw copy of Coding.codes .

The second column has a formula :

[Text quotes].Filter(Code.Contains(thisRow.[N-L Code])).Source.BulletedList()

Alledgely, it doesn’t work because it compares a string (i.e. “code 1”) with a reference (i.e. @code 1)

The third column is a lookup of Coding.codes. Fourth column has another formula, almost the same than the former one:

[Text quotes].Filter(Code.Contains(thisRow.[Lookup Code])).Source.BulletedList()

This new formula works because it compares two instances of the same reference. My first conclusion was that .Filter(field.Contains... cannot compare a string with a field populated with references. The only excepcion to that is if the string belongs to the field to which the list of references is pointing, has happens in Coding.Column 3

My second conclusion is that if you try to filter which rows are Contained into a field built from a lookup (populated by references), it is not possible to filter using just thisRow. You need to precise the specific field of the table that you want to compare (in my example, thisRow.Lookup code) or it won’t work, either if the field is an index or not

13

@Juan_Luis_Chulilla

I was having trouble putting into words my comments on your conclusions, so I made a new table for you at the top of your doc called: boolean Display column.

Above that table are some additional notes.

1 Like
14

Very elegant. Actually, Codaese forces you to certain new kind of reasoning, which is nice for a lot of problem solving

However, I’m not sure if Table.Filter(Field.Contains(Value)) can work if field is made with references and Value is made by values.

For instance, in the blue column of your example, the formula is

[boolean Display column].Filter([multi lookup].Contains(thisRow)).[non-Display column row name].BulletedList()

multi lookup is a reference to boolean Display column row name . Therefore, multi lookup is going to contain any value of the column that it is pointed to.

However, in my example the only formula that works was the one that both field were references.

I’m not sure how can I express this specific problem in other way. Anyways, your last table has been really nice for learning about other aspect of the problem. I really appreciate it and all your help.

1 Like