FM 16: Pseudo Indexing

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.

2017-07-04_17-25-35

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…

  1. is simpler (has fewer moving parts)
  2. scales better
  3. is less brittle (prone to breakage if schema is renamed)
  4. enables you to bring new fields on board faster
  5. can sort the pseudo index in ascending or descending order

Demo file: FM-16-Pseudo-Indexing.zip (requires FM 16) Continue reading

FM 16: SortValues and UniqueValues

[Update 7/1/17, 12 noon (PDT): demo file has been updated to make the found set tracking more robust; article text has been revised to reflect this.]

Today we’re going to look at two functions introduced in FileMaker 16: SortValues and UniqueValues, and you may want to download and follow along in this demo file: FM 16 – SortValues & UniqueValues (requires FM 16 or later).

Each of these functions operates on a return-delimited list of values, and the names are self explanatory: SortValues allows you to intelligently sort the list in ascending or descending order, and UniqueValues removes duplicate entries. 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

User-Friendly Excel Exports, part 7

This is a follow up to last week’s part 6, with three refinements.

Continue reading

User-Friendly Excel Exports, part 6

29 Mar 2017: this file (Excel Exports, v6d) fixes a formatting bug when currency values < 1000 are not preceded by a “$”. Explanation forthcoming in next week’s “part 7” article.

Recently I was asked to implement user-friendly Excel exports on a WAN-based solution, utilizing some of the techniques explored earlier in this series (part 1, part 1.1, part 2, part 3, part 4 and part 5 — all from five years ago). There was just one problem: while performance was great locally, and okay on a LAN, it was decidedly less-than-satisfactory across a WAN.

Well today we’re going to look at three demos showcasing a brilliant, outside-the-box technique I picked up from Beverly Voth, which is not only significantly faster on a WAN (4x or 5x faster), but also remedies some other shortcomings associated with my earlier approaches.

Demo files:

Continue reading

PDF with Embedded Links

Author’s note: I’d like to offer a big thank you to Christian Schmitz of Monkeybread Software, not only for creating and continually improving a fantastic plug-in, but also for promptly and patiently answering my many questions.

A few weeks ago we looked at creating a table of contents for a PDF containing nine individual reports. The article ended by asking a somewhat rhetorical question…

Now that we have a table of contents for our master report, wouldn’t it be nice if we could hyperlink each TOC entry to its corresponding report within the PDF?

2017-02-10_180551

FileMaker doesn’t provide a built-in way to accomplish this, but it can be done using a combination of the MonkeyBread plug-in + DynaPDF Lite, and this will be explored in my next article.

…and today we’re going to dig into the nuts and bolts of what it takes to make this happen.

As usual, we have a demo file (Embedded PDF Links), but since it will only work for developers having licensed copies of the above-mentioned products, here is a PDF showing the end result: Report with Hyperlinked TOC.pdf

Continue reading

Virtual List Table Of Contents

A few years back I did an article on producing a PDF catalog + table of contents, with the TOC based on a vendor table and the catalog entries based on a products table. Today we’re going to use a different approach to generate a table of contents, building on techniques explored in last year’s Virtual List Reporting series… specifically we’re going to generate one big PDF combining nine reports, prefaced by a table of contents.

2017-01-30_173715

Demo files: virtual-list-toc-v1.zip and virtual-list-toc-v2.zip

2017-01-30_201617

Continue reading

FM/SQL Portal Filter + Dynamic Sort

Editor’s note: Today I’m pleased to present a guest article and accompanying demo file written by Joel Englander on using ExecuteSQL + a global multi-line key as an alternative to FileMaker’s built-in portal filtering mechanism, with dynamic portal sorting included as an added bonus.

Demo file: fm-sqlportal-filter-and-dynsort.zip (75 Mb compressed, 230 Mb uncompressed)

browseaksortcitycomp Continue reading

Exploring Problem Spaces

Given its primary role as a database product, it’s easy sometimes to forget what a great teaching tool FileMaker Pro can be. One of my favorite educational uses for it is building models to explore problem spaces, for example…

  • viewing all possible outcomes for a problem with boundary conditions
  • conducting trials to confirm the likelihood of a predicted outcome
  • using “brute force” to find the solution to a complex problem

..and today we’re going to look at an example of each of these.

Dice Roll

2016-11-29_021650

Demo Files: Dice Roll, v1 and Dice Roll, v2

Continue reading