Adobe Forms Javascript Help

[christopherk39855053]

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.

[Thom Parker]

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

[christopherk39855053]

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]

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?

[christopherk39855053]

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

[George_Johnson]

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.

[Thom Parker]

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

[christopherk39855053]

Thanks again Tom, hopefully you can have a look at my last post against the thread to see what is happening that I cannot figure out.  Let me know if you have any thoughts and again, I appreciate your time and help.

[christopherk39855053]

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

[gkaiseril]

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:

1. var nHTotal = this.getField("TotalsHistoricPrice").value;
2. var nFAve = this.getField("TotalsFirstorAverage").value;
3. var nFPrice = this.getField("TotalsFinalPrice").value;
5. switch(true) {
6.      case (nHTotal == 0):
7.           event.value = nFAve - nFPrice;
8.      break;
9.      case (nHTotal < nFAve):
10.           event.value = nFAve - Math.max(nFAve ,nFPrice );
11.      break;
12.      case (nHTotal == nFAve):
13.           event.value = 0;
14.      break;
15.      default:
16.           event.value = "";
17.      break;
18. }

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.

[christopherk39855053]

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]

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;

}