Data Format

updated

DB2000V3 Tutorial

Data File Format

DB2000 maintains two internal data tables. Members and Enhanced Members.
Members is generally used for screen display.
Enhanced members (listed below as Enh Membs) is used for queries.
The output file format .cvs can be input by word processors, spreadsheets and other databases.

In the tables below, the ' Loc ' column shows the screens where the data can be viewed or changed.

R = Roster : G = Grade : H = History : C = Comm : B = Boats : U = User

USPS Maintained

Squadron Maintained

Locally Stored

Hidden

Roster, Comm and User screens fields

Field Name			Loc	Data Type	Field Length	Notes
(Members)	(Enh Membs)	.cvs	Loc	Data Type	Field Length	Notes
Mem_ID				Long	4	See note¹⁴
	Mem_ID			String	16	See note¹⁴
	Squadron	Squad No.		String	4
		Squad. Code		String	4
Squadron		Squad. Desc	R	String	30
	District	Dist No		String	2
Last Name	Last	Last Name	R	String	25
First Name	First	First Name	R	String	16
Certificate Number	Cert	Certificate	R	String	7	X123456
Tag	Tag			T/F	1	See note
HQ Rank	Rank	HQ Rank	R	String	8	(see rank.html)
SQ Rank	SquadRank	SQ Rank	R	String	8	(see rank.html)
Grade	Grade	Grade	R	String	2	S/P/AP/JN/N/SN
DA	SB_Status	DA	R	String	1	Dual Address
Swap	SB_Where	H/A	R	T/F	1	Home/Away
Address 1	Address	Address 1	R	String	30
Address 2	Address2	Address 2	R	String	30
City	City	City	R	String	30
State	State	State	R	String	2
Zip Code	ZIP	Zip Code	R	String	10	01234-6789
Telephone		Telephone	R	String	12	see note¹¹
Birthday		Birthday	R	String	8	MMDDCCYY
	Birthday	Birthday		Date/Time	8
Sex	Memsex	Memsex	R	String	1	M/F
Spouse	Spouse	Spouse	R	String	30
Spo. Cert	SpoCert	Spo Cert.	R	String	7	X123456
Spo. Sex	Sposex	Spo Sex	R	String	1	M/F
Wedding	Wedding	Wedding	R	String	8	MMDDCCYY
Affiliation	x	Affiliation	R	String	?
Grade/Education	(see Grade table below)
History	(see History table below)
Home Phone	Home Phone	Phone	C	String	12	AAAEEENNNN
Fax Phone	Fax Phone	Fax	C	String	12	AAAEEENNNN
Email Address	E-Mail	Email	C	String	40
Bus Phone	Bus. Phone	BusPhone	C	String	12	AAAEEENNNN
Ext	Ext	BusExt	C	String	5	Work extension
OK	OK	PhoneTag	C	String	1	Call work Y/N
Cell Phone	Cell Phone	CellPhone	C	String	12	AAAEEENNNN
Boat Information	(see Boat table below)
Squadron Field 1	Squad1	Squadron 1	U	String	30
Squadron Field 2	Squad2	Squadron 2	U	String	30

History

Field Name			Loc	Data Type	Field Length	Notes
(Members)	(Enh Membs)	.cvs	Loc	Data Type	Field Length	Notes
Cert Date	CertDate		H	String	50
	CertDate	Cert. Date		Date/Time	8
Prim Cert	PCert	Prim.Cert	H	String	7	X123456
Original Squadron	OS	Org.Sqd	H	String	4	(see sqd.html)
Original District	OD	Org.Dst	H	String	2	(see sqd.html)
Previous Squadron	PS	Pre.Sqd	H	String	4	(see sqd.html)
Previous District	PD	Pre.Dst	H	String	2	(see sqd.html)
Total Years Member	TotYrs	Tot.Years	H	Long	4
Last BDU Change	LastBDU	Last BDU	H	String	8	CCYYMMDD
Last HQ Change	LastHQ	Last HQ	H	String	8	CCYYMMDD
Type/Status Desc	MemTypeStat	Type / Stat Code	H	String	4	(see member.html)
Type/Status Desc	MemTypeStat	Type / Stat Desc	H	String	4	(see member.html)

Grade
Course, Test Dates and Awards

Loc

Field Name			Loc	Data Type	Field Length	Notes
(Members)	(Enh Membs)	.cvs	Loc	Data Type	Field Length	Notes
Grade	Grade	Grade	G	String	2	S/P/AP/JN/N/SN
MM	GH_Merit	MM	G	String	2	NN
Senior	GH_Senior	Senior	G	String	4	CCYY
Life	GH_Life	Life	G	String	4	CCYY
Edpro	GH_Edpro	Edpro	G	String	4	CCYY
EdAch	GH_Edach	EdAch	G	String	4	CCYY
S	GH_S	S	G	String	6	CCYYMM
P	GH_P	P	G	String	6	CCYYMM
AP	GH_AP	AP	G	String	6	CCYYMM
JN	GH_JN	JN	G	String	6	CCYYMM
N	GH_N	N	G	String	6	CCYYMM
CP	GH_CP	CP	G	String	6	CCYYMM
EM	GH_EM	EM	G	String	6	CCYYMM
ME	GH_ME	ME	G	String	6	CCYYMM
WX	GH_W	W	G	String	6	CCYYMM
IQ	GH_IQ	IQ	G	String	6	CCYYMM
IT	GH_IT	IT	G	String	6	CCYYMM
IA	GH_IA	IA	G	String	6	CCYYMM
SA	GH_SAIL	SAIL	G	String	6	CCYYMM
OT	GH_OT	OT	G	String	6	CCYYMM
SE_000C	SE_000C	SE_000C	G	String	8	CCYYMMDD
		SE_001C	G	String	8	CCYYMMDD
		SE_002C	G	String	8	CCYYMMDD
PI_000C	PI_000C	PI_000C	G	String	8	CCYYMMDD
PI_000O	PI_000O	PI_000O	G	String	8	CCYYMMDD
AP_000C	AP_000C	AP_000C	G	String	8	CCYYMMDD
AP_000O	AP_000O	AP_000O	G	String	8	CCYYMMDD
JN_000C	JN_000C	JN_000C	G	String	8	CCYYMMDD
JN_000O	JN_000O	JN_000O	G	String	8	CCYYMMDD
JN_000S	JN_000S	JN_000S	G	String	8	CCYYMMDD
NA_000O	NA_000O	NA_000O	G	String	8	CCYYMMDD
NA_000C	NA_000C	NA_000C	G	String	8	CCYYMMDD
NA_000S	NA_000S	NA_000S	G	String	8	CCYYMMDD
SA_001C	SA_001C	SA_001C	G	String	8	CCYYMMDD
WX_101C	WE_101C	WE_101C	G	String	8	CCYYMMDD
WX_102C	WE_102C	WE_102C	G	String	8	CCYYMMDD
		WE_000C	G	String	8	CCYYMMDD
VSC_01	VSC_01	VSC_01	G	String	8	CCYYMMDD
		CP_000C	G	String	8	CCYYMMDD
		EM_000C	G	String	8	CCYYMMDD
		EM_101C	G	String	8	CCYYMMDD
		EM_102C	G	String	8	CCYYMMDD
		ME_000C	G	String	8	CCYYMMDD
		IQ_101C	G	String	8	CCYYMMDD

Boat Information

Field Name			Loc	Data Type	Field Length	Notes
(Members)	(Enh Membs)	.cvs	Loc	Data Type	Field Length	Notes
Boat Name	BoatName	Boat Name	B	String	30
Boat Type	BoatType	Boat Type	B	String	30	P/S
Home Port	BoatPort	Boat Port	B	String	30
OAL	BoatOAL	OAL	B	String	6
Beam	BoatBeam	Beam	B	String	6
Draft	BoatDraft	Draft	B	String	6
Power	BoatDraft	Power	B	String	15
Fuel Cap	BoatFuelCap	Fuel Capacity	B	String	10
Speed	BoatSpeed	Speed	B	String	10
Range	BoatRange	Range	B	String	10
Clearance	BoatClearance	Clearance	B	String	6
Berths	BoatBerths	Berths	B	String	15

Notes:

All fields are separated by a comma (,).
String values are always enclosed in double-quotes (").
Field length given is maximum characters allowed for the field.
Unknown, empty, or otherwise invalid strings may be the empty string, all spaces, or zero-filled.
Integer values might contain leading zeros, particularly when used to represent dates.
(This applied to months and days from 1-9.Use 01-09 to obtain the required 6 or 8 digits.)
Birth and Wedding dates are entered DDMMCCYY but stored CCYYMMDD.
Unknown, empty, or otherwise invalid integers are represented by 0.
(This applies to older versions. Presently they have a NULL value.)
Zip codes will require leading zeros to correctly format 5-digit and zip-plus-4 codes.
(This applies to older versions. Presently Zip and Zip+ fields are combined.)
Zip-plus-4 notation using a plus-4 of all zeros (-0000) is not valid - use the 5-digit instead.
(This applies to older versions. Presently there is no Zip+ field.)
Previous versions of DB2000 kept membership type and membership status in separate field. In the present version they are combined.
Telephone is not the same field as Home Phone
Telephone is available on the Roster screen. Home Phone is on the Comm screen. However, an entry in either field will fill both fields. Phone numbers are entered AAAEEENNNN with no spaces. Punctuation is displayed by the program
Tag - this field is set to FALSE unless the record is tagged
BDUChange - this field is set to FALSE unless the record has been changed
Mem_ID - This field is internally generated/maintained and is of no use for queries or displays. It simply refers to the record order in the table.

The list maintained on the National web site is quite incomplete, obsolete and inaccurate. After months of requesting this page be updated and sending updated pages, nothing has been done.

Hidden fields displayed in the Members Query

Field Name		Data Type	Field Length	Notes
(Members)	(Enh Membs)	Data Type	Field Length	Notes
Displayed on Roster screen if Dual Address option is used	SB_Address	String	30	Snow Bird Fields
	SB_Address2	String	30
	SB_City	String	30
	SB_State	String	2
	SB_ZIP	String	10
	SB_Fax	String	12
	SB_Phone	String	12
	SB_BusPhone	String	12
	SB_BusExt	String	5
	SB_BusOK	String	1
	SB_Email	String	40
	SB_Status	T/F	1
	SB_Where	String	1
	SB_CellPhone	String	12
	BDUChange	see note¹³
	OtherMember	Possible Affiliation option