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 ;
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':''}}
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
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});