JavaScript that calculates specific selections made in multiple dropdowns

[slaron]

Here's the scenario:

A company has 7 representatives (rep) and they need to track how may times that rep was chosen throughout the year. On the form there there is one dropdown that will be repeated 6 times for each month (72 dropdowns per form). Although the dropdowns will have different field names, all will have the same 7 representatives listed. There is a separate text field for each rep to track how may times they were chosen.

I was hoping that a validation script that would be in all of the dropdown fields could include the necessary calculations.

Something like this:

if (event.value=="Rep KD") {
this.getField("QCTeam.KD").value = + "1";
} else this.getField("QCTeam.KD").value = event.value;

if (event.value=="Rep CS") {
this.getField("QCTeam.CS").value = + "1";
} else this.getField("QCTeam.CS").value = event.value;

if (event.value=="Rep SL") {
this.getField("QCTeam.SL").value = + "1";
} else this.getField("QCTeam.SL").value = event.value;

if (event.value=="Rep JC") {
this.getField("QCTeam.JC").value = + "1";
} else this.getField("QCTeam.JC").value = event.value;

if (event.value=="Rep RL") {
this.getField("QCTeam.RL").value = + "1";
} else this.getField("QCTeam.RL").value = event.value;

if (event.value=="Rep CR") {
this.getField("QCTeam.CR").value = + "1";
} else this.getField("QCTeam.CR").value = event.value;

if (event.value=="Rep RM") {
this.getField("QCTeam.RM").value = + "1";
} else this.getField("QCTeam.RM").value = event.value;

I know that the above is not correct (+ before the "1" will not make this calculate) but is basically what I am looking for. Is there a way to write a validation script for the dropdown fields that will add 1 to a text field (and also remove it if a mistake is made and a different rep is chosen)?

If it is not possible to include calculations in a validation script, how do you write a custom calculation scipt that refers to the specific rep chosen (not just the field itself) and then calculate the export value (which would be 1 for all reps) for each rep?

Hope this makes sense. Any help would be appreciated. Thanks for you time.

[PDF Automation Station · Accepted solution]

Since your naming convention is not conducive to one loop, I would suggest looping through all the fields and testing for field type, field value, and part of the field name ("QC.By.").  Name your text fields the exact same names as the dropdown values then enter the following custom calculation script in each text field:

var num=0;
for(var i = 0; i< this.numFields; i++)
{
var fName = this.getNthFieldName(i);
var f=this.getField(fName);
if(f==null) continue;
if(f.type=="combobox" && /QC.By./.test(f.name) && f.value==event.target.name)
{num+=1}
}
event.value=num;

Do not add export values to the dropdown selections.

[try67]

This won't work. You need to use a calculation script. What are the names of the drop-down fields?

[slaron]

Thanks for getting back so quickly. The naming convention for the dropdown fields is:

Month.QC.By.Number

Three letters at the front represent each Month (Jan, Feb, Mar, Apr, May, June, Jul, Aug, Sep, Oct, Nov, Dec) and for each month group, there is a 1, 2,3,4,5, or 6) at the end. For example the for January, the six dropdown would be

Jan.QC.By.1  

Jan.QC.By.2  

Jan.QC.By.3  

Jan.QC.By.4

Jan.QC.By.5

Jan.QC.By.6

For the other months, "Jan" is replaced with "Feb", "Mar", "Apr", "May", "June", "Jul", "Aug", "Sep", "Oct", "Nov", or "Dec" for a total of 72 dropdown fields

As stated earlier, all the dropdowns have the same list of names. They are:

Rep KD

Rep CS

Rep SL

Rep JC

Rep RL

Rep CR

Rep RM

Please let me know if you need any more information. 

[PDF Automation Station · Accepted solution]

Since your naming convention is not conducive to one loop, I would suggest looping through all the fields and testing for field type, field value, and part of the field name ("QC.By.").  Name your text fields the exact same names as the dropdown values then enter the following custom calculation script in each text field:

var num=0;
for(var i = 0; i< this.numFields; i++)
{
var fName = this.getNthFieldName(i);
var f=this.getField(fName);
if(f==null) continue;
if(f.type=="combobox" && /QC.By./.test(f.name) && f.value==event.target.name)
{num+=1}
}
event.value=num;

Do not add export values to the dropdown selections.

[slaron]

I tried entering this into the first of the 7 text fields (named exactly as the 1st dropdown value) and got the following error:

SyntaxError: syntax error
1:Field:Calculate

[PDF Automation Station]

That means you have a syntax error on the first line of your script.

[PDF Automation Station]

Post your script or upload the form.

[slaron]

The script I used is exactly what was given in the previous post:

var num=0;
for(var i = 0; i< this.numFields; i++)
{
var fName = this.getNthFieldName(i);
var f=this.getField(fName);
if(f==null) continue;
if(f.type=="combobox" && /QC.By./.test(f.name) && f.value==event.target.name)
{num+=1}
}
event.value=num;

[slaron]

I entered the above script into all the coordinating text fields and the script works even though I am getting errors; however, the error message changed to:

TypeError: f is null
1282:byteCodeTool

This error message now also appears when I make a selection in a unrelated dropdown field on the form (that is not calculated, but has a validation script). 

[try67]

There's a problem with the installation of JS in your application. Run a Repair Installation from the Help menu.

[PDF Automation Station]

Do you have other scripts in the form besides the one provided?  There might be an error elsewhere.  You can test the script by changing "event.target.name" to the field name and running the code in the console like this:

var num=0;
for(var i = 0; i< this.numFields; i++)
{
var fName = this.getNthFieldName(i);
var f=this.getField(fName);
if(f==null) continue;
if(f.type=="combobox" && /QC.By./.test(f.name) && f.value=="Rep KD")
{num+=1}
}
num

[slaron]

Thank you! The form is now functioning properly. I ran the code in the console (changing "Rep KD" to the actual name) and got a result of 0. I found and removed an old calculation from an unrelated field that I believe was causing the issue.

On a related note, I need the 7 text fields to total, but when I select the fields from the calculate pick menu it says:

The "Last Name" field does not exist, please reenter.

Even though the fields show up for me to pick. Is there a way to calculate the sum of the 7 fields created?

[PDF Automation Station · Accepted solution]

You should be able to select the fields from the pick menu.  If not, you can use the following custom calculation script:

event.value=
Number(this.getField("Rep KD").value)+
Number(this.getField("Rep CS").value)+
Number(this.getField("Rep SL").value)+
Number(this.getField("Rep JC").value)+
Number(this.getField("Rep RL").value)+
Number(this.getField("Rep CR").value)+
Number(this.getField("Rep RM").value);

[slaron]

That worked, thanks again! Don't know why the pick fields didn't work. It let me select the fields, but when I was done I would get an error message asking me to reenter.

[PDF Automation Station]

The calculation script should loop through all the dropdown fields and count how many times the rep shows up, then set the value of the text field to that number.  For example, if you create the first dropdown and name it Reps, then right-click the field, select "Create multiple copies", and enter 72 down and 1 across, it will produce fields named Reps.0, Reps.1, through Reps.71.  For simplicity, and to make one script that will work in every text field, name the text fields the same as the Reps field values (Rep KD, Rep CS, etc.)  Enter the following custom calculation script in each text field.

var num=0;
for(var i=0; i<72; i++)
{
if(this.getField("Reps."+i).value==event.target.name)
{num+=1}
}
event.value=num;

[slaron]

How will the above determine which selection is being made? For example, in text field "QCTeam.KD" how does the above calculation script calculate just the number of times "Rep KD" is selected and not when another selection is made? 

In case it is helpful, here is some more information about the form. The form will include 24 pages (two pp. per month) with 3 accounts per page. Each account will consist of several different fields, each of which will appear 72 times on the form (6 accounts x 12 months). Some of the other fields will also be tracked, but they are not dropdown fields. There will be a 25th page where all the tracking will be shown (in calculated text fields). 

[PDF Automation Station]

event.target.name is the name of the text field that contains the calculation.  The text field should be named "Rep KD".  If a dropdown value is "Rep KD" it will be counted.

[JR Boulay]

You don't need a script to do this.

Add ‘1’ as the export value to each relevant menu item, and then ask the text field to add up the relevant fields.
Using the form field naming convention, to calculate the sum you can simply designate the (virtual) parent field in the list of fields to be added, which is faster and more flexible (it allows you to add/remove fields without having to modify the calculation).

See attachment.

[PDF Automation Station]

They are adding up 7 different names across 72 dropdowns.  If you put all the export values as 1 the total will be all dropdowns that have a value, not the number of dropdowns with a specific value.

[slaron]

Unfortunately, this does not distinguish who was selected. It gives all the reps credit when one rep is selected.