Answered

Problem with pull data from two SQL tables.

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Greetings and happy holidays all.

I have a simple canvas app that I'm trying to build. Table one is dbo.project and table two is dbo.project type. Both tables are properly connected, both bring in data, etc. All of that works.

I've got a gallery against the dbo.project and that also works. An FK in dbo.project is ProjectTypeID which is the PK in dbo.ProjectType. In Project Type there are two fields, the ProjectTypeID and the ProjectTypeName.

I have a form to the right of the Gallery. When I select one of the items in Project, the form correctly fills out. What I am trying to do is change the ProjectTypeID on form to show the ProjectTypeName instead of ProjectTypeID.

I believe you use a lookup command as shown in the screen below. This lookup command works on the first gallery item selected as shown below. But if I go to select another one, the ProjectTypeName stays the same. I'm missing the right code so when the gallery selection is made the right ProjectTypeName displays.

It is, I'm sure obvious so a nudge towards the light, so to speak, would be gratefully appreciated.

Screen Shot 2020-12-27 at 9.03.42 PM.jpg

Thanks!

Rick

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

eka24 20,925 on at

Like (0)

Report
Copy link

Link copied!

From the line "What I am trying to do is change the ProjectTypeID on form to show the ProjectTypeName instead of ProjectTypeID." , ProjectypeID is the main lookup factor and so replacing it might not work.

What is the datasource on the Gallery items, is the ProjectTypeID part of the datasource on the Gallery?

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

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello @eka24 ,
The Gallery you see comes from dbo.Project. As you select each up in the Gallery, the form next two it, does update with the record from dbo.project. In dbo.Project, yes, there is a field called ProjectTypeID which is a Foreign Key. The card in the form has the name changed to class and instead of the ProjectTypeID, I want to show the ProjectTypeName which comes from dbo.ProjectType. The lookup does work but only the first time the app is run. You can't select another record in the gallery to get a different result. It seems like I have not created the logic to say when the user selects an item in the gallery, use the current ProjectTypeID, then look it up in the dbo.ProjectType and show the ProjectTypeName. Hopefully, that helps.

Was this reply helpful? Yes No
timl 36,815 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi Rick,

Just to clarify your existing LookUp formula, the reference to ProjectTypeID refers to the ProjectTypeID field in your ProjectType table.

Your existing condition (ProjectTypeID=ProjectTypeID) will return the first record where the value of ProjectTypeID matches ProjectTypeID in the ProjectType table. This will effectively return the first record in the ProjectType table, and will not return the correct record that you expect.

The solution to this is to qualify one side of the condition so that it refers to the value of ProjectTypeID in your Project table.

If you were to change the formula on the label in your ProjectTypeID card to the following, that should resolve your problem.

LookUp('[dbo].[ProjectType]', ThisItem.ProjectID=ProjectID, ProjectTypeName)

Alternatively, you could reference the selected item in your gallery control like so:

LookUp('[dbo].[ProjectType]', YourGallery.Selected.ProjectID=ProjectID, ProjectTypeName)

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @timl ,
Thank you for taking the time to try and help. I've created a movie that I hope will show the error/problem in a better way. The two solutions you suggested don't appear to work but it is likely on me.
https://www.dropbox.com/s/dla8wtfff2z3kej/Full_Movie.mp4?dl=0
I hope that seeing it may give you a better idea of the mistake. Here are some screenshots of the dB from Data Studio. Again, thank you very very much for helping me.

Was this reply helpful? Yes No
Verified answer

timl 36,815 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Thank's for the video @Anonymous.

I see the error now. The condition in the call to LookUp should refer to ProjectTypeID like so.

LookUp('[dbo].[ProjectType]', ThisItem.ProjectTypeID=ProjectTypeID, ProjectTypeName )

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi, @timl !
I was busy sending you another movie with we're ever so close until the 'duh' moment hit me. The formula did work with a scope error. It did change in the default property as I moved through the gallery selections except it wouldn't print. A slow look at the properties showed the format was still a number which, duh, wasn't coming back, text was coming back. Change format to text and the angels sang from above. Thank you very very much for your kind efforts, this puppy is working, I get how to properly do this, and the planet continues to spin. Not bad for a day's work, eh?
Stay safe, sir!

Rick

Was this reply helpful? Yes No
timl 36,815 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Glad you solved the problem! Hope you stay safe too.

Was this reply helpful? Yes No