Query Database from within web Template

OL Connect Professional General
1

Are there any examples of how to query a database from within a Web Template?

The notion is to select a “Client” with a Select Field and have it return all the available values for the “Line of Business” Select Field. When selected returns all of the available values for the “Job Name” Select Field. When one item is selected then the vast majority of the remaining fields would be populated.

CLIENT - Linked to SQL table

LINE OF BUSINESS - Linked to filtered SQL table where records equals the selected CLIENT value

JOB NAME - Linked to filtered SQL table where records equals the selected CLIENT and LINE OF BUSINESS values

OTHER VALUES - Linked to filtered SQL table where records equals the selected CLIENT and LINE OF BUSINESS and JOB NAME values

Basically pre-populating the Web form based on the first 3 fields.

2

You could use AJAX calls pointing to a Workflow process that uses the Database Query plugin to return the recordsets you’re looking for (I know you’re familiar with Workflow!).

For instance, you could use the onChange() event to pass the Client value to a workflow process which would build a dynamic SQL Query using that value and would return an XML recordset. Once you receive that XML, you can use it to populate your next field, and so on.

If your Template is based on Foundation, you automatically have access to the JQuery$.ajax()method. So for instance, let’s say you have something like a <SELECT id="myField" onChange="getRecordSet();"> template field. You could then have a function similar to:

function getRecordSet() {

$.ajax({
  async: false,
  url: "/getRecords",
  data: {clientID: document.getElementById('myField').value},
  dataType: "XML",
 }).done(function(data){
  document.getElementById('myNextField').innerHTML=data;

}

Where /getRecord is the name of an HTTP Server action in Workflow. The above example is just that, by the way: an example. You’ll have to read/write the values according to your Template, but I think this should help you get started.

Note: make sure your Workflow process handling the Ajax call is set to a short polling interval (say 1, or 0) so that each call to the process is executed ASAP.