You know how we’ve always been looking for ways to export data from Dynamics, and that’s never been a simple task. There are reports, there are templates, there are plugins, workflows, and custom actions.. But how about this (hint – it’s all javascript):
Export data from Dynamics
This is something that I learned from the guys at MNP (former “A Hundred Answers”). And the technique itself is pretty well documented here:
When applied to Dynamics, here is how it works step by step, and I will provide a link to the demo solution below:
- We can use javascript to download an “in-memory” csv file from the browser. Open the script above, do a search for exportToCsv, and you’ll get the idea how it’s done.
- In the V9, we can use Xrm.WebApi.retrieveMultipleRecords to get the list of records
- So all we really need to do is to combine the two items above. We can use this with a ribbon button, or we can just add a web resource with a regular html button in it (that’s what I did in the demo solution below)
You can download the demo solution here: http://itaintboring.com/downloads/DownloadScriptDemo_1_0_0_0.zip
Once you get the solution, create a DD Company record, add a few DD Location records, and click export button (have a look at the screenshots above) to see how it works.
You will see the web resource below, and it’s somewhat hardcoded to work with the ita_ddcompany and ita_ddlocation entities, but it should not be difficult to make it work with other entities. Although, it’s worth mentioning a few things about the javascript there:
- Xrm.WebApi.retrieveMultipleRecords would not work in the html web resource just like that. It’s expecting to have access to the jQueryApi object which seem to be created while the form is being loaded. Unfortunately, it’s not available in the ClientGlobalContext.js, so, there is get_jQueryApi function in that javascript below. It works, even though I’m not sure if it’s really how we should be doing it.
- I am getting access to the record id through the parent property, and that’s another one of those technique which we might better avoid. I could probably just pass that ID to the web resource, but it’s a demo script.. it works, and that’s all I wanted for now.
<html><head>
<style>
body,html
{
margin: 0px,
padding: 0px
}
</style>
<script src="../ClientGlobalContext.js.aspx" type="text/javascript"></script>
<script>
function exportToCsv(filename, rows) {
var processRow = function (row) {
var finalVal = '';
for (var j = 0; j < row.length; j++) {
var innerValue = row[j] === null ? '' : row[j].toString();
if (row[j] instanceof Date) {
innerValue = row[j].toLocaleString();
};
var result = innerValue.replace(/"/g, '""');
if (result.search(/("|,|\n)/g) >= 0)
result = '"' + result + '"';
if (j > 0)
finalVal += ',';
finalVal += result;
}
return finalVal + '\n';
};
var csvFile = '';
for (var i = 0; i < rows.length; i++) {
csvFile += processRow(rows[i]);
}
var blob = new Blob([csvFile], { type: 'text/csv;charset=utf-8;' });
if (navigator.msSaveBlob) { // IE 10+
navigator.msSaveBlob(blob, filename);
} else {
var link = document.createElement("a");
if (link.download !== undefined) { // feature detection
// Browsers that support HTML5 download attribute
var url = URL.createObjectURL(blob);
link.setAttribute("href", url);
link.setAttribute("download", filename);
link.style.visibility = 'hidden';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
}
}
var jQueryApi = null;
function get_jQueryApi()
{
var p = parent;
while(p != null)
{
if(typeof p.jQueryApi != 'undefined') {
jQueryApi = p.jQueryApi;
return;
}
if(p == p.parent) return;
p = p.parent;
}
}
function get_ParentId()
{
return parent.Xrm.Page.data.entity.getId().replace('{', '').replace('}', '');
}
function downloadRelationships()
{
get_jQueryApi();
Xrm.WebApi.retrieveMultipleRecords("ita_ddlocation", "?$select=ita_name&$top=10&$filter=ita_ddcompanyid/ita_ddcompanyid eq " + get_ParentId(), 3).then(
function success(result) {
var data = [];
for (var i = 0; i < result.entities.length; i++) {
var row = [];
row.push(result.entities[i].ita_name);
row.push(result.entities[i].ita_ddlocationid);
data.push(row);
console.log(result.entities[i]);
}
exportToCsv("data.csv", data);
// perform additional operations on retrieved records
},
function (error) {
console.log(error.message);
// handle error conditions
}
);
}
</script>
<meta><meta><meta><meta><meta><meta><meta><meta>
<button onclick="downloadRelationships();" name="Export" text="Export">Export</button>
Hope you liked it, too.. Happy 365-ing!
Alternatively you can try using Xrm.Navigation.openFile
It’s the second craziest thing I learned in just a few days.. As if what I wrote was not crazy enough:)) Put together, I’m guessing this is more or less how openFile works then.. The only reason to use the approach I described above would be to use it in pre-V9 (although, retrieveMultipleRecords would not be available there, so the code would have to be modified for pre-V9 API)
Hi Alex, Is there any limit for exporting records in csv?
Hi Rakhi,
I would think there might be. But I did not try large files myself..