AddressFormat Table (57)

• 9 minutes to read
 • 9 minutes to read

Address formats for GUI and labels. See more information; Addressformat on http;//techdoc.superoffice.com

Fields

Name Description Type Null
addressformat_id Primary key PK
name Name of address format (descriptive, multi-language) String(239)
layout_id Corresponds to Country.layout_id ShortId
atype_idx Corresponds to Address.atype_idx UShort
address1_line Line no. for address1 field, 0 if not shown UShort
address1_subpos Position within line for address1 field UShort
address1_leadtext Lead text resource ID for address1 field, 0 = no leadtext Id
address1_zip Zip lookup functionality for address1 field: 0 = none, 1 = source, 2 = target UShort
address1_length Field length in characters for address1 field UShort
address1_flags Field flags for address1 field: See EAddrFormatFlags enum UShort
address1_mask Formatting mask for address1 field String(9)
address2_line Line no. for address2 field, 0 if not shown UShort
address2_subpos Position within line for address2 field UShort
address2_leadtext Lead text resource ID for address2 field, 0 = no leadtext Id
address2_zip Zip lookup functionality for address2 field: 0 = none, 1 = source, 2 = target UShort
address2_length Field length in characters for address2 field UShort
address2_flags Field flags for address2 field: See EAddrFormatFlags enum UShort
address2_mask Formatting mask for address2 field String(9)
address3_line Line no. for address3 field, 0 if not shown UShort
address3_subpos Position within line for address3 field UShort
address3_leadtext Lead text resource ID for address3 field, 0 = no leadtext Id
address3_zip Zip lookup functionality for address3 field: 0 = none, 1 = source, 2 = target UShort
address3_length Field length in characters for address3 field UShort
address3_flags Field flags for address3 field: See EAddrFormatFlags enum UShort
address3_mask Formatting mask for address3 field String(9)
city_line Line no. for city field, 0 if not shown UShort
city_subpos Position within line for city field UShort
city_leadtext Lead text resource ID for city field, 0 = no leadtext Id
city_zip Zip lookup functionality for city field: 0 = none, 1 = source, 2 = target UShort
city_length Field length in characters for city field UShort
city_flags Field flags for city field: See EAddrFormatFlags enum UShort
city_mask Formatting mask for city field String(9)
county_line Line no. for county field, 0 if not shown UShort
county_subpos Position within line for county field UShort
county_leadtext Lead text resource ID for county field, 0 = no leadtext Id
county_zip Zip lookup functionality for county field: 0 = none, 1 = source, 2 = target UShort
county_length Field length in characters for county field UShort
county_flags Field flags for county field: See EAddrFormatFlags enum UShort
county_mask Formatting mask for county field String(9)
state_line Line no. for state field, 0 if not shown UShort
state_subpos Position within line for state field UShort
state_leadtext Lead text resource ID for state field, 0 = no leadtext Id
state_zip Zip lookup functionality for state field: 0 = none, 1 = source, 2 = target UShort
state_length Field length in characters for state field UShort
state_flags Field flags for state field: See EAddrFormatFlags enum UShort
state_mask Formatting mask for state field String(9)
zip_line Line no. for zip field, 0 if not shown UShort
zip_subpos Position within line for zip field UShort
zip_leadtext Lead text resource ID for zip field, 0 = no leadtext Id
zip_zip Zip lookup functionality for zip field: 0 = none, 1 = source, 2 = target UShort
zip_length Field length in characters for zip field UShort
zip_flags Field flags for zip field: See EAddrFormatFlags enum UShort
zip_mask Formatting mask for zip field String(9)
extraFlags OBSOLETE: Extra flags for tag substitution mechanism, see idb.h. This function has been taken over by the tag-based formatting, controlled by the labelLayout and labelLayout2 fields UInt
labelLayout Format specification for labels, uses standard tags String(254) ●
registered Registered when UtcDateTime
registered_associate_id Registered by whom FK associate
updated Last updated when UtcDateTime
updated_associate_id Last updated by whom FK associate
updatedCount Number of updates made to this record UShort
labelLayout2 Part two of format specification, concatenated with part one above String(254) ●
isBuiltIn Is this row populated by SuperOffice? Bool ●

AddressFormat table relationship diagram

LabelLayout/LabelLayout2

In SuperOffice 6, we replaced the ExtraFlags formatting of labellayout with the two fields LabelLayout and LabelLayout2. We use some special characters to separate the template variables:

[ ] is to add a space between the template variables.

[\n] - is to add a new line.

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

Automatic copy street address to postal address

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, which uses a German address format (check this in the admin client – lists – country).

The queries that change this:

Open ISQL, HakonClient, or another tool like it.

Select * FROM addressformat WHERE name LIKE 'Ger%'

address format -screenshot

To copy street address to postal address, and the 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 zip code 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 = 514 WHERE addressformat_id=9

UPDATE addressformat SET zip_zip = 258 WHERE addressformat_id=10
UPDATE addressformat SET city_zip = 513 WHERE addressformat_id=10
Note

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 docs describe each field in table 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:

Indexes

Fields Types Description
addressformat_id PK Clustered, Unique

Relationships

Table Description
associate Employees, resources and other users - except for External persons

Replication Flags

  • Replicate changes DOWN from central to satellites and travellers.
  • Replicate changes UP from satellites and travellers back to central.
  • Copy to satellite and travel prototypes.

Security Flags

  • No access control via user's Role.