Overview
This article will detail the primary methods of passing data from a North52 formula into a Fetch-XML query. These features will allow you to write and customize your Fetch queries for optimal performance and flexibility.
Fetch-XML functions in North52 are recognizable because they end in FD.
A few examples are:
- FindRecordsFD
- FindAvgFD
- FindCountFD
- FindValueFD
*Note: there is 1 exception, FindRecordsFetchXML which is different from FindRecordsFD
Source Entity values
@fieldname@
You can read an example at the below link of how to pass a source entity field into a Fetch-XML query.
https://support.north52.com/knowledgebase/articles/135080-how-to-pass-source-record-fields-to-a-formula-de
Using the '@fieldname@' syntax you can pass any field that exists on the source entity into the Fetch-XML query to make the query dynamic at runtime.
Using the SetParams() Function
SetParams() allows you to inject dynamic values returned from other North52 formula into a Fetch-XML query.
In the below example, a URL is passed from a Dialog into a formula. Then GetParamsFromUrl() function is used to extract the GUID from the URL which is then passed into the Fetch-XML query.
https://support.north52.com/knowledgebase/articles/897357-xrm-formula-83-send-sales-literature-via-dialog
When using this method you must put {0} placeholders into the Fetch-XML query so SetParams() knows where to inject the values. Use {0} for the first parameter, {1} for the second, and so on.
You can pass multiple values into the Fetch-XML query using this method as demonstrated in the sample below:
https://support.north52.com/knowledgebase/articles/901137-xrm-formula-86-voice-of-the-customer-survey-r
Please note that SetParams will presume all {} inside the Fetch XML is its business. When SetParams is been used, you cannot use {} inside the Fetch XML except as parameters
Using the AppendFormat() Function
AppendFormat('<condition attribute="name" operator="eq" value="{0}" />', 'Decision Maker,Champion')
This nifty little function will allow you to create Fetch-XML fragments and then inject them into your query to create true dynamic Fetch-XML.
The AppendFormat function takes 2 parameters, a base string and a second string of comma, separated values.
The link below shows an example where the list of required stakeholders is stored in an xCache record. The Fetch-XML fragment is also stored in a separate xCache record.
https://support.north52.com/knowledgebase/articles/894705-xrm-formula-79-check-business-process-stage-o
In this example it will create the following :
<condition attribute="name" operator="eq" value="Decision Maker">
<condition attribute="name" operator="eq" value="Champion">
These will then be injected into the Fetch-XML query replacing the {0} here.
<fetch count="50" distinct='true' >
<entity name="connectionrole" >
<filter type="or" >
{0}
</filter>
<link-entity name="connection" from="record2roleid" to="connectionroleid" >
<link-entity name="opportunity" from="opportunityid" to="record1id" >
<filter type="and" >
<condition attribute="opportunityid" operator="eq" value="@opportunityid@" />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>