Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • Dec 06 14:58
    rappen commented #273
  • Dec 06 14:54
    dodgster02 commented #273
  • Dec 06 14:54
    dodgster02 edited #273
  • Dec 06 13:48
    rappen commented #273
  • Dec 06 11:54
    dodgster02 opened #273
  • Dec 05 10:56
    rappen commented #272
  • Dec 05 10:45
    MscrmTools opened #272
  • Dec 05 06:03
    vigneshcrm edited #271
  • Dec 05 06:01
    vigneshcrm opened #271
  • Dec 04 16:50

    rappen on master

    16 px icons updated to 2019 sty… (compare)

  • Dec 02 21:55

    rappen on FetchXMLBuilder-1.2019.12.001

    (compare)

  • Dec 02 21:52

    rappen on master

    Release notes etc (compare)

  • Dec 02 21:21

    rappen on master

    Do not show error messages duri… Merge pull request #270 from Ma… (compare)

  • Dec 02 21:21
    rappen closed #270
  • Dec 01 22:09
    MarkMpn opened #270
  • Dec 01 20:20
    MarkMpn commented #262
  • Dec 01 19:41
    rappen closed #267
  • Dec 01 19:41

    rappen on master

    Fixed #267 Flow is renamed (compare)

  • Dec 01 19:38
    rappen commented #262
  • Dec 01 19:38
    rappen commented #262
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
'#Spelled"There"CorrectlyForJonas'sSake...
Jonas Rapp
@rappen
:D
Shannon Holck
@SMHolck
New to Dynamics, but want to test out FetchXMLBuilder ... XrmToolBox says incompatible with this version (1.2017.7.18) ... any ideas what I need to do? First time using XrmToolBox too. Sorry, such a nube!
MscrmTools
@MscrmTools
Download latest version of XrmToolBox first at https://www.xrmtoolbox.com
Shannon Holck
@SMHolck
Thanks, @MscrmTools ! I think it is working now. yay!
MscrmTools
@MscrmTools
You’re welcome! And... welcome to the magnificent world of XrmToolBox 😁 I hope (well, I’m sure!) it will help you a lot in your work with D365
Shannon Holck
@SMHolck
Is it possible to open more than one FetchXML file at a time?
Daryl LaBar
@daryllabar
@SMHolck you can have multiple instances of the plugin open.
Yatin Babaria
@ya_tin_twitter
What should be the value in "Paging Cookie" to enable paging when number of records are more than 5K
Jonas Rapp
@rappen
You can see the paging cookie when you use the Raw fetch result option. Then you can copy this from the results to your query for next page.
image.png
You can also use this option to always get all pages.
Yatin Babaria
@ya_tin_twitter
Thanks Jonas.. :)
Jonas Rapp
@rappen
:)
TheFlyingBadger
@MelesVolans_twitter
Hey guys

Can anyone help with a quick FetchXML question...

<fetch top="1" >
<entity name="systemuser" >
<attribute name="systemuserid" />
<attribute name="businessunitid" />
</entity>
</fetch>

Nice'n'easy, yeah? A couple of attributes from the first systemuser record...

<resultset morerecords="0" >
<result>
<systemuserid>{DC650CAB-C453-E611-941D-00155D731475}</systemuserid>
<businessunitid dsc="" name="MRWA" type="10" >{6BB9164F-865F-E211-953C-02BFAC106E42}</businessunitid>
</result>
</resultset>

Is there any way I can suppress the @name, @dsc, @type attributes of businessunitid from being sent by the FetchXML? In my "real" use cases, i'm using FetchXML to populate a datamart and the sending across of this type of lookup data for all my rows is wasting bandwidth!

Daryl LaBar
@daryllabar
Why not use the WebAPI?
If you truly don't want it, you could apply an XLST
TheFlyingBadger
@MelesVolans_twitter
@daryllabar - a) I'm using SSIS with the Kingswaysoft extension, so FetchXML is "it"
b) I could (shudders!), but it'd still be doing the data retrieval from the server (and the network traffic as far as the XSLT processor)
Daniel Cai
@danielcai_twitter
@MelesVolans_twitter my name is Daniel Cai, I am with KingswaySoft, and want to answer your question as Daryl has looped me in this thread. Those name, type attributes are a bit noisy, but they are the way how CRM queries works. We don't use REST style queries yet, it is something that we will be looking into. REST query would sure end up with less bandwidth consumption mainly because of the message format (JSON vs XML), but not too sure if those attributes will be suppressed though.
TheFlyingBadger
@MelesVolans_twitter
@danielcai_twitter - thanks for the reply (and thanks to @daryllabar for pinging him!). I'm going nicely with kingswaysoft, I'm just marginally paranoid about network traffic - particularly once we move our CRM to 365. Cheers , Jon (in Perth, WA - hence the unusual timing!)
Jonas Rapp
@rappen
:clap: Thanks @danielcai_twitter and @daryllabar for filling in for me! :)
kamilbrz
@kamilbrz

Hi guys, is that possible to move the rows to the columns:
for example:
col1 col2 col3
x1 a 66
x2 a 33
x1 b 21
x2 b 73
x1 c 58
x2 c 11

To:
col1 a b c
x1 66 21 58
x2 33 73 11

How to achieve this using FetchXML Builder?

Jonas Rapp
@rappen
Sorry, I don't see how that could be done using FetchXML. In SQL there are some features for that, but FetchXML is a much more limited query language.
davisblumentals
@davisblumentals
I need to filter a subgrid of Account entities that are related by Connection entities. I have no problem with filtering the Connections but I cannot find a way to filter on the accountid of the current Account record. Is there any way to do this?
Jonas Rapp
@rappen
I would say that this is solved by adding a subgrid (or associated view) for Connections, where you may add the filter that the related record shall be an account. The platform itself will handle filtering by "current" account.
davisblumentals
@davisblumentals
Thank you for your response. At first I used a subgrid of connections which worked very well. But then I referenced this subgrid in a portal entity form and and found that Dynamics 365 Portal DOES NOT SUPPORT connection subgrids. I then developed a Javascript web resource to filter the account subgrid but found that all accounts are loaded before reloading filtered accounts. Perhaps the solution is to use the connection subgrid in CRM and use a liquid template with fetchxml in the portal?
Jonas Rapp
@rappen
I would suggest you contact some portal gurus out there in regards to presenting connections in the portal. Try reaching out to Adoxio and/or Colin Vermander.