Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • Jan 26 10:15
    rappen opened #630
  • Jan 21 11:45
    rappen commented #628
  • Jan 21 11:45

    rappen on master

    Updated Rappen.XTB.Helper to fi… (compare)

  • Jan 21 11:43
    rappen closed #628
  • Jan 21 08:22
    rappen closed #627
  • Jan 21 08:22

    rappen on master

    Fixed #627 - I forgot about all… (compare)

  • Jan 21 07:16
    rappen commented #628
  • Jan 21 07:07
    rappen commented #627
  • Jan 21 07:00
    rappen edited #627
  • Jan 21 00:04
    markduk opened #628
  • Jan 20 23:06
    ali-shafique commented #627
  • Jan 20 23:03
    ali-shafique commented #627
  • Jan 20 14:18
    jxs0900 opened #627
  • Jan 19 15:31
    rappen commented #624
  • Jan 19 15:20
    rappen commented #624
  • Jan 19 11:43
    citqpdarcy commented #624
  • Jan 19 11:12
    rappen closed #624
  • Jan 19 11:12
    rappen commented #624
  • Jan 19 11:01
    TaroAM commented #624
  • Jan 19 10:57

    rappen on FetchXMLBuilder-1.2022.01.004

    (compare)

Rsilla
@Rsilla
yup. thank you. will give it a try or maybe a plugin could help!
Jonas Rapp
@rappen
A plugin always helps - Jonas' First Rule ;)
CrmHM
@CrmHM
Hi
How to export results (Raw Fetch results in my case) with another encoding?
I have some data in Hebrew
Any ideas?
Jonas Rapp
@rappen
Aouch.... that might not be so easy. Is it possible to open saved file with some other editor and re-save with desired encoding? Or is information lost on the way then?
CrmHM
@CrmHM
Yes, it works (Another editor or FetchXML Tester). But I was wondering if there was a faster way.
Thank you
Jonas Rapp
@rappen
Sorry, no. But add an issue and it might get implemented!
CrmHM
@CrmHM
I'll do. It may be useful to other people
Jonas Rapp
@rappen
:+1:
Ian Nicholls
@forestuk2001_twitter
Hi, how do I use the paging option in FXB to return multiple pages? tx
Jonas Rapp
@rappen
Have you tried this setting in FXB Options?
image.png
Panayiotis Panayiotou
@panayiotisp
Possibly a context menu option or a toolbutton to refresh the metadata loaded for the entities already in the fetchxml. A lot of times it happens we are editing queries and then a new field or a change is required (like a new optionset) and you need to either close the editor or switch between entities in the query to force it to update the cached metadata
Jonas Rapp
@rappen
Nice idea :+1: Could you post it here please? https://github.com/Innofactor/FetchXMLBuilder/issues
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" alias="EventName1" />
<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>
Good afternoon - Running the above fetechXML to create a view but when I try and run it in CRM I get Could not find relationship for related entity field ad.cdi_event specified in layoutxml.
Jonas Rapp
@rappen
Hi! Could you run the View Designer tool in XTB? Open the view, click Edit XML button to see the underlying Layout XML ?
Or first test this:
On the link-entity element, add alias "ad".
On the link-entity attribute "cdi_event" element, remove the alias.
<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!