Get formatted data from Table ( not the {{table1.data}} )

[liso]

Hi there

I've been trying to generate a PDF file from a data displayed from a table.
All good with generating the PDF and formatting the data into a table BUT the data displayed in the PDF is not the data formatted in the table.

Lets see the example:
Here is my table with all data formatted

So I want to export a PDF the formatted data from the table.

I've been trying with some articles from this forum but everything is related to the original data from the db query.

Someone knows if this is possible ?

Thanks in advance!

[bobthebear]

is this the legacy table? if it is, I think you can just use table1.displayData (array of objects). also, what do you mean by formated? do you mean like numbers that have precision set are different than what's displayed or do you mean unsaved changes (problems w changeset and whatnot w the new table)?

[liso]

Hi bob, I've been trying with new tables and legacy.
I mean with formatted that I want to get the data exactly how I see it in the table.
I'm not editing any data in the table

Let me show you more examples:

I did inspect the table1.displayData and I see the data exactly like I get it from the query.

and this is how looks in the legacy table

So as you can see in the table the row monto is with dollar sign and with double format, but I'm unable to get the data exactly how it looks in the table.

thanks!

[bobthebear]

ah I see, so this is because for string/number formatting doesn't actually modify the data. it's for a good reason though, when you're mapping display values you don't want to accidently modify the data. imagine accidently adding 0.5 to the 'monto' column, it'd display that added 0.5 but if you tried to use table.data you'd never see the change and finding that bug could be a massive headache).

i guess that leaves you with 2 options =/ where you can either make a Feature Request for Retool to add a new property to the table named something like formattedData (to keep the pattern from legacy tables w displayData) OR you can make a new query... name it getFormattedData if you want, I'll be referencing the query assuming you're using it, and set the Resource type to 'Run JS Code (javascript)'. you can copy/pasta the following code for the script:

Notes:

• you could use "Array.from.call({}, typeof tableObj === 'string' ? JSON.Parse(tableObj) : tableObj);" instead if you wanna do some basic type checking. i've included it mostly because if a JSON string is passed due to some odd conversion then the code breaks, we can fix this with .Parse() so there's no need to wrap all this in an if statement just to prevent it from running if a mistake is made and a string is passed along.

// sorry i got too tiered to finish the code for ya, can't think straight.  I'll finish it up tomorrow and edit this post.

the gist of it though is to use these properties to properly format the columns data:
'columnAlignment', 'columnColors', 'columnFormats', 'columnMappers', 'columnTypeProperties', 'columnVisibility' and finally just 'columns'

what i've done so far (for the most part i think)

• I created a var named includeHiddenColumns (default false)
• I get an array of every column name, even hidden ones. if includeHiddenColumns is false, I use .filter() to remove any hidden columns

next:

• for each column name, we get the array or obj from these properties found on legacyTable 'columnAlignment', 'columnColors', 'columnFormats', 'columnMappers', 'columnTypeProperties', 'columnVisibility'. each one may or may not have a 'column name' in it, but if ones does exist, extract it and add it to and if one does not use a placeholder w a value of null

[bobthebear]

function getTableDataWithFormatting(myTable, includeHiddenColumns = false){  
  const columns = Object.values(myTable.columns);
  //console.log("All Columns:", columns);
  const visibleColumns = columns.filter((colName) => myTable.columnVisibility[colName] === true);
  const hiddenColumns = columns.filter((colName) => myTable.columnVisibility[colName] === false);
  const columnNames = includeHiddenColumns ? visibleColumns.concat(hiddenColumns) : visibleColumns;
  //console.log("Visible Columns:", visibleColumns);
  //console.log("Hidden Columns:", visibleColumns);
  //console.log("Columns To Display:", columnNames);

  let formattedObj = {};
  let formattedData = [];
  // loop through each table item
  myTable.data.forEach((unformattedObj, index) => {    
    //console.log("Table Data", unformattedObj);    
  
    columnNames.forEach(function(colName) {
      const colProps = myTable.columnTypeProperties[colName];     
      
      let padDecimal = colProps?.padDecimal? colProps.padDecimal[index] : false;
      let showSeperators = colProps?.showSeparators? colProps.showSeparators[index] : false;
      let currency = colProps?.currency? colProps.currency[index] : "USD";
      let decimalPlaces = colProps?.decimalPlaces? colProps.decimalPlaces[index] : 0;
  
      let newVal = unformattedObj[colName];
      
      switch (myTable.columnFormats[colName]){
        case "CurrencyDataCell":        
          const formatter = new Intl.NumberFormat('en-US', { style: 'currency', currency: currency });
          newVal = formatter.format(Number.parseFloat(newVal).toFixed(decimalPlaces));
          
          //console.log("NEW VALUE:", newVal);
          break;
          
        case "PercentDataCell":
          newVal = (Number.parseFloat(newVal) * 100).toFixed(decimalPlaces);
          
          //console.log("NEW VALUE:", newVal);
          break;
          
        case "NumberDataCell":
          newVal = padDecimal ? Number.parseFloat(unformattedObj[colName]).toFixed(unformattedObj[decimalPlaces] ? unformattedObj[decimalPlaces] : 2) : newVal;
          
          //console.log("NEW VALUE:", newVal);
          break;
        
        case "default": //Auto
        case "CheckboxDataCell": //boolean
        case "TextDataCell": //single line text
        case "TextMultiDataCell": //multi-line text
        case "SingleTagDataCell": //tag(Dropdown)
        case "DateDataCell": // date
        case "TimeDataCell": // time
        case "DateTimeDataCell": //DateTime
        case "LinkDataCell": //link
        case "RatingDataCell": //rating
        case "MarkdownDataCell": //markdown
        case "HtmlDataCell": //html
        case "ImageDataCell": //image url
        case "ImageUploadDataCell": //image upload
        case "UserDataCell": //user email
        case "JsonDataCell": //json
        case "button": //button
        case "ModalDataCell": //modal
        default:
          break;
      }
      formattedObj[colName] = showSeperators ? newVal?.toString().replace(/\B(?<!\.\d*)(?=(\d{3})+(?!\d))/g, ",") : newVal;
    })
    formattedData.push(formattedObj);
  
   });
  return formattedData;
}

//call our function (default hide hidden columns)
return getTableDataWithFormatting(tableLegacy10);

//call our function explicitly stating to hide hidden columns
//return getTableDataWithFormatting(tableLegacy10, false);

//call our function using hidden columns also
//return getTableDataWithFormatting(tableLegacy10, true);

you can try this code. it will take a legacy table and return a list of items w proper formatting. i included extra 'switch cases' incase you have other datatypes you want to get in their formatted form also