By Daniel Wood, 3 November 2011
Most of the time when we print records in FileMaker, the output of every record appears pretty much the same, with similar formatting and design. But what if we want records to print with a completely different output design depending on the type of record? In this article we detail some of the various ways in which this can be achieved, and the pros and cons of each method.
It all depends on the situation. If you are printing a simple list of customers for example, then you would expect the appearance of information on each line of the output to be the same. By that we mean the same fields in the same columns with consistent formatting.
Above is an example of a standard report output. Fields appear in their respective columns, and all look pretty similar, nothing fancy going on here.
Consider now a different printing scenario. What if we were printing a test, where questions could be one of 3 different types? Some questions may be multiple choice, others a short answer, and others may be longer paragraph answers - with each record being a question. In this situation it makes no sense to have every record appear exactly the same, because that would make the test useless to the person looking at it. Ideally what we would want to do is have each type of question output in its own correct format, completely independent of the format of other question types.
Something like the above would be desirable, so how do we go about doing this output? Well, it so happens there are a few ways in which you can do this. We will discuss 5 potential methods, and the pros and cons that each method presents.
It should be pointed out at this stage that the article is only concerned with changing the display of records for the purpose of printing. Actually having different record displays from a data-entry perspective is a different topic (and a more complicated one for that matter!)
Ever since the introduction of conditional formatting in FileMaker Pro 9, people have been searching for ways to manipulate its intended use, and this is no different :) Here we are going to make use of the ability of conditional formatting to alter a fields text size, font color and style.
What we are looking at here is our printing layout. On it is 3 different sections for the 3 different types of questions (colored so you can see them).
Each has been formatted as we wish for the question type to appear. Now it is simply a case of trying to hide the two designs that do not apply to the type of question for any given record.
Here is the conditional formatting calculation for the multiple choice section. In this situation we are telling the multiple choice related labels to change their formatting if the question type is NOT multiple choice.
We are setting the text color to white (same as background) in an attempt to hide any display of these fields. We also set the font point size to 1 in an attempt to make it so small it cannot be viewed. Finally we superscript that sucker in a further attempt to make it even smaller. The other question type fields have similar conditional formatting on them.
Sliding is set on all of these fields so that they slide up where possible. Because we make the multiple choice section so tiny in this case, the other sections will slide up.
This method uses a single calculation field in which all of the output design is done. Whatever you can accomplish in FileMakers calculation engine is at your disposal, but that is all.
Here is our calculation to display each of the three different question outputs. As you can see it is fairly basic and we are pretty limited to what we can do here.
Now we are beginning to get into some of the more "unconventional" methods. In this method we are going to be rendering each question type through a web viewer. The code that is used in the web viewer to render the question will change depending on the question type. We are using a Data/URL to render each question, substituting in information from the record into the html.
To look at the HTML code used, check out the example file. The html is rendered by the web viewer through the data/url syntax:
Method Four: Hiding Portals
This method makes use of a number of different features such as relationships, portal filtering and preview mode niceties.
Bit of a mess isn't it! Well that is how it looks in layout mode when it is all built, but lets deconstruct what is going on here.
That's better. What we have here is 3 separate 1-row portals. Each portal corresponds to a particular question type, and in each portal is the design you wish each question type to have. Originally all the portals were stacked on top of each other but have now been placed side by side.
Every portal is using a simple self-join relationship to relate to itself and display its own record.
Each portal also has a filtering calculation on it to determine whether the contents should be displayed or not. Each portal is setup in a similar way as shown above depending on question type.
The end result is that only one of the three portals will show its output depending on the question type:
When all of the portals are stacked on top of each other, a cool thing happens. The two portals that do not show a record will not only show nothing, but they completely disappear from the output, its as if they aren't on the layout. This leaves the one remaining portal that is showing contents to be the only portal visible on that record. Note that data entry and browse mode is another story... but for printing it works great!
This is perhaps the most extreme and convoluted method, but can deliver good results if the effort is put in.
Here we have created new layouts for each question type. Each layout contains the design we want to use for that type of question. For example, here is the Multi Choice layout:
A container field has been added to the table. The idea is that we populate this container field with an image that looks like the question design we want to output.
To do this, we basically go to one of the three layouts, take a picture of the layout, then go back to the container and paste in the results. We do this for each record until every record has an image of itself, then we output the images, simple huh!
Here is a simple script to populate the containers. As you can see, it loops through each record. For each record it then goes to that record on the corresponding design layout. We must enter preview mode to take the picture. The picture is taken simply by using a copy script step - if done in browse mode we get just the text from the record. We then return to the original layout and paste the image - now on clipboard - into the container.
A couple of things about this method:
In order to get sliding to work properly using the container method, the page setup for each different layout being copied has to be set so that the height of the page is basically the same as the height of your body part on the layout. If this is not the case, then when you enter preview mode the resulting preview is your layout body part plus the rest of the full page. Upon copying this you end up with a large image which is the entire page. So ideally what you need to do is reduce the height of your page so that when you go into preview mode, you are only copying the question component and that only.
Custom page setups can be setup and will remain attached to a Page Setup script step on the mac, which means it can be used across any other mac computer without issue. I believe on a PC it should still run fine with the page setups scripted (at least it appears to in testing).
The container field on your layout should be set to have top vertical alignment and to slide up and reduce height of enclosing part. If done properly you can have some good results with sliding - with the added bonus that you have full control over designing your outputs using FileMaker layout objects - something none of the other methods offer.
If sliding is not a requirement then custom page setups for each layout is not required. You could copy the entire page. On your output layout, set the containers alignment to top, but set it to crop image to frame. This will leave just the top of the image visible (the top part should contain your question design anyway).
Which method is chosen really depends on the complexity of output you require and whether sliding is an issue for you. Each have their benefits but come with baggage. Ideally what we are striving for is a method that gives you full control over design by using FileMakers layout objects in your design, but can nicely slide up on printing. The container method has both these features, but unfortunately it comes with a LOT of baggage! We tend to prefer the calculation method for most things, though it does struggle with multi-columnar formatting so best to avoid that if possible.
While not covered in this article, there is the potential for another method that would result in a PDF document as the output as opposed to a printed form. This would be done in a similar fashion to the container method through record looping, but instead of creating the image and pasting into a container, the record on its own special layout would be saved and appended to a PDF document. The end result would be a PDF of all records in their own format, and this method would support sliding quite nicely.
If you have any other ideas or methods that you use to output different designs for records, we'd love to hear them, feel free to leave a comment outlining your method.
UPDATE: We have had 2 further examples submitted by Stephen Dolenski and Mikhail Edoshin. Please refer to the example files section below for more information and download links.
Please find attached an example file. This file was used for all the screenshots in this article, and is provided to help you fully understand what is going on in this article, and to let you experiment in FileMaker with this solution.
This additional example file was kindly supplied by Stephen Dolenski from Fmforums.com in which he demonstrates another method using Bruce Robertson's virtual list technique.
.. and finally we have another reader submitted example file from Mikhail Edoshin in which he demonstrates a variation of the portal hiding technique. Mikhail points out that the portals need not in fact be stacked on top of each other, yet can be placed on the layout one beneath the other, and they will slide up. The portal contents within do not slide but because the portals themselves will slide, this is a nicer approach as it lets you see all of the different portals without the need to move them out from under one another.
Also in his example file he shows how it can be combined with other methods such as calculation fields or regular field sliding to achieve the desired result.
Here is information supplied by Stephen in regards to how he built his virtual list implementation, very cool stuff!
The first step is in the Questions Table need to create a field
zVirtualList this is a calc that will generate the return, pipe and %% separated text
q = Substitute ( Question ; "ÃÂ¶" ; "%%" ) ;
a = Substitute ( Answer A ; "ÃÂ¶" ; "%%" ) ;
b = Substitute ( Answer B ; "ÃÂ¶" ; "%%" ) ;
c = Substitute ( Answer C ; "ÃÂ¶" ; "%%" ) ;
d = Substitute ( Answer D ; "ÃÂ¶" ; "%%" )
ID &"|" &Type &"|Q|" & q & ÃÂ¶ &
Case ( Type â "Short Answer" ;
ID &"|" &Type &"|A|" & a & ÃÂ¶ &
ID &"|" &Type &"|B|" & b & ÃÂ¶ &
ID &"|" &Type &"|C|" & c & ÃÂ¶ &
ID &"|" &Type &"|D|" & d
The script basically goes to the Questions Table sorts and loops thru each record adding that calc field to a global variable
then it goes to the VirtualList layout where the global variable is the rendered into rows and columns as needed.
the calculation in that table determines the output calculations needed for generating the reports.. including formatting indents etc.
The benefit of this you don't need to add calcs for visual output display needs in the schema of your data tables.
I haven't employed it in the demo but you could even reduce some of the data needed and really only take the ID of the questions in the global variable
and then thru the relationship pull the question and answer text as needed.
The virtual list will auto generate the necessary records as you have need for the question & response via the script, if your schema for your survey were more
normalized ( Question - Responses ) you could simply go to the response table and grab the records building the rows as needed, and printed reports
could be dynamic where you could have different versions of tests or questions and different responses or where the responses are in different orders.
The virtual list technique will allow you to better allow for page breaks.
You could add more complexity and have more question types or options in the question to create a Likert scale
The %% conversion on the original question/response - is to preserve any returns in them in the final output.