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.

The form uses the DoNotSort handler to prevent sorting rows.

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. It is a typical 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 can 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 can use 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 db.css to the head, db.js at the bottom, and place the control like this:

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

Conclusion

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

Code Browser



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