This customer needed a sales order and invoice tracking system, and provided a sample of the current sales order in Excel. I mimicked this layout in FileMaker, with a header showing purchaser details, a body for line items, a subsummary by purchaser for order totals, and a trailing grand summary for a page of legal language. The customer had to submit these sales orders to another department for processing, and they required a specific format. The section with totals needed to appear on every page except the final page with legal language; however, the totals themselves should only be shown on the last page of line items, with the boxes blank or reading “Continued…” on preceding pages. Each page with line items should show 4 line items, even if some were blank.
In a basic FileMaker format, if there were more than 4 line items to the sales order, all line items would be included on the first page, and the subsummary section with the order totals would be pushed to the next page. The requirement for the blank lines made using the line items themselves problematic. Even an order with 3 line items, that would otherwise fit the format, would not have that blank fourth line.
This database started with a foundation of the SeedCode Complete template, so the building blocks for the Virtual List were already in place, in the CalendarRows table of the Interface file.
I added a global text field to the CalendarRows table (gInvoiceID) and created a relationship from CalendarRows to a new RowsInvoices table occurrence based on the InvoiceLineItems table (a sales order become an invoice by a change in the status).
I duplicated the current layout for the Sales Order, changed layout context to the CalendarRows table, and repointed the fields in the header and subsummary to the related Invoice fields. The line items will be fields in the CalendarRows table, which pull values from the Virtual Lists, and is covered in more detail later.
The script begins on the Sales Order layout, and line items are shown in a portal. The Sales Order should reflect the following details about the line items:
In this case, values are required for each of the line item fields, so we can use the List function from the context of the Sales Order layout to build global variables for the Virtual List. If it were possible for a record to have no entry for a field, the list function would not be suitable, without a utility calculation field to ensure a value for each record. If so, I would have looped through the portal rows to build global variables, representing a blank field with a blank line in the global variable value list.
Set the global field (CalendarRows::gInvoiceID) to the primary key for the Sales Order. Then set the following global variables:
$$sc_Qty = List ( InvoiceLines::Qty )
$$sc_Desc = List ( InvoiceLines::ZDisp_LineDescription_ncr )
$$sc_PriceEa = List ( InvoiceLines::PriceEa )
$$sc_LineTotal = List ( InvoiceLines::PriceLineTotalCalc )
You may be wondering about zDisp_LineDescription_ncr. The line description is built of several fields with some text identifiers, and includes carriage returns. Since the Virtual List relies on getting specific values from the value list, we can’t have carriage returns within the data. This calculation field substitutes a text constant for the carriage returns. That process will be reversed in the calculation to extract the values.
Four more fields were added to the CalendarRows table, to extract the values from the global variables above. Three of the four simply pull the value from the global variable based on the number in the RowNumber field. SalesOrderDescription_c handles that carriage return we needed in the description by reversing the earlier substitution. These four fields are the body of the layout. The fields are four lines tall, with a border. The lines shown within each field are overlaid line elements.
Count the number of values in one global variable to determine the number of line items. Divide this by 4 to determine the total number of pages, and set the initial page value to 1.
Set a variable to the filename to be used for this document, and set the path to:
$path = “file:” & Get ( DesktopPath ) & $filename
The script then loops to find rows in groups of 4 until all pages have been printed. Using Save As PDF for the first page, then Append PDF for subsequent pages. In PDF Options, specify page 1 of 1 until the final page, then include all pages. By always finding 4 records, we will have blank rows when there are fewer than 4 rows with data.
The $$lastpage variable is set to 1 for the final page. This allows the script to include all pages, as noted above, and also drives the conditional formatting.
Remember the requirement to only show the totals on the last page of line items? “Continued…” with transparent background overlays each of the total fields. When $$lastpage does not equal 1, the background is changed to white, so that the totals cannot be seen. Some of the totals should only be blank, and for those, the text is also changed to white. When $$lastpage equals 1, the only change is for the font size to be made very large, so large it cannot be shown in the tiny space allotted to the text, allowing the number field beneath to be seen.
The Virtual List Technique is versatile. Developers are using it to create amazing solutions. Think of the global variables as columns, with each individual value in that list as the row data. Looping scripts can gather data from portal rows, or a found set of records, or several found sets of records. The data can be simple or complex. Use the GetSummary function to gather summary data, such as totals, averages or percentages, for a cross-tab report. Understanding the basic concepts of the Virtual List Technique is the key; with that foundation and a little imagination, you can solve any number of reporting and display challenges.