The webformtodatabase statement updates the current database with information from the web form that was just submitted.
Parameters
This statement has one parameter:options – one or more option=value pairs that control how the data is processed. See the text below for a detailed description of the available options.
Description
This statement updates the current database with information from the web form that was just submitted. Only fields in the form that match the database fields will be used, any other fields will be ignored. In addition to bringing in the data, a response is generated to be sent back to the user’s browser. This response is placed in the cgiHTML global variable.
The operation of the webformtodatabase statement is very simple. The statement scans the items submitted to it from the browser, checking items that match a database field. For each match it copies the submitted data into the database. It’s very important that the field names in your web form exactly match the field names in the database. If they don’t match, the webformtodatabase statement won’t update the database with those values.
Note: The webformtodatabase statement goes back even before Panorama Server, to a predecessor program called PanSTAR in the 90’s (it got that name because it was designed to work with the WebSTAR web server software). The advantage of the webformtodatabase statement is that it is quite easy to user — if the item names in the submitted form match the database names then webformtodatabase will do most of the work for you with a single line of code (the item names will automatically match if you use a Panorama generated web form). The disadvantage is that this statement is not as flexible as processing input data using the grabwebformitems and stashwebformitems statements, which buffer the data in variables before actually storing it in the database. This makes it much easier to do input validation and custom processing. However, the webformtodatabase statement is still included in Panorama Server for compatibility with older databases, so it is documented here.
The webformtodatabase statement has one parameter, a list of options that customize the operation of the statement. For example the options can be used to specify whether the submitted data should be entered into a new record or into an existing record. Here is an example that will add a new record to the database. If the Hotel field is blank (empty), the entire data input operation will be aborted, and the browser will display an error page. The first letter of each word entered into the City field will be capitalized, even if the user neglected to do so.
webformtodatabase |||whatrecord="add" required=Hotel upperword=City|||
There are over a dozen options that can be specified: whatrecord, keyfield, notfounderror, toomanyerror, autosave, autosavethreshold, required, missingfielderror, upper, upperword, lower, responsepage and extrapage. Each of these options is described in more detail below.
This option controls what record in the database is modified. There are eight possible suboptions, listed below. Some of these options use key fields, which are described below.
This option defines a key field to be used with the find, select or findadd options. You may define more than one key field, which will be combined. This example defines the first and last names as key fields, allowing the target record to be specified by a person’s name.
keyfield=First
keyfield=Last
The keyfield is discussed in more detail further down on this page.
This option specifies that one or more fields must be automatically capitalized to all upper case. For example, the word computer would be converted to COMPUTER. You should have one upper= option for each field you want capitalized, for example:
upper="State"
upper="Country"
This option specifies that one or more fields must be automatically capitalized to Initial Caps. For example, the word computer would be converted to Computer. You should have one upperword= option for each field you want capitalized this way, for example:
upperword="Name"
upperword="Company"
upperword="Address"
This option specifies that one or more fields must be automatically converted to all lower case. For example, the word COMPUTER would be converted to computer. You should have one lower= option for each field you want capitalized this way.
This option specifies that a field is required. You may specify as many required fields as you like. If any required field is not entered (left blank) the webformtodatabase statement will not update the database. Instead, it will display an error page in the browser. You can use the default error page, or you can create your own custom message using the missingFieldError= option (see below).
required=First
required=Last
required="Zip Code"
This option controls the error page that will be displayed if a required field was not entered. You can insert a list (<li> formatted) of the missing fields with the special <missing> tag. For example:
missingFieldError=
{Please go back and enter these fields!<p><missing>}
The webformtodatabase statement automatically generates a response page to display the result of the update to the user. This response is placed in the cgiHTML variable. The responsepage option allows you to control the content and appearance of this response page. If you don’t want an automatic response page generated, set responsePage={-}. For a simple canned text message, just type in the message:
responsePage=
{<center>Thank you for your submission.</center>}
The responsePage can include information about the update that was just performed - how many fields were updated, what fields were updated with what values, etc. Use these special tags to insert this information:
<fieldcount> - number of fields modified (example 6 fields)<fields> - list of fields/values, in <ul><li>...<li></ul> list format<database> - name of the database that was updated<extras> - information about any extra form fields that were ignored (see the extrapages option below).Here is a simple example of how these special tags are used.
responsePage=
{Thanks for your contribution to the <database> database!
<p>Here’s the data you entered:<fields>}
The webformtodatabase statement automatically scans the fields passed to it from the form and updates the corresponding database fields. There may, however, be additional fields in the web form that do not correspond to any database field. If this occurs, the response page can include this information to warn the user that some of the input was ignored. This extra response is placed in the <extras> tag of the responsepage (see above). If you don’t want any error message to be displayed at all, set the extrapage option to a single dash
extraPage="-"
The extrapage option allows you to control the content and appearance of this portion of the response page. For a simple canned text message, just type in the message:
extraPage=
{<p><b>Warning:</b> One or more form fields ignored!}
The extrapage can include information about the extra fields that were ignored. Use these special tags to insert this information:
<fieldcount> - number of fields that were ignored (example 6 fields)<fields> - list of ignored fields/values, in <ul><li>...<li></ul> list formatHere is a simple example of how these special tags are used.
extraPage={<p><b>Warning:</b> <fieldcount> ignored!}
Of course if your form and database are designed correctly you should never need to use this option. However, it can be useful for debugging your procedures.
This option controls the error message to display if the find or select options don’t find the requested record. For example:
notFoundError=
{The record you tried to update for doesn’t exist!}
This option controls the error message to display if the select option finds more than one record that matches the key field. For example:
notFoundError={Can’t decide which record to update!}
For debugging you may want to simply display the names and values of the form items that are submitted. This can be easily done with a one line procedure.
webformtodatabase “whatrecord=formdump”
When the server procedure containing this code is triggered the server will respond with a list of the fields on this form along with their values. When the formdump option is used the database isn’t actually modified — this option is strictly for debugging
It’s very easy to write a procedure to use a form to add a new record to the web database:
webformtodatabase “whatrecord=add”
In fact, this procedure can be even shorter!
webformtodatabase “”
When the server procedure containing this code is triggered the server will add a new record, then display a list of the fields that have been updated.
As shown in the previous section the webformtodatabase statement will automatically generate a response confirming that the new data has been entered. However, this response page isn’t very attractive. There are several methods for generating a custom response page.
If the original data entry form was a Panorama generated web form you can specify that another web form be used as the response page using the Web Form Converter wizard. If a follow-up form has been specified then the webformtodatabase statement will automatically use it instead of the standard generic response.
If a follow-up form hasn’t been set up, or if you want to use a different form or generate custom HTML, simply program the response like any other web page. Here’s an example that displays the web form Thanks! when data is submitted.
webformtodatabase “”
cgiHTML=renderwebform("Thanks!","")
Another way to generate a custom page is to use the responsepage=message option. Here’s a simple example.
webformtodatabase “responsepage={<center>Thanks!</center>}”
Sometimes a particular field must be all upper case, or have the first letter of each word capitalized. For example, US state abbreviations are always capitalized (AK, AZ, … WA) while city names always have the first letter capitalized (Anchorage, Bakersfield, Costa Mesa, etc.). The webformtodatabase statement can automatically capitalize data before it gets entered into the database.
webformtodatabase “whatrecord=add upperword="Hotel" upperword="City" upper="State"”
If data must be forced to all lower case use the lower=field option.
The webformtodatabase statement can check for missing fields — in other words if the user fails to enter something important (like their name) it can refuse to update the database and display a page requesting that they enter the missing information. To do this add one or more required tags with the field name, like this.
webformtodatabase “whatrecord=add required="Hotel" required="City"
required="Phone" required="Rate" required="Units" ”
If you try to submit an empty form the server will send a list of the missing fields back to the browser. By default this is a generic response, but you can also create a custom response page. If you are using a Panorama web form for data entry then one method for doing this is to create another Panorama web form for missing fields and then specify that form with the Web Form Converter wizard, allowing you to create an attractive page to display the missing field names. This form must contain a Text Display Object that uses the webformmissingfields( function to display a list of the fields that are missing. This function has one parameter, a template that the function uses to format the list. The template should contain the tag <field>, and any other HTML tags you want to use to format the list. When the form is rendered the template will be repeated once for each missing field, with the name of the missing field substituted for the <field> tag. (Or you can use the template “”, which produces a carriage return delimited array of the missing field names. If displayed as HTML this will appear as names separated by spaces.)
Lets look at some examples of formulas using the webformmissingfields( function. To display a bulleted list of missing fields, use this formula.
"<ul>"+webformmissingfields("<li><field>")+"</ul>"
This formula will create a comma separated list of the missing field names.
commastr(webformmissingfields("<field>"),cr())
This formula will create a one column table listing the missing fields.
"<table border=1>"+
webformmissingfields("<tr><td><field></td></tr>")+
"</table>"
This formula will list each missing field name on a separate field (if rendered into HTML, the names will appear on the same line, separated by a space).
webformmissingfields("")
If using the Missing Fields option in the Web Form Converter wizard isn’t appropriate (for example if you’re not using a Panorama web form) you can include logic in your procedure using the webformmissingfields( function to check for missing fields, and then take whatever action you like. This example uses the renderwebform( function to display the response page, but you can use any technique you want
webformtodatabase “whatrecord=add required="Hotel" required="City"
required="Phone" required="Rate" required="Units" ”
if webformmissingfields("")=""
cgiHTML=renderwebform("Thanks!","")
else
cgiHTML=renderwebform("Error_MissingFields","")
endif
The webformtodatabase statement uses the val( function to convert numeric fields. There is no error message if an invalid number is entered, however, any characters after the first non-numeric field will be ignored.
The date( function is used to convert date fields. This means that you can use Smart Dates™ in your web forms, like today or next tuesday! Very cool.
To write a procedure that modifies an existing record, the procedure must somehow specify which record to modify. One method to do that is to pre-locate the record with the find or select statements. This example will locate the record containing Apple Computer and update it with the information submitted from the form.
find Company="Apple Computer"
webformtodatabase “whatrecord=active”
Of course you probably don’t want to create a specific form and procedure just for updating the Apple Computer record in your database! If the form contains a Company field the procedure could use the submitted company name to determine what record to update, like this:
let whatCompany=webformitemvalue("Company")
find Company=whatCompany /* or you could use select here */
webformtodatabase “whatrecord=active”
There’s a simpler way to do this. Instead of the 4 lines above, you can let the webformtodatabase statement locate the record for you. This example will do exactly the same operation as the example above.
webformtodatabase “whatrecord=find keyfield=Company”
/* or you could use whatrecord=select */
If necessary, you can specify more than one key field. For example, you might use the last name, company name, and zip code as key fields in a contact or mailing list database.
Using the whatrecord=find and whatrecord=select options generally relies on the user accurately typing in the key fields. For example, if someone types in Apple or Apple Inc instead of Apple Computer, the find will fail. Later we’ll show a foolproof method to avoid this problem completely (see Modifying an Existing Record with Embedded Record ID below in this page), but for now let’s look at methods to handle this problem when using the key field technique for updating records.
One way to handle the situation is to simply decide that the user knows best and to automatically add a new record if the submitted data doesn’t already exist in the database. To do this use either the whatrecord=findadd or whatrecord=selectadd options. Here’s how one the findadd option would be used.
webformtodatabase “whatrecord=findadd keyfield=Company”
The problem with this idea is that you’ll quickly wind up with lots of extra records, so a more common approach is to not modify the database at all and display a page with an error message. In fact, that’s what the webformtodatabase statement will normally do when the whatrecord=find and whatrecord=select options are used. You can customize the error page with the notfounderror= option, like this:
webformtodatabase “whatrecord=findadd keyfield=Company
notfounderror={<h1>Sorry, that company isn’t in the database!</h1>}”
If the whatrecord=select option is used then another possible error is that more than one record matches the key fields (for example if the database contains two or more records for Apple Computer. In that case a different error message is displayed, which can be customized with the toomanyerror option.
webformtodatabase “whatrecord=findadd keyfield=Company
notfounderror={<h1>Sorry, that company isn’t in the database!</h1>}
toomanyerror={<h1>Hey - there is more than one record for this company</h1>}”
For more control you can program a response to missing or ambiguous records. The cgiSelectedRecordCount global variable will contain 1 if the update was successful, 0 if the record could not be found, or 2 (or greater) if there were multiple records found. This example will display a Panorama web form if one of these errors occurs.
webformtodatabase “whatrecord=select keyfield=Company”
if cgiSelectedRecordCount=0
cgiHTML=renderwebform("CompanyNotFound","")
endif
if cgiSelectedRecordCount>1
cgiHTML=renderwebform("CompanyDuplicates","")
endif
The technique for modifying an existing record in the previous section relies on the user typing in one or more correct key fields to identify the record. A more reliable method is to embed record ID information in the data entry form as it is being displayed. This is done automatically if the form is generated by the renderwebform statement. If the form is generated some other way you’ll need to use the webformrecordid statement to embed the record ID into the form being generated. The only parameter needed by this statement is the name of the field containing the HTML for the page. Here’s an example that displays a record in a form. The record that will be displayed is determined by the extra parameters in the URL.
weburlselect cgiExtraParameters
cgiHTML = renderwebform("Invoice","")
webformrecordid cgiHTML /* add record id to form about to be displayed */
The user can now see the current information for the selected record in their web browser, then edit that information and press the Submit button. Here’s the procedure that processes the submitted input and updates the record:
webformtodatabase “whatrecord=record_id”
That’s it! The server will locate and update the record (unless it has been deleted, which can be handled by checking the cgiSelectedRecordCount variable as described in the previous section).
An alternate method is to add additional parameters to the webformrecordid statement to identify one or more “key” fields. The combination of all the key fields you specify should uniquely identify a single record in the database. For example in an invoice database the InvoiceNumber field probably uniquely identifies a record.
weburlselect cgiExtraParameters
cgiHTML = renderwebform("Invoice","")
webformrecordid cgiHTML,"InvoiceNumber" /* add record id to form about to be displayed */
If a single field won’t reliably identify a single record you can add more, as many as are necessary.
weburlselect cgiExtraParameters
cgiHTML = renderwebform("AddressUpdateForm","")
webformrecordid cgiHTML,"LastName","FirstName","LastName","City","State"
No matter how many key fields there are the procedure for processing the submitted form data doesn’t change, in fact it is the same as the procedure for a shared database.
webformtodatabase “whatrecord=record_id”
Again, this procedure uses the embedded record ID to locate and update the correct record.
If you let Panorama create your web forms for you it will automatically set up all the fields in the correct format to be processed by the WebFormToDatabase statement. If you create your web forms externally (for example using Coda, Visual Studio, Dreamweaver or BBEdit) you’ll need to make sure that the form is created using the exact format required by the Panorama server. See Rendering Using an External Text File containing a Form as a Template for the exact details on how to do it. It’s important to get all the details exactly right or the WebFormToDatabase statement may not be able to process the submitted data.
See Also
History
| Version | Status | Notes |
| 10.2 | No Change | Carried over from Panorama 6.0. |