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

eka24
Super User III
Super User III

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.

View solution in original post

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?

 

 

 

eka24
Super User III
Super User III

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!

View solution in original post

@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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (87,179)