Database Connection Details
The type of database we wish to communicate with. This will be set automatically based on the type of database we chose in the right-click menu.
We can change this at any time of course, but please be aware that dong so means we must take careful note of the values we set for the Database Field Details block.
Important! It's very important to note that by default the SQL+ Automator, if not provided with a Database Host Name, will attempt to use the same connection properties we set when RackForms was installed.
This can and will lead to problems if we set the database driver to anything other than your default database driver without also setting full connection properties. In other words, if you installed RackForms using the MySQLi extension, setting the Database Vendor to PDO will cause the SQL+ Automation process to fail unless we also set full and proper values for Database Host Name, Database User Name, and Database Password.
This restriction may be relaxed in future versions, but for now, please know that if we set the Database Vendor to anything other than the one we installed RackForms with, we must set full connection details.
An important and always required field, this tells the SQL+ creation process what we wish to call the table we create.
Of course we can change this to anything we choose, but if so, we must also supply a database user and password, and possibly other details such as port.
The path of this file must be relative to the job folder, that is:
Where job-name is the name of the job currently being used.
Database Field Details
Once we have specified, at minimum, a Table Name and Database Name, we can modify, if needed, the form elements that will be used in the SQL+ creation process.
It's important to note the items in this list come from the full form page of items if no selection was made, or from a specific subset of items if in free form mode and we lasso selected a group of elements.
In both cases RackForms will take our selection and add a row for each item. RackForms attempts to make intelligent assignments for the values of these fields, and in many cases they can be left as is.
That said, it's important to understand that each row will become a database column. As such, we have several options we can set to dictate the behavior and properties of each field when converted to a database field.
Please see the listings below for a more detailed description of each option.
Generally speaking, a good way to create a primary key field is to add a hidden field to a form. This hidden field can then be renamed and defined as the primary key, and if needed, deleted from the form page when done with the SQL+ Automation process.
Of course a table need not have a primary key, but it's strongly suggested that it does!
Database professionals should note that when a field is designated as Primary, it can no longer, nor could it, participate in an UPDATE operation.
In addition, any field marked as Primary and Auto-Increment is never INSERTED, and also never UPDATED.
Thus, it pays to read up on database theory and design if you're not familiar with some of the basic concepts, as the more we know the better and more secure our database will be/
As stated above, any field set with auto-increment will never participate in an UPDATE or INSERT operation, as by definition this column's value comes from the database, not the form user.
In many cases we've already set meaningful values in the RackForms editor, but if not this item features a powerful time-saver: any change we make here will update the actual form item's ID and name property.
This means if we've missed naming a form field item it's very simple to set a proper value here.
Of course we do not need to change these values, but it's strongly advised that we do. Creating meaningful values means the database will make much more sense in terms of human readability.
By default RackForms creates sensible defaults when the SQL+ Automator first opens, but this by no means is a final list we need to stick by.
Specifically, the rules are
Another very important point is we have several sub-sections of data types: MySQL, MSSQL, and Access. Please be aware that the database being used must match the data type options we select. For example, we cannot use LONGTEXT, a MySQL data type, for MSSQL. We would instead use TEXT.
Of course we should note when we first load the SQL+ automator RackForms handles these values for us. Also, some data types, like VARCHAR and INT, overlap. Despite this, we should always use the proper values for the database type we've selected.
Generally speaking, and as noted above, all text, password, radio, and select items are assigned VARCHAR(255). This is fine for most uses, but a good rule to stick by is to make these fields a little more sensible by saying, for example, a firstname field would be VARCHAR(50).
Of course if the field can be much longer, we should use a larger number or a TEXT/LONGTEXT format.
As with all databases, little quirks and idiosyncrasies can get us into trouble if we're not careful. For example, MSSQL does not accept a precision setting for int fields.
Please also note that if you switch a field from say, TEXT to VARCHAR, you will need to supply a precision value or the SQL creation process will fail.
Important! At this time RackForms does not support default values for datagrid items when using MSSQL. If you must use default values for datagrid items, please use MySQL instead.UPDATE Actions, as described below and in this section.
Specifically, if we wish to have an UPDATE action we must first define what row gets the update. The way we do this is to tell RackForms the field marked as the WHERE column will provide this link.
That is, lets say we have a database table with two fields:
id would be an INT field that has Primary, Auto-Increment, and Is WHERE checked. Its data type would be INT.
name would simple be a VARCHAR(50).
Knowing that id is an INT that auto-increments and is the Primary Key means it's a good candidate for being the WHERE field, as by definition this value will always be unique--a key attribute of any UPDATE operation.
Thus, we would check the Is WHERE box knowing that by doing so any SQL+ modules created by the SQL+ Automator will use the id field as the link between the record in the database and the row being updated.
Database And SQL+ Creation Details
Confirmation Page To Write SQL+ Items To
In this box we define which confirmation page SQL+ items are created too, if any.
By default we leave this box at the first confirmation page found, though this may not always be appropriate. Thus, we can chose which one via this option.
For each of the next few items we have four options:
This option creates an SQL+ item on the confirmation page of the job. At very minimum, this saves us from having to write any SQL code. However, it does require the table set in the Table Name option exists, and that the column names match.
This option creates an SQL+ item, plus the database table it needs to write records for a single page in your job. The key here is unlike the next two options, this option only creates SQL+ items and database tables for the currently selected page.
This can be handle for those times when you want maximum control over your database creation process.
This option creates a database table for each form page in your job, along with an index table to join them together. The table names are incremented with a numeric _n for each form page. The index page takes the value we define in Table Name and appends _index. The key field we use to join tables is defined as: entry_id.
The downside to this approach is we do not have control over the field data types, as RackForms infers the types automatically. However, we can certainly go in to our database after the fact and modify fields accordingly. By default, RackForms assigns most text fields with VARCHAR(50), text areas with TEXT, and data fields as DATETIME.
The other consideration is this option creates one database table for each form page in the job. This means a job with relatively few fields, such as 30, would gain a bit more complexity than may be needed. This is because we need to JOIN the page tables together at a later time.
However, for larger jobs this is usually preferable, as we keep table column counts lower this way.
This option creates a single database table and SQL+ item for all pages in your job. This can be very handy for smaller jobs, though may cause issues with forms with more than 50 or so fields. This is because now we'll have a database table with that many columns, which can often be a burden to work with.
However, this is often offset by the fact that a single database table for each form entry is much easier to work with than several.
Thus, for such jobs we have this item, which when clicked, creates an SQL+ item that will UPDATE the specified table.
It's important to note that as described above, in order for this process to work we must provide a suitable Is WHERE column. This is because in order for us to update a table we must know which row we wish to update.
Setting a proper column as the Is WHERE field means RackForms will create UPDATE code using the Is WHERE column for its WHERE clause.
This is very useful for times when we have a form that's part of a larger whole, where we have a main database table, say, dealers, that connects to a smaller table, inventory.
In our hypothetical form application we start by selecting a dealer which has known data, then jump into a form for managing inventory. Trick is, updating inventory is a very common operation, but so is adding inventory. Thus, instead of creating two different forms we just create one.
Normally this would create a problem of how do you know when to INSERT vs. UPDATE. Not so with Combo Actions!
If the dealer enters a piece of inventory that doesn't already exist in the system, a Primary Key of say, vehicle VIN, will tell the UPDATE/INSERT block RackForms creates, via a conditional code statement, that no record exists in the database with that VIN. Thus, it runs the INSERT SQL+ item.
If a matching VIN is found, then RackForms will run the UPDATE SQL+ item instead.
All of this is done for you when using the Combo acton.
Of course this is all dependent on having a logical data structure where we have unique keys and again, proper settings for the Is WHERE field.
The best way to learn more is to set up a simple job that has two fields, and id and name, must like the example above.
Try each of the elements out to see how they work!
Field Population Actions
Field population actions aim to significantly reduce the effort involved when creating forms that populate existing form fields. While RackForms has an existing framework for updating existing records (Builder Pages), these pages are not meant to handle large numbers of fields, nor are they designed to allow for complex form interactions, such as processing dynamic values, using validation, or allowing more advanced fields such as signatures or file uploads.
The field population items aim to address these shortcomings, though a careful process must be followed to get the most of this feature. In summary the aim of the Field Population module is simple: automate the process of creating forms that populate fields with existing values, and then update those fields upon submission.
The first step in setting up a field population form is to note this process is meant to run opposite a standard INSERT form, as in a form that collects new data. By contrast these forms should be copies of an input form, one that has no SQL+ or other database INSERT items present.
Once we have our form copy it's important to ensure the database column name and form input field name be identical. RackForms will automatically name fields in this manner when using other SQL Automator tools, though if you've created the database table by hand or in another tool, please note we'll need to match all column names exactly to the field names they map to.
With a form copy in hand and all field names matching their database columns, the next step is to
determine how we'll populate the forms initial page. To do so we must provide either a session variable
or a GET variable with the key value pair:
That is, a PHP session element that's been assigned a valid row identifier in
the form of:
$_SESSION['id'] = 1, or a query string
appended to the first page of your form as in:
Technically, the session or query string item tells RackForms which record to pull from your database when the form
first loads. It's critical to note their is a security issue here, in that the default code that's generated
creates a query that accepts GET values as well as the far more secure SESSION. Because of this we'll need to ensure
this form is either locked down behind a login of some sort or in incredibly rare cases, has data that we're ok
with being accessed and modified by the general public.
Alternatively, we can modify the SQL Query item on the first page of the form to not accept GET values:
SELECT * FROM unit_sql_automator_populate WHERE id = ?
SQL / Mongo Variables:
In general, in almost no cases will a form provide public access for pulling and updating existing records, such forms should always be behind a strong login system. RackForms provides a sample job just for this purpose under the Load Example Job menu: login-form-mssql and login-form-mysql. We also support CAS authentication, as well as basic user name and password authentication. These systems can be found under Page Attributes > Form Security.
A few other important rules should be followed when creating Population pages:
This row contains vital status messages from your database and RackForms.
If a database operation succeeds you will be told so. If it fails, the detailed database message describing why will be shown.
A common workflow we'll find in more complex jobs is the need to update data after it's been submitted to a database. The Populate All Pages action allows us to quickly create the needed scaffolding for this type of operation. Taken separarely, the populate pages action will:
It's key to note all of these items can be created mnually on your end, piece by piece until we have the full flow of pulling records, populating values, and then updating the database upon submission. The difference of course is this is all done for you with a single click.
Criticallty, this tool also sets up all logic to handle Bucket Repeater items. This is a massive time-saver, and one of our personal favorite tools!
We highly recomment taking the time to watch our overview and demo video, it contains lots of hints and tricks to get the most out of this feature!