DriveWorks Pro 20

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.

The first step is to create a new Project using the List-DataProvider 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.

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 unique and not blank.

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 will only show when editing existing data and that data has changed, delete this Label if it is not required.

If it is required, change the Variable DWVariableDataHasChanged to compare current with new values.

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.

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

A variable will be required for each field in the database table that will appear on the AddEdit form.

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

Use a similar rule to the rule for DWVariableName to get the correct value from the Database table.

For example, change the Name text to be the name of the database table field.

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

The last Argument in this Rule is the Default Value to use if the LookUp cannot find the data.

Use an appropriate value for the data type and Form Control type, for instance, if the field is a String, use an empty String.

However, if using an integer linked to a Spin Button, the Default Value could be the minimum value of the Spin Button.

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 itself should include all fields required in the list, in the correct order.

  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.

  Columns can be hidden, include the identity field from the table using a specification property called DataProviderHiddenColumns and build 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 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 [Parents] 
     (
      [Name], 
      [Value]
     ) 
  OUTPUT
  	Inserted.[Id]
  VALUES 
      (
       '@(Substitute(NameReturn,"'","''"))', 
       '@(Substitute(ValueReturn,"'","''"))'
      )"

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

  The Substitute function is used to avoid SQL Injection.

• SQLUpdate

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

  The default template rule is:

  @"UPDATE [Parents] 
  SET [Name] = '@(Substitute(NameReturn,"'","''"))', [Value] = '@(Substitute(ValueReturn,"'","''"))' 
  WHERE Id = '@(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.

  This should be modified to use the Field name of the unique ID Column or Primary Key.

  @"DELETE FROM [Parents] WHERE [ID] = '@(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.

  QueryData(DWVariableSQLConnectionString,@"SELECT Count(1)
    FROM [Parents]" & 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.

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 FullScreen which can be toggled between True and False.

Setting FullScreen to True changes the AddEdit Form to take up all of the available width and height rather than running 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:

• 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.

• 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.

These do not need to be changed.

These run the SQL statements from Variables.

Navigation Category

The Macro SetMode requires updating.

This Macro is split into 4 sections, depending on whether Adding, Editing, Deleting or Filtering.

This macro has four sections:

• Tasks that run when selecting to add a new record
• Tasks that run when selecting to edit an existing record
• Tasks for deletion
• A reset Task that also runs when filtering

There is one other possible mode that needs no Tasks here, called List.

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.

Deleting a Record

This runs the SetConfirmation Macro with an Argument of ConfirmDelete.

This shows the Confirmation Dialog Form with the data coming from the ConfirmDelete row of the Calculation Table ConfirmationDialog.

The confirmation Dialog Macros will be responsible for actually running the DeleteItem Macro.

The CancelAddEdit Macro will run when cancel edit is clicked.

If the Parent data is being edited and it has changed, it will show the Confirmation Form.

If not, it will set the Mode to Filter.

The Macro SetForm controls which form is displayed and drives DWConstantTabSelected based on the number extracted from the Macro Caller that triggers this Macro. It also sets the Mode to Edit if a List was previously being shown.

The SetList Macro runs when one of the Sub List tab buttons are clicked. This ensures that the correct Specification Host Control in the Host Specification has the correct Project loaded with the correct data.

The SetListConfirm Macro will save a new Parent record, store the new ID and then re-run the SetList Macro the Sub List data.

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.

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.