How to Clip and Concatenate Name Fields

Top  Previous  Next

First names and last names are often stored in separate fixed length fields. If printed directly from those fields they usually contain extra spaces and no punctuation, like this:

Katie              Kelton           E.

However, you may want the name to look like this: Kelton, Katie E. Follow these steps to display names with punctuation and without the extra spaces. This procedure assumes you already have a report procedure that displays your name fields.

1.

Create a variable to hold the concatenated names.

2.

Creata an expression to clip and concatenate the names.

3.

Place the variable in your report.

 

Create a Variable to Display the Concatenated Names

1.

From the Application Tree dialog, highlight your report procedure.

2.

Select the Data/Tables Pad. (Press F12 to open if not visible)

3.

Highlight the Local Data entry, and press the Insert button to add a local variable.

4.

From the New Column Properties dialog, define the new variable as follows.

In the Column Name box, type FullName. This is the name by which we will refer to the variable in our concatenation formula, and in our report.

The variable should be long enough to hold all the name fields to be concatenated, plus any punctuation and spaces. For example: if the first name field is 20 characters, the last name field is 20 characters, the middle name field is 1 character and you plan to use a comma, a period, and a space for punctuation, then you will need 20 + 20 + 1 + 3 (ie. 44) characters for your new variable.

In the Characters spin box, type 44, then press the OK button, then the Close button to return to the Procedure Properties dialog.

Create an Expression to Clip and Concatenate the Names

You can create the same result by typing the assignment statement into the "Before Print Detail" embed point for your procedure. However, we will use the Formula Editor to accomplish our goal.

1.

From the Application Tree, press the Formulas button.

2.

Highlight the Before Print Detail row, and press the Insert button to create a new formula.

3.

In the Name field, type Name Concatenation.

4.

For the Class field, verify Before Print Detail from the Template Class drop list.

The class (also called Formula Class) determines when the expression is evaluated and the assignment performed.

5.

For the Result field, press the ellipsis (...) button, highlight Local Data, highlight FullName (the variable we defined above), then press Select the button.

6.

Press the Functions button, then choose CLIP from the Functions list, then press OK.

7.

Press the Data button, highlight your report procedure file, highlight the last name field, then press Select the button.

8.

At the insertion point, type the following code:

 

& ', ' & CLIP(FirstNameField) & ' ' & (MiddleInitialField) & '.'

 

Where FirstNameField is the first name field in your report file, and MiddleInitialField is the initial field in your report file. Steps 5 - 7 show how you may type your expression, or use the Formula Editor buttons to choose valid operands and operators.

9.

Press the Check button to check your expression syntax.

A green check appears if syntax is correct, otherwise a red X appears.

10.

Press to exit the Formula Editor, and again in the Procedure Properties to return to the Application Tree.

 

Place the Variable in Your Report

1.

From the Application Tree, press the Report button.

2.

Delete all but one of the name fields from your report.

CLICK on the field, then press DELETE.

3.

CLICK on the remaining name field to select it.

4.

In the Properties Pad Picture property, type @S44.

5.

In the Properties Pad Use property, type FullName.

6.

Resize the field by dragging its handles.