JSON data creation question

[Greg Hains]

Good afternoon,

I am trying to generate the JSON lines found in the attached file, yet I am experiencing problems and would like some assistance please in generating the JSON data in the attached file. 

I can create the main "outer" layer such as the first GUID, CompletedDate etc is OK, as well as the Supplier and the 3 entries under there, however, when I "resume" the outer layer again, and then try to create the PurchaseOrderLines and everything below that, it all turns to mush.  My attempts to do so by inserting JSONSetElements again give me errors saying something similar to not allowing List items (or similar error) within.  

How do I nest the SetElement commands please to achieve this structure?  I can see the flow of the data, but just unable to write the script to do it.
(There are actually many more lines required in this data but this is the core "shape" of it - once this is correct the rest will be easy to add in.) 

(I'm told that the file here is the exact output I require, but it doesn't look right to me - it appears as though there is a missing square bracket missing to close the nest towards the bottom. Can anybody confirm or deny this as it only adds to my confusion.

I have been through Filemaker and other consultancy webpages looking for examples of this but not found something that allows me to nut it out.

Any help would be greatly appreciated.

Greg.

 

JSON data in case attachment cannot be read:

{
    "Guid": "6b35620c-9a43-44a3-b156-f10656b02710",
    "CompletedDate": null,
    "Supplier": {
        "Guid": "a07d2abb-06b1-434d-bfd5-547ca68fe1c6",
        "SupplierCode": “Fruit",
        "SupplierName": “Fruit Supplies"
    },
    "SupplierRefs”: null,
    "SupplierInvoiceDate": “datevalue",
    "PurchaseOrderLines":
    [
        {
            "Guid": "6ab9fe51-8a59-41dd-a494-d67178b67c3e",
            "LineNumber": 1,
            "Product":
                {
                    "ProductCode": “Banana",
                    "ProductDescription": “Banana Peel",                
                    "Guid": "c92823f0-a5af-4e66-91a7-d2c66c25f885"
                },
            "DueDate": “datevalue",
            "OrderQuantity": 60,
            "UnitPrice": 47.5,
            "LineTotal": 2850
        }
}

JSON.rtf

Edited October 26, 2020 by Greg Hains
Additional info required

[comment]

The JSON code you have posted is invalid.

In general, I find that the best method to create a complex JSON object is to create the outer structure first, then create a small JSON object out of each record and add it to outer structure as JSONObject. 

 

[Greg Hains]

Hi Comment, thanks for responding. 

(Yes, I thought it to be invalid too but the vendor swears its 100% correct.)

The problem I am having is the FM script syntax to actually create this nesting for the inner structure. It is eluding me - spent hours on it with crazy results.

Greg

[comment]

6 minutes ago, Greg Hains said:

the vendor swears its 100% correct.

There are online validators that can show this is not so.

I am afraid I cannot provide a more specific advice without a more specific description of the problem. Perhaps you can use the attached example as a guide; it creates a JSON for a parent record with multiple children - and it does so in the following order:

1. Creates an empty array for the child records;
2. Creates an object for each child record and adds it to the array;
3. Creates the final JSON result by adding the parent data and the child array to a new, empty JSON object.

Pay attention to the type used in each step.

Record2JSON.fmp12

[Greg Hains]

Hi Comment,

Attached is a skeleton of the file I am trying to get working.
Rather than botch up the terminology here and confuse readers (because I know I already am), this file has the short script defining the variables - which all work OK, then in a commented section below, what I would like the JSON data to look like. My query is about how to nest and embed the various sections to make it right within Filemaker. This is the bit that's stumping me.

I tried - just to see (and this is just a very basic example)- of placing one of the created variables within another JSONSetElement as a line, but get the "List Usage" error which is attached. The screen dump of this code insertion is also attached.

As silly at it sounds, I'm not really having trouble with the JSON itself, but more constructing it in Filemaker. There's tons of JSON tutorials out there - this is just how I make FM put it together.

Your assistance  - as always - is greatly appreciated.

Greg

JSON_Test.fmp12

[comment]

I am not really interested in debugging your code, but one thing jumps out even from the screenshot: you have a variable in the middle. That's not conforming to the syntax of the JSONSetElement() function that expects:

Quote

a set of keyOrIndexOrPath, value, and type parameters in brackets [ ] for each element

If you want to add a variable to the same object, you must add it as such set.

 

--
P.S. I see no reason for using global $$variables here. These variables persist until the end of the session, consuming memory and creating a potential conflict with other scripts or subsequent runs of the same script. Use script $variables instead - they expire as soon as the script has run out.

 

Edited October 26, 2020 by comment

[jbrown]

Question: From where is your data coming? I assume an PO table, PO Lines table, and a Products table, where they are related. Is that correct?

Here's how I might do this if this is all data from related tables:

1. Create a calc field in the Products table that creates an object out of ProductCode, ProductDescription, and Guid. It would simply look like this:

{
   "ProductCode": "ALIEN",
   "ProductDescription": "Alien Artifact",                
    "Guid": "c92823f0-a5af-4e66-91a7-d2c66c25f885"
                       }

2. Create a calc field in the PO Lines table that creates its JSON object with the relevant fields, including the "Product" key that pulls the above calc field into the Product key:  JSONSetElement (""; "Product" ; ProductTable::asJSON ; JSONObject )

3. IN POLines table, create a summary field that generates a ListOf the JSON object calc field, created in step 2. This will just create a list, but i can transform it into an array easily.

 

From the context of one PO record, the script will generate this JSON object. It gathers the PO properties into the object. And it grabs the PO Lines Summary field (created in step 3). It takes this POLines summary field and translates it to an array:

 

Set Variable [ $POLinesArray ; "[" & Substitute (POLines::ListOfSummary) ; "¶" ; ",") & "]")

Set variable [$yourJSON ; JSONSetElement("PurchaseOrderLines" ; $POLinesAsArray; JSONArray)]

 

This does add extra schema to your tables. But it's a clean way to get what you need.

 

 

[comment]

2 hours ago, jbrown said:

This does add extra schema to your tables. But it's a clean way to get what you need.

But what problem does this solve? I don't see that. I only see the problems it creates:

• unnecessary fields;
• increased difficulty to maintain code split between script and multiple fields.

I also don't like the use of text functions to create/manipulate JSON. 

 

 

 

 

[jbrown]

8 minutes ago, comment said:

But what problem does this solve?

It solves the problem of collecting lots of nested data, as is in the example. It's not necessary for everyone, but it is a technique. 

I don't think there's any difficulty in code split: You're gathering data using scripts, pulling data from the fields. JSON is a type of data you can collect. There's no problem in maintaining this because it's set, just like any other calculation field.

I agree: unnecessary fields. But these are the only two fields (asJSON, and ListOFJSON) that I'm okay with, calculation and 'unnecessary-wise'.

I don't use text functions:  JSONSetElement() is a JSON-creation function. So I use that 

The above is a technique I've used. It's a good way to start. It's a good way to get the feel of this.

Overall, however I agree with your idea to keep the data collection in a script. Alignment with this idea doesn't dismiss other ideas.

[Josh Ormond]

Over WAN, having the JSON already set can often create a significant performance increase on complex JSON.

When I can, I'll actually push this to a separate table, if it's in a system or file that I already have handling CRUD operations with a transaction. It's part of an overall development pattern. With that, it can be easily used with script parameters, external APIs, scripts, calculations, error trapping, etc. 

It is all about controlling WHEN the user has to wait for the actions to happen. If I can, without compromising data integrity, have those actions happen when the user is sleeping, that's the route I go. Forcing a wait time when the user expects there to be a wait is ideal.

[Greg Hains]

Good evening @comment, @jbrownand @Josh Ormond,

I appreciate your contribution to this question - it has been educational (and I'm not being sarcastic) seeing seasoned developers discuss this topic. 

Thank you for your time.

Greg