How do I create conditional formatting?

OL Connect Professional Designer
1

Hi,

I’d like to know how to create a table that has formatting applied to the cells based on a value in my variable data. For example, I’d like to show a red background inside a cell when my data field equals “value1” and a blue background inside the cell when the data field equals “value2”. Like this:

Data
value1
value2
Can I do this using a dynamic table?

thanks!

Lou Pace

1 Like
2

Hi Lou,

You have to do this with a script. In the script property that controls that cell, click on Expand to turn it into an editable script (instead of the wizard) and you’ll need to do something like this:

{
function add(value, operation) {
if (value === undefined || value === “”)
return;
operation(value);
}

var format = new TextFormatter(locale);

results.each(function(index) {

    // Turn background red on negative values
    if(parseInt(record.tables["detail"][index].fields["sales_price"]) < 0) {
        this.css('background-color', 'red');
    } else {
        this.css('background-color','blue');
   }

    var result = "";

    add(record.tables["detail"][index].fields["sales_price"], function (value) { result += "$ " + format.currencyNoSymbol(value) + " "; });
    this.html(result);
    
    
});

}

Obviously, you’ll have to adjust the added code (in blue) for the field name you’re using, but otherwise this is the only change I made to the expanded code. You can also change ‘red’ to a hex color instead (‘#0000FF’ , for instance).

(Edit: forgot to put blue color with the “else” in the condition).

3

Thanks Evie, that worked!

4

Hello Lou,

You can also use a script and use query(selector).css to change the color

the selector is given by the index going through the detail table.

See the script below: (in my example, I have 2 data fields in detail table: the field name “version” and field name “color”) the data field color can have value1 or value2. Note in the window of the script write body in Selector and check Selector

var inc=0;
//Going through the detail table
for (var i=0; i< record.tables.detail.length;i++){
// incremente index to use it in css selector
inc = i+1;
/use switch … case to test the several values of the data field “Field” (Field has value value1 or value2)
and change the color depending on the value read in this data field/
switch(record.tables.detail[i].fields[“color”]){
case “value1”:
/select the row (tr) of the table corresponding to the item by using the css selector :nth-child(), and the number
of this child is returned by index i+1 stored in variable inc
the row is selected, so take the cell (td) corresponding to your datafield to which change the color.
Exemple here, the td that class=“version” in html source is used
So, use query like this:/
query(“#table_1 tbody tr:nth-child(”+inc+“) td.version”).css(“background-color”, “red”);
break;
case “value2”:
query(“#table_1 tbody tr:nth-child(”+inc+“) td.version”).css(“background-color”, “blue”);
break;
}
}

I hope that help you

5

I’m happy you could make it work, even though I had a brain lag and thought you wanted to check for a numerical value! Oops :stuck_out_tongue:

6

Yes thank you both, this was very helpful.
Is there any way to use the style set by the script to style other elements in my template, like a second table?
I have a second table with related values, and I’d like those to be the same color as the first table if possible.

thanks again,
Lou