Free Function: JSONGetRecord v1.2 (Update)

JSONGetRecord v1.2 quickly and dynamically gathers field values into a JSON object with field names as the keys containing the corresponding value.

Pulling values from a record can be useful for many reasons, including change logging, rolling back changes after a commit, managing data transmission between scripts or exchanging data with external systems. The JSONGetRecord function, shown below,  uses five parameters to allow complete control over which record(s) and field type(s) and class(es) are gathered and the format of the data.

JSONGetRecord ( fieldTypes ; fieldClasses ; css ; recordOption ; linkID )

These can be configured to specify a desire for any combinations of:

  • Field Type(s): text, number, date, time, timestamp, container
  • Field Class(es):normal, calculation, global, summary
  • Content Format:raw content or css
  • Records: current, found set or related (all or a specified quantity)

Note: This function is a little slow when gathering found sets or related sets when there are more than a few records.

Parameters

The function can be configured to pull the current record, the current found set or any number of related records through a specified relationship. It can gather all or specified field type(s) and class(es), and can return the field values as raw content or CSS.

Click on a parameter to view the available options:

The fieldTypes parameter must contain a text value specifying which field types should be retrieved.  The values can be:

  • empty = an empty string indicating that all field types should be retrieved
  • one type = a string containing the name of one field type to be retrieved (text, number, date, time, timestamp or container)
  • many = a string containing a carriage return delimited list of two or more field types to be retrieved
The fieldClasses parameter must contain a text value specifying which class of fields should be included. The values can be:

  • empty = an empty string indicating that all field classes should be retrieved
  • one class = a string containing the name of one field class to be retrieved (normal, calculation, global, summary)
  • many = a string containing a carriage return delimited list of two or more field classes to be retrieved
The css parameter must contain a numeric boolean value indicating if the field data should be retrieved as css where applicable. The values can be:

  • true (1) = retrieve the content as css where applicable
  • false (0) = retrieve the content as raw data without format information
The recordOption parameter varies depending if fetching local or related values:

  • local = true (non-zero) or false (zero) value indicating if the request is for the entire found set or not
  • related = a number indicating a limit on how many records to fetch; zero indicates all records
The linkID parameter is used to instruct the function to fetch related records. Leave blank for local records or provide a reference to a serialized field through the desired relationship to pull records from the related table.

Practical and Educational

JSONGetRecord provides a practical JSON functionality that will work in any database for any table. It is also a great demonstration of many advanced techniques including:

  • customization control with parameters
  • open-ended programming
  • multimode recursion hierarchy
  • local variables persisting across all iterations
  • variable prefix and cleanup to avoid script conflicts
  • accessing the virtual schema for dynamic functionality

Download the Free Demo

The free downloadable Learn FileMaker JSONGetRecord demo file contains a working demo of the function and the ability to copy it into your database.

The demo contains the following layouts:

  1. Introducing the Function
  2. Defining the Function
  3. Sandbox Example 1 — Accessing Local Records
  4. Sandbox Example 2 — Accessing Related Records
  5. Nesting Calls
  6. A Brief Functional Overview of the Function’s Multimode Recursion Hierarchy
  7. Copy the Function

Download the free updated demo today and add the JSONGetRecord v1.1 function to your database.

Download

Version History

1.2 — Fixed JSON data type bug resulting from speed improvement restructuring
1.1 — Cleaner and up to 5x speed improvement
1.0 — Initial release

FileMaker Pro 16 is required to view the demo.


JSON functions were introduced in FileMaker 16 as a subset of Text Functions. These functions create and manipulate JSON objects, which contain multiple pieces of data stored in a structured text format sometimes referred to as an array or record. The JSONGetRecord function is a custom function developed by Mark Conway Munro, author of Learn FileMaker Pro 16 (Apress, 2017).