Filtering Lookups by GUID and OptionSets by Option Set Integer Values in CDS / Canvas

[anasufovic]

I pulled in a single custom entity in CDS as a data source in a Canvas app. Using the formula tool it is not obvious as to what comparisons are supported for fields and I am wondering as to what is the proper way to handle the following:

• On my custom child entity I have a lookup field to it's parent entity. Using the model form integration for Canvas apps I have the GUID of the parent entity and would like to filter the child CDS entity using this GUID. What is the proper way to accomplish this?
• Similarly on my custom entity I have a standard option set field. How do I use the option set integer values to filter on this entity?

So far I have not been able to filter option set or lookup fields in any way. I have only been able to filter with GUID using the primary key of the child entity or filtering via text fields such as the name field.

Thank you

[v-monli-msft]

Hi @anasufovic ,

To filter with lookup column, using a formula like below one:

Filter( Name, LookupColumn.LookupIdColumn = "GUID")

The ID column may have a different name but it usually consists of the entity name + id, all lowercase.  You can check in the options PowerApps gives you after you type "." behind Lookuo column name. 

Do you want to populate an Option Set column value from a CDS entity into a dropdown control?

The user @JamieD76 has faced similar issue with you, please check my response within the following thread:

https://powerusers.microsoft.com/t5/Common-Data-Service-for-Apps/Option-Set-not-Displaying-on-DropDo...

Currently, Dropdown control or Combo Box control do not support Option Set/MultiSelect Option Set data type from CDS entity/Dynamics 365, the Product Team is working on this feature.

If you want to populate an Option Set column value from a CDS entity into a Combo Box control, I afraid that there is no way to achieve your needs in PowerApps currently.

Please try to add an idea in below forum if you need this feature:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

Regards,

Mona

[anasufovic]

Hi @v-monli-msft, thank you for the pointer. I was able to get the GUID filter to work. However, I do get a delegation warning when I use this filter, is there anyway to resolve this or is this expected behavior? This entity has millions of records in it but the filter below should only return a few records. I noticed that the OData query generated by this uses an expand as opposed to filtering on the lookupid column. 

Filter(
        'Entity', Account.Account = GUID("3ee715ff-e66d-e911-a988-000d3a1378f6")
    )

In regards to my option set problem

Filter(
        'Entity', 'Option Set' = 123123
    )

Filter(
        'Entity', 'Option Set' = "Label"
    )

I tried the above two ways to filter using the integar value and the name and both give an "Incompatible Type" error. The tool doesn't make it clear what this field expects.

Thanks again for the help

[Anonymous]

Any update to this? I am also getting a delegation warning when trying to do a LookUp similar to your Filter.  My LookUp is on the Account entity's GUID column, accountid.  This same LookUp function works as expected without delegation warnings for Dynamics 365 data sources.

[FransKuylenburg]

Filter(tbl_contactens,tbl_contactRelatieGuid.tbl_relaties=setGuid)

In dataverse june 2022 this still works. Otherwise, there will also be deligation problems.

Thanks!!!!