JSON Forms

SaveToDB products support a special format for returned results.

Stored procedures can return a single cell containing a JSON object with the properties: parameters, rows, columns, and cells.

The client app generates parameter controls, rows, columns, and a table populated with cell values.

This approach allows for the creation of fully dynamic, editable forms, which are particularly useful for cube applications.

Here’s a sample of the returned result:

{"parameters":[
    {"name":"entity_id","value":1,"items":[
        {"id":1,"name":"Plant 01"},
        {"id":2,"name":"Plant 02"}
        ]},
    {"name":"category_id","value":1,"items":[
        {"id":1,"name":"Budget"},
        {"id":2,"name":"Actual"},
        {"id":3,"name":"Forecast"}
        ]},
    {"name":"year","value":2022,"items":[
        {"year":2022}
        ]}
    ],
"rows":[
    {"account_id":1,"name":"Sales"},
    {"account_id":2,"name":"Cost of sales"},
    {"account_id":3,"name":"Operating expenses"},
    {"account_id":4,"name":"Net Income"}
    ],
"columns":[
    {"time_id":1,"name":"2022-01"},
    {"time_id":2,"name":"2022-02"},
    ...
    {"time_id":12,"name":"2022-12"}
    ],
"cells":[
    {"account_id":1,"time_id":1,"value":20000000.0000},
    {"account_id":1,"time_id":2,"value":20000000.0000},
    ...
    {"account_id":4,"time_id":12,"value":4000000.0000}
    ]
}

The parameters property contains an array of parameter objects, such as entity_id, category_id, and year.

Each parameter object includes the name, value, and items properties. The items property is an array of value objects.

Parameter values are consistent across all cells.

The rows and columns properties are arrays of axis objects.

Each axis item must include property values used in the cell items and properties used as headers.

In the example above, account_id and time_id are used in the cells, while the name property serves as the header.

The cells property is an array of cell objects. This array must contain at least one item.

Each cell item must include axis values and the cell value.

You can view the SQL Server stored procedure that returns the JSON result shown above here:

https://dbgate.savetodb.com/api/mssql-201/en-us/s20/usp_web_form_01/$definition

Here’s a sample call and its result for DBGate:

<https://dbgate.savetodb.com/api/mssql-201/en-us/s20/usp_web_form_01?entity_id=1&category_id=1&year=2022>

Here’s a sample call and its result for ODataDB:

https://odatadb.savetodb.com/v4/mssql-201/default/en-us/usp_web_form_01(entity_id=1,category_id=1,year=2022)

SaveToDB and DBEdit make standard stored procedure calls.

Developers must mark these procedures with the JsonForm event type in the xls.handlers table.

For example:

DBGate and ODataDB built-in JavaScript clients automatically detect JSON forms.

JSON forms are native and offer the same configurable features as other objects.

Notably, these dynamic forms can have simple change handlers.

They can utilize parameters with names corresponding to the parameter, row, column, and cell properties.

For example, check out the automatically linked change handler for this sample:

https://dbgate.savetodb.com/api/mssql-201/en-us/s20/usp_web_form_01_change/$definition

You can try this sample:

• Using Wizards, Online Samples, Sample 20 - Cube App in the SaveToDB add-in
• Using File, New from Samples..., Sample 20 - Cube App in DBEdit
• Using the online ODataDB Sample 20 - Cube App
• Using the online DBGate Sample 20 - Cube App