DriveWorks Pro 18: List Data Provider [send feedback...]

List Data Provider

The List Data Provider template is a project template that allows a custom data provider to be used with the lists in the Dashboard and CPQ DriveApps.

Data providers are a way to get data into a DriveApp so that users can add, edit and delete data from custom database tables or other third party systems.

The first step is to create a new Project using the List-DataProvider template by following the steps here: Create a new project from a Project Template.

The next step is to consume your data provider by adding it to the List Manager, this is explained here: List Manager.

The third step is to add your list to a dashboard. This is explained here: Edit Dashboards.

Now you have your data provider linked to a dashboard, you can update the project to manage your data.

The template data provider has been created to make it easy to push and pull data from a database. It can also be modified to pull and push data in other ways, for instance via a web service or api.

This topic will cover using your data provider project to pull and push data from SQL.

Forms

The template adds three forms to the Project:

  • AddEdit
  • Delete
  • Filter

AddEdit Form

In order to add and edit data for your table, you will need to add your own form controls to the form AddEdit.

The controls Name and Value are used to ensure the template works out of the box, but can be renamed or deleted.

Use any form controls you need on the form for collecting data.

This form already has a background, OK and Cancel buttons, a header, a description and the two fields.

Change the text of the header and description in the control properties to be whatever describes your data, (The form is used to both add and edit your tables data.)

The enabled property of the OK button is linked to a variable DWVariableSaveEnabled.

The rule for DWVariableSaveEnabled is set to the following in the template.

DWVariableValidName AND NameReturn <> "" AND FALSE

This is set to ensure that the value for Name is unique and not blank (and currently forced to be false for the purposes of it being a template).

Change the rule for the variable DWVariableSaveEnabled to ensure that you have verified the data in your own form.

Delete Form

The Delete form is used to verify deletion.

The Enabled property of the OK button is set to false in the template. Change this to True, or build a rule for it to help validate the deletion of your data.

Set the Header and Description labels to be text appropriate to your data.

Filter Form

The Filter form appears at the top of your list. Add whatever buttons you need to manipulate or process your data. Add any filtering or sorting controls that you need for your data. There is already a search box form control that can be used search your table, as well as a refresh button that you can use to refresh the data coming from your Table.

Variables

The first set of variables are for storing the selected item in the list (The list is provided to you as part of the Dashboard DriveApp)

The important variable is SelectedItemID. This is populated by the list as is the value returned to the list when a row is selected. The field used here is the first field in the RawData Variable.

You then need a variable for each field in your database table that will appear on the AddEdit form.

Use a naming convention so that you can easily match each of these variables to a control on your AddEdit form.

By default, the template has 2 form controls, Name and Value, therefore there are 2 variables by default, DWVariableName and DWVariableValue.

Use a similar rule to the rule for DWVariableName to get the correct value from your Database table. For example, change the Name text to be the name of your database table field.

IfError(
	VLookup(DWConstantSelectedID
		,DWVariableRawData
		,TableGetColumnIndexByName(DWVariableRawData,"Name")
		,FALSE)
	,"")

Database SQL Variables

Your project has a number of variables that should be modified to pull and push your data.

  • SQLConnection

    This variable needs to be the full connection string for your database. The easiest way to build this rule is if you use the wizard for querydata (or other query functions) and copy out the connection string.

    An example of the connection string is:

    Server=MyServerName;Database=MyDataBaseName;
  • SQLSelect

    This is the SQL string that will pull your data from the database table. You are in complete control of this statement, however to help you we have included a search box form control as well as a paging variable (If you want to make use of paging in your list.)

    If you don’t want to make use of paging in your list, remove the variable DWVariableSQLOffset from this rule.

    The select statement itself should include all of the fields that you want to see in the list, in the right order. You should also include, as the first column, an identifying field, usually the primary key in your table. It should also include any fields that you wish to have the user add and edit.

    You can hide columns, including the identity field from your table using a specification property called DataProviderHiddenColumns and building a rule for a single column header name or a pipebar delimited list of column names.
  • SQLInsert

    The variable SQLInsert is a SQL string used to insert a new row into your database table. You are incomplete control of what you insert. You are also responsible for preventing SQL injection.

    Our default rule in the template shows you how to do this.

    The default rule in the template is:

    @"INSERT INTO [MyTableName] 
    ([Name], [Value]) 
    VALUES 
    ('@(Substitute(NameReturn,"'","''"))', '@(Substitute(ValueReturn,"'","''"))')"
            

    This would be used for inserting a new row with Name and Value fields into a database table called MyTableName

  • SQLUpdate

    The variable SQLUpdate is used to update data in your database table for the selected row.

    The default template rule is:

    @"UPDATE [MyTableName] 
    SET [Name] = '@(Substitute(NameReturn,"'","''"))', [Value] = '@(Substitute(ValueReturn,"'","''"))' 
    WHERE Id = '@(DWVariableSelectedItemID)'"
        

    Update this for your fields. Use the Substitute rule to avoid SQL Injection.

  • SQLDelete

    This variable is used to delete the selected item from your database table. This needs modifying to use the Field name of your unique ID Column or primary key.

    @"DELETE FROM [MyTableName] WHERE [ID] = '@(DWVariableSelectedItemID)'"
        

    This variable is used to delete the selected item from your database table. This needs modifying to use the Field name of your unique ID Column or primary key.

  • SQLOffset

    This variable is used to Page your data if you wish to use paging. The rule is already set up to use the Records per Page quantity as well as the selected page from the list and does not need changing.

  • TotalCount

    This variable is used to get the total number of unfiltered records from your table. It is hard set to 3 in the template, although there is an example in the rule comment in the Rule Builder of the rule that you could use. You would need to change the table name.

    QueryData(DWVariableSQLConnectionString,@"SELECT Count(1)
      FROM [MyTableName]" & DWConstantRefreshTrigger,DWVariableSQLUser,DWVariableSQLPassword)
                  
  • ExistingNames

    The ExistingNames variable is used to help validate data entry to ensure that you are preventing your user from adding duplicate records.

    In the template this is set to be a Pipebar delimited list, but in the rule comment in the Rule Builder there is an example rule for querying existing Names directly from the database table.

    QueryData(DWVariableSQLConnectionString,@"SELECT [Name] FROM MyTableName]" & DWConstantRefreshTrigger,DWVariableSQLUser,DWVariableSQLPassword)
                  
  • RawData

    The template has an example rule for this that shows some static data, mainly so that the template shows some data in the DriveApp list as soon as it is consumed.

    The actual rule that is needed for this variable is in the rule comment in the Rule Builder.

    DbQuery(DWVariableSQLConnectionString,DWVariableSQLSelect & DWConstantRefreshTrigger,DWVariableSQLUser,DWVariableSQLPassword,TRUE)
            
  • SQLUser

    Use this when using SQL Authentication.

  • SQLPassword

    Use this when using SQL Authentication.

Refreshing your data.

When database changes are executed by DriveWorks, there needs to be a mechanism for re-running the DriveWorks rules for your database table. To help with this, there is a constant called DWConstantRefreshTrigger which is used in the SQL statements.

This constant can be driven by the List itself, or by the user by clicking on the refresh button on the Filter form.

This button runs a Macro that toggles this constant between being and empty string, and being a string with a single space.

By using this constant in all queries, DriveWorks will force a refresh of the data.

DriveAppStandards

All of the Variables in the DriveAppStandards Category are used to define the UI style and are passed down into your dataprovider by the DriveApp List. Leave these unchanged if you wish to adopt the style of your DriveApp.

UI

The variables in the category UI can be changed to update your User Interface preferences.

By default, the AddEdit and Delete dialog sizes are controlled from a SimpleTable via these variables.

New Variables

Any new variables can be added without affecting the DataProvider.

Constants

All of the constants have comments explaining their purpose. None of the existing Constants should be changed.

Any new constants can be added without affecting the DataProvider.

Specification Properties

There are a number of existing Specification Properties that are used to pass data to the hosting DriveApp List.

The only ones that should be changed are:

  • DataProviderColumnJustification

    Use this as a single value, or pipebar delimited list to set the justification of your column(s)

  • DataProviderColumnWidths

    Use this as a single value, or pipebar delimited list to set the width of your column(s)

  • DataProviderHiddenColumns

    Use this as a single value, or pipebar delimited list to set which columns are hidden from your list

  • DataProviderHideFooter

    This controls the visibility of the List footer. Change this if you want to control the footer visibility.

Specification Macros

The Template provides all of the Specification Macros that the dataprovider needs although you can add more if required.

ListDataProviderSpecMacros

The Macros in the Category Data are the ones that pull and push the data to and from your Database Table. These do not need to be changed.

The Macro SetMode however does need updating.

This macro has 2 sections, the tasks that run when selecting to add a new record, and the tasks that run when selecting to edit an existing record. These tasks run when you are viewing the list, and you click either the Add button or the Edit button to load the AddEdit form.

Adding a new record.

The purpose of these tasks are to clear (or set a default value for) the form controls ready for a new record to be added. You will need one ‘DriveControl’ task for each form control that needs clearing (Or setting to a default value).

Editing an existing record

The purpose of these tasks are to populate the form with the values for the selected record. You will need one ‘DriveControl’ task for each form control that needs setting.

Specification Flow

Specification Flow is not used under normal circumstances with DataProviders however there is a paused state in the Specification Flow by default. The specification is not stored and therefore is not available in the Specification Explorer.

If you need to be able to save the Specification of a custom DataProvider for debug and performance purposes, you would need to add a Button to your form to transition the specification to a paused state. You would then be able to open edit your specification from the Specification Explorer.

Table of Contents