[ashwken]

Originally posted by janrif
Geeez... First thing I had to do was look up the word coalesce.

I mean that whole set up is so geeky as to be waaaaay over my head.

Jan,

It may help if you break an expression down into it's parts, for example the Title Expression for the default UR Contact Template:

trim(coalesce([Last Name] || ', ' || [First Name],[Last Name],[First Name],[Company],[Email (Work)],[Email (Home)],'(New Contact)'))

Let's look at it another way:

trim(

coalesce(
[Last Name] || ', ' || [First Name],
[Last Name],
[First Name],
[Company],
[Email (Work)],
[Email (Home)],
'(New Contact)'
)

)

trim(value), ltrim(value), or rtrim(value): trim white space from the value.

In the above example the coalesce function will return a value that will be acted upon by the trim funtion.

coalesce(value1,value2,value3,...): uses the first non-null value in the provided values, from left to right.

This is kinda like an if,then statement - if the first value returns null (blank), then try the next..., if all values return null, then use the "fall-thru" value which in this case is (New Contact).

The first value in the above coalesce statement:

[Last Name] || ', ' || [First Name],

demonstrates the text concatenation operator: || (Note: a NULL || anything equals NULL)

EDIT: this is not entirely true
it would appear that the text concatenation operator (||) also has the "power" to evaluate both sides of itself for True (non-NULL) or False (NULL).

In the above example an absence of a value for either [Last Name] or [First Name] will return a False (NULL) which will cause this first evaluation of the coalesce to fail (both sides of the operator (||) must be True) - forcing the coalesce to evaluate the next value.
===========

EDIT:
The only "power" that the Operator (||) has is to "join" two things.

And the NULL is not fully analegous to True or False, it's just NULL (blank or no value present).

In the example above:

[Last Name] || ', ' || [First Name],

if either the [Last Name] or [First Name] return NULL, then the whole evaluation fails because that's what coalesce is testing for (the first instance of a non-NULL value).

Looking at this example:

[Part Name] || coalesce(': ' || [Type], '') || coalesce(': ' || [Size], '')

if the center statement (for Type) returns NULL it does not negate the entire expression, it simply does not return a value and [Size] slides to the left (if it returns a value, is non-NULL).

My apologies if I've unecessarily muddied the waters.

It appears that the coalesce function requires a minimum of two values, the second of which could return a blank character string (nothing within in single quotes), as shown in this example:

coalesce(': ' || [Type], '')

It may help if you create a new database and construct it according to this example from the help file:

A title based on 2 or more other attributes: Assume a template contains a Part Name attribute, a Size attribute and a Type attribute and the title should be equal to Product: Type: Size. Create a template with a Title Expression value of: [Part Name] || coalesce(': ' || [Type], '') || coalesce(': ' || [Size], '').

Then create some Items from this template and see how the Title Expression works, especially if you leave out values for (Type) or (Size) in a record.

Anyway, that's as far as I've gotten.