1. JSON Data Type in ACF Language

  1. JSON Data Type in ACF Language
    1. Example from the FileMaker reference compared to ACF.
  2. Detailed syntax descriptions
    1. Assignments
      1. Using string arrays
      2. Using the JSON function to make JSON key / Value pairs.
  3. Extracting values from JSON objects
    1. Data types
    2. Getting the size of a JSON array
      1. To iterate an array inside a JSON object
    3. Getting a list of tags for a given path
  4. More examples
    1. Pulling JSON data from a WEB-service
    2. Writing a helper function to simplify the construct of a composite JSON.
  5. Using JSON to exchange values between a FileMaker Script and an ACF functions

Integration with FileMaker: ACF's JSON support seamlessly integrates with FileMaker, allowing complex data structures to be transmitted efficiently between the two environments. Unlike FileMaker, where JSON is managed through text strings and specific functions, ACF elevates JSON to a first-class data type.

Simplified JSON Manipulation: As a dedicated data type, JSON handling in ACF is more intuitive and streamlined compared to FileMaker. The compiler understands JSON-specific operations, reducing the need for specialized JSON functions. This simplification means standard operations on JSON variables are inherently understood by the compiler.

Key Functions:

Notation and Operations:

This powerful implementation simplifies JSON handling, making it more accessible and consistent with other data types in ACF, enhancing productivity and reducing the learning curve for developers familiar with JSON in other contexts.

1.1. Example from the FileMaker reference compared to ACF. ↑

We have this example from the Claris website about JSON. It is about adding a product with 6 fields to a JSON array object: 

 # In FileMaker
Set Variable [ $NewProduct ; Value: 
   JSONSetElement ( "{}" ;
      [ "id" ; "FB4" ; JSONString ] ; 
      [ "name" ; "Vanilla Cake" ; JSONString ] ; 
      [ "price" ; 17.5 ; JSONNumber ] ; 
      [ "stock" ; 12 ; JSONNumber ] ; 
      [ "category" ; "Cakes" ; JSONString ] ; 
      [ "special" ; true ; JSONBoolean ] 
   ) ]
Set Variable [ $NextIndex ; Value: 
   ValueCount ( 
      JSONListKeys ( $$JSON ; "bakery.product" ) 
   ) ] 
Set Variable [ $$JSON ; Value: 
   JSONSetElement ( 
      $$JSON ; "bakery.product[" & $NextIndex & "]" ; $NewProduct ; 
      JSONObject 
   ) ]

And here is the same written in ACF

    JSON fmJSON = $$JSON;  // Declaration and assignment from FM variable $$JSON
    JSON newProduct = JSON ( // New product, 6 key/value pair
        "id", "FB4", 
        "name", "Vanilla Cake", 
        "price", 17.5, 
        "stock", 12, 
        "category", "Cakes", 
        "special", true); 
    fmJSON["bakery.product[]"] = newProduct; // add it to the array
    return fmJSON; // return back to FM script with the result.

And here is what we got:

{
    "bakery": {
        "product": [
            {
                "category": "Cakes",
                "id": "FB4",
                "name": "Vanilla Cake",
                "price": 17.5,
                "special": 1,
                "stock": 12
            }
        ]
    }
}

As you can see, the tags are ordered alphabetically to improve speed on lookup.

2. Detailed syntax descriptions ↑

In this part, we will go walk you through the more detailed descriptions of the syntax and how you do different tasks. As there are not so many function names involved in this JSON implementation, we need to look closer at the syntax.

2.1. Assignments ↑

JSON elements are typically built via a series of assignment statements. All begin with a declaration of a JSON variable: 

JSON myJson;

Simple Assignments

Assigning a text string directly to a JSON variable in ACF creates a leaf node rather than an object. To handle cases where this assignment might be inappropriate (such as assigning plain text to a JSON variable), ACF reinitializes the JSON object and inserts an "ERROR" key detailing the issue:

myJson = "This is text";

This results in:

{
    "ERROR": {
        "Key": "",
        "Message": "Invalid JSON assignment; key/value pairs required. The resulting JSON was not an object",
        "Value": "This is text"
    }
}

Simple assignments can also be used for parsing JSON strings. For instance, if you receive JSON-formatted text from a web service, you can directly assign it to a JSON variable. ACF assumes it's a JSON object if the text begins with an opening curly bracket ({) and attempts to parse it accordingly.

Making assignment with a tag

MyJson["myText"] = "This is text";

Give this result:

{
    "myText" : "This is text"
}

For more levels into the JSON, you use dot-notation for the path. 

MyJson["myText.personalText.mondayText"] = "This is text";

Give this result:

{
    "myText" : {
        "personalText" : {
            "mondayText": "This is text"
        }
    }
}

Make an array with two items on the inner level Place empty square brackets at the end of the path: 

MyJson["myText.personalText.mondayText[]"] = "This is text";
MyJson["myText.personalText.mondayText[]"] = "Second text";

Give this result:

{
    "myText" : {
        "personalText" : {
            "mondayText": [
                "This is text",
                "Second text"
            ]
        }
    }
}

2.1.1. Using string arrays ↑

We can also make the same product using string arrays: 

ARRAY string myTexts; 
myTexts[] = "This is text";
myTexts[] = "Second text";
// This method replaces the inner level array. 
MyJson["myText.personalText.mondayText"] = myTexts;

2.1.2. Using the JSON function to make JSON key / Value pairs. ↑

myJson["People[]"] = JSON ("name", "Olaf Haraldsson", 
                "address", "Frivei 2", 
                "Zip", "1617", 
                "City", "NORTH POLE");

This will produce: 

{
    "People" : [
        {
            "name" : "Olaf Haraldsson", 
            "address" : "Frivei 2", 
            "Zip" : "1617", 
            "City" : "NORTH POLE"
        }
    ]
}

3. Extracting values from JSON objects ↑

Extracting simple or complex parts from a JSON object is the other important thing in this. implementation. The syntax for the path is similar, except that the object is not modified, so you cannot use the [] at the end of the path. If the path is not pointing to anything in the object, a question mark is returned.

To pull the name from the previous example, you can simply write this: 

string name; 

name = myJson["People[1].name"];

To retrieve a JSON part into another JSON variable, it can be written this way: 

JSON myPerson; 

myPerson = myJson["People[1]");

3.1. Data types ↑

A JSON object can contain data with several different data types, like other objects, strings, or numbers. When ACF functions are compiled, the compiler has no info about the structure of the JSON object you are working on. Therefore the type of an extracted value is always a string, even if it's a number type inside the object. In order to use it in a calculation, you need to use a type converter to explicitly tell the compiler the type of the extracted object.

For the calculation of the line sum based on a product price you have extracted from a JSON, let's use the very first example JSON from Chapter 1: 

float line_sum, count = 10; 
line_sum = count * float ( fmJSON["bakery.product[1].price"] );

You can use all the simple declaration words as type converters. Strings need of course no type converter.

3.2. Getting the size of a JSON array ↑

To get the size of a JSON array, the SizeOf function can be used. This function also returns the number of tags in a non-array path. 

int numberOfBakeryProducts = SizeOf ( fmJSON ["bakery.product"] );

3.2.1. To iterate an array inside a JSON object ↑

int numberOfBakeryProducts = SizeOf ( fmJSON ["bakery.product"] ); 
int i; 
for (i=1, numberOfBakeryProducts) 
    print fmJSON["bakery.product["+i+"].name"] + "\n"; 
end for

or easier with the format function: 

int numberOfBakeryProducts = SizeOf ( fmJSON ["bakery.product"] ); 
int i; 
for (i=1, numberOfBakeryProducts) 
    print fmJSON[format("bakery.product[%d].name",i)] + "\n"; 
end for

3.3. Getting a list of tags for a given path ↑

To list all the keys that can be found for a given path, you can use the list_keys function:

// From the object created in "Using the JSON function to make JSON key / Value pairs."
string myKeys = list_keys(myJson["Customers.People[1]"]);

You get the result: 

Address
City
Country
Name
Zip

4. More examples ↑

4.1. Pulling JSON data from a WEB-service ↑

In this example, we use the Norwegian company register, and access an API service to look up a company from its organization number: 

function OppslagEnhetBRREG ( string orgNr )
    // Remove non-digits from OrgNr
    orgNr = regex_replace ( "[^0-9]", orgNr, "");
    // request URL
    string url = "https://data.brreg.no/enhetsregisteret/api/enheter/" + orgNr; 
    // Headers pr API-documentation
    string hdr = 'Accept: application/vnd.brreg.enhetsregisteret.enhet.v2+json;charset=UTF-8'; 
    // Request API
    string respons = HTTP_GET ( url, hdr ); 
    // Check if error....
    if ( left ( respons, 1) != "{") then
        print respons; 
        return "ERROR";
    end if 
    
    json data; 
    data = respons;
    // Pull some data from the response....
    string CompanyName   = data["navn"]; 
    string address       = data["forretningsadresse.adresse[1]"]; 
    string Zip           = data["forretningsadresse.postnummer"];
    string City          = data["forretningsadresse.poststed"];
    string Country       = data["forretningsadresse.land"]; 
    date   Etab          = date(data["stiftelsesdato"], "%Y-%m-%d"); 
    
    // print the JSON on the console, so we can look at it to see what is there. 
    print string(data); // beautify JSON automatically, so it is easier to see the 
                        // values we can get. 
                        // Just return the values. We could update some data using 
                        // SQL to the current record...
    return CompanyName + "\n" + address + "\n"+ Zip + " "+ City + "\n" + Country+
           "\nEstablished: " + Etab; 
    
end

4.2. Writing a helper function to simplify the construct of a composite JSON. ↑

You can use the JSON function to create key/value pairs for a JSON object. Makin a helper function to create a specific type of object can make it easier:

Like this: 

// Helper function to be used in the other function below. 
function MakeJsonPerson (string name, string address, string zip, string city, string country);  
    return JSON ("Name", name, "Address", address, "Zip", zip, "City", city, "Country", country); 
end

// Main function

function json_test ()
    JSON b; // Declaration
    
    // Add some people to our JSON array...
    array JSON c; 
    c[] = MakeJsonPerson ("Olaf Haraldsson", "Frivei 2", "1617", "NORTH POLE", "NORWAY" ); 
    c[] = MakeJsonPerson ("Guri Kråkeslott", "Frivei 3", "1617", "NORTH POLE", "NORWAY") ; 
    c[] = MakeJsonPerson ("Rachel Green", "Green Road 104", "9999", "SOUTH POLE", "LANGTVEKKISTAN") ; 

    // Put the arrays into a node in our JSON. 
    b["Customers.People"] = c;
    b["Customers.Count"] = sizeof(c);
    
    array string some_countries = {"Norway", "England", "Sweden", "Denmark", "France"}; 
    b["Countries"] = some_countries;
     
    b["TimeStampNow"] = now(); // Timestamps
     
    return b; // Auto JSONstringify !!
end

This example will create this: 

{
    "Countries": [
        "Norway",
        "England",
        "Sweden",
        "Denmark",
        "France"
    ],
    "Customers": {
        "Count": 3,
        "People": [
            {
                "Address": "Frivei 2",
                "City": "NORTH POLE",
                "Country": "NORWAY",
                "Name": "Olaf Haraldsson",
                "Zip": "1617"
            },
            {
                "Address": "Frivei 3",
                "City": "NORTH POLE",
                "Country": "NORWAY",
                "Name": "Guri Kråkeslott",
                "Zip": "1617"
            },
            {
                "Address": "Green Road 104",
                "City": "SOUTH POLE",
                "Country": "LANGTVEKKISTAN",
                "Name": "Rachel Green",
                "Zip": "9999"
            }
        ]
    },
    "TimeStampNow": "2023-11-25T07:28:09"
}

5. Using JSON to exchange values between a FileMaker Script and an ACF functions ↑

Sometimes, you need to return more than one value from a function. Since there are JSON functions in both the ACF language and in FileMaker, this is an ideal method.

Let's say, we have this API call to the Norwegian Company Register, and we want to use that to pull some name and address information and use it in a FileMaker Script to create a customer record based on its organizational number.

The function can return a simplified JSON object containing only the fields we need. The API call returns a rather complex JSON based on what information is registered about the company. This means that from the API there are variations in the returned data. The address, for example, can be the business address, or a postal address. We want the postal address if it's registered, or the business address otherwise.

Let's modify the API function a bit: 

function GetCustomerInfoBrreg ( string orgNr )
    // remove non-digits from OrgNr
    orgNr = regex_replace ( "[^0-9]", orgNr, "");
    // request URL
    string url = "https://data.brreg.no/enhetsregisteret/api/enheter/" + orgNr; 
    // Headers pr API-documentation
    string hdr = 'Accept: application/vnd.brreg.enhetsregisteret.enhet.v2+json;charset=UTF-8'; 
    // Perform the API request: 
    JSON respons = HTTP_GET ( url, hdr ); 
    if ( respons["navn"] == "?" ) then
        return "";  
    end if

    JSON returnData; 
    
    // Pull some data from the response....
    returnData["CompanyName"] = respons["navn"]; 
    if ( respons["postadresse"] != "?") then
        returnData["address"] = respons["postadresse.adresse[1]"]; 
        returnData["Zip"] = respons["postadresse.postnummer"]; 
        returnData["City"] = respons["postadresse.poststed"]; 
        returnData["Country"] = respons["postadresse.land"]; 
    else
        returnData["address"] = respons["forretningsadresse.adresse[1]"]; 
        returnData["Zip"] = respons["forretningsadresse.postnummer"]; 
        returnData["City"] = respons["forretningsadresse.poststed"]; 
        returnData["Country"] = respons["forretningsadresse.land"]; 
    end if
    
    return returnData; 
end

Then in a FileMaker script, you can use this function to call the API and get a simplified JSON object to create the record:

Set Variable [$Cust; Value: ACF_Run ( "GetCustomerInfoBrreg", Globals::OrgNoToCreate ) ]
if [ $Cust ≠ "" ]
    NEW Record/Request
    Set Field [ Customer::Name ; JSONGetElement ( $Cust ; "CompanyName" ) ]
    Set Field [ Customer::Address ; JSONGetElement ( $Cust ; "address" ) ]
    Set Field [ Customer::Zip ; JSONGetElement ( $Cust ; "Zip" ) ]
    Set Field [ Customer::City ; JSONGetElement ( $Cust ; "City" ) ]
    Set Field [ Customer::Country ; JSONGetElement ( $Cust ; "Country" ) ]
    Commit Records/Requests [ With dialog: Off ]
End If