Extracting Dynamics Query
When you Create Dynamics Integrated Content in Externable, you need define your FetchXML or SQL query.
FetchXML Query
Dynamics/Dataverse comes with a very handy feature which is Advanced Find. This tool allows you to build queries to Dynamics, which you can paste to Externable to tell it what data you want extracted from Dynamics.
To open Advanced Find go to your Dynamics instance and click on filter icon:
This will open Advanced Find popup. In this view you can build the query as per your requirements. You can join multiple tables to filter and present data.
In the main window you construct a filter conditions. You can narrow down the list of records you want to be included in the Content Node. Those could be static filters, like show only records where 'Status' Equals 'Active'.
However those conditions you need remeber about the most are filtering conditions for Externable logged in Member. If you create a query which will retrieve all atcive orders, all active orders from your DYnamics will be presented to every portal visior. You most likely don't want it to happen, so you need to include a filter to show only orders, which are assigned to certain contact.
All Default Template pages natively use filtering so you don't need to worry about it. It is however very important when you build custom functionality on Externable.
In Advanced Find view you can temporarily select hardcoded contact, but when you past the view to Externable you can apply dynamic parameters which will inject current Member guid whenever query is executed.
See the video explaining hwo to create FetchXML query and add it to Externable Content Node:
Data Export Service SQL query
As explained in Create Dynamics Integrated Content page, you may prefer to connect to Data Export Service (DES) whenever high load of request or large datasets are expected.
DES is a feature of Dynamics to export data dump to Azure hosted SQL database to avoid querying Dynamics directly for records which you can accept delay for.
Querying DES is effectively building SQL query to standard SQL database as standard SQL database cannot use Advance Find exported FetchXML queries as input.
Note that Dynamics CDS/Dataverse to SQL is not API friendly as it just exposes Dynamics API with SQL layer on top. We recommend using separate Azure hosted DES database for limiting use of API calls.
Unfortunately there is no native tool in Dynamics UI to convert FetchXML to SQL queries, however you can use existing tools outside of Dynamics, which can do very decent job for you.
- https://www.xrmtoolbox.com/plugins/MarkMpn.SQL4CDS/
- https://www.xrmtoolbox.com/plugins/Cinteros.Xrm.FetchXmlBuilder/
When you have your query ready it is worth testing it in SQL Server Management Studio to make sure it gives you result you expect. When you paste SQL query to Externable, please select 'Connect To: Data Export Service Database':
