Cannot compare Text with List in ([...] = [_THISROW].[...])

[Ryan_Hagler]

I've got the age old problem of "Cannot compare Text with List in ([...] = [_THISROW].[...])", but I can't for the life of me figure out why. I've used the formula a million times but I'm not sure what I'm doing wrong here. Here's the full formula:

if(isblank([Email]),any(select(Contact[Email],[Contact]=[_THISROW].[Contact])),[Email])

Two Tables:

This table - Customers

Other table - Contact

Just trying to have it display [Email] from Contact table when Customer[Email] is blank.

[Ryan_Hagler]

Ok I realized that I have Customer[Contact] set to an Enumlist with Base Type Ref because I need to allow the selection of multiple Contacts from the Contacts table. So I thought that I may need to use the IN() function inside my ANY(SELECT(...)). Here's what I've got and I think its working but feel free to tell me if I'm wrong.

if(isblank([Email]),any(select(Contact[Email],[contact]=in([Contact],[_thisrow].[Contact]))),[Email])

[Ryan_Hagler]

Never mind. This didn't work. Back to work.

[Ryan_Hagler · Accepted solution]

Alright, pretty sure I've got it now. Tested and seems to be performing correctly.

if(isblank([Email]),any(select(Contact[Email],in([Contact ID],[_thisrow].[Contact]))),[Email])

Just a heads up, the IN() formula did not work until I used the ID column [Contact ID]. The Label column, Contact[Contact], would not work even though I imagine it is supposed to return the ID when referenced from another table. Either way, this is working.