Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- >
- Acrobat SDK
- >
- Adobe Forms Javascript Help

Adobe Forms Javascript Help

Aug 09, 2018

Copy link to clipboard

Copied

Hello,

I have an excel form that I built in Adobe Forms - the excel form calculated savings and cost avoidance in positive and negative numbers based on 3 values, historic, offer and bid. I tried using a beautifier to generate the javascript code and am stuck. Here are the excel formulas and form values related to historic, first and final. Last, it should take the value of the Cost Savings and the Cost Avoidance, add and give total in positive or negative value. The formulas work perfect in excel, I just can't figure out how to convert them to javascript.

Excel value to Adobe Form Value

P37 = TotalsHistoricPrice

P38 = TotalsFirstorAverage

P39 = TotalsFinalPrice

D42 = CostSavingsProspectiveSuccess

H42 = CostAvoidanceProspectiveSuccess

The three formulas I need to calculate in my adobe pro form

1. Cost Savings - Excel Formula

=IF(P37=0,0,P37-P39)

2. Cost Avoidance - Excel Formula

=IF(P37=0,P38-P39,IF(P37<P38,P38-MAX(P37,P39),IF(P37=P38,0)))

3. Total Success - excel formula

=D42+H42

Please help, this is the last hang up I have on the form and I have studied the tutorials and am still struggling to make these work. The formula beautifier doesn't seem to work at all in these scenarios.

TOPICS

Acrobat SDK and JavaScript,
Windows

Community Guidelines

Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

Adobe Forms Javascript Help

Aug 09, 2018

Copy link to clipboard

Copied

Hello,

I have an excel form that I built in Adobe Forms - the excel form calculated savings and cost avoidance in positive and negative numbers based on 3 values, historic, offer and bid. I tried using a beautifier to generate the javascript code and am stuck. Here are the excel formulas and form values related to historic, first and final. Last, it should take the value of the Cost Savings and the Cost Avoidance, add and give total in positive or negative value. The formulas work perfect in excel, I just can't figure out how to convert them to javascript.

Excel value to Adobe Form Value

P37 = TotalsHistoricPrice

P38 = TotalsFirstorAverage

P39 = TotalsFinalPrice

D42 = CostSavingsProspectiveSuccess

H42 = CostAvoidanceProspectiveSuccess

The three formulas I need to calculate in my adobe pro form

1. Cost Savings - Excel Formula

=IF(P37=0,0,P37-P39)

2. Cost Avoidance - Excel Formula

=IF(P37=0,P38-P39,IF(P37<P38,P38-MAX(P37,P39),IF(P37=P38,0)))

3. Total Success - excel formula

=D42+H42

Please help, this is the last hang up I have on the form and I have studied the tutorials and am still struggling to make these work. The formula beautifier doesn't seem to work at all in these scenarios.

TOPICS

Acrobat SDK and JavaScript,
Windows

Community Guidelines

Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

christopherk39855053

Aug 09, 2018
0
/t5/acrobat-sdk/adobe-forms-javascript-help/td-p/10033296
11
Replies
11

Aug 09, 2018

Copy link to clipboard

Copied

Ok, so you want to convert a set of Excel calculations into Acrobat Form calculations. This topic comes up quite a bit. If you searched this form for the keyword "Excel Formula" you'll get these results:

https://forums.adobe.com/search.jspa?q=Excel%20Formula

The gist of what you'll find is that you need to learn a bit about performing calculations in Acrobat Forms. The Beautifier doesn't really have anything to do with converting Excel to Acrobat JavaScript.

Here are some articles on the topic:

Calculating field values and more

https://acrobatusers.com/tutorials/how-to-do-not-so-simple-form-calculations

https://acrobatusers.com/tutorials/conditional-execution

**Thom Parker**- Software Developer at PDFScripting.com- Use the Acrobat JavaScript Reference Early and Often

Community Guidelines

Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

Reply

Loading...

Thom Parker

Aug 09, 2018
0
/t5/acrobat-sdk/adobe-forms-javascript-help/m-p/10033297#M19597
Aug 09, 2018

Copy link to clipboard

Copied

Hello Thom,

Thanks for the additional information, I will go through these again and really appreciate it. I think fundamentally I understand the basics of the translation, and have attempted that in the form I am building. I have even spent time on some of the exact articles you have shared as I have been searching for an answer 2 days now. I believe where I am really struggling here is how to break down that second formula into the java script code. The complexity there is really making this challenging for me to break down into javascript. I understand that I need a custom calculation code given the math functions, I just cant figure out how to leverage the case parts in a way that makes sense. In some of the posts I have seen a "if" used, and in others I have seen broken up in case 1,2,3. Not sure which approach to use here.

Any other thoughts?

Community Guidelines

Reply

Loading...

christopherk39855053

Aug 09, 2018
0
/t5/acrobat-sdk/adobe-forms-javascript-help/m-p/10033298#M19598
Aug 09, 2018

Copy link to clipboard

Copied

A "Switch" statement doesn't fit with the selection cases for #2. A switch only works for comparisons to a constant value.

Here's the calculation:

var nHTotal = this.getField("TotalsHistoricPrice");

var nFAve = this.getField("TotalsFirstorAverage");

var nFPrice = this.getField("TotalsFinalPrice");

if(nHTotal == 0)

event.value = nFAve - nFPrice;

else if(nHTotal < nFAve)

event.value = nFAve - Math.max(nFAve ,nFPrice );

else if(nHTotal == nFAve)

event.value = 0;

else

event.value = ?; What is the default?

**Thom Parker**- Software Developer at PDFScripting.com- Use the Acrobat JavaScript Reference Early and Often

Community Guidelines

Reply

Loading...

Thom Parker

Aug 09, 2018
1
/t5/acrobat-sdk/adobe-forms-javascript-help/m-p/10033299#M19599
Aug 09, 2018

Copy link to clipboard

Copied

Thom,

Thanks a lot for explaining this to me. I see what you mean now with this calculation. I was sorta on the right path with what I had come up with through the research I had done but yet so far away. I am going to study this calculation tomorrow and see if I can then apply it to the savings formula too.

I really appreciate your help with this. I will provide an update tomorrow. Thank you!

Chris

Community Guidelines

Reply

Loading...

christopherk39855053

Aug 09, 2018
0
/t5/acrobat-sdk/adobe-forms-javascript-help/m-p/10033300#M19600
Aug 09, 2018

Copy link to clipboard

Copied

Note that Thom accidentally left off something important, which is getting the field value. So that first line should be:

var nHTotal = this.getField("TotalsHistoricPrice").value;

Do the same type of thing for the next two lines. Though I'd add that it's a good idea to explicitly convert the field value to a number:

var nHTotal = +this.getField("TotalsHistoricPrice").value;

Just a good habit to get into if you intend to deal with the field values as numbers.

Community Guidelines

Reply

Loading...

George_Johnson

Aug 09, 2018
0
/t5/acrobat-sdk/adobe-forms-javascript-help/m-p/10033301#M19601
Aug 10, 2018

Copy link to clipboard

Copied

There is a way to code case 2 in JavaScript using the switch statement. One needs to place a value in the expression for the select's parameter, and then use a statement or statements that evaluate to the type of that parameters value. Using the example the code could be:

- var nHTotal = this.getField("TotalsHistoricPrice").value;
- var nFAve = this.getField("TotalsFirstorAverage").value;
- var nFPrice = this.getField("TotalsFinalPrice").value;
- switch(true) {
- case (nHTotal == 0):
- event.value = nFAve - nFPrice;
- break;
- case (nHTotal < nFAve):
- event.value = nFAve - Math.max(nFAve ,nFPrice );
- break;
- case (nHTotal == nFAve):
- event.value = 0;
- break;
- default:
- event.value = "";
- break;
- }

I use this type of construct when I need to convert ranges to a single value, like variable shipping costs based on weights or distances.

Community Guidelines

Reply

Loading...

gkaiseril

Aug 10, 2018
1
/t5/acrobat-sdk/adobe-forms-javascript-help/m-p/10033302#M19602
Aug 10, 2018

Copy link to clipboard

Copied

Opp, Thanks for catching that George. Type'n too fast

**Thom Parker**- Software Developer at PDFScripting.com- Use the Acrobat JavaScript Reference Early and Often

Community Guidelines

Reply

Loading...

Thom Parker

Aug 10, 2018
0
/t5/acrobat-sdk/adobe-forms-javascript-help/m-p/10033303#M19603
Aug 10, 2018

Copy link to clipboard

Copied

Hello George,

Thanks for pointing this out, when I attempted to load it this morning, I quickly figured out there was a problem. I added the .value based on what I had read yesterday regarding the way to set up the script in calculations. Hopefully you can have a look at my latest post against this and let me know your thoughts on the formula not behaving as it did in excel.

Thanks again for all your help.

Best Regards,

Chris

Community Guidelines

Reply

Loading...

christopherk39855053

Aug 10, 2018
0
/t5/acrobat-sdk/adobe-forms-javascript-help/m-p/10033305#M19605
Aug 10, 2018

Copy link to clipboard

Copied

Community Guidelines

Reply

Loading...

christopherk39855053

Aug 10, 2018
0
/t5/acrobat-sdk/adobe-forms-javascript-help/m-p/10033306#M19606
Aug 10, 2018

Copy link to clipboard

Copied

Hello Thom and George,

First off, thanks for all your help, I really appreciate it and am learning a lot here.

Can you possibly help me with the following, the avoidance formula is not behaving as it does in excel. I am hopeful it is a minor adjustment needed.

This morning, I wrote the following code for the savings formula - =IF(P37=0,0,P37-P39)

// Get the field values, as numbers

var nHTotal = this.getField("TotalsHistoricPrice").value;

var nFPrice = this.getField("TotalsFinalPrice").value;

// Set this field value

{

if(nHTotal == 0)

event.value = 0;

else if(nHTotal > 0)

event.value = (nHTotal -nFPrice);

else

event.value = 0.00;

}

So far this one seems to be working.

However, the avoidance formula - =IF(P37=0,P38-P39,IF(P37<P38,P38-MAX(P37,P39),IF(P37=P38,0)))

I wrote the following based on Thom's advice:

// Get the field values, as numbers

var nHTotal = this.getField("TotalsHistoricPrice").value;

var nFAve = this.getField("TotalsFirstorAverage").value;

var nFPrice = this.getField("TotalsFinalPrice").value;

// Set this field value

{

if(nHTotal == 0)

event.value = nFAve - nFPrice;

else if(nHTotal < nFAve)

event.value = nFAve - Math.max(nFAve ,nFPrice );

else if(nHTotal == nFAve)

event.value = 0;

else

event.value = 0.00;

}

I also tried to use George's Formula and received the same result I will demonstrate below.

Scenario 1

nHTotal = 90

nFAve = 100

nFPrice = 95

The expected result is -5 on the savings formula and +5 on the avoidance formula - I am getting a 0 on the avoidance formula.

Excel would present as follows:

When I enter just a nFAve and a nFPrice value I am getting the net result so the formula appears to be half working, half not:

Any thoughts?

Community Guidelines

Reply

Loading...

christopherk39855053

Aug 10, 2018
0
/t5/acrobat-sdk/adobe-forms-javascript-help/m-p/10033304#M19604
christopherk39855053
LATEST

Aug 13, 2018

Copy link to clipboard

Copied

I figured it out. Thanks for all your help. There was an error in the translation on the math.max formula part. For some reason the code was translated back in the beginning to the wrong value in the response. The incorrect value was nFave - Math.max(nFave,nFPrice), the correct value is nFave - Math.max(nHTotal,nFPrice) . The correct code for avoidance is as follows.

// Get the field values, as numbers

var nHTotal = this.getField("TotalsHistoricPrice").value;

var nFAve = this.getField("TotalsFirstorAverage").value;

var nFPrice = this.getField("TotalsFinalPrice").value;

// Set this field value

switch(true) {

case (nHTotal == 0.00):

event.value = nFAve - nFPrice;

break;

case (nHTotal < nFAve):

event.value = nFAve - Math.max(nHTotal,nFPrice);

break;

case (nHTotal == nFAve):

event.value = 0;

break;

default:

event.value = "0.00";

break;

}

Community Guidelines

Reply

Loading...

christopherk39855053

Aug 13, 2018
1
/t5/acrobat-sdk/adobe-forms-javascript-help/m-p/10033307#M19607
Using the Community
Experience League
Terms of Use
Privacy Policy
Cookie preferences
AdChoices
Language:

- Deutsch
- English
- Español
- Français
- 日本語コミュニティ
- Português

Copyright © 2020 Adobe. All rights reserved.