JSON / XML Designer

JSON/XML Designer in ERPAG is a visual tool that will allow users to transform data from the ERPAG database into JSON or XML format. This can be further used for future integration with API services. This tool provides a user-friendly interface where users can define the mapping and structure of the data they want to export.

By using the JSON/XML Designer, users can select the desired data fields, specifying the format, and configure any necessary transformations or validations. The tool generates the corresponding JSON or XML output based on the defined specifications. This transformed data can then be easily consumed by API services for various purposes. Such as data exchange, synchronization, or integration with external systems.

Overall, the JSON/XML Designer simplifies the process of converting ERPAG database data into JSON or XML format, making it convenient for users to leverage their data in API-based services.

JSON (JavaScript Object Notation) and XML (eXtensible Markup Language) are both widely used data interchange formats that allow the representation and exchange of structured data between different systems. This tool is one of the fundamental components of the ERPAG Smart API.

The ERPAG Smart API is a set of tools that enables users (even those with limited developer experience) to independently connect ERPAG with any API service.

This option, based on the data structure read from the database, is similar to the Report Designer. The difference is, of course, that instead of printing the data, the data is written to a file in JSON or XML format.

More info about The Report Designer:

This option is located in the Automatization / Customization -> Templates -> JSON / XML Designer.

When creating a document, it is necessary to determine its type (eg. Item, Sales order).

JSON and XML files essentially contain identical data. However, since JSON has gained popularity in recent years due to its simplicity, in this blog, we will primarily focus on explaining the creation of JSON. Towards the end of the blog, we will briefly explain the differences when creating data in XML format.

Start with pre-populated schema

When creating a new template document, the data is already pre-populated according to a default schema.

The designer view is divided into four sections, name, type, value, and preview.

Since some models can be complex, you have the option to expand/collapse elements. And, you can use the mouse to drag and move them. It is indicated by the red and green circles in the image below.

To add a new child element, click on the “+” button. This is where you also determine the type of element.

In JSON, there are several types of elements:

1. String: A sequence of characters enclosed in double quotes. Example: “Hello, World!”
2. Number: A numeric value. Example: 42 or 3.14159
3. Object: An unordered collection of key-value pairs enclosed in curly brackets. Example: {“name”: “John”, “age”: 30}
4. Array: An ordered list of values enclosed in square brackets. Example: [“apple”, “banana”, “orange”]
5. Boolean: Either true or false (Yes or No)

These are the basic types of elements in JSON. JSON allows nested structures; therefore, objects and arrays can contain other objects/ arrays, or a combination of different element types.

Mapping or assigning values is done using the “=” button.

Upon clicking, a tree view menu structure opens. In the bottom part of the blog, this option will be described in detail.

Removing an element is done simply by clicking on the trash icon. If the element has child elements, you will be prompted to confirm the removal.

Start from scratch

If you want to create your schema from scratch, you can reset it by clicking on the “Clear all” button.

Adding “root” elements is actioned through the buttons marked in green on the top image.

For string type elements, a value must be specified. The suggested value is “fixed content”. The input for fixed content is simply entered in the text input field.

For mapping dynamic content, you need to specify which field is assigned to the element value.

The behavior for “Number” and “Boolean” is almost identical, except that the input field for fixed content is tailored to the data type.

The “Object” type is different; it is used to add multiple different data to an element or to form a hierarchy (parent to child). The types are the same as those used at the “root” level.

Practically, there is no limit to the number of levels JSON objects can have.

The ARRAY TYPE

The Array type is a complex type because it contains an iterator. An array iterator is an object or mechanism that allows you to iterate over the elements of an array sequentially. An iterator (table source) can be a table from a document or a custom array.

With a custom array, you can determine the number of elements in the array yourself. The elements can be of any type. However, it is a good practice to have all elements of the same type.

The custom array table source is similar to fixed content.

If a table is specified, then the number of elements in the array will be the same as the number of rows in the table. The iterator will iterate over each row in the table and create a child element for each row.

Note: In the case of an array element from a table, it does not have a specific index number (represented as “[..]”) and always represents a single child element. However, with a custom array in the designer, each child element is displayed individually. On the top image, it is marked in green.

Of course, an array element can also be of the Object type, which means it can contain other elements.

To simplify the creation of elements, we have added a special “table” button. This is not a real JSON data type but a small wizard that creates JSON elements based on a table in the document.

If simply mark the fields, you will create corresponding elements with an array structure.

Creating a structure based on a JSON example

Most services that have API integration capabilities on their websites provide JSON structure examples for data exchange (Some even have a prepared “Copy” button). In our example, we will take the Invoice structure with XERO integration.

Simply copy the text and paste it into the JSON designer by using the “Paste JSON Text” option.

Based on that text, the JSON designer will create the structure. Since the example likely contains some sample data, you need to link the JSON element to the data from the ERPAG document.

The assistance of ChatGPT in creating a JSON structure

OpenAI, or ChatGPT, can be of great assistance in creating JSON structures, especially when the instructions are technically complex. The process is very simple: you write your request in the message box, and a generated text with a pre-prepared copy button will be provided to you.

The good thing is that below the example, there will be a description of almost every field.

All that remains is to paste the JSON in the JSON designer and connect it to the fields of the ERPAG tables.

Using a JSON template

The primary use of a JSON template is through the API block in Blockly Script, but there is also the option of manually downloading the generated JSON files.

Downloading is initially disabled for each template. To enable downloading, you need to determine which users have permission for it.

A single JSON file can be downloaded from within the document itself (as shown in the image below), while if you require multiple documents to be included in one JSON file, then you need to use the Bulk Action option.

Note: If multiple documents are combined into one file, the root JSON structure will be an array of individual documents.

As previously mentioned, the primary use is in Blockly Scripts, where JSON files are sent to a service via the API block.

In the JSON section of the menu, there will be a specific block where you can select the appropriate template.

Such blocks can be combined with other objects or added to specific JSON structures.

Note: Integration via API services is a complex process. You can find a simple example on this blog:

Complex JSON structures

Since our goal is to generate the appropriate JSON structure using JSON Designer, we have integrated Blockly Script into the designer. Blockly Script is used for data transformation using a logical sequence of instructions. For example, it can be used to summarize a tax list based on different tax rates, separating goods from services.

In the example shown in the image below, we have an iterator that goes through each item and, based on the product type and VAT rate, sums up the values into respective variables. These variables are then added to the JSON structure and the value is assigned to a variable named “tax_sum”.

After saving the Blockly Script, you can assign the corresponding value to the element by indicating that it is a value of the Blockly Script variable “tax_sum”.

Blockly variables can be of any type (String, Number, Array, JSON, etc.). If the parent element is of type Array and has its own table source and the variable is of type Array, the table iterator will be applied.

For simpler calculations, the JSON designer has the ability to include formulas for numeric values.

You can find more information about formulas in the JSON designer on this blog:

Date and time in JSON

JSON (and XML) does not have a specific data type for Dates, so date data is typically stored as a string. The problem arises when dealing with dates in different countries, as they follow different date formats (e.g., in Europe, the format is day, month, year, while in the USA, it is month, day, year). Another challenge is when data is sent via API services as different systems have different starting points for measuring time (e.g., the UNIX operating system counts dates from January 1, 1970). To address these issues, we have introduced a special option called “Formatted date”.

None: The default value for dates follows the US standard (en-US), which is month, day, year, and a 12-hour time format (AM/PM). The default date format can also be set for the entire document in the “Localization” option.

Any date field can be formatted according to the appropriate format. The format is entered as a text pattern or a specific format name (e.g. ISO, UNIX).

You can find more information about creating Date & time patterns and the formats used in different countries in this Wikipedia article https://en.wikipedia.org/wiki/Date_format_by_country )

In the input field, you can see a list of commonly used text patterns and predefined names as a convenient shortcut. For example, “ISO” represents the date format according to the ISO 8601 standard, while “UNIX” represents a numeric representation of the time elapsed since January 1, 1970.

Elements formatted in this way will be specifically marked with a “globe” icon.

Note: When designing a JSON or XML document, it is important to be familiar with the format required by the API service you are integrating with.

XML format

In recent years, JSON format has become dominant due to its simplicity for data interchange. However, XML format is still widely used, especially in services that have been in operation for more than 10 years (for example, XML is the base of the UBL standard for electronic document interchange).

Changing the format is simple, just specify the document type and the designer will adapt to the new format.

Both formats, JSON and XML, are tree-view structures, although in XML, all data is in string format. Additionally, XML elements can have additional attributes and can be grouped under namespace validators.

In the designer, to define attributes and namespaces just click on the settings icon (marked with a green arrow in the image above), which expands the element and allows for input.

You set the attribute values in the same way as setting the value of the element itself. It can be a fixed text or assigned a value from an ERPAG table.

Namespaces are a specific type of attribute that, in addition to having its own value (which is always fixed content), has the ability to be applied to all child elements within it.

Using XML generated files is very similar to using JSON files, with the difference that if you’re using them through Blockly Scripts, you need to select the appropriate format on the API block, depending on whether you’re working with XML or JSON.