Filter Gallery with Dataverse Text Field by Lookup in a related Dataverse table

[Hsnowbee]

Hello,

I have a listing of courses in a custom Dataverse table (Courses table). When users add a course to their shopping cart in the app, it is patched to another custom table in Dataverse (Staff Training Table) with the Course Title and the user's name so the user's 'shopping cart' is saved even after the app is closed. The "Course Title" field is a Lookup Field in the Staff Training Table that relates to the Courses Table.  

I have a gallery that I am using to display courses in the user's shopping cart, but I cannot figure out how to filter the gallery items (the gallery data source is the Courses table) to display only those courses that were patched to the Staff Training table (e.g. the shopping cart) during the user's current or prior session. I think I am trying to do something similar to this: Solved: Filter SharePoint List from Lookup Column in Anoth... - Power Platform Community (microsoft...., or this Solved: Filter CDS with related fields in Canwas app gall... - Power Platform Community (microsoft.c... but I am having trouble figuring out how to apply those solutions to my scenario. Can someone help me with how to approach this? Thanks in advance!

Filter(Courses, 'Course Title' in ForAll('Staff Trainings', 'Course Title'.Value And Name=MyUserFullName*))

*variable with the user's name

[Anonymous]

Hi HSnowbee,

have you thought of using the Staff Training Table as Gallery Data Source?

You can select columns of the Courses Table directly thanks to Dataverse relationship management.

Filter('Staff Trainings', Name=MyUserFullName)

You can select the columns in Courses with the .field notation.

I.e. if your Course Table has a column "Type", you can show this field by using 'Staff Trainings'.'Course Title'.Type

I'm linking to additional documentation:

Using Microsoft Dataverse with a canvas app in Power Apps - Power Platform | Microsoft Docs

Cheers,

RoB

[Hsnowbee]

Yes, thanks for the reply @Anonymous! That would certainly make things simpler, but the Staff Trainings table does not have all of the course details (e.g. description, cost, vendor name, etc) and I would like to be able to display those details in the shopping cart gallery. Do you think it would be eaiser to make Staff Trainings the gallery source data, and then pull those detail fields from the related Courses table? I have not tried that approach yet...

[Anonymous · Accepted solution]

I think it would be easier. You can use then in your gallery the following properties:

'Staff Trainings'.'Course Title'.description

'Staff Trainings'.'Course Title'.cost

'Staff Trainings'.'Course Title'.vendor name

[Hsnowbee]

Yes, @Anonymous , I think that is a good work-around! If you don't mind, could I bother you with one more question on this topic? The Vendor Name in the Course table is a LookUp from a Vendor table. I tried using ThisItem.'Course Title'.Vendors.'vendor name', but it did not work. Do you happen to know how I could display that lookup field? Thanks again for your help!

[Hsnowbee]

Okay, just to close the loop on this - I found the answer to my question regarding pulling in the Vendor's name from another related table. I was receiving an error regarding multiple relations and the solution was here: Solved: how to overcome multiple levels of relationship ex... - Power Platform Community (microsoft....

LookUp('Courses', 'Course Title'=ThisItem.'Course Title'.'Course Title', Vendor).'Vendor Name'

[Anonymous]

Hi Hsnowbee,

fantastic! Thanks also for sharing!

Cheers!