DBGate Sample 17 - Budget Request

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

Budget Request Sample

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.

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.

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.

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

You generate default values depending on an actual user also.

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.

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.

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>

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

Choose an object:

Object definition:

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