Dataverse View with related columns inside PowerApps.

[ingovals]

So I'm dealing with issues trying to display and do searches and filters on the properties of a parent entity.

So I'm displaying all items of some type. The user might want to filter that type based on a property on the parent. Lets say I have a Songs entity and they are children of a Album entity. A list of all songs and the user might want to select a Year from a dropdown to filter to only songs from a Album that was released that year.

I can do:

IsBlank(DrpYear.SelectedItems.Value) || IsEmpty(DrpYear.SelectedItems.Value) || Album.Year in DrpYear.SelectedItems,

in the filter part. But I get a delegation warning, and indeed I'm not finding stuff that is supposed to be there. I find this very unreasonable. It is a simple property on the parent element, a simple JOIN or in ODATA parlance a single Expand. I could see it as more troublesome if it was multiple children.

So with Views in Dataverse I thought I had the solution. I could create a view, and add related properties there, So I could just add the Album.Year property in the view and it would just appear in the table.

So when I try to use the view in PowerApps the default behaviour is to add the view as a Filter:

 Filter(
Songs,
' Songs Views)'.'My song view',

)

So wait, are we only using the filter property of the View, not what actual columns I included?

Indeed there seems to be no difference in the actual data.

Am I just doing this wrong, how do you approach this problem?

[AhmedSalih]

Hello, @ingovals, I tried to understand the issue, but I don't think I get it. However, your delegation issue is because you are using the IN operator in the filter function, and IN is only delegated for columns on the base data source.

Now let me ask a question, is the Year Column in the Parent table "Album"?

Regards,

Ahmed

If my reply helped you, please give a 👍. And if it has solved your issue, please consider a 👍 & Accepting it as the Solution to help other members of the community find it more.

My Blog: www.powerplatformplace.com

[ingovals]

@AhmedSalih 

Yes it is.

I assumed I could perhaps use AddColumns to add the relevant properties to the DataSource, the warning goes away, but it still seems delegated. I guess AddColumns also doesn't support delegation.

I mean, it seems like a common use case. Filter a collection of entities based on some prop on their parent.

• Get all cars were the manufacturer is in Germany.
• Get all accounts were the assigned worker is on holiday.
• Get all products were the warehouse is overseas.

Is this just not possible in PowerApps?

Is it maybe possible for me to just design the API method to be called and use the return raw?

[AhmedSalih]

@ingovals, before we go the API route, let us get @Drrickryp to look at this. 

For your examples, it is doable. Filter(ChildCarsTable, ChildCarsTable.LookupColumn.Manufacture="Germany
"), but you business case is to use the IN operator with a DropDown that includes multiple items.

[Drrickryp]

@ingovals and @AhmedSalih 

AddColumns() and all data shaping functions are not delegatable although they often do not give the warning, particularly even when used inside of a filter.  If the datasource can be filtered down to 2000 items or less first, then AddColumns() can be used and the result will be accurate.  The "in" operator is now delegatable in Dataverse provided the setting is turned on.

[ingovals]

@Drrickryp 

Thank you for your input. I have this feature turned on but I still get the Delegation warning. I think the issue is not that I'm using an IN operator, but that I'm using dot notation for that, as in Album.Year