2

I'm querying Dynamics CRM using fetchXML. I have an entity that have attribute (placeName) where its value is either string or value of a number. I would like to have condition that only the records with a non-number value should be selected. I didn't find any solution for that in the dynamics documents, but maybe there is a solution using an "out-of-the-box" (custom condition). This is my current fetch query:

<fetch mapping="logical" distinct="true" version="1.0">
  <entity name="locations">
    <attribute name="placeID" />
    <attribute name="placeName" /> // This can be values like "home" or 100 - I would like to take out only those which are not a number
  </entity>

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
Guy E
  • 1,775
  • 2
  • 27
  • 55
  • did my answer help you achieve what you needed? if so, please mark it as accepted. if not, let me know so I can further help you. – OfirD Feb 26 '20 at 10:53
  • @HeyJude - I didn't have the chance to check it yet. I'll update. Thanks – Guy E Feb 26 '20 at 11:28
  • any update? did it work for you? – OfirD Mar 01 '20 at 23:10
  • @HeyJude - no, not yet - it is election day today - no work - I'll try it tomorrow (meanwhile, I made a work around by filtering after getting the data) - promise to update ! – Guy E Mar 02 '20 at 16:59

1 Answers1

3

Though I couldn't find it documented anywhere, you can use like operator with regex-ish syntax.

For example, the following query would retrieve systemuser records that only contains numbers in their domainname:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
   <entity name="systemuser">
      <condition attribute="domainname" operator="like" value="%[0-9]%" />
   </entity>
</fetch>

And in your case, the following would retrieve records only with letters a-z or A-Z: 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
   <entity name="locations">
      <condition attribute="placeName" operator="not-like" value="%[0-9]%" />
   </entity>
</fetch>
OfirD
  • 9,442
  • 5
  • 47
  • 90
  • A small update - It worked with the first Regex, of the numeric, but not with that of the alpha-beta. The good news is that there is an operator of "not-like", where you can combine with the numeric regex – Guy E Mar 09 '20 at 15:01