Export Nested Data into a JSON File

[MICHELE ANNONI]

I need to export Sales Orders into a JSON file but I am struggling to write the code (I am new to JSON). I attached a simple FMP file with 3 Sales Orders Head, Sales Order Lines and 3 Customers. The JSON output I need (based on the FMP file attached) is the following. Can anybody help me to write the script to do that?

Thank you.

Michele Annoni

{

    "ORDER": [

        {

            "Order_Nr": "0001",

            "Date": "01/10/2023",

            "Total": "100.23",

            "CUSTOMER": {

                "CustomerID": "1",

                "Name": "CUSTOMER_1"

            },

            "ITEMS": [

                {

                    "Product_ID": "AA000K",

                    "Product_Desc": "GEAR"

                },

                {

                    "Product_ID": "BB333D",

                    "Product_Desc": "ENGINE"

                },

                {

                    "Product_ID": "CC444H",

                    "Product_desc": "PISTON"

                }

            ]

        },

        {

            "Order_Nr": "0002",

            "Date": "01/11/2023",

            "Total": "25.12",

            "CUSTOMER": {

                "CustomerID": "2",

                "Name": "CUSTOMER_2"

            },

            "ITEMS": [

                {

                    "Product_ID": "FFTG66",

                    "Product_Desc": "WHEEL"

                },

                {

                    " Product_ID ": "DD35TSH",

                    " Product_Desc ": "REAR WINDOW"

                }

            ]

        }

    ]

}

JSON File.fmp12

[comment]

1 hour ago, MICHELE ANNONI said:

export Sales Orders into a JSON file

This is not a trivial task. Filemaker does not support JSON as an export format and you need to do all the work yourself - and there is an awful lots of it.

Before you start:
Do you really need a file as the result? Or do you only need the JSON as text to be sent to an API?

If you need an actual file to be created, then you have 2 options:

1. Export as XML and use a custom XSLT stylesheet to transform the result to JSON;
2. Construct the JSON as text in a global field, then export the field contents to a file.

Of the two, I would say the first option is the more convenient one (though maybe not by much). But if you need the JSON as text, then there is the added task of reading back the exported file. Then the second option becomes more attractive, esp. since the last part is no longer required. If you decide to go that way, here is a demo file you could use as your starting point: 
https://fmforums.com/topic/107176-json-data-creation-question/?do=findComment&comment=483717

I thought I published a demo of the first method too, but I cannot find it. Let me know if you prefer to go that way and I will try to cook up an example.

 

Edited March 7 by comment

[MICHELE ANNONI]

Yes, your point 2 is what I need (JSON as a text in a Global Field).

Thank you for your help. I will check the demo file.

 

Michele Annoni