How to filter related ( 1:Many relationship) entity values displayed in the gallery control?

[dave8]

Hello,

I have two entities ( Entity 1, Entity 2) with one to many relationships, I am binding Gallery with "Entity 1" and displaying it related items of "Entity 2"

How do I filter gallery items that is bind with the "Entity 1" with the value of "Entity 2" fields? in the filter condition of gallery control, I am unable to pick up fields of "Entity 2" , while when I use the control that displays "Entity 2" values, it throws following error:

How can I resolve above formula error for the second filter option that comes from the related entity value?

Thanks,

[jlindstrom]

You cannot filter data across entities in a gallery. 
Everything must be in the same entity.

that doesn’t mean that you don’t have any options. One really good one in CDS is system views. For example if I want to have a gallery where I get opportunities where the related account owner is the user of the app, I would build that as a system view, then I can further filter in my filter formula.

You can also display related entity stuff in your gallery using Lookup formulas in the gallery fields

[dave8]

Hi @jlindstrom 

Thank you for your attention, what do you mean by "system view" ? do you mean that,  for related item views, I can just create a system view in entity/model driven apps which would have possibility of multiple filter options including related records?

Thanks,

[jlindstrom]

It's not for relational filtering--you have the lookup for the related entity on your primary entity, so you don't need to build the relationship details into the system view. If I want to filter where the opportunities are for company X, I have the account lookup field already in my opportuntiy entity, so I can get to that.

But consider the following scenarios:

I want to see opportunities where I am the owner of the company: I can use a system view for opportuntiies where the related account is owned by current user.

I want to see opportuntiies closing in the next X days where the region of the account is SouthWest: I can use a system view of opportunities where the related account is in the southwest region, and then filter that in the powerapp to close date = X

I want to see activities related to accounts that have an opportunity closing in the next X days. Create a system view of activities with accounts with opportunities closing in next x days and use it in your gallery.

Note this doesn't solve all of your issues, but it is a big step in that direction. I also overcomes may delegation issues.

https://powerapps.microsoft.com/en-us/blog/improved-data-source-selection-and-common-data-service-vi...

If you need to have dynamic filtering on multiple entity levels, You can't always get to it just with sytem views--if I needed to have a dynamic filter on multiple entities, I would bring it into a collection and use lookups to add the related entity fields to the collection, then use the collection in my gallery and filter off of the entire thing.

[v-xida-msft]

Hi @dave8 ,

Could you please share a bit more about your whole formula?

Regarding the error that you mentioned, it mainly told that -- within the Filter formula, you are referencing some TextInput box value, or Dropdown box selected value, ...whose value would be affected by the Filter formula result.

Also please consider remove the filter condition highlighted in "yellow" as above screen from your Filter function, then check if the issue disappear.

As an alternative solution, you could consider add two separated Galleries control (Gallery1 and Gallery2) for your Entity 1 and Entity 2. Gallery1 is used to display your Entity1 records, and the Gallery2 is used to display your Entity2 records. On your side, you could apply your Filter formula to your Gallery1 Items property. Then set the Items property of the Gallery2 to following:

Gallery1.Selected.'relationship_name'

Note: The 'relationship_name' represents the One-to-Many relationship name in your Entity 1.

When you select one item in your Gallery1, these related Entity 2 records would be shown up in the Gallery2 against the 'One-to-Many' relationship.

Regards,

[dave8]

Hi @v-xida-msft 

My requirement is to only filter single gallery that successfully displays parent Entity 1 and child Entity 2 data.However Filter formula doesn't work for the related data as follows:

Filter(Entity1,DateFilter=dateValue) - works, because Gallery is bound with Entity1 and DateFilter is a field from Entity1

Filter(Entity1,DateFilter=dateValue && ddlFieldStatus=Open) - doesn't work, because Gallery is bound with Entity1 and ddlFieldStatus is a field from Entity 2

Therefore, if I remove highlighted part in the formula, error will be removed, however my need will not be achieved.While system views of the entities, doesn't shows filter option in mobile view.

Therefore, How can I update formula that shows items in the single gallery with two filter options coming from Parent Entity 1 and Child Entity 2?

Thanks,

[v-xida-msft]

Hi @dave8 ,

Is there a LookUp field in your Entity2 reference values from your Entity1, right?

If you want to filter your Entity1 records based on the DateFilter in Entity1 and the ddlFieldStatus field in Child Entity2, I afraid that there is no way to achieve your needs.

The Entity1 has One-to-Many relationship to Entity2, so one Entity1 record could match multiple records in Enttiy2. Do you want to filter Entity1 records based on all related Entity2 records whose ddlFieldStatus field value is "Open", Or filter Entity1 records based on at least one Entity2 record whose ddlFieldStatus field value is "Open"?

If you want to filter Entity1 records based on all related Entity2 records whose ddlFieldStatus field value is "Open", please modify your formula as below:

Filter(
       Entity1,
       DateFilter=dateValue,
       CountRows(
                 Filter(Entity2, LookUpField.'Unique Identifier Column' = 'Unique Identifier Column', ddlFieldStatus <> Open)
       ) = 0
)

If you want to filter Entity1 records based on at least one Entity2 record whose ddlFieldStatus field value is "Open", please modify your formula as below:

Filter(
       Entity1,
       DateFilter=dateValue,
       CountRows(
                 Filter(Entity2, LookUpField.'Unique Identifier Column' = 'Unique Identifier Column', ddlFieldStatus = Open)
       ) > 0
)

Note: The 'LookUpField' represents the LookUp field in your Entity2, which generated through the "One-to-Many" relationship. The 'Unique Identifier Column' represents the Unique Identifier column in your Entity1, which could identify one record uniquely.

Please try above solution, check if the issue is solved.

Regards,

[dave8]

hi @v-xida-msft 

I think I need what you have mentioned for the formula, however it still throws an error!

When I set below formula for the label, label shows value "0"

CountRows(Filter('Entity2', 'LookUpFieldColumn'=ddlStatus.Selected.Value))

However, if I use the same formula in the Filter condition of gallery, it throws following error:

Any clue what could be wrong?

Thanks,

[v-xida-msft]

Hi @dave8 ,

Could you please show more details about the "ddlFieldStatus"? Is it a Option Set field or a Dropdown box?

Regarding the formula that you mentioned, I think there is something wrong with it. Firstly, The 'LookUpField' I mentioned in my formula represents the LookUp type field in your Entity2, which is generated by the "One-to-Many" relationship automatically (from Entity1 to Entity2).

I suppose that the 'Status' column is a Option Set type column in your Entity2, but in your first Filter formula, you compare the Option Set type column value with the LookUp type column value, it's wrong.

For your first Filter formula, you should modify as below:

CountRows(
           Filter(
                  'Entity2', 
                  'LookUpFieldColumn'.'Unique Identifier Column' = "A GUID value of a specific record from your Entity1"
           )
)

Note: The 'Unique identifier column' in a Entity would have same name as the Entity itself, which is "Unique Identifier" type

If you want to filter Entity2 records based on Option Set field value, please try the following formula:

Filter(
           'Entity2', 
           OptionSetField = [@OptionSetName].Option1
)

Note: The OptionSetName represents the Option Set related to the Option Set Field in your Entity2.

Filter(
       Entity1,
       DateFilter=dateValue,
       CountRows(
                 Filter(Entity2, LookUpField.'Unique Identifier Column' = Entity1[@'Unique Identifier Column'])
       ) = 0
)

or

Filter(
       Entity1,
       DateFilter=dateValue,
       CountRows(
                 Filter(Entity2, LookUpField.'Unique Identifier Column' = Entity1[@'Unique Identifier Column'])
       ) > 0
)

Please try above formula, check if the issue is solved. If you could share more details about your formula (do not hide the field name), it would be help in solving your problem.

Regards,

[dave8]

Hi,

ddlFieldStatus == Opetion Set in Entity 2, It's not a relationship field with Entity 1.while relationship between Entity 1 and 2 is as follows:

Below will not solve an issue, as I do not need to filter records based on relationship field, but based on status field selected in the dropdown.

Filter(
       Entity1,
       DateFilter=dateValue,
       CountRows(
                 Filter(Entity2, LookUpField.'Unique Identifier Column' = Entity1[@'Unique Identifier Column'])
       ) > 0
)

Further, while using following formula in Gallery items's Filter formula:

Filter(
           'Entity2',
           OptionSetField = [@OptionSetName].Option1
)

It throws an error that Filter condition has some invalid arguments, which works well separately, but doesn't work inside filter formula.
 
Therefore, it looks like to me that - multiple filter is not possible with related items!

Thanks,