cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhenderlight
Helper III
Helper III

Lookup call fails

I have a screen that has a gallery list that is filtered, once you choose one of the items in the list I navigate to another screen where I use the following lookup call:

 

LookUp(Students, 'Student Full Name'=Upper(TemplateGalleryList2.Selected.Result), 'Student Full Name')

 

It is grabbing the correct value for TemplateGalleryList2.Selected.Result, but I get a syntax error from the server:

 

The requested operation is invalid. Server Response: Students failed: Syntax error at position 12 in 'Student Full Name eq 'DOE, JOHN''. inner exception: Syntax error at position 12 in 'Student Full Name eq 'DOE, JOHN''. clientRequestId: d6d61879-1866-4b2d-a708-35254c67465a

 

I don't get it.

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User II
Super User II

@mhenderlight 

You can add a label to the gallery with the text property Lookup(Yourdatasource, ID=ThisItem.Result, FullName)

View solution in original post

11 REPLIES 11
Drrickryp
Super User II
Super User II

@mhenderlight 

You don't need to use a lookup.  If you are using a textinput box control, set the default property to Upper(TemplateGalleryList2.Selected.Result) or if it is a label, make that the Text property 

The reason I am doing a Lookup is that I have TemplateGallery2 bound to one table and I want to lookup the information on the current form from another table.  I am essentially trying to do a join on the Student Full Name across two tables.  Does that make sense?

 

What you propose works for the name case because the two tables share the name column.  However it won't work for the other columns that I need.

@mhenderlight 

It would make more sense to use the ID fields.  If you have a one to many relationship between the tables, it is more typically done using the primary key in the parent table and setting a foreign key in the child table.  In addition to working better because it is indexed, there is no risk of duplicates.  For more details see my blog post on this. https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-... 

I get that what you propose is the "proper" way of doing things in relational databases, however I am working with Excel spreadsheets and I don't have total control over the data and its schema.  And if I used an ID field instead of a name field I would use the very same syntax as I have used above, only the column name would be different and it seems like that would still result in the same syntax error I have now, no?

 

I guess the only difference would be that I would be matching numeric values instead of string values.

For kicks, I modified the data in my source table to include the ID as a test and that worked.  However, in my primary screen I do a Distinct call on a filter which returns a single column.  So if I use ID as the column to return from the distinct call, my list displays a list of IDs instead of a list of names which is not useful.  How can I still have a distinct function and get back more than one column?

Drrickryp
Super User II
Super User II

@mhenderlight 

I admit, I am an "old school" database designer and it pains me to not follow the basic principles.  Almost every backend datasource automatically creates an ID column but in Excel you have to do it manually.  Your syntax failed because of something in the name field.  A numeric index field likely would not have the same problem. If your students have a ID number, I would try that.

Drrickryp
Super User II
Super User II

@mhenderlight 

You have me "shadowboxing" here.  Please show some concrete data and/or a screenshot of what you are doing. 

As you can see above, I tried that and you are right it works fine except for the fact that my start screen uses a Distinct call to filter the data and I can only figure out how to return a single column when using Distinct.  If I use ID, my Distinct functions returns a list of IDs and I need a list of names.  I can't figure out how to do that.

Initial screen:

mhenderlight_0-1614190512815.png

Which sets the Items property of the list to:

mhenderlight_1-1614190561814.png

Students are assigned to specific staff members and student records can occur in the dataset multiple times therefore, I need to use Distinct function to return the list of students (without duplicates)  that are assigned to say me for example.  The problem is that if I use the ID in the Distinct function, my list returns a list of IDs as you see above.  Those IDs work fine downstream when you select a particular ID and go to the details screen where I pull data from a different table based upon the ID.

 

The problem is the screen you see above is not very useful and it only shows the list of IDs assigned to me and not the list of student names.  I don't know how I can use Distinct and get back more than one column.

 

Does that make sense?

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (3,083)