cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stephenkln
Resolver I
Resolver I

LookUp Values from Related SQL Tables

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

EmployeeIdFullNameJobIdLocationId
1Smith, John11
2Doe, Jane22

 

Jobs Tbl

JobIdJobTitle
1Some Job Title
2Another Job Title

 

Locations Tbl

LocationIdLocationName
1Some Location Name
2Another 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?

2 ACCEPTED SOLUTIONS

Accepted Solutions

I have attached 3 screen shot, one for each process.

Gallery:

'Employee.Employee'

 

Jobtitle LookUp

LookUp('Employee.JobTitle',JobId=ThisItem.JobId,JobTitle)

 

Location LookUp ( If needed)

LookUp('Employee.Location',LocationId=ThisItem.LocationId,LocationName)
------------

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.

View solution in original post

Confirmed there is definitely some weird behavior. I deleted the gallery and created a new one with the same formulas as before, including the Search function on the gallery. Location and Job Title came back blank. So then I changed the gallery reference to '[Employee.Employee]' and the Job Title and Locations started to show up, but still missing some. So I changed the gallery formula back to Search('[Employee].[Employee]',SearchBox.Text,"LastName","FirstName","FullName") and then all of the job titles and locations displayed correctly. Kinda glitchy if you ask me. What a headache for a first time build. Thanks for everyone's help!

View solution in original post

19 REPLIES 19
Drrickryp
Super User
Super User

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'.  

 

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.

@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.

@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.

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.

@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)))

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.

@eka24 Here you go:

stephenkln_0-1593026257788.png

 

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.

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,633)