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

  • 1
  • Question
  • Updated 5 months ago
  • In Progress
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
    });
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,256 Points 10k badge 2x thumb
  • hopeful

Posted 2 years ago

  • 1
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,256 Points 10k badge 2x thumb
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/skuid/topics/snippet-to-show-hide-set-of-column-in-table

var params = arguments[0],
	$ = skuid.$;
	
var model = skuid.$M('ListingTransplant');	

var fields = [];
var columnsArray = [];

var settings = params.component._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);} );

//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;
    
    if (!!field && !hidden){
        fields.push(field);
    }
    
});

//the result gets populated in reverse order, this puts it right
var fieldNames = fields.reverse();


//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(fieldNamess, function(v){
    var actualField = model.getField(v);
     
    return {
        id: actualField.id,
        label: actualField.label
    };     
     
});


model.exportData({
    fields: fieldsWithCorrectLabels,
    doNotAppendRowIdColumn: true,
    useAPINamesForHeaders: false
    });
(Edited)
Photo of Mark L

Mark L

  • 1,656 Points 1k badge 2x thumb
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?
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,256 Points 10k badge 2x thumb
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
    });

    
     
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,256 Points 10k badge 2x thumb
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;
(Edited)
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,256 Points 10k badge 2x thumb
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
(Edited)
Photo of Mark L

Mark L

  • 1,656 Points 1k badge 2x thumb
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?
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,256 Points 10k badge 2x thumb
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. 
(Edited)
Photo of Mark L

Mark L

  • 1,656 Points 1k badge 2x thumb
Thanks Jack,

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

Jack Sanford, Champion

  • 10,256 Points 10k badge 2x thumb
not sure... your model has data in it for sure? is the model processing client-side?
Photo of Mark L

Mark L

  • 1,656 Points 1k badge 2x thumb
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.
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,256 Points 10k badge 2x thumb
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
fields = [
    'Field_Name1__c',
    'Field_Name2__c']
(Edited)
Photo of Mark L

Mark L

  • 1,656 Points 1k badge 2x thumb
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? 
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,256 Points 10k badge 2x thumb
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.
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,256 Points 10k badge 2x thumb
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    }); 
(Edited)
Photo of Mark L

Mark L

  • 1,656 Points 1k badge 2x thumb
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.
Photo of Mark L

Mark L

  • 1,656 Points 1k badge 2x thumb
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    });
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,256 Points 10k badge 2x thumb
Excellent!
Photo of Mark L

Mark L

  • 1,656 Points 1k badge 2x thumb
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

    });


(Edited)
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 12,584 Points 10k badge 2x thumb
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!