cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Problem with pull data from two SQL tables.

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

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User
Super User

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
)

 

View solution in original post

7 REPLIES 7
eka24
Super User
Super User

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.

 

Anonymous
Not applicable

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. 

timl
Super User
Super User

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)
Anonymous
Not applicable

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.

Screen Shot 2020-12-28 at 6.45.54 AM.jpg

 

Screen Shot 2020-12-28 at 6.46.12 AM.jpg

 

Screen Shot 2020-12-28 at 6.46.33 AM.jpg

 

 

timl
Super User
Super User

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
)

 

Anonymous
Not applicable

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

timl
Super User
Super User

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

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 (3,930)