Author Archives: beverlyvoth

Excel Exports using XML and XSLT

Editor’s note: Once again it is an honor and a privilege to present an in-depth guest article written by Beverly Voth.

This is a follow-up to the User-Friendly Excel Exports series by Kevin Frank, but using an XML export along with various XSLT to give you .csv or .xls (for Excel). The first demo is a quick export (to CSV – comma-separated values) using an XSLT to help explain the relationship with FileMaker Pro XML export. How an XSLT style sheet reads that data to transform it into another text format (.csv or .xls) is also explained throughout the article and in comments within the XSLT. The demos will get increasingly more complex, but ultimately more “user-friendly”.

Demo file: Excel-Exports-using-XML-and-XSLT.zip

Continue reading

Export Field Contents (Update for FM 16)

Editor’s note: I am thrilled to feature a guest article by Beverly Voth.

In the article “An In-Depth Look at “Export Field Contents” (here), the study of how FileMaker exports text as single field contents is compared to the standard FileMaker Exports. Some of these present a problem when the field (exported) gets converted to something which a receiving system may reject as invalid. Several methods and alternative “fixes” are presented, including using XML & XSLT.

Update! NEW IN FMP 16 – we have a FIX!

There is a new function in FileMaker Pro (and Advanced) 16 that “fixes” a couple problems with Export Field Contents (namely the encoding and end-of-line). Instead of the Base64 nested function trick use the new Function:

TextEncode( text ; encoding ; lineEndings )

Reference: FM16 Help: TextEncode

Using TextEncode() & Export Field Contents

Set Field [ container ; TextEncode ( text ; encoding ; lineEndings ) ]

Continue reading

FileMaker UI Tricks: Dual-Purpose Layout

Editor’s note: Today it’s my honor and privilege to present an article by guest author Beverly Voth, whose contributions to the FileMaker community are numerous and much appreciated. It’s great to have her back here on FileMaker Hacks.

FileMaker 13 & 14 have some features that can be leveraged to allow one layout to toggle between Form View & Table View and look differently! We will be using the Starter Solution, “Contacts”. Feel free to download the demo file (Contacts14.fmp12.zip) or you may start with a fresh copy of the database file or another Starter Solution and follow the steps below.

NEW to Starter Solutions? Select “New From Starter Solution” under the File menu. Click the “Contacts” icon and click on the “Choose” button. A dialog will come up with the “Save As” field filled in with the name of the database file you chose. Navigate to where you wish to save the file and click the “Save” button. This will open the file to the default layout and be ready to change.

The default layout for Contacts is “Contact Details”, is in Form View and shows two “tab-like” buttons called: Contact List & Contact Details. These are buttons that Switch you to different layouts. Notice how the placement is the same and there is a smooth transition between the two layouts. You may also notice that the view is “locked” to just allow View as List or View as Form (respectively). You cannot select other views on these two layouts.

CONTACTS_details_tab

CONTACTS_list_tab

Continue reading

An In-Depth Look at “Export Field Contents”

Have you ever wanted to export a single field and maintain all the characters in that field? This article explores the possibility with XML Export and the use of a simple XSLT. But first we’ll explain the good, the bad and the ugly of some different standard ways to export TEXT out of FileMaker.

Using this sample text in one field (and one record) we will make different exports and review the results:

Continue reading

Aggregates (Summary Fields) in Filtered Portals

The question on one or more of the FileMaker forums was asked and answered. Q: How do you get the Count() of the related records in a filtered portal? A: (paraphrased) duplicate the filtered portal and make it one row in height. Place the related summary field, “Count of…”, in the single row filtered portal. Voila! your count is now filter-specific. (The instructions for creating the filter for a portal and summary field are below.)

This recent topic led me to consider what other aggregate fields could be used with the filtered portal. And what about that Go To Related Record script step? Does it only show the related FILTERED records or all related records? The demo (bvoth_aggregates_in_portals.fmp12) and article have been created to answer these questions.

I started using filtered portals in cross-tab reports shortly after they were introduced. These are generally ways to show something very specific in each portal with sorts and filters and usually one row only. I had not explored the use of aggregates in these cross-tab reports until now.

Kudos to those before me that may have discovered these answers and tricks, too.

Continue reading

Fixed Width for EDI and Other Reporting

Editor’s note: Today it’s my pleasure to present a guest article written by Beverly Voth. Like many other developers, I have enjoyed and benefitted from her ongoing contributions to the FileMaker community.

I do a lot of text manipulation for EDI (Electronic data interchange – http://en.wikipedia.org/wiki/Electronic_data_interchange) and plain text exports with fixed-width field data. Some varieties of EDI use XML, but this article is about plain text. EDI may or may not use the fixed-width format. Fixed-width reports may or may not use delimiters and various “padding” characters.

I created two FileMaker custom functions to help me calculate fixed-width and EDI text for export, and if you wish, you can follow along in today’s demo file, Fixed Width EDI.

Continue reading