Can calculations of many rows be made simpler with the help of a custom script?

[fredrikochpetra]

Hi, I have an order form which I have converted to a pdf form to be able to use as basis for an invoice for my client. I want each row of articles to sum up, i e pris*antal=totalt (price*quantity=total) and I know how to do this manually, however it is a very tedious task and I wonder whether there was a simpler way to do this? 

Thank you!

[try67]

Yes, but in order for it to save you work the field names have to follow the same naming pattern, which they don't seem to do in your case. That means you'll have to manually write out all the fields names in the code, which defeats the purpose of automating it. If they were all named the same, with just different numbers for each set, then you could have easily done it using a generic function as a doc-level script.

[fredrikochpetra]

I see. Somehow the naming of the first column started with the number 4 (Text Field 4) but the order is consecutive, please see attached picture. Will it still not work? 

Thank you so much 🙂

[try67]

Is the offset with all of the fields 3, though?

[fredrikochpetra]

Yes, always 3 🙂

[try67]

You can run this code from the JS Console, then:

for (var i=1; i<=100; i++) {
	this.getField("totalt_"+i).setAction("Calculate", "event.value = Number(this.getField(\"Text Field "+(i+3)+"\").valueAsString) * Number(this.getField(\"antal_"+i+"\").valueAsString);");
}

Change "100" in the first line to the actual number of fields in your file.

[try67]

Sorry, I had some mistakes in the code. Use the new version.

[fredrikochpetra]

I am so thankful for the help, I did however forget to disclose the fact that I am a total newbie when it comes to scripts and thus have no idea what I'm doing. Could you perhaps give me some guidance?

[try67]

Copy the code (adjusting the maximum number, if needed), go to Acrobat, press Ctrl+J, paste the code into the window that opens (overwriting all the text that's already there), select all of it with the mouse or keyboard and press Ctrl+Enter to run it.

[fredrikochpetra]

Hi again, tried it out now following your instructions but get the following error: TypeError: this.getField(...) is null. Googled it and it seems this error "means that you are calling a field that does not exist. Check the spelling of your field names in your code against the actual field names (including case sensitive)". I can't find any mispelling or other mistakes, any idea what I'm doing wrong?

[try67]

Yes, it means you have misspelled the name of one of the fields.

Try running it like this and it will let you know field(s) could not be found:

for (var i=1; i<=100; i++) {
	var fname = "totalt_"+i;
	var f = this.getField(fname);
	if (f==null) console.println("ERROR: Can't find \"" + fname + "\".");
	else f.setAction("Calculate", "event.value = Number(this.getField(\"Text Field "+(i+3)+"\").valueAsString) * Number(this.getField(\"antal_"+i+"\").valueAsString);");
}

[fredrikochpetra]

So I did that and found the error (a missing line) so I decided to start over and rename the fields in a proper way. I realize that I have already lost all the time I hoped to gain from using a script but am taking this opportunity to learn for the future. I have now updated the file and attach a copy here should anyone want to take a closer look at my invoice.

Also for the next step, is there an easy way to sum up all the totals (all the fields named totalt should be summed up in the field Summa) without having to click in 105 boxes?

So thankful for all the help 🙂

[try67]

Sure! Use this code as the custom calculation script of your "grand total" field:

var total = 0;
for (var i=1; i<=105; i++) {
	var fname = "totalt_"+i;
	var f = this.getField(fname);
	if (f==null) console.println("ERROR: Can't find \"" + fname + "\".");
	total+=Number(f.valueAsString);
}
event.value = total;

[fredrikochpetra]

Can I still use this code when I have changed the names and order of the list? The names are now pris 1, antal 1 and totalt 1 and in a consequtive order from 1 to 105. How do I upgrade the above code, am feeling very lost?

[try67]

Sure. Just change this line:

var fname = "totalt_"+i;

To:

var fname = "totalt "+i;

[fredrikochpetra]

Thank you but doesn't work, I am probably doing something wrong but can't for the world of me figure out what. I have changed all the places where I have changed names and everything is now consecutive and very orderly, see my file above.

This is the code I'm using:

for (var i=1; i<=105; i++) { this.getField("totalt "+i).setAction("Calculate", "event.value = Number(this.getField(\"pris "+i+"\").valueAsString) * Number(this.getField(\"antal "+i+"\").valueAsString);"); }

[try67]

The problem is that you used a non-standard (ie. Non-US) format for your price fields, and you didn't apply it as a Number format, but just entered the value directly. As a result, the Number constructor can't handle these values and returns NaN (Not a Number).

Also, the way I told you to do it is not ideal, because it doesn't use a function. I think this is the point in time where we should switch to a single function, and then just call it from each field.

[try67]

Before we do that, though, I need to know what thousands separator you were planning to use in your numbers.

[fredrikochpetra]

I plan to use space as thousand separator.

[try67]

OK, then here you go: https://drive.google.com/file/d/17J6LYmPcgtu2ZA0j-5ninCOVhcuiAUqN/view?usp=sharing

I created a separate function to convert your number formatted strings to an actual Number object. You will need to use it when calculating the total, too, which I'm guessing is your next step.

[fredrikochpetra]

Wow, magic, thank you so much! How do I do the last steps?

1. Change the comma separator in the "totalt" field to comma instead of period?
2. Get the sum of all the fields "totalt" as well as "leverans_totalt" in the field "summa 1"?
3. Get the sales tax (+25%) to calculate from the field "summa 1" (think I've got this actually even though there may me a simpler way to do it)?

And that's it, I think even I should be able to sum up the rest 🙂

[try67]

You can use the util.printf command to set the number to have the desired format, and you can use the convertToNumber method I created to sum up all the total fields.

[fredrikochpetra]

Thank you so much for all your kind help but I need to learn when to throw in the towel, I don't understand any of this and don't know if I've learnt anything more than to leave the hard stuff to the pros :-). So sorry to have wasted your time, hopefully someone with more knowledge than me has learnt something from my mistakes.

Again, thank you so much for your time!

[try67]

I think I've provided the basic tools needed to do it, but I understand this is not a simple task, especially if you don't have any experience in this field.

If you're interested I could write the rest of the code for you, for a small fee. You could then use that to learn how it's done. You can contact me privately via [try6767 at gmail.com] to discuss it further.