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

I prefer using a snippet to export so i don’t have to have checkboxes on my table, and it’s just more user-friendly to have a button. 

However, in doing so, I lose the ability to use table columns as export columns. Is there a way to access the list of fields that are used in the table?

Even better, is there a way to access only the visible columns in a table? With the standard table export, it includes hidden columns, which is a bummer. 

I’m thinking if I could get an array of only the visible columns we could do this

var params = arguments[0], $ = skuid.$; var model = skuid.$M('ModelName'); var fields = [ //Insert Array of API names for visible columns in table ]; model.exportData({ fields: fields, doNotAppendRowIdColumn: true, useAPINamesForHeaders: false });<br>

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 :slight_smile: 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 :slight_smile:

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); 
if you're getting the API name like First_Name__c, then you might need to translate it to FirstNamec, or if you're getting FirstNamec you might need to translate it to First_Name__c etc.
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

    });