I am working on changing a formula in Notion.
There are three databases involved. The formula in question is in the "Activities" database. This database is related to a "Settings" and "Days" database.
Currently transforms a list of dates into a sorted list of timestamped activities, preparing it for visualization. It constructs the grid from the processed dates, marking days with activities, today, future days, and filling gaps with no activity.
In the "Days" database there is a formula property called "Heatmap" that displays a "L", "M", or "H" depending on the time logged that day. In the "Activities" database there are three color properties: Color (L), Color (M), and Color (H).
I want to change the color property used depending on the Heatmap property.
I am having trouble getting this to work. I am starting to question if its even possible. I would appreciate some insight.
Here is the original formula.
lets(
settings, prop("Settings").first(),
isHorizontal, settings.prop("Orientation").contains("Horizontal"),
dayNameSize, settings.prop("Day Format").length(),
cellSize, (max(1, if(isHorizontal, 0, dayNameSize)) - 1) / 2,
dayNameTemplate, settings.prop("Day Format").replaceAll(".", "x") ,
cellTemplate, " ".repeat(cellSize) + "x" + " ".repeat(cellSize),
today, parseDate(formatDate(now(), "YYYY-MM-DD")),
dayOfWeek, if(settings.prop("Start Week on")=="Monday", day(today), day(today) % 7 + 1),
startOfWeek, dateSubtract(today, dayOfWeek - 1, "days"),
weekCount, 1-1,
startOfStats, dateSubtract(startOfWeek, weekCount, "weeks"),
endOfStats, dateAdd(today, 7 - dayOfWeek, "days"),
monthPlaceHolder, style(" ".repeat(length(settings.prop("Day Format"))),"gray"+"_background","c","b"),
recordDates, prop("Days")
.filter(current.prop("Date") >= startOfStats)
.map(
let(
localDate, fromTimestamp(current.prop("Date").timestamp()), /* convert to current timezone */
parseDate(formatDate(localDate, "YYYY-MM-DD")) /* drop time */
)
)
.sort(),
gridData,
recordDates
.map(
let(
space,
if(
index > 0,
dateBetween(current, recordDates.at(index - 1), "days") - 1,
dateBetween(current, startOfStats, "days")
),
";0".repeat(space)
+ if(space > -1 or index == 0, ";", "")
+ "x"
)
).join("")
+ if(
recordDates.length() == 0,
";0".repeat(dateBetween(today, startOfStats, "days")) + ";T",
if(
recordDates.last() != today,
";0".repeat(dateBetween(today, recordDates.last(), "days") - 1) + ";T",
""
)
)
+ ";?".repeat(dateBetween(endOfStats, today, "days")),
gridData, gridData.substring(1), /* remove leading ; */
/* create grid cells */
gridCells, gridData.split(";").map(
ifs(
current == "0", /* no activity */
style(cellTemplate.replace("x", " "), "c", prop("Color (B)") + "_background"),
current.substring(0, 1) == "x", /* day with activity */
style(cellTemplate.replace("x", prop("Symbol")), "c", "b", prop("Color (B)"), prop("Color (B)") + "_background"),
current == "T", /* today without activity */
style(cellTemplate.replace("x", "○"), "c", "b", prop("Color (B)"), prop("Color (B)") + "_background"),
/* future day */
style(cellTemplate.replace("x", " "), "c", "gray_background")
)
),
/* create month lablel */
monthLabel, repeat(" ", weekCount+1).split("")
.map(dateAdd(startOfStats, index, "weeks"))
.map(ifs(date(current) <= 4, formatDate(current, "MMMM").split("").at(0) ,
date(dateAdd(current, 3, "days")) <= 4, formatDate(dateAdd(current, 3, "days"), "MMMM").split("").at(0),
"◌"))
.map(current.style(prop("Color (B)")+"_background", prop("Color (B)"), "c", "b"))
.join(" "),
/* account for vertical view */
monthLabel, ifs(!isHorizontal, monthLabel.split(" ").map(current.style(prop("Color (B)")+"_background", prop("Color (B)"), "c", "b")).join("\n"), monthLabel),
monthRow, ifs(settings.prop("Day Names Location").contains("Start"), monthPlaceHolder+" "+monthLabel, settings.prop("Day Names Location").contains("End"), monthLabel+" "+monthPlaceHolder, monthLabel),
/* define monthCol for vertical view */
monthCol, ifs(!isHorizontal, monthPlaceHolder+ "\n" +monthLabel),
/* create day name cells */
dayNameCells, [0,1,2,3,4,5,6].map(
style(
dayNameTemplate.replace("x+", formatDate(
dateAdd(startOfWeek, current, "days"),
"ddd"
).substring(0, settings.prop("Day Format").length())),
"c",
ifs(
settings.prop("Day Name Color").contains("Grid"), [prop("Color (B)"), prop("Color (B)") + "_background"],
["gray", "gray_background"]
)
)
),
/* join day name cells */
gridCells, ifs(
settings.prop("Day Names Location").contains("Start"), concat(dayNameCells, gridCells),
settings.prop("Day Names Location").contains("End"), concat(gridCells, dayNameCells),
gridCells
),
/* convert cells into grid */
grid, [1].repeat(gridCells.length() / 7).split("").map(
gridCells.slice(index * 7, (index + 1) * 7)
),
/* transpose grid if horizontal */
grid, if(
isHorizontal, grid.first().map(let(column, index, grid.map(current.at(column)))),
grid
),
grid, grid.map(current.join(" ")).join("\n"),
/* add month to grid */
finalGrid, ifs(isHorizontal, monthRow + "\n" + grid, grid),
/* render grid with month */
finalGrid
)
Here are the changes I've made:
lets(
settings, prop("Settings").first(),
isHorizontal, settings.prop("Orientation").contains("Horizontal"),
dayNameSize, settings.prop("Day Format").length(),
cellSize, (max(1, if(isHorizontal, 0, dayNameSize)) - 1) / 2,
dayNameTemplate, settings.prop("Day Format").replaceAll(".", "x") ,
cellTemplate, " ".repeat(cellSize) + "x" + " ".repeat(cellSize),
today, parseDate(formatDate(now(), "YYYY-MM-DD")),
dayOfWeek, if(settings.prop("Start Week on")=="Monday", day(today), day(today) % 7 + 1),
startOfWeek, dateSubtract(today, dayOfWeek - 1, "days"),
weekCount, 1-1,
startOfStats, dateSubtract(startOfWeek, weekCount, "weeks"),
endOfStats, dateAdd(today, 7 - dayOfWeek, "days"),
monthPlaceHolder, style(" ".repeat(length(settings.prop("Day Format"))),"gray"+"_background","c","b"),
heatmapColor,
let(
heatmapValue, prop("Days").map(current.prop("Heatmap")),
if(
heatmapValue == "L",
prop("Color (L)"),
if(
heatmapValue == "M",
prop("Color (M)"),
prop("Color (H)")
)
)
),
recordDates, prop("Days")
.filter(current.prop("Date") >= startOfStats)
.map(
let(
localDate, fromTimestamp(current.prop("Date").timestamp()), /* convert to current timezone */
parseDate(formatDate(localDate, "YYYY-MM-DD")) /* drop time */
)
)
.sort(),
gridData,
recordDates
.map(
let(
space,
if(
index > 0,
dateBetween(current, recordDates.at(index - 1), "days") - 1,
dateBetween(current, startOfStats, "days")
),
";0".repeat(space)
+ if(space > -1 or index == 0, ";", "")
+ "x"
)
).join("")
+ if(
recordDates.length() == 0,
";0".repeat(dateBetween(today, startOfStats, "days")) + ";T",
if(
recordDates.last() != today,
";0".repeat(dateBetween(today, recordDates.last(), "days") - 1) + ";T",
""
)
)
+ ";?".repeat(dateBetween(endOfStats, today, "days")),
gridData, gridData.substring(1), /* remove leading ; */
/* create grid cells */
gridCells,
gridData.split(";").map(
ifs(
current == "0", /* no activity */
style(cellTemplate.replace("x", " "), "c", prop("Color (N)") + "_background"),
current.substring(0, 1) == "x", /* day with activity */
style(cellTemplate.replace("x", " "), "c", "b", heatmapColor + "_background"),
current == "T", /* today without activity */
style(cellTemplate.replace("x", " "), "c", "b", prop("Color (B)"), prop("Color (B)") + "_background"),
/* future day */
style(cellTemplate.replace("x", " "), "c", "gray_background")
)
),
/* create month lablel */
monthLabel, repeat(" ", weekCount+1).split("")
.map(dateAdd(startOfStats, index, "weeks"))
.map(ifs(date(current) <= 4, formatDate(current, "MMMM").split("").at(0) ,
date(dateAdd(current, 3, "days")) <= 4, formatDate(dateAdd(current, 3, "days"), "MMMM").split("").at(0),
"◌"))
.map(current.style(prop("Color (B)")+"_background", prop("Color (B)"), "c", "b"))
.join(" "),
/* account for vertical view */
monthLabel, ifs(!isHorizontal, monthLabel.split(" ").map(current.style(prop("Color (B)")+"_background", prop("Color (B)"), "c", "b")).join("\n"), monthLabel),
monthRow, ifs(settings.prop("Day Names Location").contains("Start"), monthPlaceHolder+" "+monthLabel, settings.prop("Day Names Location").contains("End"), monthLabel+" "+monthPlaceHolder, monthLabel),
/* define monthCol for vertical view */
monthCol, ifs(!isHorizontal, monthPlaceHolder+ "\n" +monthLabel),
/* create day name cells */
dayNameCells, [0,1,2,3,4,5,6].map(
style(
dayNameTemplate.replace("x+", formatDate(
dateAdd(startOfWeek, current, "days"),
"ddd"
).substring(0, settings.prop("Day Format").length())),
"c",
ifs(
settings.prop("Day Name Color").contains("Grid"), [prop("Color (B)"), prop("Color (B)") + "_background"],
["gray", "gray_background"]
)
)
),
/* join day name cells */
gridCells, ifs(
settings.prop("Day Names Location").contains("Start"), concat(dayNameCells, gridCells),
settings.prop("Day Names Location").contains("End"), concat(gridCells, dayNameCells),
gridCells
),
/* convert cells into grid */
grid, [1].repeat(gridCells.length() / 7).split("").map(
gridCells.slice(index * 7, (index + 1) * 7)
),
/* transpose grid if horizontal */
grid, if(
isHorizontal, grid.first().map(let(column, index, grid.map(current.at(column)))),
grid
),
grid, grid.map(current.join(" ")).join("\n"),
/* add month to grid */
finalGrid, ifs(isHorizontal, monthRow + "\n" + grid, grid),
/* render grid with month */
finalGrid
)
Here is a picture of the two formula results side by side. The top one is the original formula, and the bottom is the one I've made changes to.
Here is an image of the properties in the activities database as well. Activities database
I added a variable called heatmapValue that is an if statement. I then tried to change the style in the gridData section to include that variable and display the color that matches. However, instead of displaying the matching color, it is displaying the color as grey.