1. Pages:
  3. 1
  4. ...
  5. 6
  6. 7
  7. 8
  8. 9
  9. 10
  10. 11
  11. 12
  12. ...
  13. 28
JSON, Level: Intermediate

JSON + Virtual List, part 2

Kevin Frank

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:

2017-07-29_180704

Continue reading “JSON + Virtual List, part 2” →

JSON, Level: Intermediate, Version: FM 16 or later

JSON + Virtual List

Kevin Frank

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.

One nice thing about FileMaker being on a yearly release cycle is that there is always something new to learn and write about… and, having recently attended a pair of highly informative sessions on the topic of JSON (JavaScript Object Notation) at Devcon 2017 — thank you Todd Geist and Anders Monsen — it is clear to me that JSON + virtual list is a compelling combination.

2017-07-27_152145

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 “JSON + Virtual List” →

Level: Advanced, Version: FM 13 or later, Virtual List

Virtual List Reporting, part 3

Kevin Frank

Update 30 September 2021: while this article is worth reading for background information, I now recommend using the approach utilized here: Virtual List Reporting, part 4

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.

2017-07-21_18-46-06

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.

Continue reading “Virtual List Reporting, part 3” →

Level: Intermediate

FM 16: Pseudo Indexing

Kevin Frank

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: Pseudo Indexing” →

Level: Intermediate, Version: FM 16 or later

FM 16: SortValues and UniqueValues

Kevin Frank

[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 “FM 16: SortValues and UniqueValues” →

Beverly Voth, Level: Intermediate, Version: FM 16 or later

Export Field Contents (Update for FM 16)

beverlyvoth

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 “Export Field Contents (Update for FM 16)” →

ExecuteSQL, Level: Intermediate, SQL, Version: FM 12 or later

User-Friendly Excel Exports, part 7

Kevin Frank

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

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

Continue reading “User-Friendly Excel Exports, part 7” →

Level: Intermediate, Version: FM 12 or later

User-Friendly Excel Exports, part 6

Kevin Frank

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

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 “User-Friendly Excel Exports, part 6” →

Level: Intermediate, Version: FM 13 or later

PDF with Embedded Links

Kevin Frank

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 “PDF with Embedded Links” →