The list has the ability to show child data in sub-lists.
For example, a customer list that has Contacts and 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-DataProviderWithSubLists template by following the steps here: 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 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.
The template adds five User 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.
It appears on top of the Main List and can be shown as a dialog form, or full screen using the Constant DWConstantFullScreen.
This forms main purpose is to show tabs so that sub forms of data can be shown.
In the template, there are 2 types of tabs, Details and Sub-Lists, with 2 of each added as examples.
If one of the Details tabs is clicked, the Macro SetAddEditDetail sets the form to show in the frame control, via a Constant.
If one of the SubList tabs is clicked, a Specification Host Control is shown with the DriveApp driveapp-list running in it for which you can specify your own data provider.
This form already has a background, OK and Cancel buttons, a header, a description and the two fields.
The background consists of 9 pictures, with rules for the positions so that if you change the size of the form, the background will always appear correct. If you change the Constant DWConstantFullScreen to be True, then the central background image will be made full screen and the border images will be hidden.
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
This is set to ensure that the value for Name is unique and not blank.
Change the rule for the variable DWVariableSaveEnabled to ensure that you have verified the data in your own form.
There is also a label on this form in the center at the bottom that you can use to inform your customers that data has changed.
This will only show if you are editing existing data and that data has changed. Delete this label if it isn’t required. If it is required, you will need to change the variable DWVariableHasDataChanged to compare current with new values.
The AddEditDetail1 and AddEditDetail2 forms are used for controls that will pull and push data to and from your parent database table. The AddEditDetail1 form has 2 example controls on it. Rename or delete these controls as required for your data. Add more controls to this form and the AddEditDetail2 form so that your users can add and edit data in your parent table.
The Confirmation Form is used to confirm selections. By default, these confirmations are:
The information on and behavior of this form are controlled using the Calculation Table - Confirmation Dialog.
If you wish to use this dialog form for any other confirmations, simply add a new row to the calculation table.
The calculation table lets you set 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 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 to search your table, as well as a refresh button that you can use to refresh the data coming from your Table.
If you add new filtering controls, make sure you use these in the variable DWVariableSQLSelect for selecting data.
The Filter form is also the main form that is shown for your data provider when running in a List.
This form also contains 2 overlays used to gray out the form when the dialogs are shown. There are also 2 Specification Host controls used to host the 2 SubLists available in this template. They are on this form so that the overlay controls can obscure everything else when they are in use.
The first set of Variables are for storing the selected item in the list (The selected item ID 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.
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) ,"")
The last argument in this rule is the default value to use if the lookup cannot find the data.
Use an appropriate value for your data type and form control type, so for instance if the field is a string, use an empty string, however if you are using an integer linked to a spin button, the default value could be the minimum value of your spin button.
Your project has a number of Variables that should be modified to pull and push your data.
This Variable needs to be the full connection string for your database.
The easiest way to build this rule is to use the wizard for the QueryData function (or other query functions) and copy the connection string.
An example of the connection string is:
Use this when using SQL Authentication.
Use this when using SQL Authentication.
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.
The Variable SQLInsert is a SQL string used to insert a new row into your database table.
You are in complete 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 [Parents] ([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 Parents.
The Substitute function is used to avoid SQL Injection.
The Variable SQLSelectNew is used to retrieve the ID of the newly inserted Parent record so that you don’t have to close the new item and open it again in order to add Child data.
You will need to change this to include all of the fields (Apart from the ID) that you have inserted, that make the record unique.
An alternative to this would be to change the SQLInsert string to be a stored procedure that returns the new ID.
The Variable SQLUpdate is used to update data in your 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 your fields. The Substitute function is used to avoid SQL Injection.
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 [Parents] WHERE [ID] = '@(DWVariableSelectedItemID)'"
These Variables are used for the SQL statements required to delete child data linked to the Parent Data being deleted.
Running these ensures that you don’t leave orphaned Child data when a Parent is deleted.
You need one of these Variables for each sub-list you have.
Modify these Variables based on the names of your Child Data tables and the ID fields in those tables that link them to the Parent.
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.
This Variable is used to get the total number of unfiltered records from your table.
You would need to change the table name.
QueryData(DWVariableSQLConnectionString,@"SELECT Count(1) FROM [Parents]" & DWConstantRefreshTrigger,DWVariableSQLUser,DWVariableSQLPassword)
The ExistingNames Variable is used to help validate data entry to ensure that you are preventing your user from adding duplicate records.
Change the SQL string in this rule to get values from your database table that you do not want to duplicate.
This Variable is used to enable and disable the OK button when adding and editing items.
QueryData(DWVariableSQLConnectionString,@"SELECT [Name] FROM Parents]" & DWConstantRefreshTrigger,DWVariableSQLUser,DWVariableSQLPassword)
The Variable RawData uses the Variable SQLSelect to get data from your Parent database table.
DbQuery(DWVariableSQLConnectionString,DWVariableSQLSelect & DWConstantRefreshTrigger,DWVariableSQLUser,DWVariableSQLPassword,TRUE)
These Variables are used to get the number of records in 2 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.
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 an 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 Confirmation dialog sizes are controlled using 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 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.
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:
Use this as a single value, or pipebar delimited list to set the justification of your column(s).
Use this as a single value, or pipebar delimited list to set the width of your column(s).
Use this as a single value, or pipebar delimited list to set which columns are hidden from your list.
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 the Specification Macros that the dataprovider needs, although you can add more if required.
The 3 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 Category Data are the ones that pull and push the data to and from your Database Table.
These do not need to be changed.
They run the SQL statements from Variables.
The Macro SetMode needs updating.
It may look complicated, but its split into 4 sections, depending on whether we are Adding, Editing, Deleting or Filtering.
This macro has 4 sections:
There is 1 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.
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).
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.
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 ClearLists macro is run when the DataProvider is closed, and makes sure that the projects showing the sub-lists are also closed.
Only change this if you add or delete (or change the name of) the sub-list specification host controls.
The macro SetAddEditDetail runs when one of the Details tab buttons is clicked and sets the correct Details form in the frame control.
It also sets the Mode to Edit if a List was previously being shown.
The SetList macro runs when one of the List tab buttons is clicked and ensures that the correct Specification Host control has the correct project loaded with the correct data.
Before doing this, it checks if the Parent is currently being added, or edited.
In order to show Child data in a sub-list, the new Parent does need to be saved, so there is a condition to make sure this happens.
If the Parent is currently being added, it will run the SetConfirmation macro with an argument of ConfirmNewTab and then store the list name in a Constant.
The Confirmation Dialog will then control which macro runs (Cancel or Confirm).
If cancelled, then the record is not saved and the list is not shown.
If Confirm is selected, then the macro SetListConfirm is run.
The SetListConfirm macro will save a new Parent record, store the new ID and then re-run the SetList macro which loads the Child sublist data in a Specification Host control.
If the record is being edited when the SetList macro is run, then a check is made to see if the Specification Host already has its project loaded, and if not, loads it.
The project loaded is always DriveApp-List which has its Child data provider set in a related Calculation table.
There is a simple table called SubLists.
This table has one row for each SubList you want to run.
By default, it has 2 rows.
Column A is the name of the Specification Host control that is used for the sub list.
Column B is the name of the list that you have set up in the DriveApp Setup List Administration.
These tables are linked to the Specification Host controls to pass data to them.
The record for Data in each table itself a lookup to the simple table SubLists which relates Specification Host control names to the Lists they should run.
Since each of these is also a data provider, they will need an entry of their own in the List Administration section of the DriveApp Set up.
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 debugging 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.