New to this: Need Javascript to employ Excel's "count" formula

If you've already started with an AcroForm, and it works for you, then you should stick with it. The only reason to make the switch to LiveCycle forms is if you want the dynamic features, which require more experience and skill to use.

When writing scripts that deal with multiple fields in the same way, for example all the fields in a column in a table, naming is very important. The JavaScript code has to be able to automatically generate the names of the fields it needs to work on.

So if the fields in a column are named "Count1" thru "Count9". You could write a script for an AcroForm (i.e. an Acrobat Form) to count up all the fields that contain valid numbers like this.

The function "isNaN()" is a Core JavaScript function that determines if a particular value is numeric.

If you were doing this on a LiveCycle form the concept would be the same. The only difference would be in how the form field values are acquired.

JavaScript is simple as far as programming languages go, but it's still a programming language, and Acrobat provides a forms technology, not a spreadsheet technology, the operational concepts are very different. To do this effectively you don't need to be an expert, but you do need understand some of the underlying concepts in designing and programming forms in Acrobat and LiveCycle.

You can find tutorials and other info on this spcific topic, here at this web site (www.acrobatusers.com). Look at the Tutorials section. And even better info on scripting at www.pdfscripting.com. This is a membership web site, but there are some free videos that will give you the basics. Check it out.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Hi Thom,

I'll check out the site you suggested. I did change the form fields to Count1, Count2, etc

The error message states:

"missing ) after condition
6: at line 7"

I inserted the code in "Custom Javascript" in "Calculate" tab.

Ah Yes, the "if" statement is missing a closing parenthese, It should be

This is of course exactly the kind of thing you have to deal with when scripting. Every comma, bracket, and parenthese counts. It's a very good idea to do a little fomal learning before attempting to develop the kind of features you want on a form.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Yes, I agree. I'm like the last leg, only 10 yards left to go, bam! I hit a brick wall with Javascript programming. I wish Acrobat can incorporate Excel formulas (it can do that).

It would be easier to hire someone to do the scripting. I bet it would only take 10 minutes to finish my form.

I still need to get percentage on two form fields. Like 30 points of 50 points = what percentage? Can you help me out with it?

Thanks

First, set the field's format to Percent, its a built in format. Look on the format tab in the properties dialog for the text field. Next, add a calculation script to field that calculates a ration of the two source fields.
event.value = this.getField("Field1").value/this.getField("Field2").value;
Unfortunately Adobe can't add Excel style formulas to Acrobat. Excel is a spread sheet. It's a table of regular data. The Excel formulas work because the field names are regular and predicable. AcroForms is a general purpose forms technology. It is not a table of regular data and the field names are not predicable or regular. The LiveCycle (with FormCalc) forms technology is a lot closer to Excel formulas than AcroForms, but its still a long way off.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Thanks!

I'm still struggling with "count". Maybe I have to point it out clear so you can understand what I'm trying to achieve.

There are 5 (daily) Period Totals, with a scale of 1 to 4. So each period has 16 max. points.

However, if a student or a teacher is absent that day, the max points should not be counted toward the weekly total points.

I.E.

10 of 16 (Monday)
12 of 16 (Tuesday)
ABSENT (Wednesday)
ABSENT (Thursday)
10 of 16 (Friday)

32 of 48 (Weekly Total)

The form field - value - 48 is the big problem I'm trying to achieve. The value - 48 will increase if a student attends 5 school days.

I want to add that for each period total, there are 4 text fields and 4 numeric fields

IE:

Monday

Text 1 - 2 (droplist menu of 1, 2, 3, 4 and AB)
Text 2 - 4
Text 3 - 3
Text 4 - 2

So the total points of daily period should be 11 of 16. If absent, AB will be selected for numeric fields.

That's always helpful;) Being short and explicit is pretty important when you are asking for free help.

So you want to add up all the points in all the numeric fields on all the lines, excluding lines that contain the value "AB" in the first numeric field. Is this correct?

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Yes, you are correct. I want to stress that I would like to add up the "max" possible points for all numberic fields excluding "AB". That would be 4 points for each numberic field.

Ahh, hence the count. Ok.

You have kind of a table setup so name your fields in a regular fashion that paralells this.
Ex: Day1Period1, Day1Period2, etc.

Now you can write a double loop that will add it all up.

I just wrote this off the top of my head so I'm not promising it's syntactically correct.

This is exactly the kind of thing you'd learn how to do if you watched the video tutorials at www.pdfscripting.com

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

I'm sorry. Nothing happened. :( I do appreciate your help. I did re-label the form fields to be Day1Period1, Day1Period2 - Day2Period1, Day2Period2, etc.

I selected the form field, "Total Points" and insert the JavaScript in "Calculate" tab to run this script. The number remains the same.

I also created another text field and applied the JavaScript. It stands as "0".

Thanks for the heads-up on pdscripting.com

Well there is one last thing that needs to be done to make this work.

To find out, either watch the tutorials at pdfscripting.com or read this article.
http://www.acrobatusers.com/tutorials/2006/form_calculations

You should also read about how to debug scripts. So you can see how it's working.
http://www.acrobatusers.com/tutorials/2006/javascript_console

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

One thing for certain is that I will make a lousy programmer. That's a fact. I did launch Javascript debugging window and it looks fine...No error messages. I'm puzzled. I'll try the simple notation calculation method...will ask a math teacher here.

Thom, Thank you for your big help! Just one more field to go!

Ok, ok, I was really hoping it would be obvious. This is the kind of thing people pay a lot of consulting fees for, so feel lucky,

For this particular operation you can't use the "simplified notation" because a decision is required.

As I stated above, the count calculation code I provided is untested, I just wrote it off the top of my head. The first thing you need to do is make sure it's working, That's what the console window is for. You didn't see any errors so that's good, but how do you know that the "nCnt" value is correct? or is there some kind of operational error? Copy and paste the code into the console window and run it from there.

If it's working correctly then it's good for a custom calculation script. The trick is to assign the result of the calculation to the field value. This is done through the "event.value" property. So the last thing you need to do is end the script with:

event.value = nCnt;

That's all:)

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Hi Thom,

I managed to get our School Information System guy to try his hand on this script. He's very familiar with Javascript. He said your code is very elegant and he would not come up with it on his own. He was able to fix it (basically assigned the result of calulation to a field value like you said). I want to say many thanks for your efforts. I truly appreciate it.

Here's the code:


var nCnt = 0;
var DayName = "";
var cFldName = "";
var objField = this.getField("Total Points");


// Row (day) loop first
for(var nD=1;nD<6;nD++)
{
DayName = "Day" + nD;
// Period loop next
for(var nP=1;nP<5;nP++)
{
cFldName = DayName + "Period" + nP;
if(this.getField(cFldName).value != "AB")
nCnt += 4;
}
}

objField.value = nCnt;