Solved: How do I get row where a column value is within an...

HCF · 12-02-2020 05:12 AM

I have a Contacts table and a Tasks table. The Tasks table has a column for Contacts.
Now I want to see all the Tasks where the Contact appears in Tasks[Contacts]
Is that possible?

LeventK

@HCF

SELECT(
    Tasks[KeyColumn],
    IN(
        [_THISROW].[Contacts],
        SELECT(
            Contacts[ContactName],
            TRUE,
            TRUE
        )
    )
)

View solution in original post

LeventK

HCF

Hi, although I found some interesting stuff, this was not what I was looking for.
The Tasks[Contacts] field can have several contact names and thus, I want to find only the records where the Contacts[ContactName] appears in Tasks[Contacts]. I realize that this can be done with a relationship, but that’s not the intention here. Hope this clarifies.

LeventK

@HCF
You can create a Virtual Column with this expression which will be a LIST type

SELECT(
    Tasks[KeyColumn],
    IN(
        [Contacts],
        Contacts[ContactName]
    )
)

HCF

Hi, tanks - I tried that, but it will list all the records - not only the ones where the ContactName appear.

LeventK

@HCF
Provided you have 100 contacts in your Tasks table and only 20 of them appear in the Contacts table [ContactName] column, the expression will list you 20 of them. Isn’t this what you are asking?

HCF

@LeventK
The Tasks table has a field named Contacts. It’s a text field with several contact names separated by a comma. I am trying to match these records and to show which Tasks appear with the respective ContactName when looking at a Contacts record. That is; the tasks should show on the current contact.

LeventK

@HCF

SELECT(
    Tasks[KeyColumn],
    IN(
        [_THISROW].[Contacts],
        SELECT(
            Contacts[ContactName],
            TRUE,
            TRUE
        )
    )
)

HCF

Thanks a lot, that did the tricks

LeventK

You’re very welcome

How do I get row where a column value is within another value