Filter doesn't work with Dynamics 365's columns with space

[Anonymous]

I'm getting my data from Dynamics 365 and I want to filter by "Contact Type".

The data source Contacts has field called "Contact Type". When writing the Filter function the suggested writinng from the formula dropdown is:

Filter(Contacts, 'Contact Type' = "Customer")

It gives an error saying that:
'Contact Type' = This data type is unsupported for evaluation.
I've confirmed that "Customer" does exist in in "Contact Type". I've also tried using Contact_x0020_Type instead of 'Contact Type'. Also I used the filter function for columns without spaces (i.e. Email) and it worked out fine.

What can I do to filter based on Contact Type?


Also to add the problem seem to be related with Complex data types. I tried another Complex data type field that didn't have any spaces and it gave the same problem.

[Anonymous]

Also to add the problem seem to be related with Complex data types. I tried another Complex data type field that didn't have any spaces and it gave the same problem.

[v-siky-msft]

Hi @Anonymous ,

What is the type of "Contact Type" field?

If it is option set, the formula shoud be modified as below:

Filter(Contacts, 'Contact Type' = 'Contact Type(Contacts)'.Customer)

If it is LookUp field, the formula shoud be modified as below:

Filter(Contacts,  'Contact Type'. <Primary field of related entity> ="Customer")

Hope this can help

Best regards,

Sik

[Anonymous]

I checked a form's fields:
 
It says, "option set". I tried the formula you mentioned for option set and it didn't work. Curiosely, 'Contact Type(Contacts)' wasn't suggested by PowerApps. So I tried a different complex type and it worked fine:

Filter(Contacts, Status = 'Status (Contacts)'.Active)

I've also seen similar suggestion by Powerapps, like 'Column (DataSource)' , but not for 'Contact Type'.

[Anonymous]

I found a workaround but it is quite slow.

Filter(Contacts, Text('Contact Type') = "Customer")

It works but it takes various seconds to load while the example with status was less than a second.

In another note, there is basically no documentation on Complex Types on PowerApps. You would expect a data type that is often used between two microsoft services would have documentation but all there is, is a page on licensing.

[v-siky-msft]

Hi @Anonymous ,

Please go to check the Option set name of Contact Type column in Contacts entity.

If the Option set name is different from "Contact Type" as below,  you can try with this:

Filter(Contacts, "Contact Type" = 'Option set name'.'Customer')

If option set name is the same with "Contact Type" and still can't find it in PowerApps, I suggest you to create a new field and a new option set which name is different from field, also the name shuold be without space. 

Hope this can help.

Best regards,

Sik

[Anonymous]

I was checking the local option set and the on defining difference that I can find between field that did work (e.g. Status Reason) and the one that didn't work (e.g. Contact Type), is that "Status Reason"  doesn't have a name in option set.

The other difference is that "Contact Type" field is Custom type while the rest of the option set fields are Managed Type.

I tried changing the option set name without space , saved the entity and reloaded PowerApps but it didn't work. I tried all the formulas posted here.

I don't want to create a new field as there is already a lot of data connected witht that field. I don't want to damage anything.

[v-siky-msft]

Hi @Anonymous ,

In the multiple test, I found the workaround for this.

The error is caused by ambiguity . The names of field and option set are same, when calling the option set, we need to use disambiguity operators “@”.

Please refer the following formulas:

Filter(Contacts, 'Contact Type' = [@'Contact Type'].'Customer')

Hope this can help.

Best regards,

Sik

If this post helps, then please click on “Accept as Solution”  to help the other members find it more quickly.