Working with JSON data and v25 new features #94, 95, 823, and 959

Working with JSON data and v25 new features #94, 95, 823, and 959

As you know we work with a lot of web services for different projects here at wxPerts, in fact, a lot of our consulting/mentoring time is spent assisting clients with either integrating with existing 3rd part web services or creating a web service to expose their own project to other 3rd parties.

JSON is becoming the defacto standard for communication via REST services. And over the last few versions, PCSOFT has done a great job of giving us more and more ability to work with JSON natively. With v25 I think we might be approaching nirvana with JSON!

Using JSON datatype instead of Variant datatype for Webservices

We generally create a class for each web service we work with. And a call to a method of that class returns an object. Up until now, we have been returning that object as a Variant. Let look at some simple example code.

2020-04-26 11_46_42-Desktop

This call is returning JSON data to us, in fact this is what it returns


But you notice we are defining the return data as a Variant and using JSONToVariant to translate the returned data into a variant. You can also see that on lines 8 and 9 we populate a couple of error fields so we can deal with letting the user know that an error has occurred outside of the class.

Here is our code to call the method.

2020-04-26 11_53_52-Desktop

BTW, we can see the variant data in the debugger as well

2020-04-26 11_50_28-Desktop

And here is what it looks like if there was an error
2020-04-26 11_56_13-Desktop

With the JSON data type first introduced with v24 and the improvements made in v25, we are no longer using variants. Our code now looks like this:

2020-04-26 12_04_27-Desktop

We can still see our data in the debugger, in fact, it looks pretty much the same as it did with a variant except you will notice the datatype is JSON.

2020-04-26 12_00_39-Desktop

But because it is actually still in its native form we can also view it as JSON, which can definitely help when debugging and working with 3rd party web services and shall we say at times less than complete documentation!

2020-04-26 12_02_16-Desktop

By keeping the data in its native JSON format not only do we simply our code but we avoid any conversion or performance issues.

Using External descriptions

XML files can have an XSD file to describe their layout, which helps you not have to blindly guess what fields and field types will be available.  PCSOFT gave us the ability to import these as external descriptions quite a few versions back, which amount other things lets you use IntelliSense when working with an XML datatype.

However many XML files don’t come with an XSD, so PCSOFT went a step further and lets us import a sample XML file as an external description, and it uses that sample file to again provide us with IntelliSense, etc.

Why am I talking about XML? Because PCSOFT lets us do the same thing for JSON files!

In our upcoming Quickbooks Online API, one of the obvious items we need to retrieve is the CompanyInfo the JSON for that looks like this

    "SyncToken": "4", 
    "domain": "QBO", 
    "LegalAddr": {
      "City": "Mountain View", 
      "Country": "US", 
      "Line1": "2500 Garcia Ave", 
      "PostalCode": "94043", 
      "CountrySubDivisionCode": "CA", 
      "Id": "1"
    "SupportedLanguages": "en", 
    "CompanyName": "Larry's Bakery", 
    "Country": "US", 
    "CompanyAddr": {
      "City": "Mountain View", 
      "Country": "US", 
      "Line1": "2500 Garcia Ave", 
      "PostalCode": "94043", 
      "CountrySubDivisionCode": "CA", 
      "Id": "1"
    "sparse": false, 
    "Id": "1", 
    "WebAddr": {}, 
    "FiscalYearStartMonth": "January", 
    "CustomerCommunicationAddr": {
      "City": "Mountain View", 
      "Country": "US", 
      "Line1": "2500 Garcia Ave", 
      "PostalCode": "94043", 
      "CountrySubDivisionCode": "CA", 
      "Id": "1"
    "PrimaryPhone": {
      "FreeFormNumber": "(650)944-4444"
    "LegalName": "Larry's Bakery", 
    "CompanyStartDate": "2015-06-05", 
    "Email": {
      "Address": ""
    "NameValue": [
        "Name": "NeoEnabled", 
        "Value": "true"
        "Name": "IndustryType", 
        "Value": "Bread and Bakery Product Manufacturing"
        "Name": "IndustryCode", 
        "Value": "31181"
        "Name": "SubscriptionStatus", 
        "Value": "PAID"
        "Name": "OfferingSku", 
        "Value": "QuickBooks Online Plus"
        "Name": "PayrollFeature", 
        "Value": "true"
        "Name": "AccountantFeature", 
        "Value": "false"
    "MetaData": {
      "CreateTime": "2015-06-05T13:55:54-07:00", 
      "LastUpdatedTime": "2015-07-06T08:51:50-07:00"

We can retrieve that via the Webservice and put it into a JSON data type and use it similar to this:

2020-04-26 13_37_53-Desktop

Notice CompanyName is a darker green than the rest of the line? That is because there was no IntelliSense and because WL doesn’t know what members might be in my JSON datatype it can’t show it as a compile error, but the darker green at least indicates to me that this variable may or may not exists at runtime, which means this line can blow up at runtime! This is a very common issue when working with web services, it is very easy to introduce a bug that doesn’t show up until runtime because of a simple type on a variable name.

Now watch what happens when we add an external description

2020-04-26 13_43_45-Desktop

Now as I start typing IntelliSense kicks in and shows me the available members, helping me avoid a simple typo that will lead to a runtime issue. When I complete the line notice it is in yet a different shade of green indicating that it is a known member.

2020-04-26 13_45_15-Desktop

I can still type a member name that isn’t in the external description and WL won’t indicate it as a compile error, it is still technically possible for it to exists at runtime, however, now it shows it with extra squiggles to indicate that it is more likely to be a runtime error.

2020-04-26 13_48_33-Desktop

I am sure by this point I have convince you of the virtues of external descriptions and you are wondering how to get some of this WL love for yourself. Well, it’s very easy to do, from the Project Explorer Right Click on “External descriptions” and select “Import an XML or JSON into this project…”

2020-04-26 13_50_53-Desktop

As you can see you can even expand the descriptions and see the details, similar to how you can other objects in the project explorer.

2020-04-26 13_52_05-Desktop

You can even drag and drop from the project explorer to your code and it will create a stub of the code needed to define the variable, like so:

2020-04-26 13_54_33-Desktop

Now let’s look at some new JSON features with v25

#94 Ability to delete a member from a JSON variable

When working with JSON data, you quickly learn that just like SQL a null and an empty string are not the same thing. It doesn’t sound like a big deal but many REST services handle database updates based on the fields you include.

For example, you need to update just need to update the following object via a REST call, however, you know that the AccessToken value might have been updated by someone else.

2020-04-26 12_30_43-Desktop

You need to submit this object without the AccessToken member. If you were to do something like

tmpJSON.AccessToken = ""

You would be updating the AccessToken to an empty string, which isn’t what you want. Prior to v25 this meant you had to build another JSON object and not include the AccessToken in it, coping all the values that you do want to update from the original object. Something like this

newJSON is JSON 
newJSON.RealmID = tmpJSON.RealmID
newJSON.AccessTokenExpiration = tmpJSON.AccessTokenExpiration
newJSON.RefreshToken = tmpJSON.RefreshToken
newJSON.RefreshTokenExpiration = tmpJSON.RefreshTokenExpiration

Imagine if there were 20 or more fields you need to update! v25 to the rescue, now all we need to do is

tmpJSON.AccessToken..Exist = False

And now our JSON object looks like this

2020-04-26 12_39_43-Desktop

This is just one simple example, but there are all kinds of scenarios when working with web services, where this will come in handy!


These new functions do exactly what you would expect them to do, and will come in handy when you are needing to store JSON in a database field or get JSON data as text from some other source and need to get it into a native JSON source where you can work with the individual members.

#823 JSON datatype available in browser code

This is huge! Especially when dealing with payment processing API’s. More and more those PCI compliance is pushing web services toward doing all processing in the browser so that the credit card information never even passes through our server. Knowing that most web services these days use JSON data, it doesn’t take much imagination to understand how having a native JSON datatype in browser code can pay off!

#959 Webservice parameter now directly accept JSON data types

Not to beat a dead horse, but most web services communicate with JSON data. Previously this mean you had to build the JSON data as a string. Which wasn’t pretty without the other new feature of JSONTOSTRING. It either involved literally building it as a string with text replacement or using a Variant data type and using VARIANTTOJSON. Now you can send a JSON data type is handled directly by the web services.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s