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
Note: Leaner does not always mean faster, especially if record counts will be large… in which case I would recommend going with the approach discussed in part 2.
[3 Jan 2018: demo updated to version 3.2 as per Jens Rasmussen’s comment below.]
[2 Nov 2017: demo updated to version 3.1 as per my exchange with Jonathan Mickelson in the comments section below.]
I thought after part 2 that this technique had been thoroughly discussed and dissected, but Geoff Gerhard of Creative Solutions writes to point out that, if the goal is to have as few moving parts as possible, the table occurrence (TO) count can be further reduced, from four to three…
…and he has provided a demo file which I am sharing with his permission.
Demo file: reciprocal-linkage-GGv3.2.zip
This is a quick follow up to last week’s article on reciprocal (bi-directional) linking, and will assume the reader is familiar with that material. Paul Jansen of APJ Computing Solutions sent in a revision of one of my files demonstrating that, among other things, the graph could be a good deal leaner (as per my stated goal of making the approach as simple and lightweight as possible), and I am sharing that file today with his permission, along with my own attempt at further simplification.
Challenge: in a multiuser document management system, facilitate the linking (and unlinking) of any document with any other. Make it clear which documents are linked to the current document, and make sure the approach is multiuser friendly — in other words, allow document records to be linked and unlinked even if one or both of those document records are currently being edited by another user.
Today we have two demo files, one using standard serial numbers and one using UUIDs. Except for serials vs. UUIDs, the two files are functionally identical, and in the interest of clarity, I’m going to focus primarily on the serial number version.
Demo files: reciprocal linkage & reciprocal linkage with uuids Continue reading
29 June 2018: I have taken this article offline as it has been completely superseded by this one: Virtual List Reporting with JSON Arrays.
Update 29 June 2018: see Virtual List Reporting with JSON Arrays for updated thoughts on this topic.
Today we’re going to look at an alternative approach to the multi-window VLR technique we examined last month in Virtual List Reporting part 3, utilizing and extending techniques introduced in last month’s JSON + Virtual List. To avoid needless repetition, today’s article will assume the reader is familiar with that material.
Demo files: vlr-plus-json-v1 and vlr-plus-json-v2
As you may recall from VLR Part 3, we have a system that allows us to spawn as many separate reports as we wish (each in its own browse mode window) and sort each report independently via clickable column headings. Continue reading
31 July 2017: As per my exchange with Beverly Voth in the comments section, the date encoding technique used here is strictly intended for situations where FileMaker will both produce AND consume the JSON.
Demo file: json-plus-virtual-list-part-2.zip
This is a short follow-up to part 1, to demonstrate an alternative idea re: JSON date encoding. As you may recall, JSON does not have a “date” type, and one of our goals is to encode and decode JSON dates in a region-agnostic manner (e.g., encode in a region where MM/DD/YYYY is the default, but then decode in a region where DD/MM/YYYY is the default).
To accomplish this goal, in part 1, we wrapped our FileMaker dates inside a GetAsNumber function when pushing them to JSON so they looked like this:
31 July 2017: As per my exchange with Beverly Voth in the comments section of part 2, the date encoding technique used here is strictly intended for situations where FileMaker will both produce AND consume the JSON.
29 July 2017: demo has been updated to require a minimum version of FileMaker 16.02 as per my exchange with John Renfrew in the comments section.
Demo file: json-plus-virtual-list.zip
My original plan was to integrate JSON with virtual list reporting, but in the interest of clarity, I decided to save that for a future article. Today we have a straightforward demo that a) generates a small amount of JSON from a standard FileMaker table, and then b) renders it in a virtual list table. Continue reading
Last year I posted a couple articles on the topic of virtual list reporting (part 1 and part 2), a.k.a. VLR. Today we’re going to look at some additional things you can do with VLR, and to avoid a lot of repetition, this article will assume the reader is familiar with the material covered in part 1, wherein benefits are extolled and fundamentals explained.
Demo file: VLR-part-3.zip (3.4 Mb compressed)
Did you notice in parts 1 and 2 that we were limited to producing a single report at a time? Yes, we could sequentially output them and concatenate them into a PDF, but we couldn’t display multiple reports on-screen simultaneously, nor could we interact with them in browse mode.
This time around we’re going to look at a method to produce multiple independent reports concurrently, each in its own window, based on a single virtual list table. This will entail generating a large number of dynamically instantiated $$vars[withReps], so we will also explore a way to automatically clear them out (without the overhead of having to keep track of them during instantiation).
Disclaimer/Warning: before running multiple Customer reports, open your Data Viewer and activate the Watch tab. You don’t want to display the Current tab with 5K or more variables in memory, unless you’ve got a lot of… time… on… your… hands.
During our recent discussion of SortValues and UniqueValues, I made passing reference to an article I wrote a few years ago on the topic of “pseudo indexing“, i.e., displaying a list of unique values for a specific field for a found set, rather than for all records.
Well today we’re going to dust the pseudo indexing demo off, and update it to take advantage of the above-mentioned new-in-16 functions. Why bother? Here’s why: the new implementation…
- is simpler (has fewer moving parts)
- scales better
- is less brittle (prone to breakage if schema is renamed)
- enables you to bring new fields on board faster
- can sort the pseudo index in ascending or descending order
Demo file: FM-16-Pseudo-Indexing.zip (requires FM 16) Continue reading