Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • Dec 07 09:19
    GuidoPreite commented #599
  • Dec 06 22:16
    filcole edited #599
  • Dec 06 22:16
    filcole opened #599
  • Nov 26 04:04
    dependabot[bot] labeled #598
  • Nov 26 04:04
    dependabot[bot] opened #598
  • Nov 26 04:04

    dependabot[bot] on nuget

    Bump NuGet.Frameworks from 5.9.… (compare)

  • Nov 26 04:04
    dependabot[bot] labeled #597
  • Nov 26 04:04
    dependabot[bot] opened #597
  • Nov 26 04:04

    dependabot[bot] on nuget

    Bump NuGet.Configuration from 5… (compare)

  • Nov 26 04:04
    dependabot[bot] labeled #596
  • Nov 26 04:04
    dependabot[bot] opened #596
  • Nov 26 04:04

    dependabot[bot] on nuget

    Bump NuGet.Versioning from 5.9.… (compare)

  • Nov 25 19:33

    dependabot[bot] on nuget

    (compare)

  • Nov 25 19:33

    dependabot[bot] on nuget

    (compare)

  • Nov 25 19:33

    dependabot[bot] on nuget

    (compare)

  • Nov 25 19:33

    dependabot[bot] on nuget

    (compare)

  • Nov 25 19:33

    dependabot[bot] on nuget

    (compare)

  • Nov 25 19:33

    dependabot[bot] on nuget

    (compare)

  • Nov 25 19:33

    dependabot[bot] on nuget

    (compare)

  • Nov 25 19:33

    dependabot[bot] on nuget

    (compare)

Jonas Rapp
@rappen
<fetch version='1.0' distinct='true' count='50' >
  <entity name='contact' >
    <attribute name='parentcustomerid' />
    <attribute name='lastname' />
    <attribute name='firstname' />
    <attribute name='contactid' />
    <attribute name='cog_ambassadorpreferredlocation5' />
    <attribute name='cog_ambassadorpreferredlocation4' />
    <attribute name='cog_ambassadorpreferredlocation3' />
    <attribute name='cog_ambassadorpreferredlocation2' />
    <attribute name='cog_ambassadorpreferredlocation1' />
    <attribute name='cog_lastdelivered' />
    <attribute name='cog_ambassadortimetotal' />
    <attribute name='cog_amabassadorregistrationdate' />
    <attribute name='cog_employersite' />
    <attribute name='emailaddress1' />
    <attribute name='cog_ambassadortrainingcompleted' />
    <attribute name='cog_stemambassador' />
    <attribute name='gendercode' />
    <attribute name='birthdate' />
    <attribute name='cog_contactethnicity' />
    <order attribute='firstname' descending='false' />
    <filter type='and' >
      <condition attribute='statecode' operator='eq' value='0' />
      <condition attribute='cog_sipambassador' operator='eq' value='1' />
      <condition attribute='parentcustomeridname' operator='like' value='%pfizer%' />
    </filter>
    <link-entity name='cdi_eventparticipation' from='cdi_contactid' to='contactid' link-type='outer' alias='ad' >
      <attribute name='cdi_event' />
      <filter type='and' >
        <condition attribute='cdi_event' operator='in' >
          <value uiname='Science in a Box Training' uitype='cdi_event' >{ED3622A6-F907-E711-80F1-000C29502450}</value>
          <value uiname='Science in a Box Training - Scotland' uitype='cdi_event' >{11BE5EEB-107D-E711-8119-000C29502450}</value>
        </condition>
      </filter>
    </link-entity>
  </entity>
</fetch>
James Rees
@wintonhero_twitter
<fetch version="1.0" distinct="true" count="50" >
<entity name="contact" >
<attribute name="parentcustomerid" />
<attribute name="lastname" />
<attribute name="firstname" />
<attribute name="contactid" />
<attribute name="cog_ambassadorpreferredlocation5" />
<attribute name="cog_ambassadorpreferredlocation4" />
<attribute name="cog_ambassadorpreferredlocation3" />
<attribute name="cog_ambassadorpreferredlocation2" />
<attribute name="cog_ambassadorpreferredlocation1" />
<attribute name="cog_lastdelivered" />
<attribute name="cog_ambassadortimetotal" />
<attribute name="cog_amabassadorregistrationdate" />
<attribute name="cog_employersite" />
<attribute name="emailaddress1" />
<attribute name="cog_ambassadortrainingcompleted" />
<attribute name="cog_stemambassador" />
<attribute name="gendercode" />
<attribute name="birthdate" />
<attribute name="cog_contactethnicity" />
<order attribute="firstname" descending="false" />
<filter type="and" >
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="cog_sipambassador" operator="eq" value="1" />
<condition attribute="parentcustomeridname" operator="like" value="%pfizer%" />
</filter>
<link-entity name="cdi_eventparticipation" from="cdi_contactid" to="contactid" link-type="outer" >
<attribute name="cdi_event" />
<filter type="and" >
<condition attribute="cdi_event" operator="in" >
<value uiname="Science in a Box Training" uitype="cdi_event" >{ED3622A6-F907-E711-80F1-000C29502450}</value>
<value uiname="Science in a Box Training - Scotland" uitype="cdi_event" >{11BE5EEB-107D-E711-8119-000C29502450}</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>
<grid name="resultset" object="2" jump="fullname" select="1" icon="1" preview="1" >
<row name="result" id="contactid" >
<cell name="firstname" width="100" />
<cell name="lastname" width="100" />
<cell name="parentcustomerid" width="150" />
<cell name="cog_ambassadorpreferredlocation1" width="100" />
<cell name="cog_ambassadorpreferredlocation2" width="100" />
<cell name="cog_ambassadorpreferredlocation3" width="100" />
<cell name="cog_ambassadorpreferredlocation4" width="100" />
<cell name="cog_ambassadorpreferredlocation5" width="100" />
<cell name="cog_lastdelivered" width="100" />
<cell name="cog_ambassadortimetotal" width="100" />
<cell name="cog_amabassadorregistrationdate" width="100" />
<cell name="cog_employersite" width="100" />
<cell name="emailaddress1" width="100" />
<cell name="cog_ambassadortrainingcompleted" width="100" />
<cell name="cog_stemambassador" width="100" />
<cell name="gendercode" width="100" />
<cell name="birthdate" width="100" />
<cell name="cog_contactethnicity" width="100" />
<cell name="cdi_event" width="100" />
</row>
</grid>
Hi Just tried that and the result is the same.. Thanks for your help and quick response.
Above is the Fetch and XML Layout.
Jonas Rapp
@rappen
Strange the LayoutXML does not seem to be looking for ad.cdi_event as the error message indicated...
Also, looking at the link-entity element, it looks like the relationship is 1:N from contact to cdi_eventparticipation. It is not possible to include child records in the view of a parent entity in CRM. You can only include information from related parents.
James Rees
@wintonhero_twitter
Thanks Jonas - I think I see the error of my ways now.
Jonas Rapp
@rappen
:+1:
Joost Pach
@joostpach_twitter
Hi, I try to create a fetchxml from multi-level linked entities. Would be like from Opportunity, go to Account and from Account go to Owner and show Opportunity.Topic, Related Account.telephone1 and Account related Owner.internalemailaddress. Would this be possible and if so you give an example?
Jonas Rapp
@rappen
Hi Joost, this is quite possible. It will not be possible to use in a view in CRM though, as you can only include information one level up the relationships.
The query would look like this:
<fetch>
  <entity name='opportunity' >
    <attribute name='name' />
    <link-entity name='account' from='accountid' to='customerid' alias='A' >
      <attribute name='telephone1' />
      <link-entity name='systemuser' from='systemuserid' to='owninguser' alias='O' >
        <attribute name='internalemailaddress' />
      </link-entity>
    </link-entity>
  </entity>
</fetch>
Joost Pach
@joostpach_twitter
Hi Jonas, thanks for your adequate and quick reply. This fetch works well indeed, but it's a petty CRM doesn't support these multi level fetches.
Jonas Rapp
@rappen
Indeed it is.... Dare we hope for it in v10 ? :smirk:
ryanmortfield
@ryanmortfield
Hi, question: Is it possible to generate a somewhat conditional fetchxml? There are two contact lookups on account, contact1 and contact2. I want to return all accounts where contact1 does not have a certain status, and where contact2 also does not have a certain status. But contact2 can potentially be null. Is this possible in one query or am I stuck doing multiple?
Jonas Rapp
@rappen
Hi Zaphod! It should be possible, will try to compose an example. Pls hold.
Something like this?
<fetch>
  <entity name='account' >
    <attribute name='name' />
    <filter type='and' >
      <condition entityname='C1' attribute='statuscode' operator='neq' value='1' />
      <filter type='or' >
        <condition entityname='C2' attribute='contactid' operator='null' />
        <condition entityname='C2' attribute='statuscode' operator='neq' value='1' />
      </filter>
    </filter>
    <link-entity name='contact' from='contactid' to='new_contactid1' link-type='inner' alias='C1' />
    <link-entity name='contact' from='contactid' to='new_contactid2' link-type='outer' alias='C2' />
  </entity>
</fetch>
ryanmortfield
@ryanmortfield
Hi Jonas, thanks for the reply! Unfortunately it doesn't quite work. Apologies for explaining poorly maybe. The case I'm having the most trouble with is: If contact1 has a status eq1 but contact2 of the same account has neq1, that account should be returned.
Jonas Rapp
@rappen
Ah.... no, comparing two fields is not possible. One of the major downsides of FetchXML in my opinion...
miller1976
@miller1976

Hi Jonas,

Running version 1.2018.2.2 and I have this query, but the QueryBuilder has trouble understanding the rowaggregate attribute. Maybe a fix in a future version?

´´´

<fetch distinct="false" no-lock="false" mapping="logical" page="1" count="250" returntotalrecordcount="true" >
<entity name="account" >
<attribute name="name" />
<attribute name="address1_city" />
<attribute name="primarycontactid" />
<attribute name="telephone1" />
<attribute name="accountid" />
<attribute name="processid" />
<attribute name="name" />
<attribute name="telephone1" />
<attribute name="address1_city" />
<attribute name="primarycontactid" />
<attribute name="parentaccountid" alias="HierarchyDataParentID6dcd9e4c001441b09237d51a34853d55" />
<attribute name="accountid" alias="HierarchyDataChildCount6dcd9e4c001441b09237d51a34853d55" rowaggregate="CountChildren" />
<filter type="and" >
<condition attribute="ownerid" operator="eq-userid" />
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<order attribute="name" descending="false" />
<link-entity name="contact" to="primarycontactid" from="contactid" link-type="outer" alias="accountprimarycontactidcontactcontactid" >
<attribute name="emailaddress1" />
</link-entity>
<link-entity name="workflow" to="processid" from="workflowid" link-type="outer" alias="processidworkflowworkflowid" >
<attribute name="versionnumber" />
</link-entity>
</entity>
</fetch>
´´´

Jonas Rapp
@rappen
Hi Miller
How did you compose that query? According to the FetchXML specs, the RowAggregate attribute is only available for the condition node in the query, and not for the attribute node.
miller1976
@miller1976
Hi Jonas,The fetchXML is taken from a view in CRM. So I guess this is MS composed.

If you run this query it actually executes, so the innards of CRM has support for the RowAggregate on the attribute node.

More precisely the fetch was taken from an out-of-the-box "Active Accounts" view.

Jonas Rapp
@rappen
That's really strange, since their own schema does not allow it...
But you're right, the query executes fine. You can do it by clicking Execute below the fetchxml window, then the query is not validated against the schema. I will bring this up with the team at MS.
However the Active Accounts view in a brand new environment (9.0.1.569) looks like this:
<fetch version='1.0' output-format='xml-platform' mapping='logical' >
  <entity name='account' >
    <attribute name='name' />
    <attribute name='address1_city' />
    <order attribute='name' descending='false' />
    <attribute name='primarycontactid' />
    <attribute name='telephone1' />
    <filter type='and' >
      <condition attribute='statecode' operator='eq' value='0' />
    </filter>
    <link-entity alias='accountprimarycontactidcontactcontactid' name='contact' from='contactid' to='primarycontactid' link-type='outer' visible='false' >
      <attribute name='emailaddress1' />
    </link-entity>
    <attribute name='accountid' />
  </entity>
</fetch>
If I can reproduce / find the query you mention, I will have a case with MS.
KiranM
@mittapallikiran
Hi All.
Its great to see a chat, I have a quick question, I am looking for a fetchXML to sql class to include in my web app, do anyone built a class/library for that?
I downloaded the code from github and found the SQL Query Generator class which is helpful, but I am not able to generate FetchType
any input is appreciated :)
Jonas Rapp
@rappen

(Sorry about the delay, I'm currently at eXtreme365 in Dubrovnik)
The FetchType object is created from the FetchXML in this method: https://github.com/Innofactor/FetchXMLBuilder/blob/master/FetchXmlBuilder/DockControls/TreeBuilderControl.cs#L202-L212
The FetchType class is generated from the fetch.xsd schema file provided by Microsoft: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/org-service/fetchxml-schema
I use this cmd file to generate the class from the schema: https://github.com/Innofactor/FetchXMLBuilder/blob/master/FetchXmlBuilder/Resources/XSD%20fetch.cs.cmd

Hope this helps!

And feel free to send a pull request my way if you find improvements to the SQL conversion! It is still a bit beta...
Jonas Rapp
@rappen
Ping @mittapallikiran :)
Clint Woods
@clintolin
Is there a page showing examples of "not in" FetchXML?
Clint Woods
@clintolin
well more complicated than not in. Where a child table contains some records with this value and no records with so me other value
The query can be enhanced to do more complex filtering, let me know scenario and I might be able to assist!
Clint Woods
@clintolin
Thanks @rappen. Contacts (students) have Leads (students at a school) which have Enrollments (custom entity). I am looking for Students who enrolled last semester, but have not registered next semester.

In this fetch, the UI complains that "entityname" is invalid in the condition which I believe I see in the example given. Haven't gotten much past that. I see that my filters may need work, but this syntax issues has me stumped

<fetch mapping="logical" version="1.0" distinct="true" output-format="xml-platform" >
<entity name="contact" >
<attribute name="fullname" />
<attribute name="xyz_studentid" />
<attribute name="contactid" />
<filter type="and" >
<condition value="0" attribute="statecode" operator="eq" />
<condition value="1" attribute="xyz_isstudent" operator="eq" />
</filter>
<link-entity name="xyz_enrollment" to="contactid" from="xyz_student" alias="Fut" link-type="outer" />
<link-entity name="xyz_enrollment" to="contactid" from="xyz_student" alias="Prev" >
<filter type="or" >
<filter type="and" >
<condition entityname="Prev" attribute="xyz_code" operator="in" >
<value>{E165DCFC-785B-E711-8119-E0071B669EB1}</value>
<value>{E065DCFC-785B-E711-8119-E0071B669EB1}</value>
</condition>
<condition entityname="Prev" value="802430000" attribute="xyz_enrollmentstatus" operator="eq" />
</filter>
<filter type="and" >
<condition entityname="Fit" attribute="xyz_code" operator="in" >
<value>{EA65DCFC-785B-E711-8119-E0071B669EB1}</value>
<value>{E865DCFC-785B-E711-8119-E0071B669EB1}</value>
<value>{E665DCFC-785B-E711-8119-E0071B669EB1}</value>
<value>{E365DCFC-785B-E711-8119-E0071B669EB1}</value>
</condition>
<condition entityname="Fut" value="802430000" attribute="xyz_enrollmentstatus" operator="eq" />
</filter>
</filter>
</link-entity>
</entity>
</fetch>

entityname should be "Prev" and "Fut" ("FIt" was a typo)
image.png
Jonas Rapp
@rappen
The entityname attribute is not supported on condition elements under linked entities. You can only use them on conditions in filters on the base entity in the query. That is why this is not possible using the designer in FetchXML Builder.
Pushkaraj garge
@Pushkaraj_garge_twitter
Hi Guys,
can i use Left or Substring function to retrieve left 100 characters of any column?
Jonas Rapp
@rappen
Hi @Pushkaraj_garge_twitter , you can't do it in the query, it has to be done in the code processing the results unfortunately.
Daryl LaBar
@daryllabar
Just export to excel and do it there
Chris Nockolds
@flexsolvetech_twitter
Now call me a dumbbum, but I can't find this plugin in XRMToolBox. What might I be doing wrong?
Chris Nockolds
@flexsolvetech_twitter
Upgrade to the latest version of xrmtoolbox - easy really.
Daryl LaBar
@daryllabar
no problem dumbbum ;) #BeenThereDoneThat