We’ve developed some resources to help you work effectively from home during COVID-19 Click here to learn more

Addressformat

In this article

    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).