Part 1 provided a broad outline of the Virtual List Technique. Part II will step through a specific example of how this technique solved a MightyData customer’s request.
Requirements
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.
Table Structure
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).
Layout Modifications
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:
Scripting
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.
Summary
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.
I’m trying to find ways of applying variable formatting to Virtual Lists, so you can have differenf fonts, styles, colours etc on a line by line basis. I’ve managed to get some way with this technique by maintaining a ‘shadow’ global variable which holds a list of formats to be applied. So in a simple example the virtual list $$docNote contains:
Heading
Para 1
Para 2
the shadow list $$docFormat contains:
Bold
Plain
Plain
so that the vitual list produces
Heading
Para1
Para2
The virtual list field calculation is defined as:
TextStyleAdd(GetValue($$DocNote;VirtualListID);GetValue($$DocStyle;VirtualListID)) so that the formatting from the corresponding value item is applied to the line.
I’ve not so far been able to find a way to produce formatted vertical or horizontal lines in a virtual list – any suggestions?
TIA
Brian
OOPS! The shadow list is called $$docStyle, not $$docFormat as above.
Brian
Brian,
That’s a great way to add the formatting. I’ve often handled it with conditional formatting, so that, for example, Row1 is bold as a header, or in a cross-tab report if the value in the field in the first “column” has the word total in it, that entire row record has a gray fill to the fields.
The borders and lines are a little trickier, but here’s how I would go about it.
Add 2 global container fields to your Rows table: gLine and gBorder.
Create the line object and a rectangle object in layout mode, copy each object while in layout mode and paste it in to the respective field. I suspect a bit of trial and error will be required with the rectangle. Be sure to leave the fill as transparent.
Create a global variable similar to your shadow list to indicate whether a line or border is required. I would use 2 different global variables and simple boolean values.
In the Rows table, create 2 fields to display the graphic in the global based on the value in your global variable. The result should be of type container. [ Case ( GetValue ( $$array_line ; RowNumber ) = 1 ; gline ) ]
On the layout, place the DisplayLine and DisplayBorders above your data fields with their fill as transparent. Again, you’ll have to experiment a bit with the size of the rectangle to get it to match up. If you find you need different sizes of rectangles, I suggest using a repeating field, and then you can reference specific repetitions for specific sizes.
Great question!
Well, my html for making those bullets didn’t take. Sorry it’s a bit mashed together.
Hi Lisette
Since my last foray into virtual lists, I think I have found a much more elegant way to manage passing data into the virtual list, which leverages off nameValue pairs so you can pass as much data as you want to the virtual list.
The technique still uses the basic $$varContent list to select the appropriate data for the row, so the first step is to build the list information to go into $$varContent. However I now build the each list line as a dictionary of nameValue pairs, which allows me to pass more or less anything into the list which can be represented as text. So for example a virtual list line might look like this:
|PoNumber:=5000114409|PartNumber:=X123-23001|Quantity:=5|deliveryNo:=118894|
(Note, my actual dictionary is more complex than this but uses lt and gt chars which would get messed up by the Blog. I use Six Fried Rice’s dictionary functions but you probably have your own.)
The unstored calculation field in the virtual list table ( getValue($$varContent;Row), now acquires the complete data dictionary for each virtual list record. For ease of use, I have renamed this field Dictionary.
In the virtual list table, I define another four calculated fields for the data I wish to display in the list – in my example above these fields would be
V_POnumber, V_PartNumber, V_Quantity and V_deliveryNo. Each field is set up to extract the value for the appropriate nameValue pair in the dictiionary.
e.g V_POnumber is defined as an unstored calculation with the value DictGet(VirtualList::Dictionary;”POnumber”) which would yield the result 5000114409 from the example line above. (DictGet is one of SixFriedRice’s custom parameter passing functions which returns the value for a name stored in the dictionary)
This means that you now have fields which you can place on any Virtual List layout that you please, and you can now format them in any way you like. You are no longer dealing with just a line of text, you can mix labels, fields and merge fields on the layout, format them as you please and even use conditional formatting. No more messing around with multiple concurrent global variable lists.
Virtual Lists Rock!
Brian
Hi –
I have a question about using a virtual list in a multi-user context. I have my report set up to use a single virtual list table. But if I have multiple users running reports (on different record sets) at the same time I imagine I would run into problems. I was wondering if setting the fields in the virtual list table to store as globals would solve this problem.
Thanks!
Tom
Thanks for the question.
Actually, one of the beauties of the Virtual List is that is it multi-user friendly. Since those calculations in the table are all referencing global variables, they are unique to each user.
Thanks, Lisette, for explaining to me what I now see was stated plainly at the start of Part 1! Sorry about that!
Hi Lisette,
the global container field approach is really nice. However- how would you get this in a print layout with variable cell width? It has been impossible for me to format horizontal lines for that. Would you have an approach?
I’m not sure if I’m clear on your question as I’m not getting a visual, but I’ll take a stab at it.
If you ensure the line is the width of the page, you can use the size of the display field to determine how much of that line shows in each instance of the field, using the crop settings for the graphic. You can even apply resizing rules with the anchors to have those fields expand, though that may not be of as much help with a print layout.
Well, that did not work on the print layout- or I misimplemented it. I however found a way to do it. I placed two text fields below the content lines, the lower one with a line showing and right above one with conditional formatting that would change upon the line having the status line showing. Because of the conditional formatting, the line would show (because not hidden), otherwise it would be hidden by the top field being white (in my case).
If the line should not appear, I was leaving the text field blank, which made the line and the conditional text field wrap with the other parts of the line segment.
Thanks anyhow. I hope I was able to explain comprehensibly.
Thanks for letting us know how you solved the problem!
Hi all,
As I’m a newbie, I’m unable to get all the above points. So it’ll be better if you give me a demo file on this functionality. This will help me to understand everything.
Thanks
Thanks for stopping by our blog. We do not have a demo file available. If you are interested in coaching to help you work through this, please contact us at info@mightydata.com or call Dave at 972-390-8600 x 103.