Difference between revisions of "Data tables"

Field Name

Data Type

Description

Entity Table

ipkEntityID

AutoNumber

Entity key

datEntered

Date/Time

Date the record was added

txtUsername

Text 50

Login ID

txtPassword

Text 50

Password

blnTrainer

Yes/No

Yes or No: certified trainer

blnLocalSupporter

Yes/No

Yes or No: local NVC supporter

blnLocalOnlyAccess

Yes/No

Yes or No: Determines access permissions within the website, not a data-entry field for users. For Local NVC Supporters/ Groups/Websites primarily, but used at administrators’ discretion.

blnOrganizer

Yes/No

Yes or no: organizer for Center-sponsored trainings

ifkContactType

Text 50

NOTE: don’t understand what this field is for.

One of 3: coordinator/contact person/organizer

blnCenterDirector

Yes/No

Yes or no: director of the Center

blnOfficeManager

Yes/No

Yes or no: office manager of the Center

blnIITAdministration

Yes/No

Yes or no: IIT administration

blnEdDirector

Yes/No

Yes or no: director of educational services

blnIntlProjectCoord

Yes/No

Yes or no: international project coordinator

blnCNVCStaff

Yes/No

Yes or no: CNVC staff

blnScheduler

Yes/No

Yes or no: scheduler

blnWebmaster

Yes/No

Yes or no: webmaster

blnLocalGroup

Yes/No

Yes or no: regional center

blnLocalWebsite

Yes/No

Yes or no: regional website

blnBoardMember

Yes/No

Yes or no: board member

rdolstBoardOffice

Text 50

One of 5: president/vicepresident/secretary/treasurer/ memberatlarge; we have had a person hold multiple offices

blnBoardPresident

Yes/No

Yes or no: board president

blnBoardVicePresident

Yes/No

Yes or no: board vice president

blnBoardSecretary

Yes/No

Yes or no: board secretary

blnBoardTreasurer

Yes/No

Yes or no: board treasurer

blnBoardMemberAtLarge

Yes/No

Yes or no: board member at large

blnEdMaterialsCom

Yes/No

Yes or no: educational materials committee

blnFinanceCom

Yes/No

Yes or no: finance committee

blnFundraiseCom

Yes/No

Yes or no: fund raising committee

blnInsideCNVCAdmin

Yes/No

Yes or no: insidecnvc administrator

blnSuspend

Yes/No

Yes or no: “yes” means this entity doesn’t show up anywhere (on no pages or lists) except for the administrator/center who would need to be able to remove the suspension or delete the person.

blnProjectLeader

Yes/No

txtProjectName

Text 70

Name(s) of project(s) Store as is

(there are about six in existence, but we can have new ones).

txtGivenName

Text 25

* for display, only. Store as is. People from foreign countries may use accents here.

txtSurname

Text 35

* for display, only. Store as is. Ditto.

txtGroupName

Text 70

* for display, only. Store as is. Ditto.

memPostalAddress

Memo

Probably up to 7 lines of 50 characters. Store as is. Might be more lines in some parts of the world, though I did hear that 7 was the most.

tfkPhoneType1

Text 20

(value from drop-down list)

txtNationalPhone1

Text 18

National phone number 1; number of digits will vary; may contain dashes, parentheses, periods; store as is.

txtInternationalPhone1

Text 18

International phone number 1; number of digits will vary; may contain dashes, parentheses, periods; store as is.

tfkPhoneType2

Text 20

(value from drop-down list)

txtNationalPhone2

Text 18

National phone number 2

txtInternationalPhone2

Text 18

International phone number 2

tfkPhoneType3

Text 20

(value from drop-down list)

txtNationalPhone3

Text 18

National phone number 3

txtInternationalPhone3

Text 18

International phone number 3

txtEmail

Text 70

Email address, can be blank.

txtLanguages

Text 100

Languages, can be blank

txtWebsite

Text 70

Website address, will make into working link on output pages. Can be blank.

blnInsidePermit

Yes/No

NOT USED

blnYahooPermit

Yes/No

Yes or no: agree to be on “CNVC Trainers’ Network” (the Yahoo! Group)? (interactive email group)

txtYahooEmail

Text 70

Email address for the Yahoo! Group (interactive email group).

blnWebPermit

Yes/No

Yes or no: agree to be on the public CNVC website. If “no” do not collect any web information (below). If they key in web information and permission is “no,” that should be an error. Error message: “If you want your contact information to display on the public cnvc.org website, you must click on the “yes” button and give us permission”

txtWebCity

Text 50

Store as is.

ifkWebCountry

Number

(value from a modifiable drop-down list)

ifkWebState

Number

(value from a modifiable drop-down list)

tfkWebPhoneType1

Text 20

(value from drop-down list)

txtWebNationalPhone1

Text 18

Web National phone number 1

txtWebInternationalPhone1

Text 18

Web International phone number 1

tfkWebPhoneType2

Text 20

(value from drop-down list)

txtWebNationalPhone2

Text 18

Web National phone number 2

txtWebInternationalPhone2

Text 18

tfkWebPhoneType3

Text 20

(value from drop-down list)

txtWebNationalPhone3

Text 18

Web National phone number 3

txtWebInternationalPhone3

Text 18

Web International phone number 3

txtWebEmail

Text 70

Web email address, can be blank. NOTE: I want our email addresses protected better than they are now.

We do have some people without email addresses.

txtWebLanguages

Text 100

Languages the entity speaks, can be blank. On the public website would like to be able to search for trainers/people by language

txtWebWebsite

Text 70

Website address, will make into working link on output pages. Can be blank

tfkStatus

Text 50

Drop Down fed from tblStatus

(“active” “suspended” and “pending”)

blnNewsletterEnabled

Yes/No

NOTE: this seems to me to be a duplicate field to BlnYahooPermit

blnPublicInfoEnabled

Yes/No

NOTE: this seems to me to be a duplicate field to BlnWebPermit

Event Table

Training events

ipkEventID

AutoNumber

Event key

tfkStartYear

Text 4

(value from drop-down list) On the contact information page, the drop-down list only shows the current year, next year and 9999.

tfkStartMonth

Text 2

(value from drop-down list: 1–12 and 99)

tfkStartDay

Text 2

(value from drop-down list: 1–31 and 99)

datStart

Date/Time

NOTE: not currently used. Created from the 3 fields above, will be used to stop showing trainings on the public trainings pages after their start date.

txtDisplayDate

Text 100

Written out date to display, in their language.

tfkEventLevel

Text 50

(value from modifiable drop-down list) Level of the training

ifkTopicID

Number

(value from modifiable drop-down list) Topic of the training

txtShowStartDate

Text 60

This is the start date of the training for display purposes. Store as they key it in. I expect it to be in their own language. Blank for trainings with a type of “practice group.”

_txtShowEndDate

Text 30

This is the end date of the training for display purposes. Ditto above.

ifkDuration

Number

Integer

tfkDuration

Text 50

From 3 radio buttons: “hours” “days” “weeks”

txtEventCity

Text 100

* City name. Store as is.

ifkEventCountryID

Number

(value from modifiable drop-down list) Name of the country of the training.

ifkEventStateID

Number

(value from drop-down list) For the *US only, you will get state names as well as Washington D.C. For other countries, this will be blank.

txtEventTitle

Text 100

* Title of the training. Store as is.

txtPreparation

Text 200

Also known as a “prerequisite.” Store as is.

memDescription

Memo

The class description. This field is limited to 1000 characters, could make it 10,000.

txtTrainingWebsite

Text 140

Training website address, will make into working link on output pages. Can be blank.

tfkExpYear

Text 4

(value from drop-down list) On the contact information page, the drop-down list only shows the current year and next year.

tfkExpMonth

Text 2

(value from drop-down list: 1–12)

tfkExpDay

Text 2

(value from drop-down list: 1–31)

datExp

Date/Time

NOTE: this field is unnecessary. We are not going to show past trainings. Created from the 3 fields above, this is the date the training will stop showing up on the public trainings pages. So someone can show all his or her trainings for the past year.

ifkEntityID_Contact

Number

(value from the entity record of the contact person/group) Want to change this to allow multiple contact people the same way we have multiple trainers.

txtLanguages

Text 200

Currently specify the languages the training will be in to view. Would like to be able to search for trainings by language.

Countries Table

Modifiable drop-down list

starting position: “select a country”

ipkCountryID

AutoNumber

Country key

txtCountry

Text 45

Country name. This field needs to be 70 characters

nbrSortOrder

Number

This is a sequential number applied to the countries when they are in alphabetical order. It is used to put them in alphabetical order in the drop-down list.

txtCountryCode

Text 50

This is the 2-character internet country code—want it lower case, always.

Level Table

Modifiable drop-down list

starting position:“select one”

tpkEventLevel

Text 50

txtLevelAbbr

Text 50

nbrSort

Number

Phone Type Table

Drop-down list starting position: “select one”

Also includes an entry “unused” that causes the other 2 related phone fields to be cleared.

tpkPhoneType

Text 50

nbrSortOrder

Number

State Table

Drop-down list starting position: “select a state”

ipkStateID

AutoNumber

State key

txtState

Text 50

Written out state name, including Washington D.C.

nbrSortOrder

Number

This is a sequential number applied to the states when they are in alphabetical order. It is used to put them in alphabetical order in the drop-down list.

txtRegion

Text 50

Regional names: divided the USA into 6 regions (NW, NC, NE, SW, SC, and SE, written out)

txtStateCode

Text 50

The 2-character postal state code (e.g., MN)

Status Table

Drop-down list “active” “pending” “suspended”

tpkStatus

Text 50

Status name

nbrSort

Number

Number for sequence of drop-down list

Topic Table

Modifiable drop-down list

starting position:“select one”

ipkTopicID

AutoNumber

txtTopic

Text

Trainer Report Table

This table needs to be cleared out on the first of the year.

ipkTrainerReportID

AutoNumber

ifkEntityID

Number

The trainer’s entity table key

datPosted

Date/Time

Date the report was posted

datExpire

Date/Time

This will always be 1/1 of the next year

memReport

Memo

The trainer report. These can be very long and are usually created offline in a word processing program.