This topic applies to DriveWorks v20 SP3 only.
For earlier versions of this template, and the now obsolete List Data Provider with Sub-Lists template please refer to the in product help of the installed version.
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 Data Provider template by following the steps in the topic Create a new project from a Project Template.
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:
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.
The template adds six forms to the Project:
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.
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.
The Confirmation Form is used to confirm selections, by default these confirmations are:
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.
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.
The List Data Provider includes three Calculation Tables:
The ItemData Calculation Table has the following columns:
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.
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.
An alternative name for the Control. In the template this is used in the Control Caption of the Name and Value Controls.
The value that will be passed into the Control when an item is being edited. This rule should not be edited.
The value that appears in the Control when adding a new item.
The currently inputted value in the Controls. This is used in the DataHasChanged Variable. This rule should not be edited.
The width of the column. If left blank, a default small width will be used.
The justification of the column text. If left blank, left will be used.
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.
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.
Whether the data field is visible in the List Table.
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:
This Calculation Table is only required when the List is a Parent of a Sublist.
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:
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.
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.
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.
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.
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.
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.
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.
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.
This column determines whether the overlay should be shown over the tab UI when the sub list being shown is edited.
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.
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.
Do not edit these Variables.
The project has a number of variables that should be modified to pull and push the data.
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:
Use this if using SQL Authentication.
Use this if using SQL Authentication.
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).
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.
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.
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.
This variable is used to delete the selected item from the database table.
@"DELETE FROM [Table Name] WHERE [@(DWConstantUniqueIdentifierField)] = '@(DWVariableSelectedItemID)'"
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.
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.
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)
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)
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.
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.
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.
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.
Any new variables can be added without affecting the DataProvider.
All of the constants have comments explaining their purpose.
None of the existing Constants should be changed with the exception of UniqueIdentifierField and FullScreen.
Any new constants can be added without affecting the DataProvider.
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:
This controls the visibility of the List footer. Change this if you want to control the footer visibility.
This controls the default number of rows shown in the List.
The Template provides all of the Specification Macros that the Data Provider needs although you can add more if required.
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.
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.
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 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.