DBGate Sample 17 - Budget Request

This example shows to create forms like budget requests in the Gartle Budgeting application.

Budget Request Sample

Expanded Rows and Fixed Row Order

The s17.usp_request has the @rows parameter, and it returns the required number of rows. Take a look at the procedure source code.

To prevent sorting rows, the form uses the DoNotSort handler.

To keep the user's row order, the s17.usp_request_insert and s17.usp_request_update procedures uses the @row_index and @rownum parameters.

The @row_index parameter gets a value from the row_index column. This is a regular case.

The @rownum parameter has a special meaning in DBGate. DBGate passes the actual row number in this case.

So, when a user deletes or inserts a row, DBGate saves changes for all rows below have new row indexes.

Saving Changes

The s17.usp_request procedure has procedures used to save changes: s17.usp_request_insert, s17.usp_request_update, and s17.usp_request_delete.

DBGate detects such procedures automatically, and this case does not require any configuration.

As mentioned above, these procedures use the special @rownum parameter.

Also, they use a special @transaction_start_time parameter that contains a start time of the transaction.

Parameter and Cell Drop-down Values

The sample loads values from tables configured in the xls.handlers table. See the ParameterValues and ValidationList handlers.

Of course, developers may specify stored procedures or views instead.

Take a look at the $metadata to get details.

Default Values

This sample has constant default values configured in the xls.handlers table.

You generate default values depending on an actual user also.

Default and Conditional Formatting

This sample uses two types of formatting rules:

You may the SaveToDB add-in to save Excel table formats. Use the Save, Save Table Format menu item.

DBGate loads Excel formats for the first connection only. It hides hidden columns and sets column number formats.

For the second type of format, the DBGate client loads such formats every time.

You can use a free version of the SaveToDB add-in to convert Excel conditional formatting to such formats.

Use Wizards, Developer Tools, Show Table Format.

Calculated columns

This form contains the total column calculated by the formula defined in the s17.usp_request procedure.

Note that DBGate supports Excel formulas that use column names and JavaScript-supported operators.

Embedding Forms

To embed the form, add odatadb.css to the head, odatadb.js at the bottom, and place the control like this:

<div class="dbgate-control" data-url="v4/mssql-171/en-us/s17/usp_request"></div>


DBGate has a lot of features. Feel free to contact us to get the assistance.

Code Browser

You may download samples for any supported database platform with the SaveToDB SDK.