cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

Gallery Search to include fields from related table

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!!

 

 

 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Gallery Search to include fields from related table

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

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: Gallery Search to include fields from related table

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

View solution in original post

Highlighted
Microsoft
Microsoft

Re: Gallery Search to include fields from related table

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

Highlighted
Microsoft
Microsoft

Re: Gallery Search to include fields from related table

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

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Users online (8,400)