Workflows have the capability to query and update a database. By using the built-in query builder, workflow designers can make their workflows interact with databases without any knowledge of SQL.

Insert a new record into a database

From a database standpoint, inserting a new record is different from updating an existing one. For this reason, Business Pilot makes the distinction between the two concepts.

To create a new database record, add a Create new database record item to the workflow.

Business Pilot is automatically aware of the attributes of the database table selected. It presents a table with the name of the fields in the left column and the workflow designer must fill out the right column with the data to insert in each field. This data can reference other steps in the workflow as in the example shown in the illustration.

Update a database record

Updating a record is similar to inserting, except that a condition identifying the records to be updated has to be specified. To update database record, add an Update a database record item to the workflow.

In addition to the assignment of the data to the database fields, the workflow designer must identify the records to be updated by applying a selection criteria. Not specifying any criteria means that every record will be updated.

Delete a database record

To delete one or more records from the database, you must apply a selection criteria that will identify the records to delete.

WARNING: Not specifying any selection criteria will delete all the records in the database. This action cannot be undone.

Query a database

Querying a database allows the workflow to search for data in a database. The selection is based on a condition and may optionally require human intervention to select a record in the matching result set.

To configure a query, you must specify the database to go to, a selection filter and an action. The action can any of the following:

1. Return first record found: This option will return the first record found that matches the query. It is a common option to use when the query is expected to return only one result (ie. because it is made against the primary key of the table for instance.
2. Return a random record: This option will pick on record amongst the matching records, randomly. This is a useful setup for instance, when the workflow is trying to choose a sales rep. serving given geographical area, whenever several candidates are possible.
3. Let the user pick a record in a form: Displays all the matching results in a web form that is presented to the user so that one record can be picked manually. One a record is picked, the workflow continues its course, remembering which record was picked.
4. Just show the result in a web page: This action is similar to the one above, except that it does not allow the user to pick a record. This option is used when the workflow should display the result of a query in a form, which any further expectation from the user.

When the query is configured with option 3 or 4, you must fill out the Labels tab. It presents a table with the list of the database table field names in the left column. For each field you would like displayed in the resulting web page table, you must specify a label that will be used as the column header. Labels that you leave empty will not be displayed in the result set. If you do not specify any label, the resulting page will contain a blank table.

Database queries automatically make the database field names available as Live data that can be accessed by the other steps of the workflow. Each field is populated with the data retrieved from the database in the matching record.