Skip to main content
christopherk39855053
christopherk39855053
Participating Frequently
August 9, 2018
Question

Adobe Forms Javascript Help

  • August 9, 2018
  • 2 replies
  • 1244 views

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.

This topic has been closed for replies.

2 replies

christopherk39855053
christopherk39855053Author
Participating Frequently
August 10, 2018

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?

christopherk39855053
christopherk39855053Author
Participating Frequently
August 13, 2018

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;

}

Thom Parker
Community Expert
Thom ParkerCommunity Expert
Community Expert
August 9, 2018

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 PDFScriptingUse the Acrobat JavaScript Reference early and often
christopherk39855053
christopherk39855053Author
Participating Frequently
August 9, 2018

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?

Thom Parker
Community Expert
Thom ParkerCommunity Expert
Community Expert
August 9, 2018

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 PDFScriptingUse the Acrobat JavaScript Reference early and often
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices