(Disclaimer: my title is ABAP Detective but there is no ABAP code in this post. Check the tags below for links to prior ABAP Detective yarns)
It was a hot summer day in the city. The kind where codes find listless bugs and effortlessly stomp them to the core. I sat in my temporary office space, listening to the slight hum of the electric ceiling fan stirring the page dumps, creating leaf blocks that would not chain. I needed to write. If not code, at least docs.
Where I left off was a database hint that I would be ramping up python. Not the sideshow snake charmer kind, but the open source one where you do it yourself. With a particular style that I am slowly coming to grips with. The objective--produce fill-in forms from live database calls. The case that follows is not SAP database-specific but it is being shared in this community space because (1) the techniques involved are database-agnostic, and (2) when I was on the force a couple decades ago I got all hopped up about a collaboration between SAP and Adobe to produce universal (I mean PDF) output forms, only to learn there was a per-page license cost, or a similar restriction that put that bright idea into the bottom drawer, behind the office bottle.
I'll start with more of the "why" and "how", as I explain it to my colleagues at the senior art center. There are three kinds of PDF forms. One you scan and it is only a mug-shot kind of fuzzy image. One you generate from a source document or fancy coding, but cannot be changed by the marks, er, rubes, er, customers. And one where you can check the boxes and fill in letters and numbers then send it along, like a tax form. I am sure there are other variations and missing persons forms that I have omitted here but moving on.
When I started investigating how to produce the fill-in-the-blank form, most of my peers suggested buying the deluxe package from the manufacturer. While that would probably produce the desired results, first, I had no budget, and second, what kind of challenge would that be? I looked at a few packages in Perl and quickly concluded I could use it but it would be a piece of work difficult to maintain. And the database connectivity would be harder to manage than I needed. Then I looked at Python packages I had used previously and figured that tool box just needed to be updated. In other words, I thought I was halfway there because I had some code half-working. I was totally wrong.
A little background, for reference, before I get to the case notes. Adobe PDFs have flourished because the reader is free, the libraries are well distributed, and the software suite has endured for decades. I began to use "fill in" forms for government documents.
And realized there were two flavors. One, where you could make changes, save them to a file on disk, and resume with your data intact sometime later. And one where you could fill in the boxes but could not save or forward the results except by printing on paper. My note-taking detective heart skipped a beat.
Inside one old government form, I found this digital fingerprint:
Generated a decade ago, form 8606, with a cryptic author chain (not crypto, just obscure), and a LiveCycle moniker. High roller stakes, of course, as the center city types own those marbles.
This one is the static kind; reports "Cannot Save Form Information". At least they said "please". Twice even.
Not a great labor saver, and terrible if you made the slightest error, had a power flicker or other interruption, and for a forms producer, worthless in an electronic data interchange world if you needed the result to be scanned back in as a raster image instead of crisp usable digital data. But if you had a parallel source document, whether a spreadsheet, text file or some generated database report, taking that information into the form was merely a tedious copy-paste exercise, definitely superior to hand-writing the data with pencil or pen.
Of the interactive/fill-in-the-blank variety, there were limits to the automation, as would be expected with a digital document not tied to a backend database either live or asynchronous. Spell check was practically nil, number range checks were not particularly robust (er, there were none), and forget any kind of built-in calculation or computation engine. Add those columns yourself, rookie.
Enough user experience, "if it were my code I'd do X" speculation. On to the specifications and requirements, then to the software tool shed. I dug into my detective bag of tricks for inspiration.
Users have a (legacy) paper form generated from a list in a database of partners, to use the SAP business world phrase. In this space they are called "sponsors", but no matter. Identifying fields such as name, address, phone, web site and email are printed, then a bunch of blanks for the data to be collected. Event information such as production and performance for the arty crowd. And free text entry for the usual suspects of conditions like “good only on odd-numbered Tuesdays”, or whatever. My mission was to duplicate this paper form as closely as possible, so the old way and the new way could be used interchangeably (because user change reluctance), and allow for automated data transfer to eliminate retyping. Sounded easy; I thought I had the casework down cold.
The python software library that I chose was from ReportLab. I'll include links below. I hereby disclaim any interest in said form, er, firm, and compliance with their license term is up to you, not me. I believe this code is freely reusable.
An early version found in the back of my file cabinet dates to 2010:
It's 32 bit Windows, and a Python flavor less used today. If you have it, it will probably work; if you are doing a fresh install you'd want 64-bit python 3.7 or at least 3.6. And .exe files are no longer available, by the way.
https://www.reportlab.com/dev/opensource/
https://www.reportlab.com/documentation/faq/
"The open-source toolkit is under a BSD compatible license."
I broke the task into two parts: the database pull and the fill-in fields. Because I had written earlier code to do the former, I started with the latter. No sense delaying the pain, get right to it. To avoid reinventing the wheel, I spent a good bit of time searching case files for working sample code and/or tutorials. In short order, found a double shot in this blog post:
Creating Interactive PDF Forms in ReportLab with Python
That, in combination with the excellent with a few warts manufacturers doc
... was enough to get started building testable forms. I knew I'd need checkboxes (choice), radio buttons and textfield options so tested all of the documented options, which also included choice and listbox "widgets".
The test scripts I created to check out the demos described in the above post:
The listbox and choices, as demonstrated in the post above, went smoothly, after an initial fault
https://www.reportlab.com/reportlabplus/installation/
(the above link has moved; open source code is now hosted on bitbucket)
https://bitbucket.org/rptlab/reportlab
As usual, check all links as the web is constantly evolving!
Then through the usual open source package update after package update to resolve all dependencies. Your mileage may vary; all offers void in the state of utopia.
A quick check on the Python source file size versus the generated PDF. Both pretty small, which is nice when you need to tuck those forms into the evidence envelopes for analysis.
.
Another check - the file command shows, usually, what's inside. Newer versions might not display right on older readers, and you don't want to have end users more mystified than necessary.
More file size checks, comparing the Python and PDF.
Slight deviation from the center form course to look into fonts and colors. These are user experience design elements that while not core to the data collection and transfer process involve the human factor. If the forms cannot be read, understood, or managed, they are worthless. The builtin font selections were certainly adequate for the job at hand. You will need to check them out yourself to be sure they fit your particular need. Note the small rainbow of hues in the test Employment Form.
The other deviation, which I will not dwell on here, is how to read the data from the saved form. It's fine if the user just prints the form, as that's better than the old handwriting analysis phase. But we want digital data. Suffice it to say that reading a PDF digital post mortem is not for the faint of heart. While I’m no medical examiner I can pick a few clues off a crime scene.
The demos include a nice rainbow of color selections. While I like them for variety, best to stick with the customer's preferred color scheme. And test with many users, particularly some with vision or color-blind conditions before finalizing the choices. I was unable to find a way around the pale blue background in some text fields, and think that contrast could be improved.
For style, I experimented with various backgrounds, shapes, sizes, contrasts, etc. In version 1, those values are hard-coded because I took one working stanza and copied it where needed. In version 2, or 3, I'd make these variables defined earlier in the code for quicker mass fixes.
Then I hit a wrinkle. The radio buttons didn't act right. For anyone old enough, or experienced enough with old car radios, you would push one button corresponding to an AM or FM station, any other button already selected would pop out, and the dial pointer would spin over to the new selection. I'm sure there are YouTube videos showing this, if you don't believe me. The station frequency pointer was on a wire or string loop, and would physically be pulled into location. I'll skip the part about setting the station up. But if the process failed, no music. Or the wrong station. Or the same station.
I looked at the code. It seemed okay. The other examples worked, which had me convinced I had made an error, not that the tutorial was wrong.
Went to bed. Came back at the problem the next day. Reread the specs. Tried different calls. Figured it out.
Then, as one should do, I want back to the post where I learned how to use the tools and left a comment. Alas, comment moderation. No feedback for a couple days, so I stopped looking. Perhaps the author had skipped locations and never saw my feedback, or maybe it went into the bitbucket. Or they meant to approve the comment then got distracted. It happens. Good news eventually; when I looked while drafting this I found the fix published, and for good karma, three different thumbs up.
Creating Interactive PDF Forms in ReportLab with Python: Comment
With that particular tool use cleared up, the coding frenzy could commence. Individual form fields were reviewed and placed into widget categories, repeating patterns were noted for possible function/routine calls, and text field sizes measured to allow enough space. Who hasn't used a form where the name, address or other real world content doesn't fit, whether a paper or a digital form. It's an art, I tell you.
Here is a view of the eventual product, version 1.0, as it were.
One basic requirement was to duplicate the original paper form style, which was 2 separate forms per sheet,which would then be manually cut in half prior to distribution. In the digital world, this does not make sense. One could produce a non-standard sheet size by fiddling with the configuration settings, but the prior idea of using "less paper" isn't needed if the form is electronically distributed and processed. If users chose to print the digital form, they'd use a whole sheet anyway.
Radio buttons code snippet:
When generated, the PDF version is one value, when saved with fields filled, the version has a different value:
The file grew a bit (well, more than double) but is not very large.
Three different saved files.
Notice the jump from 32KB range up to 1MB. This happened with forms where text fields allowed Rich Text Format. The plan was to allow bold, underline, etc, format marks that might be in the original text. However, the large file expansion caused some anxiety about the proposed distribution and storage plans. Sometimes, every byte counts.
As mentioned in the beginning, this is not ABAP, this is Python. You'll need to consider where you can deploy this tool, and whether the free version suits your needs versus the commercial one. A few places search showed me this approach may be viable across the code universe:
It was a hot summer day in the city. The kind where codes find listless bugs and effortlessly stomp them to the core. I sat in my temporary office space, listening to the slight hum of the electric ceiling fan stirring the page dumps, creating leaf blocks that would not chain. I needed to write. If not code, at least docs.
Where I left off was a database hint that I would be ramping up python. Not the sideshow snake charmer kind, but the open source one where you do it yourself. With a particular style that I am slowly coming to grips with. The objective--produce fill-in forms from live database calls. The case that follows is not SAP database-specific but it is being shared in this community space because (1) the techniques involved are database-agnostic, and (2) when I was on the force a couple decades ago I got all hopped up about a collaboration between SAP and Adobe to produce universal (I mean PDF) output forms, only to learn there was a per-page license cost, or a similar restriction that put that bright idea into the bottom drawer, behind the office bottle.
I'll start with more of the "why" and "how", as I explain it to my colleagues at the senior art center. There are three kinds of PDF forms. One you scan and it is only a mug-shot kind of fuzzy image. One you generate from a source document or fancy coding, but cannot be changed by the marks, er, rubes, er, customers. And one where you can check the boxes and fill in letters and numbers then send it along, like a tax form. I am sure there are other variations and missing persons forms that I have omitted here but moving on.
When I started investigating how to produce the fill-in-the-blank form, most of my peers suggested buying the deluxe package from the manufacturer. While that would probably produce the desired results, first, I had no budget, and second, what kind of challenge would that be? I looked at a few packages in Perl and quickly concluded I could use it but it would be a piece of work difficult to maintain. And the database connectivity would be harder to manage than I needed. Then I looked at Python packages I had used previously and figured that tool box just needed to be updated. In other words, I thought I was halfway there because I had some code half-working. I was totally wrong.
A little background, for reference, before I get to the case notes. Adobe PDFs have flourished because the reader is free, the libraries are well distributed, and the software suite has endured for decades. I began to use "fill in" forms for government documents.
And realized there were two flavors. One, where you could make changes, save them to a file on disk, and resume with your data intact sometime later. And one where you could fill in the boxes but could not save or forward the results except by printing on paper. My note-taking detective heart skipped a beat.
Inside one old government form, I found this digital fingerprint:
2009
8606
SE:W:CAR:MP
Adobe LiveCycle Designer ES 8.2Generated a decade ago, form 8606, with a cryptic author chain (not crypto, just obscure), and a LiveCycle moniker. High roller stakes, of course, as the center city types own those marbles.
This one is the static kind; reports "Cannot Save Form Information". At least they said "please". Twice even.
Not a great labor saver, and terrible if you made the slightest error, had a power flicker or other interruption, and for a forms producer, worthless in an electronic data interchange world if you needed the result to be scanned back in as a raster image instead of crisp usable digital data. But if you had a parallel source document, whether a spreadsheet, text file or some generated database report, taking that information into the form was merely a tedious copy-paste exercise, definitely superior to hand-writing the data with pencil or pen.
Of the interactive/fill-in-the-blank variety, there were limits to the automation, as would be expected with a digital document not tied to a backend database either live or asynchronous. Spell check was practically nil, number range checks were not particularly robust (er, there were none), and forget any kind of built-in calculation or computation engine. Add those columns yourself, rookie.
Enough user experience, "if it were my code I'd do X" speculation. On to the specifications and requirements, then to the software tool shed. I dug into my detective bag of tricks for inspiration.
Users have a (legacy) paper form generated from a list in a database of partners, to use the SAP business world phrase. In this space they are called "sponsors", but no matter. Identifying fields such as name, address, phone, web site and email are printed, then a bunch of blanks for the data to be collected. Event information such as production and performance for the arty crowd. And free text entry for the usual suspects of conditions like “good only on odd-numbered Tuesdays”, or whatever. My mission was to duplicate this paper form as closely as possible, so the old way and the new way could be used interchangeably (because user change reluctance), and allow for automated data transfer to eliminate retyping. Sounded easy; I thought I had the casework down cold.
The python software library that I chose was from ReportLab. I'll include links below. I hereby disclaim any interest in said form, er, firm, and compliance with their license term is up to you, not me. I believe this code is freely reusable.
An early version found in the back of my file cabinet dates to 2010:
-rwxrwxr-x+ User None 2263351 Oct 1 2010 reportlab-2.5.win32-py2.7.exeIt's 32 bit Windows, and a Python flavor less used today. If you have it, it will probably work; if you are doing a fresh install you'd want 64-bit python 3.7 or at least 3.6. And .exe files are no longer available, by the way.
https://www.reportlab.com/dev/opensource/
https://www.reportlab.com/documentation/faq/
"The open-source toolkit is under a BSD compatible license."
- free version - ReportLab
- commercial - ReportLabPlus
I broke the task into two parts: the database pull and the fill-in fields. Because I had written earlier code to do the former, I started with the latter. No sense delaying the pain, get right to it. To avoid reinventing the wheel, I spent a good bit of time searching case files for working sample code and/or tutorials. In short order, found a double shot in this blog post:
Creating Interactive PDF Forms in ReportLab with Python
That, in combination with the excellent with a few warts manufacturers doc
- http://www.reportlab.com/docs/reportlab-reference.pdf [59 pages]
- https://www.reportlab.com/docs/reportlab-userguide.pdf [133 pages]
... was enough to get started building testable forms. I knew I'd need checkboxes (choice), radio buttons and textfield options so tested all of the documented options, which also included choice and listbox "widgets".
The test scripts I created to check out the demos described in the above post:
-rw-rw-r--+ 1 User None 1197 Nov 23 2018 simple_listboxes.py
-rw-r--r--+ 1 User None 1185 Nov 23 2018 simple_choices.py
-rw-r--r--+ 1 User None 1231 Nov 23 2018 simple_choices_helvetica.py
-rw-r--r--+ 1 User None 2731 Nov 23 2018 colortest.py
-rw-r--r--+ 1 User None 2493 Nov 24 2018 simple_radios.py
-rw-r--r--+ 1 User None 1515 Nov 24 2018 simple_radios_v2.py
-rw-r--r--+ 1 User None 1260 Nov 24 2018 simple_radios_v3.py
-rwxrwxr-x+ 1 User None 1818 Nov 27 2018 simple_radios_v4.pyThe listbox and choices, as demonstrated in the post above, went smoothly, after an initial fault
$ python2.7.exe simple_listboxes.py
Traceback (most recent call last):
File "simple_listboxes.py", line 3, in <module>
from reportlab.pdfgen import canvas
ImportError: No module named reportlab.pdfgenhttps://www.reportlab.com/reportlabplus/installation/
(the above link has moved; open source code is now hosted on bitbucket)
https://bitbucket.org/rptlab/reportlab
As usual, check all links as the web is constantly evolving!
Congratulations. You have registered for an account. You should shortly receive an email to activate your account.
Downloading rlextra-3.1.9.tar.gz (8.4MB): 7.9MB downloaded
You are using pip version 9.0.3, however version 18.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.Then through the usual open source package update after package update to resolve all dependencies. Your mileage may vary; all offers void in the state of utopia.
$ /usr/pkg/bin/pip2.7 install rlextra -i https://www.reportlab.com/pypi
Collecting rlextra
Downloading https://www.reportlab.com/pypi/packages/rlextra-3.5.11.tar.gz (9.2MB)
100% |################################| 9.2MB 1.5MB/sA quick check on the Python source file size versus the generated PDF. Both pretty small, which is nice when you need to tuck those forms into the evidence envelopes for analysis.
-rw-r--r-- 1 jim users 1189 Nov 23 21:44 simple_listboxes.py
-rw-r--r-- 1 jim users 5060 Nov 23 22:12 simple_listboxes.pdf.
Another check - the file command shows, usually, what's inside. Newer versions might not display right on older readers, and you don't want to have end users more mystified than necessary.
$ file simple_listboxes.pdf
simple_listboxes.pdf: PDF document, version 1.4More file size checks, comparing the Python and PDF.
-rw-r--r-- 1 jim users 1189 Nov 23 2018 simple_listboxes.py
-rw-r--r-- 1 jim users 5060 Nov 23 2018 simple_listboxes.pdf
-rw-r--r-- 1 jim users 1185 Nov 23 2018 simple_choices.py
-rw-r--r-- 1 jim users 1750 Nov 23 2018 simple_form.py
-rw-r--r-- 1 jim users 8423 Nov 23 2018 simple_form.pdf
-rw-r--r-- 1 jim users 1515 Nov 23 2018 simple_checkboxes.py
-rw-r--r-- 1 jim users 14420 Nov 23 2018 simple_checkboxes.pdf
-rw-r--r-- 1 jim users 10534 Nov 23 2018 simple_radios_v2.pdf
-rw-r--r-- 1 jim users 1260 Nov 24 2018 simple_radios_v3.py
-rw-r--r-- 1 jim users 1515 Nov 24 2018 simple_radios_v2.py
-rw-r--r-- 1 jim users 2493 Nov 24 2018 simple_radios.py
-rw-r--r-- 1 jim users 14811 Nov 24 2018 simple_radios.pdf
-rw-r--r-- 1 jim users 4993 Nov 24 2018 simple_choices.pdf
Slight deviation from the center form course to look into fonts and colors. These are user experience design elements that while not core to the data collection and transfer process involve the human factor. If the forms cannot be read, understood, or managed, they are worthless. The builtin font selections were certainly adequate for the job at hand. You will need to check them out yourself to be sure they fit your particular need. Note the small rainbow of hues in the test Employment Form.
The other deviation, which I will not dwell on here, is how to read the data from the saved form. It's fine if the user just prints the form, as that's better than the old handwriting analysis phase. But we want digital data. Suffice it to say that reading a PDF digital post mortem is not for the faint of heart. While I’m no medical examiner I can pick a few clues off a crime scene.
The demos include a nice rainbow of color selections. While I like them for variety, best to stick with the customer's preferred color scheme. And test with many users, particularly some with vision or color-blind conditions before finalizing the choices. I was unable to find a way around the pale blue background in some text fields, and think that contrast could be improved.
For style, I experimented with various backgrounds, shapes, sizes, contrasts, etc. In version 1, those values are hard-coded because I took one working stanza and copied it where needed. In version 2, or 3, I'd make these variables defined earlier in the code for quicker mass fixes.
Then I hit a wrinkle. The radio buttons didn't act right. For anyone old enough, or experienced enough with old car radios, you would push one button corresponding to an AM or FM station, any other button already selected would pop out, and the dial pointer would spin over to the new selection. I'm sure there are YouTube videos showing this, if you don't believe me. The station frequency pointer was on a wire or string loop, and would physically be pulled into location. I'll skip the part about setting the station up. But if the process failed, no music. Or the wrong station. Or the same station.
I looked at the code. It seemed okay. The other examples worked, which had me convinced I had made an error, not that the tutorial was wrong.
Went to bed. Came back at the problem the next day. Reread the specs. Tried different calls. Figured it out.
Then, as one should do, I want back to the post where I learned how to use the tools and left a comment. Alas, comment moderation. No feedback for a couple days, so I stopped looking. Perhaps the author had skipped locations and never saw my feedback, or maybe it went into the bitbucket. Or they meant to approve the comment then got distracted. It happens. Good news eventually; when I looked while drafting this I found the fix published, and for good karma, three different thumbs up.
Creating Interactive PDF Forms in ReportLab with Python: Comment
With that particular tool use cleared up, the coding frenzy could commence. Individual form fields were reviewed and placed into widget categories, repeating patterns were noted for possible function/routine calls, and text field sizes measured to allow enough space. Who hasn't used a form where the name, address or other real world content doesn't fit, whether a paper or a digital form. It's an art, I tell you.
Here is a view of the eventual product, version 1.0, as it were.
One basic requirement was to duplicate the original paper form style, which was 2 separate forms per sheet,which would then be manually cut in half prior to distribution. In the digital world, this does not make sense. One could produce a non-standard sheet size by fiddling with the configuration settings, but the prior idea of using "less paper" isn't needed if the form is electronically distributed and processed. If users chose to print the digital form, they'd use a whole sheet anyway.
Radio buttons code snippet:
form.radio(name='Category',
tooltip='Field radio1',
value='free',
selected=False,
x=columnA+spacer3,
y=row7,
buttonStyle='check',
borderStyle='solid',
shape='square',
borderWidth=2,
textColor=blue, )
form.radio(name='Category',
tooltip='Field radio2',
value='voucher',
selected=False,
x=columnG+spacer3,
y=row7,
buttonStyle='check',
borderStyle='solid',
shape='square',
borderWidth=2,
textColor=blue, )
form.radio(name='Category',
tooltip='Field radio3',
value='ticket',
selected=False,
x=columnH+spacer3,
y=row7,
buttonStyle='check',
borderStyle='solid',
shape='square',
borderWidth=2,
textColor=blue, )
When generated, the PDF version is one value, when saved with fields filled, the version has a different value:
$ ls -l simple_listboxes*
-rw-r--r--+ 1 User None 5060 Nov 23 2018 simple_listboxes.pdf
-rw-rw-r--+ 1 User None 1197 Nov 23 2018 simple_listboxes.py
-rwxrwxr-x+ 1 User None 12960 Nov 23 2018 simple_listboxes_edited.pdf
$ file simple_listboxes*
simple_listboxes.pdf: PDF document, version 1.4
simple_listboxes.py: Python script, ASCII text executable
simple_listboxes_edited.pdf: PDF document, version 1.6The file grew a bit (well, more than double) but is not very large.
Three different saved files.
-rwxrwxr-x+ 1 User None 33394 Jan 1 2019 sponsor_event_Q1_0.pdf
-rwxrwxr-x+ 1 User None 28362 Jan 1 2019 sponsor_event_Q1.pdf
-rwxrwxr-x+ 1 User None 1022379 Jan 2 2019 sponsor_event_Q1_b.pdf
Notice the jump from 32KB range up to 1MB. This happened with forms where text fields allowed Rich Text Format. The plan was to allow bold, underline, etc, format marks that might be in the original text. However, the large file expansion caused some anxiety about the proposed distribution and storage plans. Sometimes, every byte counts.
What else?
- bugs and anomalies (color perception, widget uniformity) form layout; the whole user friendliness needs to be planned and verified.
- user testing: with actual users, not just bots or automats
- database pulls to be discussed in part 2.
- data form distribution and retrieval--you've created a form. Now how do people fill it out, and how do you pull it back in, on a timely basis?
- unexpected user operation (uouo/yoyo). In one test, instead of filling in the predefined text box, a colleague somehow pasted a new text box, thwarting the planned extraction.
- code refactoring: the above snippet has some roughed in variables, used to put content where it was in the original paper form. In version 2, there are new fields (improvements), larger fields (fixes), and hopefully a better workflow (first fill in field 1, then 2, etc. then jump to 7). Beneath the covers is just code, and macros, routines, and variables are at play.
SAP?
As mentioned in the beginning, this is not ABAP, this is Python. You'll need to consider where you can deploy this tool, and whether the free version suits your needs versus the commercial one. A few places search showed me this approach may be viable across the code universe:
- SAP MaxDB Python Library 2.7 for Linux
- Modeling Guide for SAP Data Hub ... Using Python Libraries
- Subengines>Working with Python2.7 and Python3.6 Subengines to Create Operators>Normal Usage>Using Python Libraries
- KBA - 2536123 - How to add python libraries to Data Services
- SAP HANA 2.0 SPS02 new feature: updated python driver
- SAP Managed Tags:
5 Comments
