Lookup call fails

[mhenderlight]

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.

[Drrickryp]

@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 

[mhenderlight]

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.

[Drrickryp]

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

[mhenderlight]

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.

[mhenderlight]

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]

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

@mhenderlight 

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

[mhenderlight]

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.

[mhenderlight]

Initial screen:

Which sets the Items property of the list to:

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?