LookUp Values from Related SQL Tables

[stephenkln]

Hi everyone,

This is my first time using Power Apps and my first time posting on this forum, so go easy on the noobie. 😉

The basic scope of the project I'm working on is that I have a SQL Server Express dB that tracks supplemental employee data.

I'm trying to build out an app for viewing and updating employee records in an intuitive way. My rough vision was to have a main gallery listing the employees and on selecting an employee an additional gallery would open up listing the related tables and you would be able to go into each and add/modify records. Anyway, we're not there yet. I'm stuck on step one--just building the first gallery. I'm connected to my SQL data and have the following tables to feed the main gallery.

Employees Tbl

EmployeeId	FullName	JobId	LocationId
1	Smith, John	1	1
2	Doe, Jane	2	2

Jobs Tbl

JobId	JobTitle
1	Some Job Title
2	Another Job Title

Locations Tbl

LocationId	LocationName
1	Some Location Name
2	Another Location Name

I'm trying to set up my gallery like this:

Smith, John

Some Job Title

Some Location Name

Doe, Jane

Another Job Title

Another Location Name

I'm using LookUp to pull job title and location name:

LookUp('[Jobs Tbl]',JobId=JobId,JobTitle)

But what I'm getting is this:

Smith, John

Some Job Title

Some Location Name

Doe, Jane

Some Job Title

Some Location Name

I guess the LookUp function is pulling the first related value from the Jobs Tbl, but irrespective of the employee. I need it to pull the correct Job Title and Location Name for each individual employee.

I'm not sure how to proceed at this point since I'm so new at this. Anyone have any suggests?

[Drrickryp]

Hi @stephenkln 

I believe you are running into a disambiguation problem. There are a couple of ways to solve this.  I have always left the primary key of a table as simply ID and the foreign key to relate back to the Parent table as you are doing with locationID or jobID.  Then the Lookup is straightforward as  LookUp('[Jobs Tbl]',ID = JobId, JobTitle).  So changing the name of your primary keys in your SQL tables would be one way to solve the problem and it would follow best practices.  

Another way to solve it is to rename the field in PowerApps.  It would look like this:

LookUp(RenameColumns('[Jobs Tbl]',"JobID","JID"),JID=ThisItem.JobId,JobTitle)

This will not make any permanent change in the underlying table, but if all of your tables are like the ones you show here, it will be aggravating to do that every time you use Lookup().  

Finally there is the disambiguation operator [@fieldname]  There is a video on how this works here https://www.youtube.com/watch?v=YIaccWcAIf0 .  Since I don't have this problem, I have never had to use this but this would be the recommended way to use the disambiguation operator.

 LookUp('[Jobs Tbl]','[Jobs Tbl]'[@JobID] = JobId, JobTitle)

FYI another "best practice" is to avoid spaces in naming your tables.  I prefer camel case so 'Jobs Tbl' would be 'jobsTbl' or snake case where spaces are replaced with underscore 'Jobs_Tbl'.  

[stephenkln]

Thanks for the response @Drrickryp. I gave it a try:

LookUp(RenameColumns('[Employee].[JobTitle]',"JobId","Id"),Id=JobId,JobTitle)

But I'm getting an error on the conditions for the LookUp. It's telling me JobId for the Employees Tbl is an invalid reference.

[eka24]

@stephenkln 
OnVisible of the Screen or Onstart, Create a Collection that Joins the Tables:

ClearCollect(JoinedTables1,AddColumns(EmployeesTbl,"JobTitle",LookUp(JobsTbl,JobID=EmployeesTbl[@JobID],JobTitle)));
ClearCollect(JoinedTables2,AddColumns(JoinedTables1,"Location",LookUp(LocationsTbl,LocationId=JoinedTables1[@LocationId],LocationName)))

Then on the Items of the Gallery, put the JoinedTable2

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[stephenkln]

@eka24 I followed your instructions, but both collections are empty.

It seems like I would just be able to use LookUp(JobsTbl,JobID=EmployeesTbl[@JobID],JobTitle) and that work to pull the correct record. When I try that it tells me that the "=" is an invalid argument. Doesn't seem like I'm trying to do something complicated. Frustrating to me, but probably just my unfamiliarity with Power Apps.

[eka24]

Can you give a copy of the Formula you use:

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[stephenkln]

@eka24 

For the basic lookup:

LookUp('[Employee].[JobTitle]',JobId='[Employee].[Employee]'[@JobId],JobTitle)

For the collections:

ClearCollect(JoinedTables1,AddColumns('[Employee].[Employee]',"JobTitle",LookUp('[Employee].[JobTitle]',JobId='[Employee].[Employee]'[@JobId],JobTitle)));

ClearCollect(JoinedTables2,AddColumns(JoinedTables1,"Location",LookUp('[Location].[Location]',LocationId=JoinedTables1[@LocationId],LocationName)))

[eka24]

Please can you give a screen shot of your SQL table Names. Not sure how the names have been stated in the formula.

Both for the Basic and other formula

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[stephenkln]

@eka24 Here you go:

[eka24]

Since you prefer the basic,

On the Gallery Items, put the employee table

Then in one of the Labels inside the Gallery put:

LookUp('[Employee].[JobTitle]',JobId='[Employee].[Employee]'[@JobId],JobTitle)

In your first request, you made mention of a location table, but i cant see that

If you get errors, take a screenshot

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.