Need to manipulate field colors at the Row level in Report Data Page

Lynda · May 9, 2023

I need to manipulate the font colors at row level in a report data page because each of the field values may change and are associated with the individual record.

In the script, the value to be manipulated is a calculated field ([@calcfield:3]). It is the td:nth-child(4) position on the Row.

I use two data source fields that are Not displayed on the data page in order to make the calculations ( var DailyGoal = [@field:MyFoodGroup_Daily]; and var DailyQual = [@field:MyFoodGroup_DailyQual];

I have tried to make my script work both trying to use field names and then position, but to no avail.

Here is my script:

// Set the Actual Daily Servings for manipulation
const Servings = document.querySelector('td[class*='cbResultSetDataRow'].td:nth-child(4)');

// Set the Variables
var ServValue = Servings.innerHTML;

// These two values are in the source data but not displayed on the report data page
var DailyGoal = [@field:MyFoodGroup_Daily];
var DailyQual = [@field:MyFoodGroup_DailyQual];

    if (DailyQual == 3) {                           // Not Measured
Servings.parentNode.style.color = 'blue';
     }

   if (DailyQual == 1) {                           // Actual <= Goal
       if (ServValue == 0) {
Servings.parentNode.style.color = 'orange';
       }
else {
if (ServValue > DailyGoal) {
Servings.parentNode.style.color = 'red';
         }
         else {
Servings.parentNode.style.color = 'green';
}
}
     }

    if (DailyQual == 2) {                           // Actual >= Goal
       if (ServValue == 0) {
Servings.parentNode.style.color = 'orange';
       }
else {
       if (ServValue < DailyGoal) {
Servings.parentNode.style.color = 'red';
          }
         else {
Servings.parentNode.style.color = 'green';
         }
}
    }
});
</script>

Data Source Fields

Data Page Elements

image.png.bfc51f0d64eb92e1701a4eae9cd4c380.png

Page Inspection

What the Page Looks Like

image.png.2457a7cf9bcb7d62c910b8245d1ec4c2.png

Can anyone help me?

Lynda

kpcollier · May 9, 2023

Sorry, I can give a better answer later. But, first thought that comes to mind... Maybe try to include the Daily and DailyQual fields into the results page. I would create an HTML block (or use one of the ones you've got) and do something like in this article:

https://howto.caspio.com/tech-tips-and-articles/customize-background-and-font-colors-in-report-datapage/

In the HTML block, create two elements with different IDs, but make sure the ID is something like 'daily[@IDField]' and 'dailyQual[@IDField]', where ID is the unique field in your table. You can hide these elements, or the entire column if you'd like.

<span id="daily[@field:ID#]">[@field:MyFoodGroup_Daily]</span>
<span id="dailyQual[@field:ID#]">[@field:MyFoodGroup_DailyQual]</span>

Now the values are available for you to use in JS.

var DailyGoal = [@field:MyFoodGroup_Daily];
var DailyQual = [@field:MyFoodGroup_DailyQual];

Would now become

var DailyGoal = document.getElementById("daily[@IDField]").value;
var DailyQual = document.getElementById("dailyQual[@IDField]").value;

Lynda · July 28, 2023

I wanted to post the final working script. Thanks to all who helped.

This script is applied at row level for report data pages. It is implemented by adding an HTML Block to your row, and then adding the following script to the HTML Block.

// This must be a unique identifing field for the record

var FoodGroup = document.querySelectorAll('td:nth-child(2)')

// These are the Fields whose attributes(colors) are to be manipulated

var Dservings;

var servings;

FoodGroup.forEach(fg=>{

// [@field:FoodGroup_FoodGroupDescription] is the Field assigned to child(2)

if (fg.innerHTML == '[@field:FoodGroup_FoodGroupDescription]'){

Dservings = fg.parentNode.querySelector('td:nth-child(7)');

servings = fg.parentNode.querySelector('td:nth-child(11)');

}

})

// Fields used in calculations

var DailyGoal =[@field:MyFoodGroup_DailyAmt#];

var DailyQual = [@field:MyFoodGroup_DailyQual#];

var DServValue = '[@calcfield:3]';

// Not Measured

if (DailyQual == 3) {

Dservings.style.color = 'blue';

Dservings.style.fontWeight = "700";

}

// Actual <= Goal

if (DailyQual == 1) {

if (DServValue == 0) {

Dservings.style.color = 'Orange';

Dservings.style.fontWeight = "700";

}

else {

if (DServValue > DailyGoal) {

Dservings.style.color = 'Red';

Dservings.style.fontWeight = "700";

}

else {

Dservings.style.color = 'Green';

Dservings.style.fontWeight = "700";

}

// Actual >= Goal

if (DailyQual == 2) {

if (DServValue == 0) {

Dservings.style.color = 'Orange';

Dservings.style.fontWeight = "700";

}

else {

if (DServValue < DailyGoal) {

Dservings.style.color = 'Red';

Dservings.style.fontWeight = "700";

}

else {

Dservings.style.color = 'Green';

Dservings.style.fontWeight = "700";

}

// Weekly Servings Processing

var WeeklyGoal = [@field:MyFoodGroup_WeeklyAmt#];

var WeeklyQual = [@field:MyFoodGroup_WeeklyQual#];

var ServValue = '[@calcfield:4]';

// Not Measured

if (WeeklyQual == 3) {

servings.style.color = 'Blue';