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.
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.
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:
- 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
- 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
- true (1) = retrieve the content as css where applicable
- false (0) = retrieve the content as raw data without format information
- 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
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:
- Introducing the Function
- Defining the Function
- Sandbox Example 1 — Accessing Local Records
- Sandbox Example 2 — Accessing Related Records
- Nesting Calls
- A Brief Functional Overview of the Function’s Multimode Recursion Hierarchy
- Copy the Function
Download the free updated demo today and add the JSONGetRecord v1.1 function to your database.
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).