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.

automatization module

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

create json / xml designer template

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

json item template populated

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.

json item template drag and move blocks

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

json item template defining element type

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.

json item template tree-view menu

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.

json item template remove elements

Start from scratch

json item template clear all

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.

json item template adding root elements

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.

json item template mapping dynamic content

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.

json item template object type

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

json item template

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.

json item template array type

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.

json item template custom array

The custom array table source is similar to fixed content.

json item template custom array table

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.

json item template array table

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.

json item template array element

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.

json item template

json item template

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

json item template

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.

JSON example

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

copy paste to JSON designer

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.

link JSON elements

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.

chat gpt creating JSON file

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

chat gpt creating JSON file

chat gpt creating JSON file

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.

using JSON template

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.

using JSON template
using JSON template

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.

using JSON template in on-demand blockly script

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.

using JSON template in on-demand blockly script

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.

complex JSON structure

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”.

complex JSON structure in blockly script

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”.

complex JSON structure in blockly script

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.

complex JSON structure in blockly script

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”.

date and time in JSON designer

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.

date and time in JSON designer

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).

date and time in JSON designer

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.

date and time in JSON designer

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

date and time in JSON designer

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.

XML 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.

XML format

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.

XML format

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.

XML format

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.

XML format in blockly scripts

Start NOW

Read More

Related Posts

General Data Protection Regulation (GDPR) in ERPAG

General Data Protection Regulation (GDPR) in ERPAG

Until few years back, GDPR strictly meant Gross Domestic Product of Region. But, since 2016 (in Europe) and 2018 (in California, USA) GDPR abbreviation got a new meaning - General Data Protection Regulation. The goal of GDPR is to give you more control over how your...

read more
Conditional formatting in ERPAG

Conditional formatting in ERPAG

What is conditional formatting? Conditional formatting is a feature commonly found in spreadsheet software, such as Microsoft Excel, Google Sheets, and similar applications. It allows users to automatically apply formatting—such as font color, background color, font...

read more
ERPAG API reference documentation

ERPAG API reference documentation

Overview ERPAG offers a suite of APIs that allow you to use the pre-set default API endpoints as usable baselines, or you can refine them or create your own entirely new API endpoints to suit your needs. In order to successfully use the API, you need knowledge of...

read more