Showing results for 
Search instead for 
Did you mean: 
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

1Smith, John11
2Doe, Jane22


Jobs Tbl

1Some Job Title
2Another Job Title


Locations Tbl

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?


Accepted Solutions

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




Jobtitle LookUp



Location LookUp ( If needed)


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

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


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.

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



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.



For the basic lookup:



For the collections:



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:



Since you prefer the basic,

On the Gallery Items, put the employee table


Then in one of the Labels inside the Gallery put:



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

Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

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