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:
• |
Template variables will be substituted by their actual value |
• |
Conditional text (separated with square brackets [] )will be left standing, unless it is the first or last text on a line, or there are two or more conditional texts next to each other; in that case only the last text is kept and the rest are deleted. If there are only conditional texts on a line, the whole line will be deleted. |
• |
Any literal text will be left standing |
• |
|x| Sometimes only the address part is needed, so a special delimiter will be used in the template string to tell where the name/title/… ends and the address begins. The delimiter is |x| , so this sequence of characters can not be used for anything else in the template string stored in the database. To be safe the sequence |X| is also reserved |
• |
If there are line breaks in the form of “\n”, these will be substituted. |
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
• |
Literal text in the format string is anything that does not follow the syntax for a template variable or a conditional text. |
• |
Conditional text is indicated by having square brackets around it. So [, ] is a conditional text consisting of a comma followed by a space. Conditional texts can not be nested. If it lands at the beginning or end of a line (following expansion of all templates), it will be deleted from the string. If several conditional texts follow right after each other, all but the last is deleted (if it is the last on the line it will be deleted) If there is nothing on a line except one or more conditional texts, the whole line (including its new line) is deleted. |
• |
Template variables are any of the SCurrentAbbr variables, using the standard <> syntax. The variables cannot be referenced here (recursion). Note also that since we are expanding a simple string programmatically, we do not need to keep the length constant and therefore do not need to “reserve” space by having blanks in the template variable. |
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:
What |
Example |
Value |
Where in addressformat table |
Uppercase all |
FRANKFURT AM MAIN |
1 |
address1_flags, address2_flags, address3_flags, city_flags, county_flags, state_flags |
Uppercase Initial |
Frankfurt am main |
2 |
address1_flags, address2_flags, address3_flags, city_flags, county_flags, state_flags |
Uppercase Words |
Frankfurt Am Main |
4 |
address1_flags, address2_flags, address3_flags, city_flags, county_flags, state_flags |
Include country prefix |
D-12345 |
8 |
zip_flags |
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)
Resource id
|
English name
|
14000
|
Street Address:
|
14001
|
Postal Address:
|
14002
|
Postcode/City
|
14003
|
Country:
|
14004
|
Address:
|
14005
|
Address 1:
|
14006
|
Address 2:
|
14007
|
Address 3:
|
14008
|
State/Zipcode:
|
14009
|
City:
|
14010
|
County/Pcode:
|
14011
|
P.O. Box:
|
14012
|
Province/Pcode:
|
14013
|
City/Pcode:
|
14014
|
Postcode:
|
1202
|
City/State/Zip Code:
|
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.
Address |
Type |
Contact |
1 |
Person |
16384 |
SubAddress |
Type |
Postal |
1 |
Street |
2 |
Private |
3 |
E.g.: A person’s private address will have Address type Person 16384 + Subtype private 3 = 16387 as atype_idx in the address table.
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).