Export from a table using snippet that uses table columns as export rows? Also only visible column

Solved!

mega thanks to Youri with his snippets to get at the column names in a table and his super cool idea to have buttons that can quickly change the set of visible columns: https://community.skuid.com/t/snippet-to-show-hide-set-of-column-in-table

var params = arguments[0], $ = skuid&#46;$; var model = skuid&#46;$M('ListingTransplant'); var fields = []; var columnsArray = []; var settings = params&#46;component&#46;_personalizationService&#46;getSettings(); var columns = settings&#46;columnSettingsByUID; &#47;&#47;put each column into an array that can be sorted $&#46;each (columns, function(i,column){ columnsArray&#46;push(column); }); columnsArray&#46;sort(function(a,b) {return (a&#46;order &gt; b&#46;order) ? 1 : ((b&#46;order &gt; a&#46;order) ? -1 : 0);} ); &#47;&#47;loop through the columns and push the field api name to the fields array $&#46;each (columnsArray, function(i,column){ var field = column&#46;fieldId; var hidden = column&#46;userHidden; if (!!field &amp;&amp; !hidden){ fields&#46;push(field); } }); &#47;&#47;the result gets populated in reverse order, this puts it right var fieldNames = fields&#46;reverse(); &#47;&#47;get the field objects from each field api name &#47;&#47;note template fields are not included, and lookup fields seem to always return the Id not the Name var fieldsWithCorrectLabels = $&#46;map(fieldNamess, function(v){ var actualField = model&#46;getField(v); return { id: actualField&#46;id, label: actualField&#46;label }; }); model&#46;exportData({ fields: fieldsWithCorrectLabels, doNotAppendRowIdColumn: true, useAPINamesForHeaders: false });<br />

Jack and Youri, thank you both for taking the time to highlight this use case and your solution.  Your contributions to the community don’t go unnoticed!

Thanks for this. This is precisely what I’m trying to do.

Just tried this but I can’t get it working. I entered my model name into the snippet at the top and otherwise just used the code you provided. There’s a typo “fieldNamess” in the code; I corrected that and the CSV downloaded, but there’s no columns or data in the CSV. Not sure where it’s going wrong. Any Ideas?

Hi Mark, here’s my current version of the working code, it’s been tweaked since I posted last year

it looks like i’ve removed the fieldNames part you found was a mistake
couple things to check out - make sure your table has the same unique you put into the code, and i noticed my table model is set to process client-side, not sure if that will make a difference, and also my page settings have personalization set to process server-side, again not sure if that makes a difference. If you need help finding where those things are set let me know. 

var params = arguments[0], $ = skuid.$; var model = skuid.$M('YourModelName'); var fields = []; var columnsArray = []; var table = skuid.$C('your-table-unique-id'); var settings = table._personalizationService.getSettings(); var columns = settings.columnSettingsByUID; //put each column into an array that can be sorted $.each (columns, function(i,column){ columnsArray.push(column); }); columnsArray.sort(function(a,b) {return (a.order > b.order) ? 1 : ((b.order > a.order) ? -1 : 0);} ); console.log(columnsArray); //loop through the columns and push the field api name to the fields array $.each (columnsArray, function(i,column){ var field = column.fieldId; var hidden = column.userHidden; var label = column.label; //how to deal with Template Fields if (label==='Template Field Name' && !hidden){ var field1 = 'First_Field__c', field2 = 'Second_Field__c'; fields.push(field1); fields.push(field2); } if (!!field && !hidden){ fields.push(field); } }); //get the field objects from each field api name //note template fields are not included, and lookup fields seem to always return the Id not the Name var fieldsWithCorrectLabels = $.map(fields, function(v){ var actualField = model.getField(v); return { id: actualField.id, label: actualField.label }; }); model.exportData({ fields: fieldsWithCorrectLabels, doNotAppendRowIdColumn: true, useAPINamesForHeaders: false }); 

Mark I think, looking at my old code, just updating this part will get it to work

var table = skuid.$C(‘your-table-unique-id’); var settings = table._personalizationService.getSettings(); var columns = settings.columnSettingsByUID;

The other neat thing about the latest version is being able to handle Template fields 🙂 You could use the same pattern to return the Name value from Lookup fields instead of the Id

Thanks Jack,

Using the new code I still can’t seem to get it to output anything in the CSV.

Could it be because I haven’t saved any specific personalization settings on the table? In Youri’s original post it mentioned that if there were no saved personalization settings it could use the default with this:

params.component.element._columnSettingsByUID;

But I’m not sure how to get that working properly. Is there a way I can check if personalization settings are saved and if not, use the default, and if so, use those personalization settings?

Great question and yes I think if you have made no personalization it doesn’t work, remembering now some voices of users haunting me from the distant past 🙂

So, maybe try rearranging or hiding/showing a column then seeing if it works.

and maybe something like this would work to get the default if the other is blank?

var settings = table._personalizationService.getSettings(); var columns = settings.columnSettingsByUID; if (!columns){ columns = table.element._columnSettingsByUID; }```
let me know if you get that work, i’ll update my code.

Thanks Jack,

That got me a little further. Now my CSV shows column headers but not the values in the rows. Any Ideas?

not sure… your model has data in it for sure? is the model processing client-side?

Model is processing client side and has data rows prior to clicking the button that runs the snippet. Even if I make a personalization change (hide a column and then show the column to make sure personalization settings are working) I’m not seeing any data only the column headers.

I would experiment with a simpler snippet that focuses on just

model.exportData({ fields: fieldsWithCorrectLabels, doNotAppendRowIdColumn: true, useAPINamesForHeaders: false }); fields just needs an array of API names, like<br alt="" name="" rel="" target="" title="" type="" value="" /><pre alt="" name="" rel="" target="" title="" type="" value="">fields = [ 'Field_Name1__c', 'Field_Name2__c']

I’m using an aggregate model. Does that affect this? I see that the Field Labels it’s getting from the aggregate model are not the same as what the Column Headers show, or the Field Alias Names. Could that have something to do with it? 

Almost certainly! Try a very simple snippet to export and play with the field names until you get it to push out data. Instead of “First_Name__c” you might need “FirstNamec” for example, or vice versa.

you can also throw in a simple console.log to see what the fieldId is being read as in the column for example:

var field = column.fieldId; console.log(field); 

var params = arguments[0], $ = skuid.$; var model = skuid.$M('YourModelName'); var fields = [ 'Field_Id__c', 'Fiel_Id2__c']; model.exportData({ fields: fields, doNotAppendRowIdColumn: true, useAPINamesForHeaders: false }); 

```

Thanks for the assistance Jack,

When I do that test to see how to translate the fields I can’t get it to output anything whether I use the field alias or the field ID.

Example:

Field Alias: ‘YearMonth’
Field ID: ‘AccountingPeriod__r.Name’


I’ve also built a non-aggregate model and made sure the model has rows in it. It also is exporting a blank CSV when I run this snippet:

var params = arguments[0],
$ = skuid.$;
var model = skuid.$M(‘TestQuery’);
var fields = [
‘GL_Account__r.Name’,
‘Amount__c’];

model.exportData({
    fields: fields,
    doNotAppendRowIdColumn: true,
    useAPINamesForHeaders: false    });


Why am I not getting model data? The model is processing client side and is fully loaded when I press the button to run the snippet.

I figured something out…

I needed model.getField()

this now works, will try to adopt for other code:

var params = arguments[0],
$ = skuid.$;
var model = skuid.$M(‘TestQuery’);
console.log(model);
var fields = [
model.getField(‘GL_Account__c’),
model.getField(‘Amount__c’)];

model.exportData({
    fields: fields,
    doNotAppendRowIdColumn: true,
    useAPINamesForHeaders: false    });

Excellent!

Here’s my updated code. It’s now working and getting the proper column names in place.

not sure how to format this as code here…

var params = arguments[0],

$ = skuid.$;

var model = skuid.$M(‘OurQuery’);

var fileName = ‘OurFileName’;

var table = skuid.$C(‘TableUniqueID’);

var fields = ;

var labels = ;

var columnsArray = ;

var settings = table._personalizationService.getSettings();

var columns = settings.columnSettingsByUID;

if (!columns){ columns = table.element._columnSettingsByUID; }

//put each column into an array that can be sorted

$.each (columns, function(i,column){

    columnsArray.push(column);

});

columnsArray.sort(function(a,b) {return (a.order > b.order) ? 1 : ((b.order > a.order) ? -1 : 0);} );

var fieldLabelMap = new Map();

//loop through the columns and push the field api name to the fields array

$.each (columnsArray, function(i,column){

    var field = model.getField(column.fieldId);

    field.label = column.label;

    var hidden = column.userHidden;

    var label = column.label;

    //how to deal with Template Fields 

    if (label===‘Template Field Name’ && !hidden){

        var field1 = model.getField(‘First_Field__c’),

            field2 = model.getField(‘Second_Field__c’);

            fields.push(field1);

            fields.push(field2);

    }

    if (!!field && !hidden){

        fields.push(field);

    }

});

model.exportData({

    fileName: fileName,

    fields: fields,

    doNotAppendRowIdColumn: true,

    useAPINamesForHeaders: false

    });

Hey @Jack_Sanford ,

Skuid has implemented an easier way to export data in the new Chicago release which is now available on the Skuid Releases page. Best practices for upgrading can be found in Upgrading Skuid on Salesforce. As a reminder, Salesforce does NOT allow reverting back to prior versions of managed packages. Skuid always recommends installing new versions in a non-business critical sandbox environment to test all mission-critical functionality before installing into a production environment.

We also recommend that you update out-of-date themes and design systems after you upgrade. Please let us know if you continue to encounter any problems with this issue after upgrading.

Thanks,