Self-Join Type Lookup

PJMatelli · 04-02-2020 10:10 AM

I am a database designer and use self-joins on a regular basis. I am trying to use this idea in my app. Here is the scenario: I have a list of employees, one of the columns is their supervisor employee number. I need the lookup to take the supervisor number of the employee and look back at the same employee list to get the supervisor name. I hope that makes sense? Any help is appreciated. Right now, my workaround was a vlookup in the Sheet, but I would like to avoid that. Thanks.

Marc_Dillon

The Supervisor column should be set as a Ref type column, pointing to the Employees table.

PJMatelli

Thanks for that, but the Supervisor column is an auto fill-in from a Ref column as Employee Number. So, the Employee Number in my app is a Ref to the EmployeeNumber in this sheet…Then, I auto fill as much information as possible, including the Supervisor Number. I need the Supervisor number to lookup back to FirstName and LastName to display the name of the supervisor.
I hope that explains it better. Thanks again.

Marc_Dillon

So you have another table that is referencing an Employee?
Sounds like you need to use a dereference expression.

llheath

can you give an example?

Supervisor is a column in the table with this value: [EmployeeNumber].[Supervisor],
Employee Number is a Ref column.
I want this to bring in the first name of the supervisor.
LOOKUP([Supervisor], “Allemps”,“EmployeeNumber”, “firstname”) seems like it would work, but does not bring back an first name.

this works:
LOOKUP(“30267”, “Allemps”,“EmployeeNumber”, “firstname”) (brings in the supervisor’s name with emp number 30267

and 30267 is what is displaying in the supervisor field so really
LOOKUP([Supervisor], “Allemps”,“EmployeeNumber”, “firstname”) seems like it would work, but does not bring back an first name.

Steve

Should you be using [_THISROW]?

llheath

that is it…thanks again

Steve