Skip to main content
tomc35195489
tomc35195489
Participant
June 14, 2018
Answered

How do I convert an excel formula into Javascript for use in PDF form fields?

  • June 14, 2018
  • 1 reply
  • 2891 views

So I've been trying to do this for a while now, I just can't seem to see what's going wrong with the formula...

This is the excel version (note: instead of cell names, I need to put the form name, which is OverallVisits);

=IF(H8=500,"250",IF(H8=1000,"350",IF(H8=2000,"500")))

So I replaced H8 with OverallVisits but it's not working.

I also used a converting website and it then came back with syntax errors...

Please help anyone

Thanks in advance!

This topic has been closed for replies.
Correct answer BarlaeDC

Hi Tom,

In the Over12Months field, you have the calculate field set to "Simplified Field Notation", if you change this to the "Custom Calculation Script" and then use the following code that should solve the problem.

// get number of overall visits
var overallVisits = this.getField( "OverallVisits").value
// set the default return value
var tempValue = 0;
// check how many overall visits and set return value as needed
if ( overallVisits === 500)
{
     tempValue = 250;
}
else if ( overallVisits === 1000)
{
     tempValue = 350;
}
else if ( overallVisits === 2000 )
{
     tempValue = 500;
}
// push the value back to the fields.
event.value = tempValue;

For information:

As this is a short form, I am happy to leave as a calculation script, but if you had more form fields you may notice a slow down of the form by using that.

Hope this helps.

Malcolm

1 reply

BarlaeDC
Community Expert
BarlaeDCCommunity Expert
Community Expert
June 14, 2018

HI,

it is not as easy as just changing names for the formula to work.

Excell automatically assigns the return value to the cell you are in, PDF does not, you have to be explicit.

taking the code a part a little to explain,

= IF (H8=500, "250")

This means , set the value of this cell to "250" if H8 is "500".

To do that in JavaScript in PDF you would need something like

var target = this.getField("TargetCell"); // ( this can be done with event.target, but I wanted to make it clear what was happening)

var H8Field= this.getField ("H8");

if ( H8Field.value == 500)

{

     target.value = "250";

}

And then you would need to assign this code to run somewhere, validate or calculate would work.

If you post you PDF, we can take a look and see if we are able to help.

Regards

Malcolm

tomc35195489
tomc35195489Author
Participant
June 19, 2018

Hi Malcolm,

Here is the PDF... Dropbox - Sales004_Business Case Doc FORM.pdf the part of the PDF that I require form filling is the bottom right section...

This version already has some calculations in to give me the other values. But basically the bit I am struggling with is the section of 'over 12 months at…'

This is the part I need to be associated with the overall visits... so if someone selects the overall visits to be 500 from the drop down list, then the over 12 months will automatically change to 250. if they select 1000 from the overall visits drop-down, it will show 350 and finally if they select 2000 from the drop-down they will see 500 in the over 12 months box...

If you can help with this that would be amazing!

thanks

Tom

BarlaeDC
Community Expert
BarlaeDCCommunity ExpertCorrect answer
Community Expert
June 19, 2018

Hi Tom,

In the Over12Months field, you have the calculate field set to "Simplified Field Notation", if you change this to the "Custom Calculation Script" and then use the following code that should solve the problem.

// get number of overall visits
var overallVisits = this.getField( "OverallVisits").value
// set the default return value
var tempValue = 0;
// check how many overall visits and set return value as needed
if ( overallVisits === 500)
{
     tempValue = 250;
}
else if ( overallVisits === 1000)
{
     tempValue = 350;
}
else if ( overallVisits === 2000 )
{
     tempValue = 500;
}
// push the value back to the fields.
event.value = tempValue;

For information:

As this is a short form, I am happy to leave as a calculation script, but if you had more form fields you may notice a slow down of the form by using that.

Hope this helps.

Malcolm

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices