Hi - Help needed please?

Community Beginner ,
Apr 05, 2022 Apr 05, 2022

Copy link to clipboard

Copied

As a complete javascript novice I've been trying to duplicate the formulae in an operating Excel file, in an Acrobat 'form'. Below is an example of the typical Excel formula (of which there are x40), with reference to calculations performed on various cell data :

=D14-(D22*1000*0.02)/($T$20*35.6)-$M$5

I have attempted to use Acrobat 'custom calculation script' to perform the same process and arrived at this:

event.value = (this.getField("22_AWG01").value)-(0.326*1000*0.02)/(this.getField("Total_current").value*35.6)-(this.getField("Metres").value)

In many cases this does return the answer/s I was expecting - but not in every case. No doubt my method or syntax is probably inappropriate

I do have some extra functions/conditions to apply if possible, should I manage to get this fixed

MTIA
Steve


TOPICS
JavaScript , PDF forms

Views

120

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
community guidelines
Community Expert ,
Apr 05, 2022 Apr 05, 2022

Copy link to clipboard

Copied

(0.326*1000*0.02)

Why does you use 0.326 here?

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
community guidelines
Community Beginner ,
Apr 05, 2022 Apr 05, 2022

Copy link to clipboard

Copied

Hi Bernd - thanks for looking.

Sorry,  I confused the issue.
In the Excel file there are some referenced cells "D12, D22, D32, D42, D52" - that all have a set value 0.326.
I thought that it would be easier for me to 'read what was going on' if I substituted the Cell ref for its set value.

 

the sum I get from the above calc has to be converted from metres to feet

event.value = (this.getField("22_AWG01").value)/0.3048

 

Is my javascript fit for purpose?

Steve

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
community guidelines
LEGEND ,
Apr 05, 2022 Apr 05, 2022

Copy link to clipboard

Copied

I think you need to break it down and look at the exact detail. The formula looks plausible, but of course we don't know what's in your fields or what you see. Take one case where it doesn't do what you expect.

-Tell us the value Excel gives and the value found in D14, D22, T20 and M5.

-Tell us the value Acrobat gives.

-And tell us the value of 22_AWG01, Total_current and Metres

(As well, of course, as telling us why you've replaced the reference to D22 by the constant 0.326)

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
community guidelines
Community Beginner ,
Apr 05, 2022 Apr 05, 2022

Copy link to clipboard

Copied

Test Screen Name, thanks for replying.
I'll do as you ask.

The Acrobat form is to calculate voltage drop over a 'custom' user entered 'wire length (max 2m)' & user entered 'wattage' figure.

A figure is calculated for each of the different wire gauge (AWG) sizes: 22 (0.326mmsq), 20 (0.5mmsq), 18 (0.75mmsq), 17 (1.0mmsq), 15 (1.5mmsq), 13 (2.5mmsq), 11 (4mmsq) and 9 (6.6mmsq)

As you indicate, it's my data entry into the javascript that's at fault

MTIA
Steve

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
community guidelines
Community Beginner ,
Apr 06, 2022 Apr 06, 2022

Copy link to clipboard

Copied

Hi Test Screen Name
I've been through the figures, and the calculations do appear to be correct. The main issue I now think is that the x80 fields containing calculations don't all update simultaneously when the custom user data is inserted. As I've been working on this project I found the first & second rows (2x8=16 fields at the top of the page) were calculating instantly - as I scrolled down the page, what I thought were incorrect calculations (eg my errors) were fields that hadn't yet re-calculated. If I retun to the pdf 15 seconds later all the calculations have updated correctly. I have been through and checked the field calculation order which luckily, for the most part, followed my construction of the field layout.

Is there a way I can speed up the calculations, or add a button to click to 'Update all fields'?
As there are a few more issues to resolve on this project I'm happy to reimburse for time spent helping.
Attached is the 'bare bones' design of the form showing the fields which I intend to copy into a designed layout.
Steve

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
community guidelines
Community Expert ,
Apr 06, 2022 Apr 06, 2022

Copy link to clipboard

Copied

Change the field calculation order.

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
community guidelines
Community Beginner ,
Apr 06, 2022 Apr 06, 2022

Copy link to clipboard

Copied

Hi Bernd
I have looked into this. Would you suggest performing all the 'add/multiply/subtract/division' calculations before performing the conversion (metres to feet) calculations? 

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
community guidelines
LEGEND ,
Apr 06, 2022 Apr 06, 2022

Copy link to clipboard

Copied

Don't try and follow rules about what kind of calculation. It really is the order of calculation, and only you can know this information. (Ironically, Excel can know this without you tell it!)

 

In a simple case consider a form like this

 

Item 1 price ...  A

Item  2 price ...  B

Subtotal  ...     C     C=A+B

Tax ....    D     D=C*0.2

Total to pay .... E   E=C+D

 

You can see you cannot calcuate E until you have calculated C and D. You cannot calculate D until you have calculated C. And so on. We know this insinctively as humans, and we also tend to go from top to bottom. Acrobat is not smart, and does not automatically go from top to bottom, so we have to tell it the right order. If not sure, just work through on piece of paper without making any assumptions.

 

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
community guidelines
Community Beginner ,
Apr 06, 2022 Apr 06, 2022

Copy link to clipboard

Copied

I think I've ordered the calcs correctly, but will keep experimenting to see which way offers quickest response. After further searching I found this 'this.calculateNow()' which I have assigned to a button (labelled 'Click to refresh') next to the two user input fields, which seems to satisfactorily force the calculation update.

I've seen that it's possible to attribute a background colour to a cell using 'conditional formatting'. Is it possible through a condition to change a calculated field result into a text string. If the field result is below a certain figure (x8 different instances) I'd like the field to turn red, with white text saying N/A?

 

Steve

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
community guidelines
Community Beginner ,
Apr 13, 2022 Apr 13, 2022

Copy link to clipboard

Copied

LATEST

Thanks both for your contributions. Unfortunately this week I've found out that the form/calculations have been updated to include a further x2 input variations. Given that I was struggling with the simpler form (and I have no further time to experiment), I think it would be wise to commission a third party to produce the form for me.

Many thanks
Steve

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
community guidelines