Someone at my church asked me if I could create a website for the youth group’s plant sale. Usually, I would say no, mainly because I am lazy and not a web developer, but I agreed to help. I think the phrase I used was, “I could muddle through it.” Mistake number 1.
I didn’t have login credentials for the church’s website, but they expressed interest in setting up a website for just the youth. I’m okay with that. Unfortunately, nobody had access to the youth group’s email address, and I wasn’t going to set it up with my information and be responsible for it until the end of time. So people made some calls, and we waited.
In the meantime, they sent me a couple of PDFs containing scans of copies of plant lists. At this point, I was still in the mindset of building an online order form, so I needed to create a spreadsheet of plants, categories, and prices that I could later import into some magical e-commerce tool. So I sat down at my M4 MacBook Pro and fired up Numbers. Within an hour, I had everything pulled into a spreadsheet. Because I am not a heathen, I used styled fonts, added column headers, and hid columns with metadata. It looked nice for a spreadsheet.
I printed out the spreadsheet to get comments on descriptions and prices. The powers that be decided that an e-commerce site would be too expensive for a youth group plant sale. And, because Numbers does such a nice job, an editable PDF form would be fine, and “if it could do calculations, that would be great.”
“Not a problem,” I said. Mistake number 2.
I changed some prices, fixed a typo, and hit export to PDF.
“Welp, that doesn’t look right…ok Numbers exports PDFs at 100%; the printout was fit to sheet at about 75%. Okay, fine. Print and save it to PDF. Great, that worked…”
Okay, now how do I make the form editable? Preview recognizes some fields but not all the quantity and price fields. It treated some merged cells as fields when they weren’t. Okay, I need a better tool. The AppStore has PDFGear, which seemed well-reviewed, so I tried that.
It worked fine, but I promised the yutes that they would have an editable form WITH calculations, which is possible only with Adobe Acrobat. Fine, I’ll sign up for a free trial and use Acrobat. Mistake number 3.
After agreeing to their TOS, giving them my credit card, signing over my firstborn, and agreeing to pay NEARLY $20 A MONTH, I sat down to the piece of crap that is modern software. It seems like it belongs on macOS, but not really. It’s just lipstick on a pig. I had some other things going on, so I didn’t put too much effort into it then, which was fortunate since the powers that be decided the prices needed to be tweaked again. After everyone agreed that everything was how they wanted it, I sat down to finalize the form.
I started by creating the PDFs of the order form and instruction sheets. I merged them using macOS Preview because it is dead simple to open a PDF, drag in a new page, reorder them, and save it. I opened the merged PDF in Acrobat, hit Edit, and Prepare Form. Acrobat did its thing and identified what it believed were the form’s fields. Hah, not really. It created some but not others; it added fields where there shouldn’t have been. Clearly, I would need to do this by hand.
I added a text field for quantity and one for cost. I created multiple copies, which resulted in the creation of quantityRow.1, quantityRow.2, quantityRow.3, etc. However, because the order form has multiple sheets the Multiple Copies command only creates multiple copies per page. I found out through lots of trial and error, that the quantityRow and costRow fields on page 1 needed to start with quantityRow.1, and costRow.1, fields on page 2 needed to start with quantityRow.2, and costRow.2, and likewise for page 3. When the Multiple Copies command is executed on page one, those fields become quantityRow.1.0, quantityRow.1.2, quantityRow.1.3, etc. On page 2, they would be quantityRow.2.0, quantityRow.2.2, quantityRow.2.3, etc. I did that for all three pages.
I hate using a PDF form, and the fields don’t line up with the fields in the document, so I spent way too much time getting everything laid out to the point. I now have quantity and cost fields for all plants, over 100 in total, perfectly laid out in the boxes on the printed form.
Can anyone tell me what I’ve done wrong? You, in the back of the class, what did Steve do wrong?
That’s right; Steve didn’t set the format of the fields.
The quantity field should have a number format with no decimal points, and the cost field should be formatted for currency.
I set out to correct that; in Acrobat, you need to select every field, open the properties panel, choose the number format, set the number of decimal places, and select the currency symbol. I needed to do that for both the quantityRow and costRow.
Did I mention that there were over 100 line items?
Fine, everything is laid out AND formatted the way I want it. Now, about those calculations.
Acrobat gives you several ways to perform a calculation. You can perform a simple calculation by selecting two fields and an operation; this option wouldn’t work since prices weren’t a field with a value. You can perform calculations using Simplified Notation, which meant that I could have done something like 25.00 * quantity.1.0, but simplified notation doesn’t work with hierarchical fields. That left using Javascript.
And you guessed it, that operation where I had to open the properties panel for each field, I would need to do the same thing for all the calculations.
Now, I’ve seen Javascript, but I’ve never written it! It can’t be that difficult, right? After trial and error, I came up with the following script. To extract the number portion of the current field, and use it to get the matching hierarchial quantityRow.
function calculateCostWith( price ) {
var fieldName = event.target.name;
var parts = fieldName.split(".");
var indexPart = parts.slice(-2).join("."); // Extract ".x.y"
var quantityField = "quantityRow." + indexPart; // Corresponding quantity field
var quantity = this.getField(quantityField).value || 0; // Get quantity
if (quantity == 0) {
event.value = ""
} else {
event.value = quantity * price; // Calculate cost
}
}
Because I know prices will change as soon as I finalize the PDF, I modified it to get the price from another field.
function calculateCostWith( priceField ) {
var fieldName = event.target.name;
var parts = fieldName.split(".");
var indexPart = parts.slice(-2).join("."); // Extract ".x.y"
var quantityField = "quantityRow." + indexPart; // Corresponding quantity field
var quantity = this.getField(quantityField).value || 0; // Get quantity
var price = this.getField( priceField ).value || 0;
if (quantity == 0) {
event.value = ""
} else {
event.value = quantity * price; // Calculate cost
}
}
Now, it was just a matter of updating all 100 or so costRow.X.Y fields with the JavaScript command:
`calculateCostWith( "hangingBasketPriceField" )`
The final line of the form is a calculated total of items and total cost. This was surprisingly easy to do with a simple calculation using the add operator on the hierarchical roots, quantityRow and costRow.
With that, I was done. I sat back and admired my work. I finally had a form someone could fill out, enter quantities of plants they wanted, and the form would calculate the totals. Exactly what I promised the yutes.
I then made a mistake, a BIG MISTAKE, one that I haven’t made in many, many years. I opened the PDF in another App to see how it performed, macOS Preview, to be exact. The form fields were there, but the calculations didn’t work.
“Okay, I guess Preview doesn’t support that feature; maybe it is just Acrobat. That’s fine; most people would use it on Windows anyway.”
I don’t know the exact catastrophic sequence of events, but Preview saved my changes at some point. Even though I only edited the PDF fields, it rewrote the whole PDF, wiping out all the formatting, JavaScript, calculations, and work I had done for the last several hours.
I felt as sick to my stomach as that poor girl back in college who lost her whole research paper when the computer crashed, and she never saved her work.
I tried to recover, but I was too demotivated at that point. I said, “Screw it, the yutes get just a plain PDF.” I sent it off to the Church admin and canceled the Adobe subscription.
What’s the takeaway?
- Don’t go muddling where you shouldn’t be muddling.
- It is okay to say NO, even though it is for a friend.
- One of the yutes could have whipped up a form in Google Docs on their school Chromebook in much less time, but nobody asked them.
- Adobe products are overpriced pieces of crap!
- It used to be that one could buy a copy of Acrobat or Photoshop for a fixed price and use it until you needed new features. But I can’t justify paying $20/month to make and edit PDFs. I’m sure some people could, but I am not one of them.
- AcroForms is part of the open PDF standard, a part that, sadly, too few tools support, including Apple’s Apps.
- If Apple supported it, Numbers or Pages would be ideal for creating AcroForm PDFs. Why?
- Numbers and Pages are Apps that come with every MacBook and iPad.
- They are well thought out and easy to use.
- They work as you expect them to work.
- You don’t need a full-blown spreadsheet with 1,048,576 cells when you only need 100.
- And the reason I use Numbers and Pages is because they are gorgeous and a pleasure to look at. The results they produce are equally gorgeous. They may seem simplistic, but they pack a punch.
Danger! Steve Bytes 