• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Using GetField

The GetField function returns the contents of a specific field. At first glance, this function might not seem all that useful, since you could simply use any field name to get its contents.

But the magic is in how you apply GetField. If you put quotes around the field in GetField(“Abracadabra”), nothing special happens, and you get the contents of the Abracadabra field.

But if you put the name of another field (say, Magic) into Abracadabra, then eliminate the quotes around the field name in GetField(Abracadabra), GetField won't return the word “Magic” in the calculation. Instead, you'll get the contents of the field Magic. Now that's useful!

In “Formatting Labels or Envelopes” earlier in this chapter, we showed you how to create a calculation field that combines name and address information into a single field that can be conveniently placed in a label or envelope layout. But databases often contain both home and work addresses, and people frequently prefer to be contacted at one address rather than the other. You could create yet another field for the preferred contact but it would be a nightmare to keep straight. Printing labels in two groups for work or home preferences is equally inconvenient. Using the GetField function, you can create a field to choose which address to use for the label.

To follow these steps, you'll need a database with separate fields for home and work addresses (Figure 4.47).

Figure 4.47. The address data for each record must appear in separate Home and Work fields for this technique to work.


To select a mailing address using GetField

1.
Choose File > Define > Database (Control+Shift+D/Command+Shift+D).

2.
Click the Fields tab, name the field Mail Address, choose Text as the type, and click Create.

3.
Double-click the Mail Address field. When the Options dialog box appears, click the Auto-Enter tab.

4.
Click the Data check box. Enter either Home or Work as the default value in the box to the right (Figure 4.48). Click OK.

Figure 4.48. Set the Auto-Enter option for the Mail Address field.


5.
In the Fields dialog box, type Home for the field name, choose Calculation as the type, and click Create.

6.
When the Specify Calculation dialog box appears, double-click Trim in the function list on the right.

7.
With the “text” parameter highlighted in the formula box, double-click the Home Address field in the field list. Click to the right of the parenthesis (Figure 4.49).

Figure 4.49. The Trim function deletes any spaces around the home address.


8.
In the Operators keypad, click the ampersand button to add it to the formula box, then click the quotes button.

9.
With the cursor still between the quotes, click the paragraph operator button () (Figure 4.50).

Figure 4.50. The paragraph operator puts all of the text that follows it on a new line.


10.
Click to the right of the quotes, then click the ampersand button and press Return (Mac) or Enter (Windows).

11.
Next you want to put the city, state, and Zip code together on the second address line. In the function list, double-click Trim.

12.
With the “text” parameter highlighted, double-click Home City in the field list. Click to the right of the parenthesis, then click the ampersand button.

13.
Click the quotes button. Type a comma and a space between the quotes. Click to the right of the quotes, then click the ampersand button (Figure 4.51).

Figure 4.51. Text, spaces, and punctuation must appear within quotes if you want them to print as part of the address line.


14.
In the function list, double-click Trim. With the “text” parameter highlighted, double-click Home State in the field list. Click to the right of the parenthesis, then click the ampersand button.

15.
Click the quotes button. Type a space between the quotes. Click to the right of the quotes, then click the ampersand button.

16.
In the function list, double-click Trim. With the “text” parameter highlighted, double-click the Home Zip field in the field list.

17.
Be sure to set “Calculation result is” to Text (Figure 4.52). Click OK to finish.

Figure 4.52. Since the fields in the calculation contain text, “Calculation result is” must be set to Text.


18.
In the Fields tab of the Define Database dialog box, click the Home field and then click Duplicate.

19.
Change the name of the duplicated field from Home Copy to Work and click Change (Figure 4.53).

Figure 4.53. Duplicating the Home field and changing the calculation saves you from having to create the new field from scratch.


20.
Double-click the Work field. When the Specify Calculation dialog box appears, change all the instances of “Home” to “Work” (Figure 4.54). Click OK.

Figure 4.54. Change “Home” to “Work” throughout the calculation.


Now that we've set up the two calculations for home and work addresses, we need to write the calculation that will connect the person with the default address.

21.
In the Fields tab of the Define Database dialog box, type Mail Label for the field name, choose Calculation as the type and click Create.

22.
When the Specify Calculation dialog box appears, double-click Trim in the function list. With the “text” parameter highlighted, double-click the First Name field. Click to the right of the parenthesis, then click the ampersand button.

23.
Click the quotes button, type a space between the quotes, then click to the right of the quotes. Click the ampersand button.

24.
In the function list, double-click Trim. With the “text” parameter highlighted, double-click the Last Name field. Click to the right of the parenthesis and click the ampersand button.

25.
Click the quotes button, then the paragraph button. Click to the right of the quotes and click the ampersand button (Figure 4.55). Press Return (Mac) or Enter (Windows).

Figure 4.55. To make sure the address displays properly, use the Trim function to strip extra spaces from the field data.


26.
In the function list, double-click the GetField function (Figure 4.56).

Figure 4.56. The GetField function will return the data from the field name you choose.


27.
With the “fieldName” parameter highlighted, double-click the Mail Address field in the field list (Figure 4.57).

Figure 4.57. Don't put quotes around the field name in a GetField function, or GetField will return the name, not the contents.


28.
Make sure that the Calculation result is set to Text, click OK and then OK again.

We have our calculations, but we need to create a value list so we can choose between the Home and Work addresses.

29.
Choose File > Define > Value Lists. In the Define Value Lists dialog box, click New.

30.
When the Define Value List dialog box appears, click New. When the Edit Value List dialog box appears, type Mail Address for the Value List Name.

31.
Click the “Use custom values” radio button. Enter Home and Work in the custom values box (Figure 4.58). Click OK twice.

Figure 4.58. Enter the work and home address field names in the value list.


32.
Go to a layout that has the address fields. Choose View > Layout Mode (Control+L/Command+L).

33.
Drag the Field tool into the layout. When the Specify Field dialog box appears, choose Mail Address (Figure 4.59).

Figure 4.59. Specify the Mail Address field to add it to the same layout as the address fields; the user can easily choose which address to use for each record.


34.
Click the Mail Address field and choose Format > Field Format.

35.
When the Field Format dialog box appears, choose Radio Button Set from the “Format field as” drop-down list. Choose Mail Address from the “Display values from” drop-down list (Figure 4.60). Click OK.

Figure 4.60. Because the field name used in GetField must match the data entered, set the Mail Address field to use radio buttons to prevent input errors.


36.
Go to the label or envelope layout where the Mail Label field will be used. Choose View > Layout Mode (Control+L/Command+L). Drag the Field tool into the layout. When the Specify Field dialog box appears, choose Mail Label. Make the field large enough to display the address properly (Figure 4.61).

Figure 4.61. Add the Mail Label field to the label layout and size it to show all of the data.


In each record, the Mail Label field will display the name and either the work or home address, depending on which you choose in the Mail Address field.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint