How would I colorize rows in a table chart based on multiple work date ranges?
i.e.
I have many tasks for a project each with different durations and finish dates. I want to colorize based on % complete based on a point in time to the finish date dependent on duration.
i.e.
I have many tasks for a project each with different durations and finish dates. I want to colorize based on % complete based on a point in time to the finish date dependent on duration.
 968 Points
Posted 11 months ago
QuickBaseCoach App Dev./Training, Champion
 51,346 Points
Chad, can you say in words what the rules are for coloring and I will try to help you. List the conditions in the order that you want then evaluated.
for example
if this ......, then blue,
else if those other conditions ....., then green,
else if yet another test is true , then red .....
for example
if this ......, then blue,
else if those other conditions ....., then green,
else if yet another test is true , then red .....
 968 Points
If calculated finish day is <= 3 days away and duration is <=10 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.
If calculated finish day is <= 10 days away and duration is <=30 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.
If calculated finish day is <= 30 days away and duration is <=90 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.
If calculated finish day is <= 35 days away and duration is >=91 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.
If calculated finish day is <= 10 days away and duration is <=30 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.
If calculated finish day is <= 30 days away and duration is <=90 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.
If calculated finish day is <= 35 days away and duration is >=91 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.
QuickBaseCoach App Dev./Training, Champion
 51,346 Points
=We can try this, but it is not tested and its not easy to type perfect code with out the Syntax editor to help check for errors.
var number CFD = ToDays([Calculated Finish Date]  Today();
var number Dur = [Duration field in days];
var number PC = [Percent complete] *10
var text Red = "#FFAEB9";
var text Yellow = "yellow";
var text Orange = "#fda302";
// laura has a great tool here https://laurahillier.quickbase.com/db/bhy8pumtp
IF(
$CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,
$CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$CFD < 10and $Dur < 30 and $PC < 85, $Yellow,
$CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,
$CFD < 35 and $Dur > 90 and $PC < 50, $Red,
$CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)
var number CFD = ToDays([Calculated Finish Date]  Today();
var number Dur = [Duration field in days];
var number PC = [Percent complete] *10
var text Red = "#FFAEB9";
var text Yellow = "yellow";
var text Orange = "#fda302";
// laura has a great tool here https://laurahillier.quickbase.com/db/bhy8pumtp
IF(
$CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,
$CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$CFD < 10and $Dur < 30 and $PC < 85, $Yellow,
$CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,
$CFD < 35 and $Dur > 90 and $PC < 50, $Red,
$CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)
 968 Points
Thanks for the reply.
I made a couple of slight changes. But, still can't seem to get this to work. I'm getting a Formula Error
 Incorrect variable type
The type of the variable does not match the return type of the expression.
I'm having trouble placing where this error is occuring
var number CFD = ToDate([Calculated Finish Date])  Today();
var number Dur = [Duration];
var number PC = [% Complete] *10;
var text Red = "#FFAEB9";
var text Yellow = "yellow";
var text Orange = "#fda302";
If(
$CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,
$CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,
$CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,
$CFD < 35 and $Dur >90 and $PC < 50, $Red,
$CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)
I made a couple of slight changes. But, still can't seem to get this to work. I'm getting a Formula Error
 Incorrect variable type
The type of the variable does not match the return type of the expression.
I'm having trouble placing where this error is occuring
var number CFD = ToDate([Calculated Finish Date])  Today();
var number Dur = [Duration];
var number PC = [% Complete] *10;
var text Red = "#FFAEB9";
var text Yellow = "yellow";
var text Orange = "#fda302";
If(
$CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,
$CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,
$CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,
$CFD < 35 and $Dur >90 and $PC < 50, $Red,
$CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)
QuickBaseCoach App Dev./Training, Champion
 51,346 Points
The first line will need to be what i posted
var number CFD = ToDays([Calculated Finish Date])  Today();
Also what field type is [Duration]
var number CFD = ToDays([Calculated Finish Date])  Today();
Also what field type is [Duration]
 968 Points
[Duration] is a numeric field
[Calculated Finish Date] is a formula work date
I get an error with ToDays
Formula error  Bad or missing arguments in function call
I'm guessing the [Calculated Finish Date] field is the bad guy here. It's been a thorn in side trying to figure this out
[Calculated Finish Date] is a formula work date
I get an error with ToDays
Formula error  Bad or missing arguments in function call
I'm guessing the [Calculated Finish Date] field is the bad guy here. It's been a thorn in side trying to figure this out
QuickBaseCoach App Dev./Training, Champion
 51,346 Points
I had it wrong. wrong placement of the brackets.
CFD = ToDays([Calculated Finish Date]  Today());
CFD = ToDays([Calculated Finish Date]  Today());
 968 Points
That was the spark I needed. Thanks! Still have some tweaking to do to get the colorization right though. Not showing the yellows or oranges...
var number CFD = ToDays(ToDate([Calculated Finish Date])  Today());
var number Dur = [Duration];
var number PC = [% Complete] *10;
var text Red = "#FF3030";
var text Yellow = "yellow";
var text Orange = "#fda302";
If(
$CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,
$CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,
$CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,
$CFD < 35 and $Dur >90 and $PC < 50, $Red,
$CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)
var number CFD = ToDays(ToDate([Calculated Finish Date])  Today());
var number Dur = [Duration];
var number PC = [% Complete] *10;
var text Red = "#FF3030";
var text Yellow = "yellow";
var text Orange = "#fda302";
If(
$CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,
$CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,
$CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,
$CFD < 35 and $Dur >90 and $PC < 50, $Red,
$CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)
 968 Points
Got it! Grrr math
var number CFD = ToDays(ToDate([Calculated Finish Date])  Today());
var number Dur = [Duration];
var number PC = [% Complete] *100;
var number SD = ToDays(ToDate([Start])  Today());
var text Red = "#FF3030";
var text Yellow = "yellow";
var text Orange = "#fda302";
If(
$SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,
$SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,
$SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,
$SD < 1 and $CFD < 35 and $Dur >90 and $PC < 50, $Red,
$SD < 1 and $CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$SD < 1 and $CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)
var number CFD = ToDays(ToDate([Calculated Finish Date])  Today());
var number Dur = [Duration];
var number PC = [% Complete] *100;
var number SD = ToDays(ToDate([Start])  Today());
var text Red = "#FF3030";
var text Yellow = "yellow";
var text Orange = "#fda302";
If(
$SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 50, $Red,
$SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
$SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,
$SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 50, $Red,
$SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
$SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,
$SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 50, $Red,
$SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
$SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,
$SD < 1 and $CFD < 35 and $Dur >90 and $PC < 50, $Red,
$SD < 1 and $CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$SD < 1 and $CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)
 968 Points
For anyone still following this... I hope this is the finished product. Note I did change some field types...
var number CFD = ToDays([Assigned Finish Date]  Today());
var number Dur = [Calc Duration];
var number PC = [% Complete] *100;
var number SD = ToDays(ToDate([Start])  Today());
var text Red = "#f75d59";
var text Yellow = "yellow";
var text Orange = "#fda302";
If(
$SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 50, $Red,
$SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 70, $Orange,
$SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 85, $Yellow,
$SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 50, $Red,
$SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 70, $Orange,
$SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 85, $Yellow,
$SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 50, $Red,
$SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 70, $Orange,
$SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 85, $Yellow,
$SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 50, $Red,
$SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 70, $Orange,
$SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 85, $Yellow,
$SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 50, $Red,
$SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)
var number CFD = ToDays([Assigned Finish Date]  Today());
var number Dur = [Calc Duration];
var number PC = [% Complete] *100;
var number SD = ToDays(ToDate([Start])  Today());
var text Red = "#f75d59";
var text Yellow = "yellow";
var text Orange = "#fda302";
If(
$SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 50, $Red,
$SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 70, $Orange,
$SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 85, $Yellow,
$SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 50, $Red,
$SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 70, $Orange,
$SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 85, $Yellow,
$SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 50, $Red,
$SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 70, $Orange,
$SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 85, $Yellow,
$SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 50, $Red,
$SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 70, $Orange,
$SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 85, $Yellow,
$SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 50, $Red,
$SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
$SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)
QuickBaseCoach App Dev./Training, Champion
 51,346 Points
 968 Points
Related Categories

App builders
 761 Conversations
 41 Followers