Customize an Exact Form

You can use Quickbase's Exact Form template to generate form letters and invoices that display information from your Quickbase application. (Read more.) You design an exact form in Microsoft Word. You can insert text and format it however you want. Add field codes, image references and special formulas that draw data from Quickbase. As you'll read below, you can even capitalize on your Quickbase relationships by displaying a table of detail records that are linked to the record on your form. That last feature is great if you want to do something like itemize an invoice with records from your Orders table, for example.

To learn how to download the template and begin the process of creating an exact form, please read Creating an Exact Form. Once you've downloaded the template, you can customize it following the instructions below.

Tip: You can edit your Exact Form at any time after you create it. To do so, open the Quickbase Exact Form template file and then select File > Open from Quickbase or, if you're working in Word 2007 or above, Add-Ins > Open from Quickbase. In the Open dialog box that displays, select the table that contains the exact form. When the form name displays, select it and click Open. Microsoft Word opens the form for editing. When you're done making changes select File > Save to Quickbase or Add-Ins > Save to Quickbase.

In this topic you'll learn to:

• Insert Field Codes

• Add Images

• Display Dates

• Display a Table of Related Details (and format the table with formulas)

• Use Formulas for Advanced Functions

Inserting Field Codes

When you insert a field code in an exact form, you're telling Quickbase to insert the value that appears in this field. For example, say you're generating letters to all your clients. You'd like the salutation to be personalized for each letter, reading "Dear Harriet" or "Dear Frank" depending upon you client's name. To do so, you'd just embed the "First Name" field in your letter. Then, when Quickbase generates a document from the form, the program replaces the field code with the actual content of that field.

A field code is the field name enclosed by tildes. To add a field code to your exact form, type:

~field name~

where field name is the name of the field that contains the value you want to insert, like ~lastname~ or ~invoice#~ for example.

Enter this text at the spot you want the value to appear. For instance, to create the salutation in a letter you'd type:

Dear ~First Name~,

Note: Text between the (~) tildes must match the field name exactly.

Adding images

There are two ways to add images to your exact form:

Show a different image on every form

If each record in your table has an image attachment that you want to display on your document, just enter the field code for the field that contains the attached image file. Type it on your exact form as you would any other field code (~image~ for example).

Show the same image on all forms

But what if you want the same image to appear on every document your exact form generates? For example, maybe you'd like to include your corporate logo at the top of your letter. In this case you can add an image link to the form. When you do so, the same picture appears on all documents generated by the exact form.

If you do want to include an image link, you must store it as a file attachment in Quickbase so that the image is accessible from the Quickbase server. In order for this to work, the application that stores the image file must be accessible by everyone on the Internet. When inserting an image link, you have two storage options:

• If your application is already available to "everyone on the Internet" you can add a new field to your application and designate its type as File Attachment. Then create a new record and use the new field to attach the image file.

• If you don't want the application to be available to "everyone on the Internet," create a separate Document Library application for your Web graphics and make that application open to the public. To create a document library, go to Quickbase Exchange, select the Project Management category, then click Get this app on the Document Library app. Make the application available to Everyone on the Internet. (Quickbase users on 30-day free trial are not allowed to grant access to Everyone on the Internet.)

Note: You may be tempted to link to an image that already exists on a Web server outside of Quickbase. You can do so, but if the image is hosted on a non-secure site (the URL begins with HTTP instead of HTTPS) you might confuse your users. In this situation, the user's browser displays a warning that not all items on the page are secure. This may alarm and perplex users who fear that displaying non-secure items (like your image) would make their computer vulnerable to a virus or worm.

To add an image link to your form:

1. In Quickbase, create a file attachment field in an application that's accessible to "Everyone on the Internet" (if you don't already have an image stored in such an application).

2. Add a record to the application, and upload your image to the file attachment field and save the record.

3. Copy the web address of the image file in one of the following ways:

   • In Internet Explorer, right click the link that opens the image and select Copy Shortcut.

   • In Firefox, right click the link that opens the image and select Copy Link Location.

   • Click the link to display the record. Then select and copy the address that appears within your browser's address bar.

4. Open your Exact Form in Microsoft Word.

   To open a previously saved form, select either File > Open from Quickbase or, if you're working in Word 2007, Add-Ins > Open from Quickbase. Then find the related table and select the form.

5. From the Word menu, select Insert > Picture > From File. Or, in Word 2007, select Insert > Picture.

   The Insert Picture dialog box displays.

6. In the File name box, paste the web address you copied in Step 3.Then click the down arrow within the Insert button (circled in illustration) and select Link to File.
   
   

   

   Note: If Microsoft Word displays a Convert File dialog box or another error, the link didn't work. The link usually fails because Word can't access the graphic file. Make sure that you've granted Everyone on the Internet access to the application where the image is stored.

Displaying Dates

Often you'll want to display a date on your form. Don't bother entering this manually. You can easily insert a formula that automatically inserts "today's date." This feature is especially handy if users will view and print the form from your Web site. When you use a formula, the final form (showing Quickbase data) always displays the current date, no matter what day you created the exact form. You can insert the date in any one of the following formats:

Embedding a table of related detail records in your form

Imagine that you're generating invoices and you want to show your customers a list of orders included on the invoice. After all, they'll want to know what they're paying for. The items you want to display exist in a related table called Orders. How do you get these records into the exact form you created for the Invoices table? No problem. You can easily draw in related records from a details table by inserting a field code that calls the Report Link field. (If you don't know what a report link field is, read About table-to-table relationships.)

First, find the name of the report link field from the Fields list in Quickbase. Next, in Microsoft Word, type a field code for the report link field in your exact form. For example, say the table you're creating an exact form for is called Invoices, and the report link field that connects to your Orders details table is called Orders. Within your exact form, the field code you'd type would be: ~Orders~. Later, when your exact form generates final invoice documents, a table of order records displays where you inserted the field code. On each invoice, the table contains only orders that relate to the specific invoice record shown. Magic.

Formatting an embedded details table

You can't change an embedded table's look and feel, but you can use formulas to change which columns appear and how records are sorted and grouped.

An embedded table displays with no grouping or subtotals. You can change this by typing the following formula on your exact form:

~=qdb.GetURL("DetailsTableDBID", "API_GenResultsTable^query={'DetailsFieldID'.EX.'"+ field["MasterSourceField"]+"'}^options=nvw.ned.phd.nfg.sortorder-A.groupby-V^slist=DetailsGroupFieldID^ts="+ new Date().getTime());~

The timestamp at the end of the formula prevents caching issues that occur in some browsers when displaying embedded reports on your exact form.

You'll need to replace the following values in this formula with your own information, as outlined in the table below:

Tip: To find field identifiers for your table, first access the Fields list in Quickbase. Click the Advanced Options link and select the Field Id checkbox on the dialog that displays. Click Save and Quickbase adds a Field Id column to the Fields list. This column shows the field identifier (fid) for each field.

You can also show only records that meet certain criteria and call only specific columns into the table. Without this intervention, the columns that appear in the table are determined by the reporting defaults you establish for the table.

Advanced formulas

Using field IDs instead of names

If you think your field names may change at some point, you can use field identifiers (fids) instead by replacing the name of the field with its field identifier. For example, suppose the field identifier for a field named "Invoice Date" is 7. Instead of entering the field name, you would enter:

~7~

Using JavaScript in your Exact Forms

You can also use JavaScript on your form by putting the JavaScript between tildes (~) and starting off with an equal sign (=). For example: ~=2*3~ would display 6.

Using JavaScript, you can also access Quickbase field data. For example, if you want to display the Price times the Quantity enter the formula: ~=field["Price"]*field["Quantity"]~

Note: If the Price field is a Numeric - Currency field then you'd need to use the field identifier instead. Say the Price field identifier were 17. You'd enter: ~=f["17"]*field["Quantity"]~

You can always access field data by using field identifiers. For example: ~=fid["7"]*fid["8"]~

Using AND and OR to construct advanced queries

The queries you construct to return data can include more than one condition. To do so, use AND to separate the conditions if you want records that match both conditions. Use OR separate the conditions if you want records that match either condition. The query below returns records where the value of field ID 503 is today's date and the value of field ID 12 is equal to "Active".

query={'503'.EX.'today'}AND{'12'.EX.'Active'}

You can use multiple ANDs or ORs for more complex queries, grouped with parentheses for clarity:

query={'503'.EX.'today'}AND({'12'.EX.'Active'}AND{'225'.TV.'_curuser_'})

To construct even more advanced and complex queries, see the Quickbase HTTP API Guide.

Related topics:

• Creating an exact form

• Editing an exact form

• Deleting an exact form