Addressformat

Addressformatting

The field ExtraFlags for formatting is obsolete from CRM SIX and we now use the two fields LabelLayout and LabelLayout2 for formatting of the template variables fadx with the database values.

The entire label, including company, person, address and country sub parts, will be specified as a template string. The string contains a mix of literal text, conditional text and template variables. During label generation, the following will be done:

The resulting string will be passed on as it used to in earlier versions with one line for each fadx variable. You may not mix fadx template variables with other addresslines template variables.
The format string comes from two text fields in the AddressFormat table, with a total length of 508 characters; this should be enough, given that we do not have blanks in template variables.

Format string syntax

Examples

<name>\n<atmr> <atfn> <atln>\n<apad>\n<apa2>\n<acsz>

This example contains mainly template variables, and a little literal text (the spaces and new lines). There is no conditional text. It might expand to
Bogus Inc.
Mr. John Doe
45, Main Street

Bedford, MA 01730

There are some problems with the above example. If there is no Mr/Mrs entered, the space between and will be left standing; we also don’t want the blank line caused by the blank second address line. An alternative is like this:

<name>\n<atmr>[ ]<atfn> <atln>\n<apad>\n[<apa2>]\n<acsz>

Supposing that John Doe is no longer a “Mr”, the first step of the expansion would convert the template variables like this:

Bogus Inc.\n[ ]John Doe\n45, Main Street\n[]\nBedford, MA 01730

The following removal of conditional code would see that the first conditional block is at the beginning of a line, and delete it; it would also see that the second block is alone on a line, and delete both block and the following new line. The end result is:
Bogus Inc.
John Doe
45, Main Street
Bedford, MA 01730

But for a doctor with a two line address, we would still get

Bogus Medical Inc.
Dr. John Doe
45, Main Street
Richmond harbour
Bedford, MA 01730

Upper case

Some address formats require names to be in upper case. This is triggered by writing the template variable in all upper case, like this: <ATLN>
would expand into HANSEN, even if it says Hansen in the database.
Similarly, <Atln> should give initial upper case, converting hansen into Hansen.
<atln> will as today keep the cases from the database.

Preference for Casesensivity

Use case sensitive template variables is a preference in the section functions. It must be set in SOAdmin and is set for the database. Default is false, then template variables is case-insensitive as before. Meaning that it don’t matter what case the letters in the template variable has, the text fetched from the database keeps the cases.
If the preference is set to true, how template variables is written matters.
<NAME> gives upper case on all the text.
<Name> upper cases the first letter in the text.
<name> uses the text with cases as it is written in the database.

New template variables

We need to be able to access the first letter of the first name, <atin> seems to be free so use that. <ATIN> and <Atin> would give the initial in uppercase regardless of what is in the database. <atfn> <atmn> <atln> becomes Pierre van Mever, but would give us a double space if there were no middle name (bad). <ATIN>[. ]<atmn>[ ]<atln> becomes P. van Mever or just P. Mever (if he had no middle name), and <ATLN>, <atfn> becomes MEVER, Pierre (the French sometimes like this).

Automatically uppercase words or first letter in Sales & Marketing company and person cards or include Country prefix before zipcode

The client will automatically uppercase words or letters based on the settings in the addressformat table. This is controlled by the fields xxx_flags in the addressformat table.

To uppercase all letters in the city for a specific country, change the addressformat.city_flags value to 1. The values that may be used are:

Automagic copy street address to postal address in Sales & Marketing company and person cards

You would in some cases like that the Street address is automatically copied to the postal address, and this is done by adding the following bitmask to the different *_zip fields:
This example will automatically copy of street address for The Netherlands, who use a German address format (check this in the admin client – lists – country).

This is the queries that change this:
Open ISQL, HakonClient or another tool like it.

Select * from addressformat where name like ‘Ger%’

To copy street address to postal address, and reverse is done by updating the following in table addressformat.
update addressformat set address1_zip =1024 where addressformat_id=9
update addressformat set address1_zip =2048 where addressformat_id=10

update addressformat set city_zip =1025 where addressformat_id=9
update addressformat set city_zip =2049 where addressformat_id=10

update addressformat set zip_zip =1026 where addressformat_id=9
update addressformat set zip_zip =2050 where addressformat_id=10

To get it to look up the City or Zipcode information from the table ZipToCity:
update addressformat set address1_zip = 0 where addressformat_id=9
update addressformat set address1_zip = 0 where addressformat_id=10

update addressformat set zip_zip = 257 where addressformat_id=9
update addressformat set city_zip = 513 where addressformat_id=9

update addressformat set zip_zip = 258 where addressformat_id=10
update addressformat set city_zip = 514 where addressformat_id=10

Note that addressformat_id = 9 have the Street address information (atype_idx = 2), and addressformat_id=10 have the postal address information (atype_idx=1). To make them copy between the text entered (only when adding a new customer) you update the xxx_zip value. If you take the number 1024 as HEX, it’s 400, 2048 in HEX is 800, and this tells the CRM client to copy between these lines.

The database manual give a description on each field in Addressformat, but you need to know some of our internal resources (lead text to the address lines)

Values needed to understand the address table.

This table must contain special values that would be difficult to guess correctly. These are listed below with a description of how to use them.

E.g.: A person’s private address will have Address type Person 16384 + Subtype private 3 = 16387 as atype_idx in the address table.