DriveWorks Pro 22

How To: Force a data refresh when data has changed (KB13103011)

How To: Force a data refresh when the data has changed

When using a function to display database data on a user form, the data is loaded when the user form is loaded.

If the data gets updated (by using the DBExecute function for instance) the displayed data will not refresh unless one of it's arguments changes.

This article demonstrates how the functions QueryData and QueryDataValues can be modified to trigger an automatic refresh.

Summary

This example uses a Data Table control on a user form to display data from a database.

The Items Property for this control uses the QueryDataValues function to retrieve the database data as an array.

The user form accepts an entry, in a textbox control, for the user to add a record to the database.

The data entered into the textbox is passed into the database by clicking a Macro Button which executes a Specification Macro.

The Specification Macro uses the Drive Constant Value task to update a constant in the project with a new value. This task can run the DBExecute function to update the database with the data entered into the textbox.

The constant being populated by the Macro Button is used in the function used in the Items Property of the Data Table control to force a refresh as soon as the macro button is clicked.

Create a Constant to be Driven

A constant is required so it can be updated when the macro button is clicked.

1. Open the project in DriveWorks Administrator and go to Define Constants.
2. Add a constant and enter the name RefreshData.
3. Click OK.
4. Enter a value of 1 for the constant.

Create a Specification Macro

The macro will export data to the database and update the constant.

1. In DriveWorks Administrator go to Specification Macros.
2. Add a new specification macro and enter the name Execute.
3. Click OK.
4. With the Execute macro selected drag the Drive Constant Value task from the Toolbox into the Tasks area.

We will return to this task, once the form controls are in place, to add the properties.

Create the User Form Controls

The user form controls need to be added that will:

• Display the data from the database.
• Allow data to be entered that will be inserted into the database.
• Trigger the specification macro.

First add the control that will display the data.

1. In DriveWorks Administrator go to Form Design and select the form to add the controls to.
2. Click the Text Box icon and enter the name NewRecord.
3. Click OK to add the control and drag it to the required position and size.
4. Click the More... icon and select the Macro Button icon.
5. Enter the name Execute and click OK to add the control. Drag into position and size as required.
6. With the macro button selected, locate the Macro Name property, select and launch the Rule Builder.
7. From the Rules Builder command bar click the Macros... button.
8. From the Macros... dialog select the Execute macro, click OK.
9. Click the More... icon and select the Data Table icon.
10. Enter the name Data and click OK to add the control. Drag into position and size as required.

Apply a Rule to the Items Property

We will use the QueryDataValues function to retrieve the data from the database.

1. With the data table control select in the form designer, locate and select the Items Property and launch the Rule Builder.
2. Click the Functions button and select the QueryDataValues function, click OK to launch the wizard.
3. Go through the steps to connect to your database, table and retrieve the field to display.
4. Add a Select Statement Condition
   The field in the database to run the select statement condition should not contain data that is the same as the value being driven into the constant (i.e. a number).
   Choose the field to use, choose the does not equal operators (< >)and type DWConstantRefreshData. Continue through the wizard and click Finish to append the rule to the rule builder.

The rule will now look something like:

Where:

ServerName is the name of the Server hosting the database.

FieldName is the name of the Field to retrieve data from.

TableName is the name of the Table that contains the Field.

DWConstantRefreshData is the name of the constant added above that will cause the refresh.

Add the Properties to the Drive Constant Value Task

1. Go to Specification Macros, select the Execute macro and then select the Drive Constant Value task.
2. For the Constant Name property select DWConstantRefreshData.
3. For the Constant Value property, make this property dynamic and then launch the Rule Builder.
4. Click the Functions button and select the DBExecute function, click OK to launch the wizard.
5. Complete the required fields for the function.
   The required fields for this function can be extracted from the QueryData function wizard.

The rule will now look like:

Where:

ServerName is the name of the Server hosting the database.

Database is the name of the Database that holds the table.

TableName is the name of the Table the record will be inserted into.

NewRecordReturn is the name of the Text Box control added above that holds the data to be added to the database.