Color table cell according to currentRow

[jul]

Hi,

I created a table according to an sql query
I created a transformer to ensure my columns are properly ordered as I want

I'd like now to color each cell of my table according to its position.

My tables columns stand for month number : 01,02... up to 12.
My tables rows stand for day numbers: 01,02... up to 31.

I've been trying to enhanced my transformer by adding the property "colorMapper" while building my list of columns and passing an expression to it as explained here

here is my transformer

columns=[];
columns.push({"name":"Day","type":"string"});
let d = new Date();
let currentyear = d.getFullYear();
let max_month_index = d.getMonth(); //renvoie un entier en 0 et 11
if ({{year.value}} != currentyear) {
  max_month_index = 11; }

for (let i = 0; i <= max_month_index; i++) {
  let j=i+1;
  let columnJ = (j+'').padStart(2, '0') ;
  let m = "{\{(new Date('"+{{year.value}}+"-"+columnJ+"-"+"(currentRow.Day)')).getDay() == 0 ? '#00ff00' : '#ffffff'\}}";
  columns.push({"name":columnJ,"type":"number","colorMapper":m});
}
return columns ;

here is a extract of what my transformer returns

[
   {
      "name":"Day",
      "type":"string"
   },
   {
      "name":"01",
      "type":"number",
      "colorMapper":"{{(new Date('2022-01-(currentRow.Day)')).getDay() == 0 ? '#00ff00' : '#ffffff'}}"
   },
   {
      "name":"02",
      "type":"number",
      "colorMapper":"{{(new Date('2022-02-(currentRow.Day)')).getDay() == 0 ? '#00ff00' : '#ffffff'}}"
   }
]

Nevertheless, the only effect for now, is the full column for May (i.e. "05") to turn green (all cells of the column).

And we all now that all days are not sundays

Could someone help me pointing out what I'm missing ?

thanks in advance

Julien

[ScottR]

You probably don't need to add the ternary in your data because you can do it within the background field in the column of the table component - If I am reading your question correctly
{{currentRow.05 == 'Sunday'?'#00ff00':''}}

[jul]

Hi @ScottR
thanks for getting back to me.

I have to use a ternary as my columns are built dynamically.

Thanks to one of my colleagues, I eventually achieved what I wanted to do.
Here is the final result of my ternary for columnMapper property.

"{\{(new Date("+myStringYearMonth+",currentRow.Day)).getDay() == 0 || "+"(new Date("+myStringYearMonth+",currentRow.Day)).getDay() == 6 ? 'lightgrey' : ((new Date("+myStringYearMonth+",currentRow.Day)).getDay() == 5 && Math.floor((new Date("+myStringYearMonth+",currentRow.Day)).getDate() / 7 ) + 1 == 3 ? 'yellow' :'white')\}}"

→ 3rd friday in yellow, week end in lightgrey.

I'm wondering now how to script that ternary into a function, outside my transformer and call that function with parameters. Any clue ?

[ScottR]

Can you post a screenshot of your table - and since I don't have context, what exactly is it that you are trying to build? Retool has quite a bit of functionality that should allow you to achieve what you need without so much work....more clarification would help me help you

[jul]

Here is what my table looks like now.

[jul]

@ScottR I close it as I resolved by myself and will open another ticket for transformer/ternary simplification.

thanks

[jul]

Hi @fabianmu

Actually, assigning columns colors on the fly works good but you can rapidly get lost in building the différents strings (conditions and formats), where to put a ' or " and when to double them. Especially, when conditions are expressed in ternaries.

I solved that by cleaning and factoring my code

Here is below an extract of my code, hope that helps

//FORMATS AND COLORS
const defaultColor = "'white'";
const colorDateDontExist = "'#f8958d'"; //pale red
const colorFallOnWE = "'#eeeeee'"; //pale grey
const color3rdFriday = "'#ffe599'"; //pale yellow
const colorToday = "'#c2daff'";
const defaultFormat =  "FormatNumber(self)";
const formatToday = "'**'+"+defaultFormat+"+'**'"; // #,#.# bold
const upperOrEqual ="'🟢'";
const lower ="'🔴'";
const formatColumn = 'markdown';

//iterate through your columns
for (let idx = 0; idx <= 11; idx++) {
  let j=idx+1;
  
  //columns on 2 digits with left leading 0, like '01', '02'...
  let columnJ = (j+'').padStart(2, '0') ;
  
  // format yearmonth yearmonth as yyyy-mm 
  let myStringYearMonth = "'"+{{year.value}}+"-"+columnJ+"-'+";
  
  //CONDITIONS
  let conditionMonthDoesExist = '(new Date('+myStringYearMonth+'currentRow.Day)).getMonth()<='+currentMonth;
  let conditionRequestNotCurrentYear= (d.getYear()+1900)+''=={{year.value}} ? false : true ;
  let conditionDateDontExist = '!(DateExists('+myStringYearMonth+'currentRow.Day))';
  let conditionDateDoesExist = '(DateExists('+myStringYearMonth+'currentRow.Day))';
  let conditionFallOnWE = 'IsDayFallingOnAWeekend('+myStringYearMonth+'currentRow.Day)';
  let condition3rdFriday = 'IsDayA3rdFriday('+myStringYearMonth+'currentRow.Day)';
  let conditionIsToday = _today+'=='+myStringYearMonth+'currentRow.Day';
  let differenceOverYear = "self - state_LastYearData.value['"+columnJ+"'][i]"
  let conditionIsBetterThanLastYear = differenceOverYear + " > 0" ;//"state_LastYearData.value['"+columnJ+"'][i]<=self";
  
  let mapperForBackgroundColor = "{\{"+conditionDateDontExist+" ? "+ colorDateDontExist +" : ( "+conditionFallOnWE+" ? "+colorFallOnWE+" :("+condition3rdFriday+" ? "+color3rdFriday+" :("+conditionIsToday+" ? "+colorToday+":"+defaultColor+")))\}}";
  
  let mapper = "{\{!"+conditionFallOnWE+" && "+conditionDateDoesExist+" && ("+conditionMonthDoesExist+" || " + conditionRequestNotCurrentYear +") ? ("+conditionIsBetterThanLastYear+" ? "+upperOrEqual+":"+lower+")+("+conditionIsToday+" ? "+formatToday+" :"+defaultFormat+")+(' <sup>('+FormatNumber("+differenceOverYear+",1,0,0,true)+')</sup>') : ''\}}" ;
  columns.push({"name":columnJ,"type":""+formatColumn+"","colorMapper":mapperForBackgroundColor,"mapper":mapper});