AddressFormat Table (57)

•

Environment: onsite, online

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