AnsweredAssumed Answered

Help with straightforward JSON

Question asked by PaulSeabright on Sep 6, 2017
Latest reply on Sep 7, 2017 by PaulSeabright

I'm new to JSON (like 3 days new!)

 

I have successfully pulled a list of JSON Sales data from our cloud based accounting package, with the help of Tod Giests excellent 'Generator' Generator - Geist Interactive .

 

Where I am struggling is parsing the JSON to populate matched fields in an invoice table.  At the moment this is just an exercise to develop my understanding of the process.

 

If I follow the examples given by Tod in his video using

 

Set Variable [$customerRef; Value: JSONGetElement( $json ; "customerRef" )] // $json contains the JSON data from the web server.

 

I get the following error

 

? in Json::Value::find(key, end, found): requires objectValue or nullValue.

 

If I use query parameters to get just one sales record, I get no errors parsing the JSON data, and using the $var in Set Field script steps, fields in the Invoices table are populated.

 

None of the documentation I have so far read has enabled me to solve this problem. I've tried using array notation for the keyOrIndexOrPath part of JSONGetElement  e.g. "customerRef[1]"  but I guess I'm not using it correctly - I'm sure I've missed something fairly fundamental!

 

Would anyone be able to kindly explain what I am missing and where I'm going wrong?

 

Many thanks,

 

Using FMPA 16.0.2.205 and Windows 7 Pro, 64bit.

 

This is the starting section of JSON data after being passed through jsonPrettyPrint:-

 

[

{

  "brandingThemeId": null,

  "creditTemplateId": null,

  "currencyId": "830071c8-e5a6-454d-9aa7-a92dc5609ab5",

  "currencyIsoCode": "AUD",

  "customerContactFirstName": "LLLLLLLLLLL",

  "customerContactId": "de68720a-bc66-455f-87bd-0c0d29756d93",

  "customerContactLastName": "YYYYYYYYY",

  "customerId": "5fce9673-7fe0-40f8-bc7e-3dc562202c14",

  "customerName": "XXXXXXXXXXX",

  "customerRef": "",

  "deliveryAddress": "AAAAAAA\r\nAAAAAAAAA 33333\r\nAA",

  "description": "",

  "effectiveDate": "2017-09-05T00:00:00",

  "effectiveDateUtc": "2017-09-04T12:00:00",

  "enteredDate": "2017-09-05T17:50:59.577",

  "enteredDateUtc": "2017-09-05T05:50:59.577",

  "extAccTrackingId1": null,

  "extAccTrackingId2": null,

  "invoiceNo": "INV-1913",

  "invoiceNote": "",

  "invoiceTemplateId": null,

  "materialLocationCode": "ANDREW",

  "materialLocationId": "e9dc1fae-bf25-41b2-bc87-05359a155205",

  "materialLocationName": "Andrew",

  "materialPricingLevelCode": null,

  "materialPricingLevelId": null,

  "materialPricingLevelName": null,

  "packingSlipTemplateId": null,

  "postalAddress": null,

  "quoteTemplateId": null,

  "saleId": "5369fda6-a1df-441c-ba5a-cbcf4e77a3fa",

  "saleLines": [

   {

    "description": "Bagel Kit, 2016 onwards",

    "discount": 0,

    "materialCategoryCode": "CONSUM",

    "materialCategoryId": "01077e45-07f0-4fea-aa34-19e98063602f",

    "materialCategoryName": "Consumables",

    "materialItemCode": "10475",

    "materialItemId": "2f797c75-5513-425c-8a23-54b25de3a8db",

    "materialItemName": "Bagel Kit, 2016 onwards",

    "price": 1111,

    "quantity": 1,

    "rowOrder": 0,

    "saleLineId": "3d38e9ef-82bb-478e-9a3a-4cbc76f4a30a",

    "taxRate": 10,

    "taxTypeCode": "GST",

    "taxTypeId": null,

    "taxTypeName": "AU Goods & Services Tax"

   },

   {

    "description": "Bagel Additional System",

    "discount": 0,

    "materialCategoryCode": "CONSUM",

    "materialCategoryId": "01077e45-07f0-4fea-aa34-19e98063602f",

    "materialCategoryName": "Consumables",

    "materialItemCode": "M67",

    "materialItemId": "d7da49eb-f285-47a5-a712-1d0bb288b08f",

    "materialItemName": "Bagel Additional System",

    "price": 111,

    "quantity": 3,

    "rowOrder": 1,

    "saleLineId": "83164d76-36c0-4341-84df-50d10de6a927",

    "taxRate": 10,

    "taxTypeCode": "GST",

    "taxTypeId": null,

    "taxTypeName": "AU Goods & Services Tax"

   }

  ],

  "saleNo": "SALE-1682",

  "saleUpdateOptions": null,

  "specialInstructions": null,

  "status": "Invoiced",

  "totalPriceExcl": 1111,

  "userId": "49dd25e8-f16f-41b9-ad19-0d9cd2096866",

  "userName": "Joanna Lumley"

},

{

//

//

//  data removed

//

//

//

 

    "materialItemCode": "M0",

    "materialItemId": "996857c8-8a32-4f61-ad02-a236b80b5135",

    "materialItemName": "Labour in Ute",

    "price": 11,

    "quantity": 1,

    "rowOrder": 1,

    "saleLineId": "fa9a8764-a892-4ede-ae2a-fdef69f6582e",

    "taxRate": 10,

    "taxTypeCode": "GST",

    "taxTypeId": null,

    "taxTypeName": "AU Goods & Services Tax"

   }

  ],

  "saleNo": "SALE-663",

  "saleUpdateOptions": null,

  "specialInstructions": null,

  "status": "Invoiced",

  "totalPriceExcl": 111.11,

  "userId": "49dd25e8-f16f-41b9-ad19-0d9cd2096866",

  "userName": "Joanna Lumley"

}

]

Outcomes