cancel
Turn on suggestions
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Exit
search
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
0
Upvote

Convert Complex Excel Calculation Formula to PDF Custom Calculation Script

AaronIsh
New Here ,
Jan 14, 2025 Jan 14, 2025

Copy link to clipboard

Copied

I have an Excel formula calculates the Unit_Price which is then multuplied by the quantity to establish a quote for printed matierial. I have been asked to create a PDF Form for orders of printed material but I have very little to no experiience with PDF Custom Calculation Script (or java).

 

I need help with translating the formula below into Custome Calculation Script for the Unit_Price field. It is calculated on 2 variables, the Item (H26) and Quantity (I26). I was able to do a basic custom calculation based just on the item and set a single price but not able to work out how to include the second variable which is the price change based on quantity).  Unfortunatley I don't know javascript at all and nothing I found anywhere was able to really assist me in the translating this Excel formula.

 

If anyone can help or point me to a great tutorial on translating Excel to Java, I would really appreciate the assisatance.

 

(H26) - Printed_Item (var v1 = this.getField("Print_Item").value;)

(I26) - Print_Quantity (var v2 = this.getField("Print_Quantity").value;)

 

EXCEL FORMULA to set UNIT_PRICE

=IF(H26="A4 Flyers",(IF(ISBLANK(I26),"",IF(AND(I26>=1,I26<151),0.25,IF(AND(I26>=151,I26<451),0.2,IF(AND(I26>=451,I26<951),0.14,IF(AND(I26>=951,I26<1401),0.1,IF(AND(I26>=1401,I26<1901),0.09,IF(I26>=1901,0.08,"")))))))),IF(H26="DL Flyers",(IF(ISBLANK(I26),"",IF(AND(I26>=1,I26<151),0.08,IF(AND(I26>=151,I26<451),0.07,IF(AND(I26>=451,I26<951),0.05,IF(AND(I26>=951,I26<1401),0.04,IF(AND(I26>=1401,I26<1901),0.04,IF(I26>=1901,0.03,"")))))))),IF(H26="A4 Poster (100 gsm)",(IF(ISBLANK(I26),"",IF(AND(I26>=1,I26<11),1.00,IF(AND(I26>=11,I26<26),0.07,IF(AND(I26>=26,I26<51),0.05,IF(AND(I26>=51,I26<101),0.03,IF(I26>=101,0.25,""))))))),IF(H26="A3 Poster (100 gsm)",(IF(ISBLANK(I26),"",IF(AND(I26>=1,I26<151),0.08,IF(AND(I26>=151,I26<451),0.07,IF(AND(I26>=451,I26<951),0.05,IF(AND(I26>=951,I26<1401),0.04,IF(AND(I26>=1401,I26<1901),0.04,IF(I26>=1901,0.03,"")))))))),IF(H26="Corflute - A1 Poster Coreflute",(IF(ISBLANK(I26),"",IF(AND(I26>=1,I26<11),25,IF(AND(I26>=11,I26<26),22.5,IF(I26>=26,20,""))))))))))

TOPICS
Acrobat SDK and JavaScript

Views

123
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

Nesa Nurani Community Expert
Community Expert , Jan 14, 2025 Jan 14, 2025

Try this as custom calculation script of  "Unit_Price" field:

var printItem = this.getField("Print_Item").valueAsString;
var quantity = Number(this.getField("Print_Quantity").valueAsString);
var unitPrice = "";

if (!isNaN(quantity) && quantity > 0) {
    switch (printItem) {
        case "A4 Flyers":
            if (quantity >= 1 && quantity < 151) unitPrice = 0.25;
            else if (quantity >= 151 && quantity < 451) unitPrice = 0.2;
            else if (quantity >= 451 && quantity < 951) u
...

Votes

Translate
replies 4 Replies 4
latest latest Jump to latest reply
AaronIsh
New Here ,
Jan 14, 2025 Jan 14, 2025

Copy link to clipboard

Copied

This what I was able to get out of ChatGPT:

(function () {
    var H26 = this.getField("H26").valueAsString; // Replace with the actual field name for H26
    var I26 = +this.getField("I26").value; // Replace with the actual field name for I26
    var result = "";

    if (H26 === "A4 Flyers") {
        if (I26 === "") {
            result = "";
        } else if (I26 >= 1 && I26 < 151) {
            result = 0.25;
        } else if (I26 >= 151 && I26 < 451) {
            result = 0.2;
        } else if (I26 >= 451 && I26 < 951) {
            result = 0.14;
        } else if (I26 >= 951 && I26 < 1401) {
            result = 0.1;
        } else if (I26 >= 1401 && I26 < 1901) {
            result = 0.09;
        } else if (I26 >= 1901) {
            result = 0.08;
        }
    } else if (H26 === "DL Flyers") {
        if (I26 === "") {
            result = "";
        } else if (I26 >= 1 && I26 < 151) {
            result = 0.08;
        } else if (I26 >= 151 && I26 < 451) {
            result = 0.07;
        } else if (I26 >= 451 && I26 < 951) {
            result = 0.05;
        } else if (I26 >= 951 && I26 < 1401) {
            result = 0.04;
        } else if (I26 >= 1401 && I26 < 1901) {
            result = 0.04;
        } else if (I26 >= 1901) {
            result = 0.03;
        }
    } else if (H26 === "A4 Poster (100 gsm)") {
        if (I26 === "") {
            result = "";
        } else if (I26 >= 1 && I26 < 11) {
            result = 1.00;
        } else if (I26 >= 11 && I26 < 26) {
            result = 0.07;
        } else if (I26 >= 26 && I26 < 51) {
            result = 0.05;
        } else if (I26 >= 51 && I26 < 101) {
            result = 0.03;
        } else if (I26 >= 101) {
            result = 0.25;
        }
    } else if (H26 === "A3 Poster (100 gsm)") {
        if (I26 === "") {
            result = "";
        } else if (I26 >= 1 && I26 < 151) {
            result = 0.08;
        } else if (I26 >= 151 && I26 < 451) {
            result = 0.07;
        } else if (I26 >= 451 && I26 < 951) {
            result = 0.05;
        } else if (I26 >= 951 && I26 < 1401) {
            result = 0.04;
        } else if (I26 >= 1401 && I26 < 1901) {
            result = 0.04;
        } else if (I26 >= 1901) {
            result = 0.03;
        }
    } else if (H26 === "Corflute - A1 Poster Coreflute") {
        if (I26 === "") {
            result = "";
        } else if (I26 >= 1 && I26 < 11) {
            result = 25;
        } else if (I26 >= 11 && I26 < 26) {
            result = 22.5;
        } else if (I26 >= 26) {
            result = 20;
        }
    }

    event.value = result; // Assign the calculated result to the field
})();

Votes

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
AaronIsh
New Here ,
Jan 14, 2025 Jan 14, 2025

Copy link to clipboard

Copied

In Response To AaronIsh

Looks like it worked. Hope this may be helpful for others.

Votes

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
Nesa Nurani Community Expert
Community Expert ,
Jan 14, 2025 Jan 14, 2025

Copy link to clipboard

Copied

Try this as custom calculation script of  "Unit_Price" field:

var printItem = this.getField("Print_Item").valueAsString;
var quantity = Number(this.getField("Print_Quantity").valueAsString);
var unitPrice = "";

if (!isNaN(quantity) && quantity > 0) {
    switch (printItem) {
        case "A4 Flyers":
            if (quantity >= 1 && quantity < 151) unitPrice = 0.25;
            else if (quantity >= 151 && quantity < 451) unitPrice = 0.2;
            else if (quantity >= 451 && quantity < 951) unitPrice = 0.14;
            else if (quantity >= 951 && quantity < 1401) unitPrice = 0.1;
            else if (quantity >= 1401 && quantity < 1901) unitPrice = 0.09;
            else if (quantity >= 1901) unitPrice = 0.08;
            break;

        case "DL Flyers":
            if (quantity >= 1 && quantity < 151) unitPrice = 0.08;
            else if (quantity >= 151 && quantity < 451) unitPrice = 0.07;
            else if (quantity >= 451 && quantity < 951) unitPrice = 0.05;
            else if (quantity >= 951 && quantity < 1401) unitPrice = 0.04;
            else if (quantity >= 1401 && quantity < 1901) unitPrice = 0.04;
            else if (quantity >= 1901) unitPrice = 0.03;
            break;

        case "A4 Poster (100 gsm)":
            if (quantity >= 1 && quantity < 11) unitPrice = 1.00;
            else if (quantity >= 11 && quantity < 26) unitPrice = 0.07;
            else if (quantity >= 26 && quantity < 51) unitPrice = 0.05;
            else if (quantity >= 51 && quantity < 101) unitPrice = 0.03;
            else if (quantity >= 101) unitPrice = 0.25;
            break;

        case "A3 Poster (100 gsm)":
            if (quantity >= 1 && quantity < 151) unitPrice = 0.08;
            else if (quantity >= 151 && quantity < 451) unitPrice = 0.07;
            else if (quantity >= 451 && quantity < 951) unitPrice = 0.05;
            else if (quantity >= 951 && quantity < 1401) unitPrice = 0.04;
            else if (quantity >= 1401 && quantity < 1901) unitPrice = 0.04;
            else if (quantity >= 1901) unitPrice = 0.03;
            break;

        case "Corflute - A1 Poster Coreflute":
            if (quantity >= 1 && quantity < 11) unitPrice = 25.0;
            else if (quantity >= 11 && quantity < 26) unitPrice = 22.5;
            else if (quantity >= 26) unitPrice = 20.0;
            break;

        default:
            unitPrice = "";
            break;}}

event.value = unitPrice;

Votes

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
AaronIsh
New Here ,
Jan 14, 2025 Jan 14, 2025

Copy link to clipboard

Copied

LATEST
In Response To Nesa Nurani

Thanks you. I will try that too.

Votes

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
Copyright © 2025 Adobe. All rights reserved.
Using the Community Experience League Terms of Use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices