• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers

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

New Here ,
Jun 14, 2018 Jun 14, 2018

Copy link to clipboard

Copied

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!

TOPICS
Acrobat SDK and JavaScript , Windows

Views

1.4K

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

correct answers 1 Correct answer

Community Expert , Jun 19, 2018 Jun 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)

{

 

...

Likes

Translate

Translate
Community Expert ,
Jun 14, 2018 Jun 14, 2018

Copy link to clipboard

Copied

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

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
New Here ,
Jun 19, 2018 Jun 19, 2018

Copy link to clipboard

Copied

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

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 19, 2018 Jun 19, 2018

Copy link to clipboard

Copied

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

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
New Here ,
Jun 19, 2018 Jun 19, 2018

Copy link to clipboard

Copied

LATEST

This is AMAZING Malcolm! Very helpful!

Worked a treat! Thanks!

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