Gallery Search to include fields from related table

[ceperezb]

hi experts, hoping you can help:  I am working on a Canvas Power App with two CDS entities, for which there is a 1 to many relationship:

1) Entity:  CourseDeliveries - includes fields for courseID ("cre23_coursedelivery") and courseName ("cre23_coursename")

2) Entity:  CourseRegistrations - includes field for registration_courseID ("cre23_registration_courseid") that is a lookup on courseID from the course deliveries entity

Question1:  I have a gallery to view course registrations.  My gallery search works fine if I do NOT include the field for registration_courseID.  As soon as I include the field "cre23_registration_courseid",  (which is based on lookup from my other table), I get the error that the specified column does not exist.  I have tried also including "cre23_registration_courseid.cre23_coursedelivery", but nothing seems to work. Any guidance on how to get the cre23_registration_courseid as part of my gallery search?

 SortByColumns(Search([@CourseRegistrations], TextSearchBox1.Text, "cre23_registration_courseid", "cre23_registration_email","cre23_registration_firstname", "cre23_registration_lastname"), "cre23_courseregistration", If(SortDescending1, Descending, Ascending))

question2:  I realize my gallery table is based on  CourseRegistrations entity, but I also want to get courseName that is part of the other entity (after-all, the tables are related). Is the recommended approach here to create a collection?  

Any guidance is appreciated.  thanks!!

[v-bofeng-msft · Accepted solution]

Hi @ ceperezb:

Question 1:

Do you want to search the records in “CourseRegistrations” entity by “courseId” field that is in “CourseDeliveries” entity?

I am afraid this is not feasible. The point is ” registration_courseid” is a lookup field, but the search function only supports text type.

I think this link will help you a lot:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Case1: I suggest you use the filter function instead.

I suggest you adjust your code like this:

SortByColumns(filter(CourseRegistrations,TextSearchBox1.Text in registration_courseid.courseID || TextSearchBox1.Text in cre23_registration_email || TextSearchBox1.Text in cre23_registration_firstname || TextSearchBox1.Text in cre23_registration_lastname), "cre23_courseregistration", If(SortDescending1, Descending, Ascending))

Case2: If you insist on using search function, I suggest you try this solution

I suggest you adjust your code like this:

 SortByColumns(Search(AddColumns(CourseRegistrations,"courseID",registration_courseid.courseID), TextSearchBox1.Text, "courseID", "cre23_registration_email","cre23_registration_firstname", "cre23_registration_lastname"), "cre23_courseregistration", If(SortDescending1, Descending, Ascending))

I refactored the data source of the search function by this code

AddColumns(CourseRegistrations,"courseID",registration_courseid.courseID)

This will not affect your two entities.

Question 2:

What is the “other entity”?(courseName that is part of the other entity)

Do you want to get a table with all the contents of CourseRegistrations entity, it also contains the courseName field in CourseDeliveries entity?

I assume the other entity is CourseDeliveries.

I suggest you try this code:

AddColumns(CourseRegistrations,"courseID",registration_courseid.courseID,"courseName",registration_courseid.courseName)

The point is using AddCloumns function to adjust the structure of the table so that it meets our needs

I think this link will help you a lot:

AddColumns, DropColumns, RenameColumns, and ShowColumns functions in Power Apps: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping

Best Regards,

Bof

[ceperezb]

THANK YOU, so much!!!!!  This did it.  

[ceperezb]

@v-bofeng-msft - One other related question.  Although I can successfully search with the guidance you provided (very helpful info). I do get errors on the edit and details screen of my gallery.  The selected gallery item is not recognized.  I think I know why -- from the documentation it indicates that this only transforms an input table without modifying the original into an output table for further use.

When I select the item from BrowseGallery, the Details screen is from BrowseGallery1.Selected gives error because it expects a value compatible with the data source.  The added column is not compatible with the data source, hence the error.  

Is there a way to select a gallery item to perform action on details/edit screen based on the real data columns (not the added columns)?  I still want the flexibility of the search as it provides important information, but ultimately what the user selects can be on the actual data source columns. 

I tried different options for the Item function of the Details and Edit forms (something other than BrowseGallery1.Selected), but all gave errors.

thanks!!