Virtual list Archives

Virtual List in 3 Easy Steps

April 12, 2013 by Anders Monsen 19 Comments

The Virtual List technique has been around for a few years (created by Bruce Robertson ca. 2009). Over time and with changes to FileMaker, implementation of this technique has evolved. Knowing how to set up and use virtual lists will help with creating certain reports, especially if you draw the values from ExecuteSQL statements in FileMaker Pro 12.

Articles (see Lisette Wilson’s Taming the Virtual List, Part I and Part II) and examples are fairly easy to find. So why write another one? Many examples and articles include use-specific cases to illustrate the virtual list. I’m going to attempt to boil this down to three simple steps. Once you draft the basics of this process into a FileMaker file, you can use this as a template for more complex reports. You can re-use the draft as a template, copying the table and script from one solution to another. This technique requires one dedicated reporting table, plus a script to populate some global variables. The fields in the reporting table are set up as calculation fields that get their values from the list within the global variable.

To illustrate the process, I added a table to the Contacts Start Solution as a sample database, which you can download here.

Step 1: The Reporting Table

When you visualize the report itself, it usually consists of rows and columns. Each row is a record in the table, and each column corresponds to a field. At minimum, you need two fields. One field is a hidden id field; it’s a crucial component of the report, but is not displayed. The second field appears on the report, and will contain data generated by the script. This simple two column example below could have been created as a SubSummary report. However, the power of the virtual list is that it allows you to combine data from many tables, something not easily within reach by a SubSummary report. In my demo file I have three fields, one for the id and two for the actual column data (state and count).

Step 2: The Fields

Of the two fields mentioned in the table, the id field governs what shows up in the data field or column. This field is set up as a number, starting at one, with each subsequent record incrementing by one. Most reports will have less than 100 rows, so a general rule of thumb is to just create 100 records (however, if your report demands more rows, just create more records). If you create a table view with the report layout, the id field should show 100 rows, with values from 1 through 100.

The second field is the first column of the report. Reports can have multiple columns, so new fields are created as needed, using the same principle. To keep it generic, in my report template I simply named the fields “column1” and “column2” and set them up as text fields. If you need to format data as numeric values, i.e. $43,000.00, this can be done with text formatting functions. The column field is set up as an unstored calculation: GetValue ( $$COLUMN1 ; id ). This will make more sense after Step 3, the script that creates the $$COLUMN1 global variable. Before the $$COLUMN1 variable exists, the “column1” field has no value. Once the variable is created, the field calculates from the content of $$COLUMN1, which is a list, and extracts the value that corresponds to the id number for that row. This is the magical part of the virtual list technique.

Assume that $$COLUMN1 contains these values:

Banana
Apple
Orange
Pear
Grape

This list has five values. For the first five records, as indicated by the values 1 through 5 in the id field, the GetValue() function will pull the corresponding fruit into the “column1” field.

Step 3: Filling In The Rows

This is the most flexible part of the process. It can range from complex loops within loops to a simple progression of steps to fill each row and column. First, to test if the reporting table works, you can create a simple looping script:

Set Variable[ $Limit ; Value: 100 ]
Loop
Exit Loop If [ Let( $i = $i + 1 ; $i < $Limit ) ]
Set Variable[ $$COLUMN1 ; Value: If( isempty($$COLUMN1) ; $i ; $$COLUMN1 & ¶ & $i ) ]
End Loop
Refresh Window [Flush cached join results]

This loop will fill in the first column values with numbers, by virtue of the GetValue function.

To fill in your report with real values, change the “Set Variable” line to something real. In the ContactsVL database, I wanted a count of people living in each state. I created a variable with all the states, and looped through this list. I then ran an SQL query inside the loop for each state.

#Column1
Set Variable [$$COLUMN1 ; Value: If( IsEmpty( $$COLUMN1 ) ; $State ; $$COLUMN1 & ¶ & $State ) ]
#Column2
Set Variable [ $Result ; Value: ExecuteSQL( “select count(*) from contacts where \”Work State\” = ‘” & $State & “‘” ); “” ; “” ) ]
Set Variable [ $$COLUMN2 ; Value: If( IsEmpty( $$COLUMN1 ) ; $Result ; $$COLUMN1 & ¶ & $Result ) ]
#End

In another example, rather than a list you can loop through, you have an individual requirement for the report, with text for report sub-heads. Here it’s just a matter of stepping through each row and setting the variable $$COLUMNx with the result. By adding values to a growing list, you then can parse this list into the rows of the reporting table. And for the subheads you can use text formatting functions to add bold and increase the font size to make these stand out.

Conclusion

Once you have set up a reporting table as in steps one and two, this technique can be used for many different kinds of reports. With a simple starter reporting table and a script or two ready in a template, you can quickly expand this concept to specific solutions.

Evaluating Script and Layout Performance

December 26, 2012 by Darren Burgess 6 Comments

I recently implemented a customer reporting project using the Virtual List technique. In this particular implementation, the customer needed to create a cross-tab style project schedule report with 28 columns and about 30 rows. Each “cell” in the report is an unstored calculation field that would evaluate a repeating global variable to derive the correct value to display. Creating the report is a combination of scripting to load values into the global variable and reevaluation of the 800+ unstored calculations in the report.

Concern for the performance of the report drove me to explore ways to evaluate performance. In particular, I wanted to be able to evaluate script performance vs calculation performance so that I would know where I would get best performance improvements for my efforts.

Evaluating Script Performance

Evaluating script performance was a simple matter of setting the start and end times to variables and calculating the difference at the end of the script. At the beginning of the script I use the undocumented function Get ( UTCmSecs ) to get milli-second timestamp precision:

Set Variable ( $startTime ) = Get ( UTCmSecs )

At the end of the script I simply calculate the difference between start and end times and divide by 1000 to get a script run-time duration:

Set Variable ( $$TotalScriptTime = ( get ( UTCmSecs ) - $startTime ) / 1000 & " Seconds"

Note: Make sure the data viewer is not visible when evaluating script performance. The display of fields and variables in the data viewer will slow script execution down.

Evaluating Layout Performance

Of course, script run time is only part of the story. I also wanted to know how long it took for the report to reevaluate all of the 800+ unstored calculations. This would provide excellent information into what report elements (scripts vs calcs) had the biggest effect on performance. I was also curious as to how different calculation methods affected performance (like ExecuteSQL vs FileMaker functions based on relationships).

To accomplish this I added three global variables to the layout:

The start and end time global variable objects have conditional formatting applied. Since conditional formatting evaluates whenever a layout is refreshed, I can use a Let () function in the conditional formatting calculation to create and calculate global variables. I can further take advantage of the stacking order of objects in the layout and cause the start time variable to be calculated first by applying “Arrange/Send to Back” to the start time variable in layout mode. Here is the calculation used in the conditional formatting for the start time:

Let ( $$reportStartTime = Get ( UTCmSecs ) ; "" )

The end time and total time are created with conditional formatting as well. In this case, the end time variable’s position is set using “Arrange/Bring to Front.” Lastly, the position of the total time variable is also moved to the front. Here is the calculation applied in the conditional formatting of the end time:

Let ( [
  $$reportEndTime = Get ( UTCmSecs ) ; 
  $$reportTotalTime = ($$reportEndTime - $$reportStartTime) / 1000 & " Seconds" ] ;
"")

I tuck these globals away into the undisplayed right-hand margin of the layout and slide them out whenever I have the need to evaluate the performance of the report layout. These global variable objects are easily copied to other layouts or solutions where performance evaluation is needed.

By the way, it takes about 3.3 seconds for the report scripting to execute. The report unstored calculations take about 0.015 seconds to reevaluate. Of course, it seems clear that the scripting of this report is the best target to consider when looking at how to improve its performance.

Further Explorations in SQL for FileMaker

October 21, 2011 by Anders Monsen 1 Comment

Once you start down the path exploring new features or technologies, often the deeper you dig the more cool stuff you expose. In my first post on SQL and FileMaker I focused simply on pulling and comparing data between tables. There are several advantages to employing SQL in FileMaker: speed and context perhaps the primary ones. While SQL the graph remains clean, as the queries remain inside the script, no additional Table Occurences are required.

One area in FileMaker, where we tend to rely on jumping through hoops in the graph, deals with report creation. This is especially true when pulling together reports from multiple sources. Solutions require extra TOs to build queries through relationships, and significant scripting to hop between contexts and gather data.

A recent game changer is Bruce Robertson’s Virtual List (see Lisette Wilson’s recent article and her follow-up for an overview), which gathers data into a utility table for the report output. Combining SQL and the Virtual List seems like a perfect marriage. Martha Zink, Advocate of Awesomeness at MightyData, recently showed me an example of SQL combined with the Virtual List. This post borrows heavily from concepts in her solution.

SELECT From ( SELECT )

Again, for the SQL queries I used a plugin. Several plugins expose FileMaker’s internal SQL. I selected MMQuery from CNS Plug-ins. One nice feature of its Execute SQL function is the ability to set your own delimiters between fields returned in the result. The default delimiter is the comma, but when returning number you may end up with extra commas and I set my delimiter to the pipe symbol. Other plugins may not have this feature, and require you to substitute the comma for the pipe symbol before sending the data through the Virtual List process.

(I recently discovered another plugin, from the makers of Base Elements, that is not only free but lets you execute Shell and SQL, plus a host of other features.)

Although I’m not going to show long query examples in detail, I want to highlight one SQL idea used to build the queries that get sent to the Virtual List “engine”, and how to parse the output from SQL in order for the Virtual List engine to parse the data. The beauty of the SQL in Martha’s solution turns on an elegant and powerful technique in SQL: subselects.

In essence, this is a query nested within another query. The advantage lies in removing joins, which makes the query more readable. Subselects also allow you to build result sets against which you then can run other queries. For example, imagine if you want to pull a list of fields using an ID value or set of IDs. You first want to get that ID set, and then you can select information based on this list.

select <> from Table as T where T.ID in
( select O.ID from OtherTable as O where age < 45 )

To illustrate this process I added some formatting. The key lies in bracketing the select that creates the initial result, and then selecting from this above the result. This “inner” query inside the parentheses is processed first. With “in” the outer query looks at a list of records.

The inner select in the above example is a very simple statement. As with any other select in SQL you can change this to look across multiple tables, using multiple criteria, AND/OR, and other options. Using subselect is almost like creating virtual tables that exist solely within a query. Inside the <> would be all the fields required in the final result.

Dynamic Criteria by the User

In the solution that called for SQL, each report called for user-selected criteria, and searching across multiple tables. Relying solely on the graph or scripting to build the data would have required complex TOs and scripts jumping from layout to layout to build data, possibly even hard-coding criteria. By making the reporting dynamic, users could select from drop downs for different criteria, such as “single” and/or “double” graft. The query would build a pair of select statements, with the sub-select handling building a second list.

SELECT
DISTINCT P.id
FROM (( Patient as P
JOIN Admission as A ON P.id = A.idPatient )
JOIN Intervention as I ON A.id = I.idAdmission )
JOIN allvalue as AV on I.id = AV.idIntervention
WHERE lower( AV.Type ) = 'proximal'
AND lower( AV.Field1 ) = 'aortic arch'
AND lower( AV.Field2 ) = 'graft replacement'
AND lower( AV.Field4 ) LIKE '%single y-graft%'
AND I.Proximal_Date BETWEEN DATE '2002-01-01' AND DATE '2011-08-12'

OR P.id IN (
	SELECT DISTINCT P.id
	FROM (( Patient as P
	JOIN Admission as A on P.id = A.idPatient )
	JOIN Intervention as I ON A.id = I.idAdmission )
	JOIN allvalue as AV on I.id = AV.idIntervention
	WHERE lower( AV.Type ) = 'proximal'
	AND lower( AV.Field1 ) = 'aortic arch'
	AND lower( AV.Field2 ) = 'graft replacement'
	AND lower( AV.Field4 ) LIKE '%double y-graft%'
	AND I.Proximal_Date BETWEEN DATE '2002-01-01' AND DATE '2011-08-12'
	)

While both queries end up fairly complex, imagine the complexity if she had to rely on joins to perform both queries. All of this takes place inside FileMaker, using (in her case) the SyncDek plugin for the SQL. Combine the power of SQL with the Rapid Application Development (cool interfaces) of FileMaker, and you get true development synergy.

Taming the Virtual List – Part 2

October 17, 2011 by Lisette Wilson 14 Comments

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.

Taming the Virtual List – Part 1

October 7, 2011 by Lisette Wilson 6 Comments

Have you ever wanted to create a report in FileMaker, but found it just doesn’t work that way? Perhaps you need to combine disparate data from several tables. Or export data where several records are combined into a single row. Or show totals both “across” and “down”, with a pivot table. Or format an invoice so that a set number of rows appear on each page, with a header and summary totals on every page. With the Virtual List Technique, you can achieve all of these and much more.

What is the Virtual List Technique?

It’s a powerful tool cooked up by Bruce Robertson of Concise Design. Put simply, you use a looping script to build a value list in a global variable, and each value or row becomes a field value in a record in a utility table.

It’s somewhat similar to using a dedicated reporting table, where you create records and set fields, but in this case, the values are short-lived. The Virtual List Technique has several advantages.

No need to create and destroy records, or track records for this particular report.
Multi-user friendly. Since data is stored in global variables, it’s unique to each user. A dedicated reporting table would require overhead to ensure each user viewed only the records for that report.
Speed. Global variables live in RAM on the local machine, not on the server hard drive across the network.

It’s not just for reporting. SeedCode Complete utilizes this technique in the calendar displays and in “selectors” where the user is presented with a pop-up window to select a record in another table. fmSearchResults uses it to produce search results across multiple tables.

So, what do you need?

At a minimum:

A script (possibly a loop, but the List function is your friend) that builds a return separated value list in a global variable [$$array].
A utility table with as many records as rows in your reports. (100 records have sufficed for my needs thus far, but use as many as you need.)
- A number field filled sequentially (1 to 100) [Row]
- An unstored calculation field to extract values from the global variable
  [ GetValue ( $$array ; Row ) ]
- A layout with context of the utility table showing the unstored calculation field, or a portal pointing toward that table.

This versatile technique can be expanded by using multiple global variables and unstored calculation fields, repeating global variables, conditional formatting, and even images stored as a reference. Add this foundational skill to your FileMaker toolkit, and you may never answer a reporting request with, “FileMaker doesn’t work that way,” again. In Part 2, I’ll walk through a specific example of how this technique solved a MightyData customer request for a sales order formatted with a set number of rows on each page, and header and summary totals on every page.