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:

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:

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 lives 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:

  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:

To display...

Enter...

Today's date in the following format:

January 10, 2003

~=date = new Date();qdb.format(date.getTime()-(date.getTimezoneOffset()*60000), "date friendly")~

Today's date in the following format:

1-10-2003

~=date = new Date();qdb.format(date.getTime()-(date.getTimezoneOffset()*60000), "date")~

Tomorrow's date in the following format:

1-10-2003

~=date = new Date();qdb.format(date.getTime()-(date.getTimezoneOffset()*60000)+(24*3600000), "date")~

The day after tomorrow's date in the following format:

1-10-2003

~=date = new Date();qdb.format(date.getTime()-(date.getTimezoneOffset()*60000)+(24*2*3600000), "date")~

For each additional day in the future, increment the number "2" above by one.

To display the field named "Date Created" in the following format:

January 10, 2003

~=qdb.format(record.selectSingleNode("f[@id=/*/table/fields/field[label='Date Created']/@id]").text,"date friendly")~

Or if you know the field identifier of the Date Created field (assume it's '1') you can use the following:

~=qdb.format(f['1'],"date friendly")~

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 relationships.)

First, find the name of the report link field. (Select Customize > Tables. In the left-hand pane select the table, then click the Fields tab. It's easy to figure out which field is a report link field. It says "Report Link" in the Type column.) 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 detail 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("targetdbid", "API_GenResultsTable^query={'12'.EX.'"+ field["Source Field"]+"'}^options=nvw.ned.phd.nfg.sortorder-A.groupby-V^slist=15^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 exact forms.

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

You replace...

With the...

targetdbid

Database identifier of the details table from which the embedded report comes. To learn how to find the database identifier for a table, refer to the following FAQ: http://quickbase.intuit.com/developer/knowledge-base/how-do-i-find-database-id-dbid-quickbase-table

12

Field identifier of the reference field in the details table. This field is usually called something like "Related Order" (see the tip below to learn how to find the field ID).

Source Field

Label of the source field in the master table (usually the Key field).

15

Field identifier of the field in the details table that you want to group on (for the purpose of generating subtotals).

 

Tip: To find field identifiers for your table, do the following: Select Customize > Tables. In the left-hand pane select the table, then click the Fields tab. QuickBase displays a list of all the fields in the table. 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 tab. This column shows the field identifier (or 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.

Creating advanced formulas

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~

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 your Price field is a 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"]~

Related Topics

 

This page refers to an older version of QuickBase. Online help is now located at http://www.quickbase.com/user-assistance/default.html.

 

Return to top   

© 1999-2013 Intuit Inc. All rights reserved. Legal Notices.