CDS: Filter gallery based on lookup field (combobox)

[ArleiSilva]

Hi all,

I have a custom entity named ‘Change’ as below:

Display name       Name                                     Data type                              Type                       Required

Change                   crb56_ChangeID                  Unique Identifier                 System                   Yes

Change name        crb56_ChangeName           Text                                        Custom                   Yes

Change type          crb56_ChangeType             Option Set                             Custom                   Yes

<more fields not relevant to this post>

I have another custom entity named ‘Checklist’ as below:

Display name       Name                                     Data type                              Type                       Required

Checklist                crb56_ChecklistID               Unique Identifier                 System                   Yes

Change                   crb56_ChangeChecklist      Lookup                                   Custom                   Yes

Item                        crb56_ItemChecklist           Text                                        Custom                   Yes

Answer                  crb56_AnswerChecklist     Option Set                             Custom                   Yes

<more fields not relevant to this post>

As you can see, crb56_ChangeChecklist is a lookup field that points to a record in Change entity.

Now I need to come up with a canvas app with the screen split into two areas as below:

1 – The top area should have a dropdown or a combobox I can use to select a record from the Change entity. I want to select based on the ‘Change name’ field

2 – The bottom area should have a gallery showing only the records from the Checklist entity that are related to the Change selected in the dropdown/combobox

I tried many different things (via Items property of the gallery) but no luck. I have already spent a lot of time reading posts and watching YouTube videos to no avail. I’m under the impression this is a simple thing, but I just can’t get it right.

Could you please help indicating what expression I should use on the Items property of the gallery?

Any help is appreciated.

[v-yutliu-msft · Accepted solution]

Hi @ArleiSilva ,

Do you want to filter items based on lookup field?

I've made a test for your reference:

1)connect with two entities

2)inset a drop down and a gallery

3)set the drop down's Items: testones   Value:Name

4)set the gallery's Items:

Filter(testmanies,testone.Name=Dropdown1.Selected.Name)

In my test, testones and testmanies is one-to-many relationship, testone is the look up field in testmanies.

Then, the gallery will filter based on the drop down's selection.

On your side, you could try this:

set the drop down's Items: Change  Value:Change name

set the gallery's Items:

Filter(Checklist,Change.Change name=Dropdown1.Selected.Change name)

Best regards,

Community Support Team _ Phoebe Liu

[ArleiSilva]

Hi @v-yutliu-msft 

Thank you for your help on this.
I did Filter(Checklist, Change.ChangeName = Dropdown1.Selected.ChangeName) as you suggested and it worked. I'm accepting your answer as the solution.

Now, I cannot say I really understood it.

Based on my experience using other programming languages, I was expecting the logical test (after the first comma) to be Checklist.ChangeChecklist = Dropdown1.Selected.ChangeName (and not Change.ChangeName = ...). After all, Checklist.ChangeChecklist is a look up field that points to Change table and, to me, that would be the logical way to build this filter expression (something like 'bring all records in Checklist where the content of its ChangeChecklist field is equal to ...').

Hope my confusion is clear to you. I appreciate if you can help me understand why my way does not work.

[v-yutliu-msft]

Hi @ArleiSilva ,

Do you mean that you do not understand my formula?

Firstly, the field names that I use are all display name.

Now, let my explain:

Filter(Checklist(1), Change.ChangeName(2) = Dropdown1.Selected.ChangeName(3))

Checklist is table2's name , Change is the look up field in table2 ( crb56_ChangeChecklist ).

ChangeName is the field name in table1.

(1)You want to filter Checklist, so the table name is  Checklist

(2)Look up field save the entire record's data that is related to it.

So one look up field column has one record data actually.You can see it as a table inside table.

Its structure： 

                      Change（crb56_ChangeChecklist）   

Change                       Change name                     Change type

The formula that I use:

"Change" is the lookup field's name, whose Name is crb56_ChangeChecklist.

"ChangeName" is the field inside the look up column. It means that one column in the record(of Change table) that is related to this lookup column.

(3)Dropdown1.Selected means a record of Change table.

Dropdown1.Selected.ChangeName means one column in the record.

Here's a similar issur for your reference:

https://powerusers.microsoft.com/t5/General-Discussion/Filter-gallery-items-based-on-a-lookup-field/...

Best regards,

Community Support Team _ Phoebe Liu

[victor1]

how about filtering with a combobox instead of a dropdown list?

because when i tried it it only filters by the last selected option. for example I wanted to filter by selecting optionA and optionB so the gallery shows BOTH optionA and optionB, but my gallery onlyshows optionB filtered items.

[v-yutliu-msft]

Hi @victor1 ,

Try this formula:

Filter(Checklist,Change.Change name in ComboBox1.SelectedItems.Change name)

Drop down control is used to single choice.

Best regards,

Community Support Team _ Phoebe Liu

[victor1]

---

@v-yutliu-msft wrote:

Hi @victor1 ,

Try this formula:

Filter(Checklist,Change.Change name in ComboBox1.SelectedItems.Change name)

Drop down control is used to single choice.

Best regards,

Community Support Team _ Phoebe Liu

---

Hi @v-yutliu-msft Phoebe,

I don't understand why do you 'change name' (text) rather than 'change type' (optionset)?

Sorry to be slightly off to regarding to this owner's question but my post from last week didn't get answered so i'm going to ask here. So for my demo situation I have a custom entity called 'Dog' and I have made a multi-select optionset called 'skills' containing 'Sit, Bark, Paws,....etc'. I have made a few test records: Dog1 knows Sit, Dog2 knows Bark, Dog3 knows Sit and Bark, Dog4 knows Sit,Bark,Paws, etc

In powerapps I have combobox1 which shows the skills options from above from a collection (which contains {optionset:"skillsoption", OptionValue:1, DisplayText:"Sit"},{optionset:"skillsoption", OptionValue:2, DisplayText:"Bark"}....etc). I have a gallery and want to filter to show only Dog3 and Dog4 when i select Sit and Bark in the combobox.

At the moment I have the gallery item as:
Filter(Dogs, skills in ComboBox1.SelectedItems.optionvalue)

I select Sit and Bark. However the result shows Dog1(sit) and Dog2(bark), and not Dog3 (sit and bark) and Dog4 as i wish. Is this not the correct way to do it? Do you mind showing me the correct way to doing it please?

[ArleiSilva]

Thank you for the explanations provided. They were very helpful and now I have a better understanding on how to deal with lookup fields in PowerApps.

Regards,

[JohnBrown]

@v-yutliu-msft  Does this solution work if you have two records with the same name? Take for example opportunities in the CDM that lookup to accounts. You could use this solution and match opportunity.account.name = account.name, but you are still matching based on a string/text. What would happen if you had two accounts with the same name?