DBGate Sample 17 - Budget Request

This example demonstrates how to create forms for budget requests.

Budget Request Sample

The s17.usp_request procedure has an @rows parameter that returns the required number of rows. Check out the procedure source code.

The form uses the DoNotSort handler to prevent row sorting.

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

The @row_index parameter retrieves a value from the row_index column, which is standard practice.

The @rownum parameter has a specific meaning in DBGate, where it passes the actual row number.

When a user deletes or inserts a row, DBGate updates the row indexes for all rows below the modified row.

DBGate automatically detects these procedures, so no additional configuration is necessary.

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

They also include a @transaction_start_time parameter that indicates the transaction's start time.

This sample loads values from tables configured in the xls.handlers table. Refer to the ParameterValues and ValidationList handlers.

Developers can also specify stored procedures or views instead.

Check the $metadata for more details.

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

You can generate default values based on the current user as well.

This sample employs two types of formatting rules:

The Excel table format saved in the xls.formats table using the SaveToDB add-in.
The table format saved in the xls.handlers table.

You can use the SaveToDB add-in to save Excel table formats. Just select the Save, Save Table Format menu item.

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

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

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

Utilize Wizards, Developer Tools, and Show Table Format.

This form includes a 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-compatible operators.

To embed the form, add db.css to the head, db.js at the bottom, and place the control as follows:

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

DBGate offers a wide range of features. Feel free to reach out for assistance.

Choose an object:

Object definition:

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