DriveWorks Pro 21

List Data Provider

List Data Provider

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

The template can show lists within lists, these are referred to as sub-lists.

For example, a customer list can display contacts and all corresponding orders as sub-lists.

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.

Using the List Data Provider Template

The first step is to create a new Project using the List Data Provider template by following the steps in the topic Create a new project from a Project Template.

The next step is to consume the data provider by adding it to the List Manager inside the Dashboard or CPQ setup.

The third step is to add the list to a dashboard, so users can access it. See Dashboard: Edit Dashboards or CPQ: Edit Dashboards for more information.

Now the data provider is linked to a dashboard, the project can be updated to manage the data.

The most logical approach is to this in the following order:

1. Add or Edit the Forms and the Controls.
2. Edit the ItemData Calculation Table.
3. Edit the Variables.

This template 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 List Data Provider Project to pull and push data from SQL.

Forms

The template adds six forms to the Project:

• AddEdit
• Add
• AddEditDetail1
• AddEditDetail2
• Confirmation
• Filter

AddEdit Form

The AddEdit form is the form that is shown when a new item is added to the list, or an existing item is edited and can be shown as a dialog form, or full screen using the Constant DWConstantFullScreen.

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.

This rule is used to ensure that the value for Name is not blank and in cases of editing an item, that the data has changed.

This rule can be modified to ensure the data in the new Form can be verified.

There is also a Label on this Form (center, bottom) that can be used to inform customers that data has changed. This uses the Variable DWVariableDataHasChanged.

This will only show when editing existing data and that data has changed, delete this Label if it is not required.

The Text rule for the AddEditHeader Label should be updated. The Column Name property of the TableGetColumnIndexByName function (set to "Name" in the template) is to be changed to the name of the data field to display in the header when editing an item.

AddEditDetail1 and AddEditDetail2 Forms

The AddEditDetail1 and AddEditDetail2 Forms are used for controls that will pull and push data to and from the parent database table.

The AddEditDetail1 Form has two example controls on it.

Rename or delete these controls as required.

Add more Controls to these Forms so users can add and edit data in the parent table.

Confirmation Form

The Confirmation Form is used to confirm selections, by default these confirmations are:

• Deletion
• Cancel Changes
• Save New Item

The information and behavior of this Form is controlled using the Calculation Table - Confirmation Dialog.

If this dialog form is to be used for any other confirmations, add a new row to the Calculation Table.

The Calculation Table sets the header and description as well as which Marcos are run when the OK and Cancel buttons are clicked.

This Form is shown in a Frame Control by running the Macro Set Confirmation which toggles the visibility and also sets the ConfirmationKey Constant, which references the first column in the Calculation Table.

Filter Form

The Filter Form appears at the top of the list.

Add any buttons required to manipulate or process the data.

Add any filtering or sorting controls required for the data.

There is already a search box Form Control that can be used to search the Table, as well as a refresh button that will refresh the data coming from the Table.

When adding new filtering Controls, make sure they are included in the Variable DWVariableSQLSelect for selecting data.

The Filter Form is also the main Form that is shown for the data provider when running in a list.

Calculation Tables

The List Data Provider includes three Calculation Tables:

• ItemData
• ConfirmationDialog
• SubLists

Item Data Calculation Table

The ItemData Calculation Table has the following columns:

• Control Name

  The name of the Control used to add/edit the data field. If the data field does not have a control, this column can be left blank.

• Field Name

  The field name in the database. If a field has been brought in as a different name using ‘as’, the new name should added to the Calculation Table.

• Alias

  An alternative name for the Control. In the template this is used in the Control Caption of the Name and Value Controls.

• Control Value

  The value that will be passed into the Control when an item is being edited. This rule should not be edited.

• Default Value

  The value that appears in the Control when adding a new item.

• ControlReturns

  The currently inputted value in the Controls. This is used in the DataHasChanged Variable. This rule should not be edited.

• Width

  The width of the column. If left blank, a default small width will be used.

• Justification

  The justification of the column text. If left blank, left will be used.

• Order

  The order that the data fields will appear in the List Table. If the field is not passed to the list or is not visible, the column can be left blank.

• Passed to List

  Whether the data field is passed to the list. In most cases this will be True. This should be false if the data field is not being added to, edited or deleted and therefore does not need to be passed to and displayed in the list.

• Visible

  Whether the data field is visible in the List Table.

• Index

  The column index of the field in the database table. This should not be edited.

Some of the columns contain column rules. These build the functionality of the List Data Provider and also return commonly used values. To overwrite these commonly used values, please build cell rules.

How it works:

• Each row in the Calculation Table represents a field in the data table – rows should be added for ALL fields (that are brought in using the SQLSelect Statement), including fields that do not have Form Controls to edit their values.
• The template includes 4 rows:
  • ID – the data field acting as the unique identifier. It is used to within the SQL queries to update and delete items within the list. This value is passed to the list but is not visible.
  • Name – an example data field.
  • Value – an example data field.
  • Not Passed to List Example – example of a data field that is not passed to the list, is not displayed and does not have a Form Control. This is to show how the table should be filled in for a field that is only brought into DriveWorks and is not being passed to the list. This row can be deleted.
• If the Name and Value Controls (controls included in the template) name’s are changed the Control Name values will need to be updated in each row.
• If new controls are added for data fields in the database table new rows will need to be added to the Calculation Table for each field.
• The Constant UniqueIdentifierField needs to be set to the name of the field within the database table that will act as the unique identifier. Without this the template will not work. In the template this is set to ID.

SubLists Calculation Table

Each row in this table represents a tab which needs to run a Sub List when clicked. By default, there are 3 Sub List tabs in this Template, but this can be modified in the Form Designer and in this Calculation Table as required.

The columns that should be modified are:

• TabNumber

  The index of the corresponding tab on the form, from left to right e.g. the tab ‘Sub List 2’ is fourth from the left and is called Tab4, so its TabNumber is 4.

• ListName

  Each Sub List in this table will need an entry of its own in the List Administration section of the DriveApp Settings. The name specified for each list should be specified in the corresponding row in this column.

• PositionType

  This controls how the list is displayed when its tab is clicked. "FullScreen" will display the Sub List at the maximum size in the UI. "Cropped" will display Sub List in a windowed view.

• Top

  This column should only be used if a tab is using the "Cropped" PositionType and controls the vertical offset from the top of the DriveApp UI. You can use the existing Rules in the default "Cropped" tabs or create your own.

• Left

  This column should only be used if a tab is using the "Cropped" PositionType and controls the horizontal offset from the left of the DriveApp UI. You can use the existing Rules in the default "Cropped" tabs or create your own.

• Width

  This column should only be used if a tab is using the "Cropped" PositionType and controls the width of the Sub List in your DriveApp UI. You can use the existing Rules in the default "Cropped" tabs or create your own.

• Height

  This column should only be used if a tab is using the "Cropped" PositionType and controls the height of the Sub List in your DriveApp UI. You can use the existing Rules in the default "Cropped" tabs or create your own.

• Visible

  This column controls the Visible property of each Sub List. You should copy the existing Rules in the default tab rows but update the numeric value for DWConstantTabSelected to match the index of the corresponding tab.

• ShowButtonsOverlayOnAddEdit

  This column determines whether the overlay should be shown over the tab UI when the sub list being shown is edited.

ConfimationDialog Calculation Table

This Calculation Table creates the different sections of text used in the confirmation window.

The Text rule for ConfirmDelete (DWCalcConfirmationDialog.TextRule1) needs to be updated – the Column Name property of the TableGetColumnIndexByName function (set to "Name" in the template) needs to be changed to the name of the data field which the user wants to display in the confirmation dialog when an item is deleted.

Variables

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

The important variable is SelectedItemID.

This is populated by the list when a row is selected.

The field used is the field set in the UniqueIdentifierField Constant.

Database SQL Variables

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

• SQLConnectionString

  This variable needs to be the full connection string for the database.

  The easiest way to build this rule is to 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;

• SQLUser

  Use this if using SQL Authentication.

• SQLPassword

  Use this if using SQL Authentication.

• SQLSelect

  This is the SQL string that will pull the data from the database table.

  To assist we have included a search box form control as well as a paging variable (if paging is required in the list).

  If paging is not required in the list, remove the variable DWVariableSQLOffset from this rule.

  The select statement should include all fields required in the list.

  Also include, as the first column, an identifying field, usually the primary key in the table.

  It should also include any fields the user is required to add and edit.

  Database fields can be brought into DriveWorks with different names using [FieldName] as [AlternateFieldName], as seen in the template.

  If any field names are changed, the new names need to be used in the ItemData Calculation Table not the name in the actual database.

• SQLInsert

  The variable SQLInsert is a SQL string used to insert a new row into the database table.

  Take care of what will be inserted into the database.

  Also ensure SQL injection is prevented.

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

  The default rule in the template is:

  @"INSERT INTO [Table Name] 
     (
      [Name], 
      [Value]
     ) 
  OUTPUT
  	Inserted.[Id]
  VALUES 
      (
       '@(Substitute(NameReturn,"'","''"))', 
       '@(Substitute(ValueReturn,"'","''"))'
      )"

  Change Table Name to the name of your database table.

  This would be used for inserting a new row with Name and Value fields into the database.

  The Substitute function is used to avoid SQL Injection.

• SQLUpdate

  This variable is used to update data in the database table for the selected row.

  The default template rule is:

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

  Update this for the fields being inserted.

  The Substitute function is used to avoid SQL Injection.

• SQLDelete

  This variable is used to delete the selected item from the database table.

  @"DELETE FROM [Table Name] WHERE [@(DWConstantUniqueIdentifierField)] = '@(DWVariableSelectedItemID)'"
      

• SQLDeleteSubList1 and SQLDeleteSubList2

  These Variables are used for the SQL statements required to delete child data linked to the parent data being deleted.

  Running these ensures orphaned child data does not remain when a parent is deleted.

  One of these Variables is required for each sub-list.

  Modify these Variables based on the names of the child data tables and the ID fields in those tables that link them to the parent.

• SQLOffset

  This variable is used to page the data.

  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 the table.

  Change the Table name to match your data.

  DbQuery(DWVariableSQLConnectionString,@"SELECT Count(1)
    FROM [Table Name]" & DWConstantRefreshTrigger,DWVariableSQLUser,DWVariableSQLPassword)

• RawData

  The Variable RawData uses the Variable SQLSelect to get data from your Parent database table.

  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)

• SubList1Count and SubList2Count

  These Variables are used to get the number of records in two sub-lists based on the ParentID.

  They are only used when deleting a Parent to let the user know that Child data will also be deleted.

  Update these SQL statements to reflect the number of Child records each Parent has in each sub-list.

  These two Variables can be deleted if no SubLists are being used.

  If deleted, the Confirm Delete Text Rule in the ConfirmationDialog Calculation Table will need to be updated to account for this.

Refreshing the 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 with the exception of UniqueIdentifierField and FullScreen.

• The UniqueIdentifierField needs to be set to the field within your database table that is being used to identify each individual record in the table. This is similar to the Primary Key of a database table. In the template this is set to ID.
• Setting FullScreen to True changes the AddEdit Form to take up all of the available width and height. Setting it to False runs it as a dialog style Form.

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:

• DataProviderHideFooter

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

• DataProviderDefaultRowsPerPage

  This controls the default number of rows shown in the List.

Specification Macros

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

Confirmation Category

The three Macros in the Confirmation category control the behavior of the buttons on the Confirmation Form.

The SetConfirmation macro hides and shows the Confirmation Form in a Frame Control with the correct information based on the Macro Argument.

The Cancel Macro runs the Macro specified in the ConfirmationDialog Calculation Table from the Cancel Macro column, using an Argument from the Cancel MacroArg column.

The Confirm Macro runs the macro specified in the ConfirmationDialog Calculation Table from the Confirm Macro column, using an Argument from the Confirm MacroArg column.

Data Category

The Macros in the Data Category are the ones that pull and push the data to and from your Database Table.

They also drive values into the Form Controls when items are added or edited.

These do not need to be changed.

These run the SQL statements from Variables.

Navigation Category

The Macros in the Navigation Category are used to control the Forms that are displayed when navigating through the List Data Provider.

They also call the various other Specification Macros within the Project that create its functionality.

These do not need to be edited.

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.