JSON Forms

SaveToDB products support a special form of returned results.

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

The client app creates parameter controls, rows, columns, and a table filled with cell values.

This way allows creating completely dynamic editable forms especially useful for cube apps.

Here is 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 expences"},
    {"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 like entity_id, category_id, and year in the sample.

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

Parameter values are common for all cells.

The rows and columns properties are arrays of axis object items.

Each axis item must contain 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 is used for headers.

The cells property is an array of cell object items. The array must have at least one item.

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

You can take a look at the SQL Server stored procedure that returns the JSON result shown above.

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

Here is 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 is 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 a regular stored procedure call.

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

For example:

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

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

Especially interesting, such dynamic forms can have quite simple change handlers.

They can use parameters with the names of the parameter, row, column, and cell properties.

For example, take a look to the automatically linked change handler of 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 online ODataDB Sample 20 - Cube App
• Using online DBGate Sample 20 - Cube App