Format PDF form fields based on lowest to highest value

Community Beginner ,
Jun 27, 2022 Jun 27, 2022

Copy link to clipboard

Copied

I need to change the color of my pdf calculated fields based their values from lowest to highest.

 

I found a post on how to color a field based on its value here: https://community.adobe.com/t5/acrobat-discussions/change-the-form-field-background-color-based-on-v...

 

This would be great but my values aren't always in a grouping like 0-25, 26-50, 51-60, etc. It is all relative. It could be 145, 87, 126, 81 one time and 23, 22, 25, 18 another. 

 

In Excel we are using conditional formatting based on the lowest to highest field value using a three color scale. The lowest number is green and then the color changes to different tones up to yellow being the highest.

 

I am thinking of trying to set each field to have a variable in javascript and rank them they color the backgrounds but don't know how.

 

I know I can Set Variables like this.

var a = (this.getField("FieldA").value);
var b = (this.getField("FieldB").value);
var c = (this.getField("FieldC").value);
var d = (this.getField("FieldD").value);

 

What would be the code to rank the above variables and format the field?

 

Maybe it would be better to put them into an array and then sort them lowest to highest. Then I could just say make the lowest (1st cell): #92D050, next: #b2cb39, next: #dec518, highest: #FFC000.

Can anyone help with the javascript code to do this? It has to work in the Validate Tab - Custom Validation Script area because I am using custom calculations to get the numbers in these fields. 

Thank you in advance for your help.

TOPICS
JavaScript , PDF forms

Views

55

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 28, 2022 Jun 28, 2022

Copy link to clipboard

Copied

What should happen if two values are the same? Or if one (or more) of the fields are empty?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jun 28, 2022 Jun 28, 2022

Copy link to clipboard

Copied

Excellent question. If they are empty they should be white. If the values are the same they should be the same color.

If two fields are both the lowest number (1st) they can be #92D050 and the two highest can still be the corresponding colors for 3rd and 4th. If they are both the second lowest (2nd) they would both be #b2cb39 and the lowest (1st) would be #92D050 and the highest (4th) would be #FFC000.

 

FYI, Do to another part of the form, I did change the fields to be in a group using a prefix. They are now: 
RankingScore.DBB
RankingScore.DB
RankingScore.CDBB
RankingScore.CDB

 

I am hoping that will help in finding the order of lowest to highest.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 28, 2022 Jun 28, 2022

Copy link to clipboard

Copied

OK, I see. Well, this is not a trivial task. Are you looking for someone to write the full code for you, or for help on how to do it yourself?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jun 30, 2022 Jun 30, 2022

Copy link to clipboard

Copied

Either, it would be nice to have the full code but if I can just get pointers on how to make it happen that would be great too!

 

For example, I have a code from Thom Parker (below):

 

// Default all fields to white
this.getField("RankingScore.DBB").fillColor = color.white;
this.getField("RankingScore.DB").fillColor = color.white;
this.getField("RankingScore.CDBB").fillColor = color.white;
this.getField("RankingScore.CDB").fillColor = color.white;

var oMinFld = null;
// This line gets all the fields in the "RankingScore" group and
// finds the one with the lowest value. It assumes all fields contain a valid value 
this.getField("RankingScore").getArray().forEach(
      function(oFld)
      {
         if(!oMinFld || (oFld.value < oMinFld.value))
              oMinFld = oFld;
      }
);
if(oMinFld)
   event.value = oMinFld.fillColor = color.green; // Change Background Color

else 
   event.value = "";

 

I changed it to default all fields to default to white.

 

The problem I have is assigning variables and sorting. Above it looks like oMinFld is just the field that is a minimum. Do I need to create other variables like oMinFld.2, oMinFld.3, and oMinFld.4 (or use 0,1,2,3 from the array)? If so, how do I do that?

 

Then is there a quick and easy way to just say sort the array from lowest to highest and have the field names assigned a position (0,1,2,3)?

The code below looks like it would work for that:

var numArray = [140000, 104, 99];
numArray.sort((a, b) => a - b);

Then I can just repeat the if statement at the end saying:

if(oMinFld.0)
   event.value = oMinFld.fillColor = color.green; // Change Background Color
if(oMinFld.1)
   event.value = oMinFld.fillColor = color.yellow; // Change Background Color
if(oMinFld.2)
   event.value = oMinFld.fillColor = color.orange; // Change Background Color
if(oMinFld.3)
   event.value = oMinFld.fillColor = color.red; // Change Background Color

I am dusting off old javascript knowledge and this is a little beyone me. I'd be happy with this to start. Then I can figure out how to do if fields are equal.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jun 30, 2022 Jun 30, 2022

Copy link to clipboard

Copied

LATEST

Here is an example of the fields I am using:

 

 DBBDBC+DBBC+DB
Total Ranking Score30020040050

 

The fields with numbers in them are the first set of fields below (RankingScore.DBB, etc.).

I tried to simplify all of my ideas above and came up with this:

// Default all fields to white
this.getField("RankingScore.DBB").fillColor = color.white;
this.getField("RankingScore.DB").fillColor = color.white;
this.getField("RankingScore.CDBB").fillColor = color.white;
this.getField("RankingScore.CDB").fillColor = color.white;
//Add all fields with the prefix RankingScore to an array.
var scoreArray = this.getField("RankingScore").getArray();
// Sort the array from lowest to highest
scoreArray.sort((a, b) => a - b);

// Color each Array element's background
if (scoreArray[0]) { event.value = scoreArray[0].fillColor = color.green;}
else {event.value = "";}
if (scoreArray[1]) { event.value = scoreArray[1].fillColor = color.yellow; }
else {event.value = "";}
if (scoreArray[2]) { event.value = scoreArray[2].fillColor = color.orange; }
else {event.value = "";}
if (scoreArray[3]) { event.value = scoreArray[3].fillColor = color.red; }
else {event.value = "";}

It doesn't work. It only works for the field that I add this code to whereas the code from Thom would change any field in the array and I only added it to the first field.  I feel like I am on the right track.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 28, 2022 Jun 28, 2022

Copy link to clipboard

Copied

I'd use the same script that set the text for the lowest value to also set the colors. Just expand it to detect the values needed to set the colors. 

 Colors channels in an Acrobat JavaScript are in the range 0-1, not 0-FF as they are in HTML JavaScript. 

You can read more about it here:

https://opensource.adobe.com/dc-acrobat-sdk-docs/acrobatsdk/html2015/index.html#t=Acro12_MasterBook%...

 

 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines