Miscellaneous Functions

Introduction

These miscellaneous custom functions provide essential utilities for FileMaker development. They help with script environment information, JSON validation, and handling null values in calculations.

These utility functions are used by the error handling custom functions from Proof+Geist and provide foundation capabilities for working with JSON, script environment information, and null values.

Functions

getScriptEnvironment

Description: Returns a JSON object containing information about the current script environment, including file name, script name, layout, and system information.

Parameters:

• None

Return:

• A JSON object with environment information

Example:

Let (
    [
        $env = getScriptEnvironment
    ];
    // Access individual properties
    JSONGetElement($env; "scriptName")
)
// Returns: The name of the current script

Generated JSON Structure:

{
  "fileName": "YourFileName.fmp12",
  "scriptName": "Current Script Name",
  "scriptParameter": "Any passed parameter",
  "layoutName": "Current Layout",
  "systemPlatform": "Win/Mac value",
  "systemVersion": "OS Version"
}

JSON.isValid(json)

Description: Validates whether a string is properly formatted JSON. Returns True if the JSON is valid, False otherwise.

Parameters:

• json: The JSON string to validate

Return:

• Boolean (True if valid JSON, False if invalid)

Example:

Let (
    [
        $validJSON = "{\"name\":\"John\",\"age\":30}";
        $invalidJSON = "{name:John,age:30}"
    ];
    List(
        "Valid JSON: " & JSON.isValid($validJSON);
        "Invalid JSON: " & JSON.isValid($invalidJSON)
    )
)
// Returns:
// Valid JSON: True
// Invalid JSON: False

lookupFieldValue(targetField; matchField; matchValue)

Description: Retrieves a single value (first result) from a related table using ExecuteSQL. This provides a clean, portable alternative to the standard FileMaker lookup functionality.

Parameters:

• targetField: The field to retrieve the value from
• matchField: The field to match against in the WHERE clause
• matchValue: The value to match in the WHERE clause

Dependencies:

• SQL.GetColumn - For executing the SQL query

Return:

• The first value from the result set (or empty if no match found)

Example:

Let (
    [
        // Get the email address for customer ID 12345
        $email = lookupFieldValue(Customers::Email; Customers::CustomerID; "12345")
    ];
    $email
)
// Returns: The email address for customer with ID 12345

null

Description: Returns a true null value that can be used in place of empty strings in situations where an empty string would cause errors, such as when setting date fields or working with JSON.

Parameters:

• None

Return:

• A null value (the undefined default result from a Case statement)

Example:

Let (
    [
        // Create a JSON object with a null value
        $json = JSONSetElement("{}"; "dateField"; null; JSONNull)
    ];
    $json
)
// Returns: {"dateField":null}

Common Use Cases:

// In a Set Field script step to clear a date field
Set Field [Table::DateField; null]
 
// To provide a default null value for optional parameters
Let (
    [
        $optionalParam = If(IsEmpty(Get(ScriptParameter)); null; Get(ScriptParameter))
    ];
    // Function logic here
)

Usage Tips

1. Environment Information: Use getScriptEnvironment for consistent error logging or debugging to capture the context in which operations are performed.

2. JSON Validation: Always validate external JSON data with JSON.isValid before attempting to parse it to prevent errors.

3. Database Lookups: Use lookupFieldValue as a more flexible alternative to relationship-based lookups, especially for ad-hoc queries where creating a relationship would be impractical.

4. Null Values: Use the null function instead of empty strings when:

   • Setting container, date, time, or number fields to empty
   • Creating JSON with null values
   • Working with APIs that expect null rather than empty strings