Design Notes
These are notes you can input that help you and other production staff understand your confirmation elements logic and purpose. This text is never used in the live form in any way, it is only for internal development.

When using the Query module, you will most likely be talking to a database other than the one FormBoss is installed on. Thus, the fields below are vitally important for making a connection to that external database.

Making this connection is the same as any other PHP->SQL interaction, in that you provide a DB Host, user name, password, etc. The only wrinkle is that FormBoss offers a tiered connection model, with each tier providing a slightly different path to the final connection. This tier system ultimately speeds up and secures the development and deployment process, but you should take a few moments to understand how it works.

The FormBoss Tiered Connection Model
The first tier is the default config.php file. That means If you provide no values for the DB Connector file and DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password fields, then the config.php file in your job folder is used. However, this may not be what we need with an SQL+ job, as we may not always need to write to the FormBoss database, which is what the config.php file connects to.

This is so because the connection values included in the 'default' config.php file originate form when you first installed FormBoss on your server, that is, the connection information would be the same as your FormBoss application. This makes sense for the Simple SQL module, as the FormBoss entry viewer runs on the same database as your FormBoss application. However, with the SQL+ module you will have a much greater chance of needing to talk to a different database.

The second tier are the DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password boxes. If you place values in these fields the config.php is ignored and the values you set in those boxes used instead.

The third tier is the DB Connector File box. Placing a value in this box means we override the default config.php file, as well as any DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password settings. This is considered the 'safest' model, as we could effectively hide connection information from clients or less privileged production workers by restricting access to the connection file pointed to in this field.

To summarize: if you leave everything blank, FormBoss will include and use the 'default' config.php file. If you place values in the DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password boxes, they will be used over the config.php file. Finally, if you include a value in the DB Connector File, the connection variables in that file will override all other values.

DB vendor
Choose the database vendor you will be connecting to for the dynamic query.

DB Host Name
The host of your SQL provider - could be 'localhost', an ip address, or a server/instance name.

Please note that for MSSQL we only use one backslash (\) to separate the server/instance.

DB Schema Name
The Database catalog you wish to query.

DB User Name
Database user name

DB Password
The password for this database connection.

DB Port
Much like DB Socket, this value generally needn't be set. However in those cases where needed, the port value is set here.

DB DSN
New to Build 710, this option is only used for ODBC connections.

At the most basic level, if we've set up a DSN in say, The MS Windows Data Sources (ODBC) application, this value can just be the DSN name. If we do not have a DSN, this value can also be a driver definition block (connection string), which for MS Access would be similar to:

Driver={Microsoft Access Driver (*.mdb)};Dbq=mdbFilename

Other ODBC datasources will use different connection strings.

DB Connector File
If you input a relative path in this box, FormBoss will use that file for all database connection information for this query.This means you can 'secure' your forms by making sure access to important database information is limited to only those with access to this file.

To create your own DB Connector File, simply copy the contents of your existing config.php file from your /app/movefiles/ directory and paste it into a new file named what ever you like.

You will then need to change the values of the fields shown below to match the values of the datasource you wish to use:

$db_type = 'mysql';
$db_host = 'localhost';
$mysql_socket = '';
$mysql_port = '';
$dbdsn = '';
$db_user = 'formboss';
$db_pass = 'test';
$db_catalog = 'formboss';

Now in the DB Connector File field place a path to the file you just created relative to your forms final destination. For example, by default all FormBoss output is placed in:

formboss/output/forms/

Which means if I placed my DB Connector file in:

formboss/output/forms/config.php

My DB Connector File field would have:

../config.php

as this file would indeed be one directory up from my form, which if was called external_db, would be located at:

formboss/output/forms/external_db

Now when we run the form FormBoss will not use the config.php file located in the job folder (indeed, one will not even be created unless I have a File Upload module in my form that doesn't also have a DB Connector File specified), which means in principal, any users with access to the FormBoss job folder will never see any database login info, and so long as they did not have access to the folder with the External DB Connector File, not see any DB information period.

Apply Settings To All Datasource Items...
New to build 695, this handy little button copies the current fields datasource settings to all datasource enabled fields in the job.

This is a big time-saver when we have many datasource items in the job that require the same settings.

SQL Debug/Data Return Mode
This field will allow you to view the HTTP Response object for any error messages. Pleas note however, that you will need to have FireBug or some other HTTP Request viewer for these messages to be visible.

SQL Code
Sets the currency type of the transaction. If you need a currency type added, please use the contact page to request one!

Code To Run After Query
New to Build 694, this feature allows us to run custom JavaScript code upon a successful AJAX query. It is important to note that this code must be raw JavaScript, in that we do not include <script> tags.

It is also important to note that this code only runs after a successful query, that is, one that returns results. Technically, Prototype JS uses a custom event handler: onComplete: to handle a successful query, and the code we add with this control is placed at the very end of that event handler.

One very handy use for this feature is to create code to update specific parts of our form that aren't nessesarily input fields.

For example, a common need is to update an HTML <span> or other text element with a value. We do not, in other words, need to update an input field, but rather just show some related value to the user.

In order to grab such display values out it's important to note that:

All AJAX queries return results to a variable called: res

res is an array, with each index containing an object that holds an index (always 0) to your fields value, as well as the database Column name that can be accessed via standard dot notation.

The number of indexes the array has is determined by the number of results our AJAX query returns.

This is best explained in a sample job, of which you can find under AJAX > customize-ajax-results.xml

The short version though is that so long as we know that a variable called res contains our data, we can refer to the rows data with one of the following (or both) syntaxes:

res[0][0]

res[0].your_fields_database_column_name

One final hint is that using FireBug we can enable the console and watch our JSON results populate the log. This is a great way to see a nice structured listing of all results, though we can create our own code to view raw results as well.

For example we could add the following code to the Code To Run After Query box:

var d = new Element('div', { 'class': 'ajax-debug', 'id': 'ajax-debug' }).update("AJAX Debug");

res.each(function(i){
    label = i[0];

    var br = new Element('br', { }); d.appendChild(br);
    var e = new Element('span', { 'class': 'ajax-debug-item', 'id': ''  }).update(label);
    
    d.insert(e);
});

$(document.body).appendChild(d);
            

AJAX Result Processing Mode
New to build 700, this powerful option lets us decode and process standard FormBoss tokens in the AJAX result text. The following tokens are supported:

S{sesion_element_name}

F{form_field_name}

Thus, we could include this code in the text our AJAX call returns:

Thank you for using the AJAX Module S{name}!

And so long as we have a session element already set with a value, the AJAX module will substitute the value of the token and return the modified string.

Append Label
Along with this item, the next two 'Append' items were designed to give you greater control over the results returned and how you can use them. The basic idea is that when you query the database it could be useful to append a value to the front or back of the result set.

The main use of this item for AJAX queries is to add a 'default' value to the select item you're posting to.

These fields then are broken into two main parts: the label and value.

The label, this field, is what would be set as the label of the form field. Technically speaking, this is the first index of the array (the [0] position)

Append Value
Similar to Append Label, this is the value or as far as the raw array is concerned, the [1] position.

Append Position
The last option for appending values is the append position. We have three choices; do not append, append to the front of the array, and append to the back.

AJAX Update Binding Options
Here is we actually set the AJAX properties. One of the most powerful features of this implementation is that we can create as many bindings as needed.

Watch This Field...
This is the form field we'll watch for update events as set in the next item. It is important to note that the event we chose should make sense for the item type.

Please note that as of Build 691 we can watch an auto-suggest field. However, their are important limitations and items you should know about before you do!

Using This Event:
The JavaScript event that will trigger the AJAX POST call.

Please note that as of Build 691 we can use auto-suggest fields to drive AJAX queries. However, due to a specific requirement of the auto-suggest logic, we must set this value to onblur to work. Please see this link for more.

AJAX Query Result Population Mode:
This option lets us define what happens when a query returns results and populates its bound fields. By default we clear all existing field values, be it text from a text field or all existing radio or checkbox items.

In most cases this is the preferred behavior, but their may be times when clearing old values is not desired. If this is the case, we want to set this field to Retain Existing Fields/Values.

Why the two settings?

Imagine we have a text field that's being populated by an AJAX query. When we populate this text field with a new value from an AJAX call we obviously do not want to save or append to the old value. However, what if our AJAX query returns no results? If we automatically clear the old value our users may be left confused as to why a form field element has decided to clear itself of a value. This is especially true if we're creating auto-suggest type logic.

A sample job detailing this scenario and why the AJAX Query Result Population Mode setting is important can be found in the editor under: Load Example Job > AJAX > ajax-clear-null-results

Another common scenario is for checkbox and radio items. By default an AJAX result will clear all old checkbox and radio items. However, what happens if we do not want to clear old values, but just keep appending them? If this is the case, then setting this field field to Retain Existing Fields/Values would be desired.

Of course this would be rare, as again, this type of behavior would be confusing to most users. However, this brings up an important point: what happens if we have preexisting values in a checkbox or radio item array that did not come from our AJAX query?

In most cases we've added these values for a reason, and we would not want an AJAX query to clear them out. If this is the case, then we want to use Retain Existing Fields/Values, as this way, while we'll retain old values from previous queries, we will not remove preexisting values.

That said, it's important to note that FormBoss will not re-populate existing items when in Retain Existing Fields/Values mode. That is, if we have an existing checkbox value of 'Matt', and the AJAX query has a result item with the same value, 'Matt' will not be added twice.

That, in essence, is what this option controls. Do we clear the value when we have no valid values or do we leave it be.

Item Options
These are the AJAX bindings well use when an AJAX request occurs.

Bind This Field...
This is field that will receive the AJAX response. Once we have this item set, we need to set which values we want for the Label and Value are.

Major New Feature!
As of Build 700 new functionality has been added that allows the AJAX module to act upon a much richer set update targets: You'll notice when we click this item an expanded list of elements appears broken into two main categories:

Form Fields - All form elements on a page that receive their ID value from the fields Name/Value property. This is the 'classic' behavior of this field, and is used when we want to monitor a form field for changes.

Display Elements - New to Build 700, these id's are automatically harvested by FormBoss from the first instance of a label field that possess an id="" attribute. That is, for any form element that has the label property (which includes all forms fields and text elements) if we add HTML code such as a div and assign an id to that div as in:

<div id="sample">Some Text</div>

FormBoss will add this id to the list of fields the AJAX module can update with AJAX content! Thus, we could have a select item trigger a content update on a random div in our form, and so on.

Please note that at this time the only binding used to update our element is the Label As... property, described below.

Important Note: As noted above, FormBoss will only find the first instance of an id="" declaration. This means if we have several nested divs with multiple id="" declarations FormBoss will not add that element to the list of items we can dynamically update. The rule of thumb is to keep our nested structures simple.

Label/Element As...
This is the value that will show as the label of our Select items, and the primary value of any text based item such as text boxes and text areas.

Please note that if we're binding a non-form field element such as a DIV, this is the value that is always used to populate the value, not the Value To... option.

Value To...
Required for selection based items, this will become the HTML value of the field. By default this field is set to be blank, so take care to set a value (binding) for this item!

As noted below, when using select based items such as select and check box items, we can 'chain' AJAX calls together by putting, for example, an ID field that relates to another queries label field.

Important Notes And Limitations
As of Build 675 FormBoss handles the following form items: [text, textarea, radio, select, checkbox]

Most items, particularly Select items, work as you would expect. However, their are caveats for the other field types.

First, text items, as you would expect, can only receive 1 value, the Label, which is unlike Select items which can have a separate Label and Value. Also, Text items can only receive 1 value in total. Thus, if you have a SQL statement that will return more than 1 row, you should use a select item to store the extra values. By design, should a bound text box item receive multiple items, only the last item is shown.