14 Replies Latest reply on Nov 1, 2012 1:16 PM by gkaiseril

# Javascripting problems

I have an Excel form that I use often. I would like to make it a PDF. I did the conversion and all is well with the text fields. The problem is with two sections: a fee calculation which works like this: “X” sqFt * \$2.50 plus \$70. I have searched and searched to find a formula but the best I can do will actually make the calculation, but it leaves \$70 in the results field even if the quantity field is blank or zero. The calculation is pretty simple but the “plus \$70″ seems to throw it off somehow.  Additionally, the total, which is calculated via the radio button in the preferences section, will usually show a zero.  And should the form calculate as soon as entries are made in the appropriate fields or is there a key stroke or something needed to make it update?

QtyUnitFee

Sq Feet (Text only)

Lin Feet (Text only)
Total

The second problem is that the form is issued with an expiration date. So, we have a date field, a days field and an expiration date field. The permit is issued on a date, for a certain number of days and thus expires on the expiration date. Works fine in Excel but I cannot make it work using Javascript.

I would be very grateful if someone could help me.

Message was edited by: InspectorHills

• ###### 1. Re: Javascripting problems

I'm not sure exactly what you want. so it might be helpful to show the Excel formulas that you're currently using.

Regarding a calculation script, it is triggered whenever any field value changes.

• ###### 2. Re: Javascripting problems

But, that's the right maths. X times something + 70 is going to be 70 if X is zero. Obviously you want something different to happen, but what?

• ###### 3. Re: Javascripting problems

The basic Excel formula for the fee calculation is - =IF(B24="","",B24*2.5+70)

Where B24 is the quantity (sq ft, lineal feet, etc.)

The Excel formula for the date calculation is - =(G12+H13)

Where G12 is the inserted date and H13 is number of days the permit is issued for.

Let me say I am very grateful for your assistance.

• ###### 4. Re: Javascripting problems

Actually, the form starts out with the field blank.  Truthfully, I never inserted a 0 in that field in the Excel form, so I don't know what the result would be.  However, it shouldn't be difficult to account for that.  As a practical matter, typically only one or two of the quantity fields have a number inserted into them- the rest are blank, so, I dont think that it is really a big issue.

• ###### 5. Re: Javascripting problems

OK, the custom Calculate script for the first could be:

// Custom Calculate script

(function () {

// Get the value of the input field, as a string

var s1 = getField("sqft").valueAsString;

// Only do the calculation if the input field is not blank

if (s1) {

// Perform the calculation, rounding to the nearest cent

event.value = utiul.printf("%.2f", +s1 * 2.5 + 75);

} else {

// Blank this field if input is blank

event.value = "";

}

})();

Change "sqft" in the code above to the actual name of the field.

The other calculation involves date arithemetic, which is a bit more involved than in Excel. The calculate script could be something like:

// Custom Calculate script

(function () {

// Get the field values. as string

var sDate = getField("issued_date").valueAsString;

var sDays = getField("num_days").valueAsString;

// Proceed if both fields are not blank

if (sDate && sDays) {

// Convert date string to date object

var oDate = util.scand("mm/dd/yyyy", sDate);

// Convert days string to number

var nDays = +sDays;

// Add number of days to date

oDate.setDate(d.getDate() + nDays);

// Set this field value to end date

event.value = util.printd("mm/dd/yyyy", oDate);

} else {

// Blank this field

event.value = "";

}

})();

Again, change the field names to match the form and change the date format strings (mm/dd/yyyy) to match what you're using.

• ###### 6. Re: Javascripting problems

George:

I cut and pasted your code into the appropriate fields.  In the case of the date function, I changed the name of the form fields to Box1 (date) and Box2 (days).  Changed the date format in the fields and you code to dd/mm/yy.  When I tested it, the results field (expiration date) was blank.  I could click on the field and a 0 would appear and then disappear.

With my non-existant ability to read the code, it looks as though it should work.

The fee calculation sections do not produce a result either.

I am sure the answer is staring me right in the face, but I can't, for the life of me, see it.

• ###### 7. Re: Javascripting problems

In the first script, change the line to:

event.value = util.printf("%.2f", +s1 * 2.5 + 75);

And in the second one, that one line should be:

oDate.setDate(oDate.getDate() + nDays);

• ###### 8. Re: Javascripting problems

George:

Thanks for the PM re the typos.  I think you have a handle on the fee formulas.  I was able to edit them so, for the most part they work.  That said, there is no joy on the date script.  I've pasted it below just in case I messed something up.

// Custom Calculate script

(function () {

// Get the field values. as string

var sDate = getField("Box1").valueAsString;

var sDays = getField("Box2").valueAsString;

// Proceed if both fields are not blank

if (sDate && sDays) {

// Convert date string to date object

var oDate = util.scand("mm/dd/yy", sDate);

// Convert days string to number

var nDays = +sDays;

// Add number of days to date

oDate.setDate(d.getDate() + nDays);

// Set this field value to end date

event.value = util.printd("mm/dd/yy", oDate);

} else {

// Blank this field

event.value = "";

}

})();

I noticed on the fee area during testing that if I enter a number in the quantity field, it will calculate the result (good) but the "total" field does not update.  However, if I delete the entry in the "qty" field the "fee" field goes blank but the number that was in the "total" field becomes whatever was in the "fee" field.  Inserting a 0 in the fee field will usually cause the "total" field to update back to blank or 0.

Oh, and to answer your question about what I expect for free?  I expect to do my best to help someone else with the same grace and enthusiasm that you have demonstrated, should the occaision arise.

• ###### 9. Re: Javascripting problems

You didn't correct that second line of code that I pointed out.

The odd behavior you're seeing is most likely due to an incorrect field calculation order, so you should double check it to make sure it makes sense for your form. Exactly how you do this depends on what version of Acrobat you're using. In Acrobat 9, you'd go into forms editing mode (Forms > Add or Edit Fields) and select: Forms > Edit Field > Set Field Calculation order

In Acrobat 11, you'd go into forms editing mode (Tools > Forms > Edit) and then: Tasks > Other Tasks > Set Field Calculation Order

I don't have access to Acrobat 10 at the moment, but it close to Acrobat 11.

Just arrange the fields in the list so that it makes sense for your form.

Just so there's no more bugs involved, I put together a demo so I know that it works: https://acrobat.com/?d=ZEIrA0Yuea5JAewp*RHB2Q

Good luck!

• ###### 10. Re: Javascripting problems

Jeez!!  I feel as dumb as a box of rocks!  I took your code, exactly from your worksheet, and pasted it into my form.  Then, I changed the names of my fields to exactly those in your code.  Then moved the tab order so it tabs from the date to the days field sequentially.  Moved the expire date so it was the next in order, but that didn't work so I moved it back.  Still, nada.  Checked the calculation order and re-ordered that sequence for the entire form.  Still nothing.  By the way, I'm using Acrobat 9.

Thanks,

"Dumb Bob"

By the way, I did manage to find my rear end. I had to use both hands and a flashlight, though. I would email you the form, but you would likely find the error so embarrasingly quickly that I would have skulk away from the forum, never to return.

• ###### 11. Re: Javascripting problems

I'd be happy to look at the file if you want to email it to me: acroscript at gmail dot com

• ###### 12. Re: Javascripting problems

Thank you very much!!

• ###### 13. Re: Javascripting problems

George:  Thank you for all your assistance with this form.

I do have another question.  I have sent the form to a vendor for them to complete and use as a template (I did not use "distribute").  What I had in mind is that they would complete the form and email it back to me without saving it.  I would then add my signature etc. and send it back to them as a valid permit.  When the vendor got the form, he was able to fill out the form fields but not the applicant signature.

He looked at the "properties" and "signing" and commenting say "not allowed".  So, I looked at my copy and it says there no security measures in place (all choices are allowed).  Is it possible that these selections change when the form is emailed?  Or, he may be using Acrobat Reader - would that make a difference?  Maybe it has to be sent via the "distribute" option?

If so, how do I overcome this?

Thanks,

Bob

• ###### 14. Re: Javascripting problems

You also need to check product and version of product.