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?

19 REPLIES 19

@eka24 I've tried that. I get the following errors that may be related:

 

The first is an invalid argument error on the "=" in the formula.

screen1.png

 

The second is a delegation warning. (My dataset does have <500 records).

Screen2.png

Use;

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

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

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 Unfortunately, that seems to return nothing:

stephenkln_0-1593088115927.png

 

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.

Thanks @eka24 ! Job Title is now showing correctly! But unfortunately, Location is returning blank...

stephenkln_0-1593101066311.png

Does this mean anything?

stephenkln_1-1593101103221.png

 

I asked that you give all the tables. You did not give the location table. Can you display the table?

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

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.

So I have no idea why, but Location suddenly started showing. I added a Text Input to use for a search box and then I changed the Gallery reference from '[Employee.Employee]' to Search('[Employee].[Employee]',SearchBox.Text,"LastName","FullName"). After that, Location started showing. Very odd behavior. I guess it's a little finicky sometimes?

 

 

 

Try:

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

or

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

 

If both Dont work,

OnVisible put:

ClearCollect(MyLocation,'Employee.Location')

 

Then revise the LookUp formula to:
LookUp(MyLocation,LocationId=ThisItem.LocationId,LocationName)

 

After the formula, move to another screen and back to check if working

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

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.

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!

@stephenkln , @eka24 

I know you found a solution .. What i find best is to create a "View" that joins the two tables thus avoiding the need to do a lookup locally in the power app. 

 

Regards

 

MRRCOMP

Helpful resources

Announcements
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 (2,993)