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?

[stephenkln]

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

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

 

[eka24]

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.

[stephenkln]

@eka24 Unfortunately, that seems to return nothing:

[eka24 · Accepted solution]

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.

[stephenkln]

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

Does this mean anything?

[eka24]

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.

[stephenkln]

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]

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.

[stephenkln · Accepted solution]

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!

[Mrrcomp]

@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