Solved: Lookup a value that exists eiter in one table or a...

khuslid · 03-25-2020 10:53 AM

Hi folks.
I’m sure this is easy, but I searched and cannot solve it.
I want to lookup a name in my table1, and find a matching email that either exists in table2, table3 or table4.
It is because I have 3 different tables with companies (one for each type of company). The matching name is of course in one of the 3 different tables, but i need to search for it in all three. No REF is used here.
Suggestions?

Aleksi

For example…
INDEX(
LIST(LOOKUP([_THISROW].[Name],“Table2”,“NameColumn”,“EmailColumn”))+
LIST(LOOKUP([_THISROW].[Name],“Table3”,“NameColumn”,“EmailColumn”))+
LIST(LOOKUP([_THISROW].[Name],“Table4”,“NameColumn”,“EmailColumn”))
,1)

View solution in original post

Aleksi

For example…
INDEX(
LIST(LOOKUP([_THISROW].[Name],“Table2”,“NameColumn”,“EmailColumn”))+
LIST(LOOKUP([_THISROW].[Name],“Table3”,“NameColumn”,“EmailColumn”))+
LIST(LOOKUP([_THISROW].[Name],“Table4”,“NameColumn”,“EmailColumn”))
,1)

LeventK

@khuslid
You can use this expression:

IF(
	ISNOTBLANK(LOOKUP([_THISROW].[NAME],"Table2","NAME","EMAIL")),
	LOOKUP([_THISROW].[NAME],"Table1","NAME","EMAIL"),
	IF(
		ISNOTBLANK(LOOKUP([_THISROW].[NAME],"Table3","NAME","EMAIL")),
		LOOKUP([_THISROW].[NAME],"Table3","NAME","EMAIL"),
		LOOKUP([_THISROW].[NAME],"Table4","NAME","EMAIL")
	)
)

khuslid

Just wondering; is this solution with IF(if… more efficient than the Index(list(lookup-solution ?
I still need it, but only for lookup in 2 tables… Like to use the least inefficient solution;)

Steve

If the email address is found in table 2 (the best case), the IF() solution performs two table scans: table 2 twice.

If the email address is found in table 3, the IF() solution performs four table scans: table 2 twice and table 3 twice.

If the email address is found in table 4 (the worst case), the IF() solution performs five table scans: table 2 twice, table 3 twice, and table 4 once.

The INDEX() solution always performs three table scans: tables 2, 3, and 4 once each.

The IF() solution can provide the best performance, but it can also provide the worst performance. The INDEX() solution always provides exactly the same performance, which is worse than IF() best and better than IF() average.

khuslid

Cool. I will calculate probability then:)
This is good to understand! Wil help me decide solutions in the future.
Thanks again!

khuslid

This is great. Thanks!
I’m struggeling with multiple issues, so I cannot see how this works yet. I’ll report back when I can test it.

But a bit in connection to this:
Is it possible to show two connected columns in an enumlist, eg. you pick the name and along in the same list you see and get the email. And then in the next turn, separate the email adresses and put them into a workflow so they all get the report by mail?
Or another way of selecting names from enumlist and then emailing all of them the report from a workflow…

Steve

Nope.

You’d have to collect the names in an EnumList then use the EnumList to select the email addresses.

Steve

Try:

ANY(
  SELECT(Table2[Email], ([_THISROW].[Name] = [Name]))
  + SELECT(Table3[Email], ([_THISROW].[Name] = [Name]))
  + SELECT(Table4[Email], ([_THISROW].[Name] = [Name]))
)

Searching three tables for an email address is horribly inefficient. Is there a reason you don’t keep all email addresses in one table?

khuslid

Thanks again for reply:)
Yes it is because I wanted to group companies into 1-Customers 2-Suppliers 3-Contractors. This makes it easier to show them in other separate views and also separate them into a org-map.

I’m a bit of a beginner in DB, so I’m sure there are more efficient ways. Mayby put them all in the same table and sort them in slices with a tag instead (eg. 1-Customers 2-Suppliers 3-Contractors)?
Is that better?

Steve

That’s what I would suggest.

khuslid

Thanks again @Steve, @Aleksi and @LeventK !
I’ve got a lot of work to do then. But better now than later, right?

Your INDEX(LIST(LOOKUP…suggestion worked great, @Aleksi.

As soon as I’ve restructured the DB, I’ll test your suggestion of collecting email adresses to a workflow from an enumlist, @Steve. Might give me some separator issues in the “mail to”-field in the wf…?

I’d love to share with you my APP once I’m a bit more done. Hopefully I’ve found som good ideas of tools you guys can use as well. I’d so much like to give something back to the community for the help I’ve received. Thanks again!

Steve

It shouldn’t.

khuslid

Hi.
I didn’t quite get it.
I have a list of names (enumlist) that I choose from Table2.
In the next field I’want to collect alle the email adresses connected to those names in previos field (enumlist) Those email adresses are also in Table2.
Hopefully I can then use that data (all the email adresses) directly in a wf and send the wf to all of them.
I tried with:
But error as you can see.
A bit norwegian here, but my enum list is “Send også til” (send also to) with option to choose multiple names (navn). The table to collect data from is “Prosjektdeltakere”. The referenced value from the enumlist (and to “Prosjektdeltakere”) is “Navn” (names).
What am I doing wrong?

Steve

khuslid

How can a IN() expression (true/false) get me a list of email adresses matching a enumlist of names…?
I’m lost here…
Is it possible at all? I mean comparing a list of names in one row with a column with many rows (names) in another table, and then collect the matching email adresses…?

khuslid

Tried with:
Select(Prosjektdeltakere[Epost],in(Prosjektdeltakere[Navn],[Send også til])
also
List(ifs(in(Prosjektdeltakere[Navn],[Send også til]),Prosjektdeltakere[Epost]))
No luck:(

khuslid

Ahhh. Got it finally, I think (must try it out tomorrow)
Changed the base-type to REF, and used:
Select(Prosjektdeltakere[Epost], in([Navn],[_Thisrow].[Send også til])

01.50 am here in Norway now, so going to bed:)

Steve

Outstanding!

Aleksi

If that’s fine with your needs, then yes it’s much better structure.

Aleksi

You’re welcome

Lookup a value that exists eiter in one table or another table