Here's an example of a Variable that concatenates fields, uses a formula to select the appropriate fields, and uses Operators and Functions within the formula. It is adapted from the Name Variable in the standard Customer Address List report.
@IF([CST:Name]~,
[CST:FirstName] [CST:LastName],
@LEFT([CST:Name],30) (,) [CST:FirstName] [CST:LastName])
Broken down into its parts:
@- Means that whatever follows is going to be a Function. A Function is like an Operator, only a little more complex.
IF- The name of the If-Then-Else Function. It follows this basic format:
@IF(Expression,TruePart,FalsePart)
- If the Expression is true, do the TruePart.
- If the Expression is false, do the FalsePart.
(- The open parentheses is where the Function starts. Everything that the IF Function does is inside the main parentheses. The IF Function breaks down into three parts:
Expression- The first part. This is what the IF Function is asking about.
[CST:Name]- The Customer Company Name field.
~- The Is Blank operator.
[CST:Name]~- Together it means Customer Company Name is blank.
,- The end of the Expression.
So far, the Formula says, @IF([CST:Name]~ - If the Customer Company Name is blank,
TruePart- The second part. This is what the IF Function does when it is true.
[CST:FirstName]- The Customer First Name. It prints the Customer First Name.
blank- A blank space. It prints a blank space after the Customer First Name.
[CST:LastName]- The Customer Last Name. It prints the Customer Last Name.
,- The end of the TruePart.
So far, the Formula says, If the Customer Company Name is blank, print the Customer First Name, followed by a blank space, followed by the Customer Last Name.
FalsePart- The third part. This is what the IF Function does when it is not true.
Depending on what the Function is designed to do, the TruePart and the FalsePart can be simple, like the TruePart in this example. They can also be a little more complicated, such as the FalsePart in this example:
@LEFT([CST:Name],30)(,)
[CST:FirstName]
[CST:LastName]
Broken down into its parts:
@- Another Function. This Function is embedded inside the IF Function.
LEFT- The name of the Left Characters Function in its basic format: @LEFT(Text,NumCharacters). It prints or otherwise uses the Text, starting from the left with the length determined by NumCharacters.
(- The open parentheses is where the Function starts. Everything the LEFT Function does is inside the parentheses. Note that the LEFT Function is already inside the IF Function's parentheses.
The LEFT Function breaks down into two parts:
Text- The first part. It is what the Function prints or otherwise uses.
[CST:Name]- The Customer Company Name. It prints the Customer Company Name.
,- The end of the Text part.
NumCharacters- The second part. 30 is the length of the Text to be printed or used. Since this is the LEFT Function, it uses the first 30 characters, starting from the left.
)- This is where the LEFT Function ends. It prints the first 30 characters of the Customer Company Name.
(- Whatever is inside these parentheses forms a separate group.
,- In this case, the group consists of just a comma. Making it a separate group tells the Variable to treat it like a character and not like a separator between parts of a Function.
)- The end of the comma group. It prints a comma after the Customer Company Name.
blank- A blank space. It means print a blank space after the comma.
[CST:FirstName]- The Customer First Name. It means Print the Customer First Name.
blank- A blank space. It means print a blank space after the Customer First Name.
[CST:LastName]- The Customer Last Name. It means print the Customer Last Name.
So far, the FalsePart of the IF Function prints the Customer Company Name, followed by a comma, followed by blank space, followed by the Customer First Name, followed by blank space, followed by the Customer Last Name.
)- This is the end of the IF Function as well as the end of the Variable.
The entire Function means:
- If the Customer Company Name is blank,
- Print the Customer First Name,
- Followed by a blank space,
- Followed by the Customer Last Name.
- If the Customer Company Name is not blank,
- Print the Customer Company Name,
- Followed by a comma,
- Followed by blank space,
- Followed by the Customer First Name,
- Followed by blank space,
- Followed by the Customer Last Name.
- The printed output looks like this:
Bargelly's Bean Bakery, Bob Bean
BB's Billiards and Beer, Bobby Brickhouse
Snitley Cratwaddle
The Doorknob Emporium,
- The third line has the Customer First and Last Name only because that Customer has no Company Name.
- The fourth line has the Customer Company name only because that Customer has no First or Last Name.
- Note the unnecessary comma at the end of the fourth line. The following version of the Name Variable removes unnecessary commas from Customer records with blank First and Last Names. The printed output looks like this:
The Doorknob Emporium
Instead of like this:
The Doorknob Emporium,
- Step through it to see how it works:
@IF([CST:Name]~,[CST:FirstName] [CST:LastName],@IF([CST:FirstName]~&&[CST:LastName]~,@LEFT([CST:Name],30), @LEFT([CST:Name],30) [CST:FirstName] [CST:LastName]))
Note: && means "and".
See:
Variables Example - Concatenate Customer First Name and Last Name - Summary
Edit Variable Screen
Variables in SubTotals and Grand Totals
Filters and Formula Functions
Filters and Formula Operators
Filters - Transaction Types
Report/Label Editor Contents