cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ealvarado
Frequent Visitor

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

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.

7 REPLIES 7
ealvarado
Frequent Visitor

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
Community Support
Community Support

Hi @ealvarado ,

 

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

 

 

 

 

I checked a form's fields:
 Capture.PNG
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'.

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.

Hi @ealvarado ,

 

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')

Snipaste_2019-10-24_09-36-44.png

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

 

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.


Capture1.PNG

 

Capture2.PNG

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.


Hi @ealvarado ,

 

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')

Annotation 2019-10-28 112643.png

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.

 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (55,792)