I was recently tasked with the challenge of creating a fully customized purchase order (PO) template. The customer wanted to reduce spacing in order to fit the maximum number of items on one page. They wanted to avoid creating multiple page POs, unless necessary. Users would be able to process POs within Banner and use the distribution features of FormFusion, but could produce output to fit their specific needs.
Diving in head first, I knew I was attempting to push the boundaries of how FormFusion is designed to work. (One page of FormFusion processing and output per one page of Banner output.) Development put my critical thinking skills on overdrive, but in the end, the customer received the professional, compact PO template they wanted.
The Design Concept
The overall concept of the design consists of building the data, not mapping it. Although some of the basic PO data can be obtained from MapForm, the only MapForm variables I ended up referencing were the PO number and page number. All other variables were created and defined in CaptureForm using those two values.
Start with the Basics
The first task in building the PO requires gathering some basic information:
- Lines per Page – the number of lines for one page of output
- Max Items – the max number of items allowed for a purchase order
- Max Pages – the max number of pages that the template will output
The three static values above need to be determined before query development begins.
Lines per Page is based solely on the layout of the PO, while the Max Items value must be established and agreed upon by the Finance Department, to include in data entry training.
There is no limit for the Max Items or Max Pages value, because once the logic is figured out and the code written, it’s simply a matter of copying and pasting code to account for larger numbers. If/when there are more items entered in the system than accounted for in the template, the queries are written to only pull the items up to the Max Items value.
- Total Item Count – the number of items included in the PO
- Total Line Count – the number of lines of item detail for the entire PO
- Line Count per Item – the number of lines of item detail for each PO item
The values above are PO specific and are pulled based on the PO number.
After the basic data is retrieved, the Items per Page values can be calculated. This query is the backbone of the design and determines which items will print on each page. It involves the most complex logic and, truth-be-told, involved the most coffee, chocolate and code-refactoring for me.
Capturing the Data
The next task was to pull the meat of the PO: the item information. The baseline FormFusion template maps the item data in columns (Item No, Quantity, UOM, Description, Unit Price and Total Price). Here, I used the same column formatting, but built the columns in CaptureForm.
The problem child in this task is the item description, as it’s the only data that can extend to multiple lines. The description is stored in three different tables in Banner (requiring union statements and multiple rows returned), so each item description is pulled using individual queries and variables.
Once the description data is retrieved, the columns for the item data can be built using one query per output page. The result should be variables for each column and each page.
Lastly, there are two important parts of this query that need to be coded correctly to ensure proper formatting:
- Only include the number of items determined to fit on each particular page
- Concatenate line feed characters to the single line data (quantity, unit price, etc.) to match the number of lines for each item description
Manipulating the Output
The final task focuses on getting only the pages needed for output, instead of what FormFusion thinks is needed. As stated earlier, FormFusion will process one page of output for each page in the Banner file. How do you trick it into printing only the pages needed? Well, that logic took a while to think through, but the code and implementation ended up being simple.
The solution combines the page number from MapForm and the Items per Page values created in CaptureForm. In a nutshell, the logic is:
“If the item count for the page is not null, then print.”
One overall print variable is used for conditional printing in FormDirector for all Copy/Director combinations and in FormStamp in the form properties.
Although your template will be designed to print multiple pages of output, all with different data, there’s no need to create multiple copies of each FormStamp. All of the data fields can be placed on a single form, overlaying each other, and set to print conditionally depending on which page is being processed.
Similar logic can be used to create print variables for the following data:
- Page numbers – If there is more than one page, page numbers will print on all pages
- PO totals – The totals will print on the last page of output only
- Continued label – ‘Continued’ will print in place of PO totals on all but the last page of output
Now you know how the magic happened and how the impossible can be achieved! There were times during this project I thought the possibility of successfully creating this custom purchase order template was unattainable. Luckily for me and the customer, and now for you, I am one determined and over-caffeinated developer.
(If you’d like a closer look at the coding & technical side of creating a custom PO, please contact me and I’d be happy to share the details.)