Datasources

Datasources are used to allow your forms to store information entered by a user, to download and update data stored in an existing remote database or Web API An API is a set of functions and procedures allowing the creation of applications that access the features or data of an operating system, application, or other service. remote resource, and to provide values for Elements, such as a Drop List, where users are presented with a predefined list of options from which to choose.

A Datasource defines a connection to a Microsoft SQL Server database or a Web API (accessed via a Connect 'Connector The means whereby a Web API can be imported into an NDL product as a separate Datasource.'). You will need to create and configure a Datasource for each different database or Web API that you want to access within a form. The database can be an existing database, containing data you want to display and/or update as part of your form's process, or it can be a database created specially for use with your form. Using Datasources means that you only have to define the connection to the database or import a Web API Connector in one place, and then whenever you want to refer to the database or Web API, you use the Datasource name instead.

For each Datasource, you need to define the database tables and columns that you want to use in the associated database. For each column you need to tell Digitise Forms what sort of data the column will hold, whether it forms part of a table's Primary key, can hold NULL values and so on. Database tables and their required columns are defined in Datasets.

For each Datasource (except Web API Connectors), therefore, you need to create at least one Dataset. Each Dataset represents a single database table and the columns in that table that you want to use within your project.

For Web APIs, the Datasets will be pre-defined and will contain the fields as specified within their OpenAPI definitions A file used to interpret and interact with a Web API (usually in YAML or JSON format)..

Like databases, the Datasets handle data in records, with each record consisting of one of each data item defined in the Dataset. A Dataset can have multiple records, e.g. where you download a set of records from a remote database or where a form needs to allow a user to store multiple sets of the same information, such as entering details about multiple children on a single form. Each Dataset has the concept of a current record. This is the record whose values are available to display or whose columns are available to be updated.

When you download data from a database, the records downloaded are stored as records in the corresponding Dataset. When a Dataset is downloaded, the first record in the set of records received is automatically set to be the current record and its values are normally automatically displayed on the form, providing you have configured the form to do this. You can move backwards and forwards through the record set, with each record you move to becoming the current record and, if the form automatically displays the current record values, the new record's values will replace the previous values displayed. If you aren't downloading data for a Dataset, Digitise Forms will generally automatically create a new blank record to hold data entered into the form by a user, or you can manually create one using Events and custom JavaScript, if you need to.

You can update the values in the current record with data entered into the Elements on the form by the user or via Events or custom JavaScript. If the current record already contains values, e.g. where it is a record which has been downloaded from a database, the existing values will be overwritten by the values taken from the Elements.

If you want to upload data from a Dataset to a database, the Dataset must have a Primary Key. This is a data field (or a combination of data fields) which must contain a unique value within the Dataset's records, and hence enables each record to be distinguished from all other records.

Datasets can also have the concept of parents and children. A child Dataset is a Dataset which has a Foreign Key field (or fields) linked to the values in the Primary Key field(s) of another Dataset. In this situation, Digitise Forms allows you to choose whether the parent and child Datasets should be handled together or separately, e.g. whether downloading a Parent Dataset will automatically download records from a child Dataset as well and when uploading the parent Dataset whether child Datasets will automatically be uploaded too.

The Recordset Element allows you to display all the records from a Dataset in a single Element, rather than just displaying the values from the current record. It is particularly useful where you have a parent/child relationship, e.g. where you want to allow the user to enter an account number and then show them all records from the child Dataset relating to that account.

Databases and their tables can be accessed directly or via SQL Stored Procedures.

A Stored Procedure is a set of SQL instructions stored together, within a SQL database, which can be executed by a SQL Server. Stored Procedures can be used to add new records to a database, update existing records and/or retrieve a set of records but they can also be used for more complex activities such as data validation. Data can be passed into a Stored Procedure, e.g. the values to be written to a new record or to provide search criteria for selecting a set of records, and Stored Procedures can also return data, e.g. the set of records which matched the search values passed in. Values are passed in through parameters and the returned data is known as a Result Set.

Stored Procedures can be used to provide additional data validation or to select the required data to be downloaded by a form, especially where these involve complex or sensitive application logic, although they do require a knowledge of SQL Server if you want to do more than basic updating of records. Using Stored Procedures allows you to remove the underlying application logic from the form and hence reduce potential exposure to unauthorised access. They can also be edited without having to republish your form and can be used to simplify forms by moving complex data selection operations to the SQL Server.

You can define different types of Datasource:

Digitise Forms Datasources

Digitise Forms Datasources can be used where you want to store information required by a form which isn't available elsewhere, e.g. to provide the items to be displayed in lists on the form, or to store information produced by a form where you don't have another database to store the form's input, i.e. to store some or all of the data entered into the form by users at runtime.

For example, in the following Radio List, the age range groupings making up the items in the list are provided from a Digitise Forms Datasource:

Picture showing sample Radio List.

The Datasource structure is defined within your project in Form Studio and the database table(s) will be created when you publish your project, if they don't already exist. By default, Form Studio will suggest using the Project Datasource as the database to use for this type of data. The Project Datasource is a database which has the same name as the Project and allows you to store your Digitise Forms data in one easily identifiable place. The Project Datasource database will be created automatically the first time you use it. You can, however, choose to store your data in a different database, or multiple databases, if you prefer. Note that any '-'s in the Project name will be replaced in the Project Datasource's name with '_'s.

For a Digitise Forms Datasource, you need to define the tables and columns that will make up the database since Digitise Forms will create the database tables and the database. The tables and columns are defined in Datasets, so you will need to create at least one Dataset for each of your Digitise Forms Datasources. Each Dataset defines a separate database table and its columns. When configuring a Dataset for a Digitise Forms Datasource, you will need to define the columns you want in the table specifying a column name, data type, maximum length and so on for each column.

You can define the database structure manually or if you will be using the database to store data input into your form, there is an option to automatically define a suitable database structure based on the Elements you have on the form.

Once you have created a Dataset for a Digitise Forms Datasource, you can specify data to be stored in it, if required. For example in the Radio List example above we created a Dataset to define the data and then entered each of the individual age ranges as separate records in the data table all within Form Studio.

A Project Datasource can be created and stored in any SQL Server instance which the published form will be able to access.

If the database specified in a Digitise Forms Datasource already exists, new tables specified in Digitise Forms Datasets will be added to it when you publish your form. If the database doesn't already exist when you publish your form, the database will be created first.

With Digitise Forms Datasources, if you want to use Stored Procedures, you can use them to insert new records and update existing records. Each Dataset has a property, Update with Stored Procs, which, if selected, instructs Form Studio to generate basic Stored Procedures for inserting and updating records when you publish your form. You can use these Stored Procedures without having to have any SQL Server knowledge or do anything different within your form than you would do if directly accessing the databases. You can, however, take the skeleton Stored Procedures produced and edit them to add further functionality, such as adding extra data validation, if you want to. In some cases a Delete Stored Procedure will also be produced, but you can ignore this Stored Procedure as it is not used.

Imported Stored Procedures

Imported Stored Procedures allow you to access a remote, pre-existing database using Stored Procedures, written outside Form Studio. You can use these Stored Procedures to perform data inserts, data updates and/or data retrieval. To import an existing Stored Procedure, you use the Import Stored Procedure option, which causes Form Studio to read the Stored Procedure and attempt to create appropriate Datasources and Datasets to match the functionality of the Stored Procedure, but you may need to fine tune the Datasets generated or in some cases create an appropriate Dataset yourself.

For a Stored Procedure which includes inserting and/or updating records but which doesn't return a Result Set, Form Studio will attempt to create a Dataset which contains fields for each of the Stored Procedure's parameters. This type of Dataset can only be used to upload data from the form to the database, it cannot be used to download data from the database.

For a Stored Procedure which does return a Result Set, Form Studio will attempt to create a Dataset containing fields for each of the values contained in the Result Set. If the Stored Procedure returns more than one Result Set, you may need to import the Stored Procedure multiple times in order to create Datasets for each of the Result Sets you want to use in your form - for details see Import a Stored Procedure.

If the Stored Procedure also includes inserting and/or updating records, the input parameters will not be included in a Dataset but instead are specified when you configure invoking the Stored Procedure. Although you could use this type of Stored Procedure to upload all the data in a form, we recommend that, if you want to upload the form's data using a Stored Procedure, you do so using a Stored Procedure which only provides for inserting and/or updating records and which doesn't also include a Result Set, so that you can use the standard form submission methods to invoke the Stored Procedure and upload your form data. We suggest that you only mix inserting/updating and returning a Result Set where limited inserts/updates are required, for example, imagine a booking form using a Stored Procedure to query a booking database for free time slots which are returned in the Result Set but where you also want to marks slots as reserved whilst the current user completes the booking form and chooses their preferred slot. Once the user has completed the form and submits their booking, you could use a separate insert/update Stored Procedure to upload the form data.

If the Stored Procedure includes a Result Set but doesn't include inserting and/or updating records, the Stored Procedure can only be used to download data from the database to the form. If it also includes inserting and/or updating records, it will both upload and download data, according to the commands specified within the Stored Procedure.

Once you have created a Datasource and its Dataset(s), you will want to configure the form to use them - i.e. to download and display data from the database and to upload entered data to update the database. There are two aspects to this - the first involves the downloading and uploading of data between the form and the physical database and the second involves manipulating the data within the form. Digitise Forms mostly takes care of the former for you, generally you will only need to initiate downloads and uploads at appropriate points within the form, e.g. at its simplest, you can configure data to be downloaded automatically by the form when it's needed and add a Submit button to the end of your form to upload data once a user has finished filling in the form. The second aspect includes displaying data which has been downloaded and writing data into the Dataset columns so that it can be uploaded when you initiate an upload. You achieve this second aspect using the Elements you place on the form, using data mappings, within Events and/or by writing custom JavaScript.

Most Elements have one or more properties which can be linked or 'mapped' to columns in a Dataset. These mappings allow you to display the value from a Dataset column within an Element at runtime, known as an input mapping, and to extract the value in an Element and write it into a column in a Dataset in order to be sent to the database, known as an output mapping. The majority of Elements allow you to specify data mappings and in most cases, you can specify different Datasources/Datasets for input and output mappings, if required, allowing you to read data into the Element from one Dataset but write it to a record in a different Dataset to update a different database to the one the data was originally read in from.

Direct data mappings for Elements are set up in the properties for the Element, but you can also take data from a Dataset column and write data to a Dataset column using Events and custom JavaScript. Values in Elements can be read from a Dataset record, input by a user or input from custom JavaScript.

Datasets which are used for input mappings need to be loaded into the form before the data can be displayed. Datasets can be configured to be automatically loaded by the form or you can provide the means to load them when needed using Events and custom JavaScript, e.g. by attaching a LoadDatasource action to a Button's Clicked Event. Once loaded, the first record is made the current record and Elements which have an input mapping to the Dataset are automatically updated with the appropriate value(s) from the data held in the current record.

You can move through the records within a loaded Dataset using Events and/or custom JavaScript. When you move to the first, next, previous or last record within a Dataset, the new record is made the current record and input mapped Elements are automatically updated.

Values in output mapped Elements can be written to the database using a Submit Button Element placed at the end of each form but can also be output using Events and/or custom JavaScript.

For security purposes, you can configure a Dataset to not allow read and/or write access, where these are not required by your form, in which case the appropriate code required to read and/or write to the database for that Dataset is not included in the form. Note, however, that if you configure Elements to have output mappings to a Dataset, you must include write access for that Dataset.

Note, however, that the situation is somewhat different when using an Imported Stored Procedure which returns a Result Set and which also includes inserting and/or updating records in a database (see above). In this case, you still use input mappings to read data downloaded into the Result Set Dataset but you don't use output mappings to define which Elements contain values to be written to the Dataset for uploading to a database. With these types of Stored Procedure, in order to download and upload data, you need to use a 'loaddatasource' action, either in an Event or in custom JavaScript. The Stored Procedure will perform both download and upload actions, according to the commands specified within the Stored Procedure. The Elements, which will contain the output values are specified when you configure the LoadDatasource action in an Event or as parameters to a call to loadDatasource in a custom JavaScript rather than as output mappings in the relevant Elements. In this case, accessing these Stored Procedures doesn't require write access; they only need read access - see Use an Imported Stored Procedure.

If you create multiple forms within a single project, the forms will share Datasources.

For security reasons, if you want to use different columns within a single database table or Imported Stored Procedure Result Set for different forms within the same project, you should consider creating separate Datasets for each form, with each Dataset containing only the columns appropriate to the relevant form, rather than create a single Dataset containing all columns to be used by all forms. Note, however, if you are using an Imported Stored Procedure which requires input parameters, you must supply a value for all parameters, you cannot use subsets of the Stored Procedure's required parameters.