Lost in Calculations???

Apr 06, 2020

Copy link to clipboard

Copied

Ok. Could someone please help? I've been tasked with taking an excel order from and creating a fillable PDF. Here is where I lose it. I need to create a calculation with user filled amounts that calculate sum and a discount.

Subtotal + Misc Amount + Shipping Costs = Final Total
Discount in percentage will applied to final total.

The calcualtion for the final total I get if there is no discount. The discount is a user filled field that may or may not apply.
If there no discount then the field need to be empty. If the user inputs a number then it must show a whole number with the percentage sign and calculates the final total.

Any help would be great. I'm a scripting noob.

TOPICS
Acrobat SDK and JavaScript, Comment review and collaborate PDF, General troubleshooting, PDF forms

Views

128

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

Lost in Calculations???

Apr 06, 2020

Copy link to clipboard

Copied

Ok. Could someone please help? I've been tasked with taking an excel order from and creating a fillable PDF. Here is where I lose it. I need to create a calculation with user filled amounts that calculate sum and a discount.

Subtotal + Misc Amount + Shipping Costs = Final Total
Discount in percentage will applied to final total.

The calcualtion for the final total I get if there is no discount. The discount is a user filled field that may or may not apply.
If there no discount then the field need to be empty. If the user inputs a number then it must show a whole number with the percentage sign and calculates the final total.

Any help would be great. I'm a scripting noob.

TOPICS
Acrobat SDK and JavaScript, Comment review and collaborate PDF, General troubleshooting, PDF forms

Views

129

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
Apr 06, 2020 0
Adobe Community Professional ,
Apr 06, 2020

Copy link to clipboard

Copied

Hi,

 


Rightclick on the "Discount Amt" field and go to the Format tab, then click on "Edit" in the Custom Format Script section.

 

You can use a line of code like this as the custom format script:

 

if(!event.willCommit)
console.println(!event.value);

{
var v = +getField("Discount Amt").value;

event.value = v/100;
if(v=="") event.value ="";
}

 

 

In the Final Total field, right-click to select Properties and goto the "Calculate" tab. Tick the radio button below "Custom calculation script" and you may add something like this:

 

 

var a = this.getField("Subtotal").value+ this.getField("Misc. Amount").value+this.getField("Shipping Costs").value;
var b = this.getField("Discount Amt").value;
if (b =="") event.value =a;
else if(b !="") event.value = a+(a/b);

 

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
Reply
Loading...
Apr 06, 2020 0
Apr 06, 2020

Copy link to clipboard

Copied

Hi ls_rbls,

 

It isn't quite working. It's adding to the final total rather than applying the discount.
Also I need to show the discount amount as a whole number. Ex. 10% rather than 0.1. 

 

Screen Shot 2020-04-06 at 6.37.26 PM.png

 

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
Reply
Loading...
Apr 06, 2020 0
Adobe Community Professional ,
Apr 06, 2020

Copy link to clipboard

Copied

 

My apologies, I made a mistake.

 

In the last line of the code above:

 

else if(b !="") event.value = a+(a/b);

 

Change the last part to  a-(a/b).

 

 

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
Reply
Loading...
Apr 06, 2020 0
Adobe Community Professional ,
Apr 06, 2020

Copy link to clipboard

Copied

I forgot to explain that the reason why I used javascript in the percentage discount field is because you won't be able to get rid of the  % sign when the using the "Select from category" options in the Format tab. You will need to do this with javascript only. See here: https://answers.acrobatusers.com/Logic-percentage-format-text-box-q40903.aspx

 

If you notice below in that dialogue box it  has a notice "The percentage format category multiplies the field value by 100 and displays it with a percent sign."

 

This right here will create problems in your financial calculations.

 

By using this format category it will also round up your percentage number entered in that field. For example, a value that you may enter as .3876 will round up to .39% and a percentage that you enter in this field as 2.33 will display as 233%

 

If you're looking for precision in managing financial calculations this is something to consider.

 

Here is a very thoruough explanation of why this happens in Acrobat posted for another user back in 2018: 

https://community.adobe.com/t5/acrobat/percentage-calculation/td-p/9943651?page=1

 

If you don't think this is an issue in the type of form that you're creating and would like to leave this field as Percentage in the format category, then the last line of the code that I posted for you should be expressed like this:

 

 

else if(b !="") event.value = Math.abs(a-(Math.ceil(a/b)/100));

 

 

So, in summary, if we use the links that I've referenced above for you, your scripts should be used in the following manner:

 

  • In your "Discount Amt" field, use a customr format script like this

 

if (event.value !=="" && !isNaN(event.value)){
event.value = util.printf("%.3f%",event.value/100);
}else{
event.value = "";
}

 

  • And a custom calculation script in the total field like this

 

var a = this.getField("Subtotal").value+ this.getField("Misc. Amount").value+this.getField("Shipping Costs").value;
var b = this.getField("Discount Amt").valueAsString;
if (b =="") event.value =a;
else if(b !="") event.value = Math.abs(a-(Math.ceil(a/b)/100));

 

These are the line of code that are working on my end.

 

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
Reply
Loading...
Apr 06, 2020 0
Apr 07, 2020

Copy link to clipboard

Copied

Hi ls_rbls,

 

I just can't seem to get it to work. Here is the link to the file. PDF Order Form 

Here is some context:
1. Form is going to retailers and should be as simple as possible

2. SubTotal1 and ShippingCosts will always have a total.

3. SubTotal1 is a sum of a previous calculations.
4. ShippingCosts is a required user filled field.

5. MiscAmount is an optional user filled field

6. Discount Amt is an optional user filled field and must be a blank empty field if it is not being used.
7. Discount Amt must show as a whole percentage number with the % symbol. (Ex. 10% not 0.10%)

As I mentioned before I'm a newbie with script but I get what you are trying to achieve.
I really appreciate with all the help you've given so far.

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
Reply
Loading...
Apr 07, 2020 0
Adobe Community Professional ,
Apr 07, 2020

Copy link to clipboard

Copied

Hi,

 

I am examining your PDF and there's a lot of fields with this as a validation script if (event.value==0) event.value = "";   This is not necessary, but if your intent is to prevent users from actually entering 0 that can be handled as a custom format script or even a custom keystroke script, not a validation script.

 

This is constantly giving errors and it doesn't even allow to fill the form properly. This  doesn't make the form to be as simple as possible.

 

Also, you mentioned that Subtotal1 and Shipping Costs fields will always have a total. But I don't see where the shipping costs field obtains its vlue from. Is it a manual entry or is it missing a calulation?

 

 

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
Reply
Loading...
Apr 07, 2020 0
Apr 07, 2020

Copy link to clipboard

Copied

Hi ls_rbls,

ShippingCosts are manually entered but it does not need to be blank. A zero being shown is fine. 
I'm only concerned with getting the manually entered DiscountAmt being shown as a % and subtracting that percentage from the sum of SubTotal1 + MiscAmount + ShippingCosts. MiscAmount is an optional manually enter amount. The user may or may not use the MiscAmount & DiscountAmt.
The only field to show an empty field is the DiscountAmt until the user enters a number.

I've been playing around a bit and I'm close with your scripts but it's just not working for me.

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
Reply
Loading...
Apr 07, 2020 0
ls_rbls LATEST
Adobe Community Professional ,
Apr 07, 2020

Copy link to clipboard

Copied

Ok, so here is the script for the Discount Amt field:

 

You have to place this script as the custom format script, do not add validation scriplets in or anything else. This is all you need. You can

 

if (v.value !=="" && !isNaN(event.value)){
event.value = util.printf("%1.0f%",event.value);
}else{
event.value = "";
}

 

copy and paste it directly in your PDF.

 

In your Total field, you can try something like this as the custom calculation script:

 

 

var a = this.getField("Subtotal").value;
var b = this.getField("Misc. Amount").value;
var c = this.getField("Shipping Costs").value;
var d = this.getField("Discount Amt").value;


if ( (d !="") && (a && b && c !="") ) event.value = (a+b+c)-((a+b+c)*(d/100));
else if ( (d !="" && a =="") &&  (b && c !="") ) event.value = (b+c)-((b+c)*(d/100));
else if ( (d !="" && b =="") &&  (a && c !="") ) event.value = (a+c)-((a+c)*(d/100));
else if ( (d !="" && c =="") &&  (a && b !="") ) event.value = (a+b)-((a+b)*(d/100));

else if ((a && b && c !="") && (d =="")) event.value = a+b+c;

 

 

 

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
Reply
Loading...
Apr 07, 2020 0