Analysis of Boolean fields across the Promise Grant App and ensure documentation is correct

Owned by Amanda Mason (Unlicensed)
Last updated: Sept 16, 2019
6 min read

JIRA:  OPENAPPLY-7049 - Getting issue details... STATUS


FieldCurrent DB ValueBOG Data Dictionary Value
app_id

bigint

  • Data Type/Format: Bigint
  • Data Type from CC Promise Grant Application Downloadable Fields: bigint
  • Length: blank
  • Allows Null: No
  • Value: blank
ccc_id

character varying(8) 

  • Format, Length: Character varying(8)
  • Data Type from CC Promise Grant Application Downloadable Fields: String
  • Length: 8
  • Allows Null: No. This is a system assigned field and is not entered by the user.
  • Value:

The format of the ccc_id is:
Positions 1 - 3: AAA thru ZZZ (Sequentially assigned beginning with AAA)

Positions 4 – 7: 0001 thru 9999 (Sequentially assigned beginning with 0001 for each value in positions 1-3)

Position 8: For future expansion if all values become exhausted.

Note: In positions 1-3, “I” and “O” are not used to avoid confusion with “0” and “1”.

confirmation_number

character varying(25)

  • Data Type/Format: Character
  • Data Type from CC Promise Grant Application Downloadable Fields: String
  • Length: 25
  • Allows Null: blank
  • Value: blank
statuscharacter(1)
  • Data Type/Format: Character(1)
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 1
  • Allows Null: blank
  • Value

Blank – In progress

I = Initially written to submitted table

D= Downloaded by college

app_langcharacter(2)
  • Format/Length: bpchar, 2
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 2
  • Allows Null: blank
  • Value:

    es –Spanish

    en –English

college_idcharacter(3)
  • Data Type/Format: Character(3)
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 3
  • Allows Null: blank
  • Value: blank
year_codebigint
  • Data Type/Format: Character varying(15)
  • Data Type from CC Promise Grant Application Downloadable Fields Table: Blank
  • Length: blank
  • Allows Null: blank
  • Value: blank


year_descriptioncharacter varying(100)
  • Data Type/Format: Character varying(15)
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 100
  • Allows Null: blank
  • Value: blank
determined_residentcacharacter(1)
  • Data Type/Format: Yes/No 1
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 1
  • Allows Null: blank

  • Value:

    1 - Yes/Sí [internal default]

    0 - No

determined_ab540_eligiblecharacter(1)
  • Data Type/Format: Yes/No 1
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 1
  • Allows Null: blank
  • Value: 

    1 - Yes/Sí [internal default]

    0 – No

determined_non_res_exemptcharacter(1)
  • Data Type/Format: determined_non-res_exempt
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 1
  • Allows Null: blank
  • Value: 

    1 - Yes/Sí [internal default]

    0 – No

lastnamecharacter varying(50)
  • Data Type/Format: char, 50

    (Extended character set, allowing hyphens, letters with diacritical marks or accents, etc.)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 50 (current code in OpenCCC is 40; however, new OpenCCC will be coded to 50 so there is no need to update the DD).
  • Allows Null: blank
  • Value: Text string
firstnamecharacter varying(50)
  • Data Type/Format: char, 50

    (Extended character set, allowing hyphens, letters with diacritical marks or accents, etc.)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Text string; may be null.
middlenamecharacter varying(50)
  • Data Type/Format: char, 50
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Text string; may be null
mainphonecharacter varying(14)
  • Data Type/Format: char, 19 (OpenCCC Account) char, 14

    (CCCApply Standard and Promise Grant (BOG) applications) char, 25 (International application) 10 to 14 characters, numeric plus: (nnn) nnn-nnnn

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 14
  • Allows Null: blank
  • Value: Null or text string
mainphone_extcharacter varying(4)
  • Data Type/Format: char, 4
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 4
  • Allows Null: blank
  • Value: Null or numeric string; integer up to 9999
mainphone_auth_textboolean
  • Data Type/Format: char, 1 (OpenCCC Account)

    boolean, 1 (CCCApply Standard and International and Promise Grant (BOG) applications)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: boolean
  • Length: blank
  • Allows Null: blank
  • Value: 

    1 = Yes (checked)
    0 = No (not checked)

emailcharacter varying(128)
  • Data Type/Format: char, 254

    Note: Field length for Promise Grant (BOG) application is 128, Two fields; each up to 48 characters, alphanumeric plus ‘@’ and ‘.’

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Text string in valid email address format
non_us_addressboolean
  • Data Type/Format: boolean
  • Data Type from CC Promise Grant Application Downloadable Fields Table: Boolean
  • Length: blank
  • Allows Null: blank
  • Value: 

    encrypted

streetaddress1character varying(50)
  • Data Type/Format: char, 50

    Two fields (extended character set, allowing hyphens, letters with diacritical marks or accents, etc.)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Two text strings; second may be null
streetaddress2character varying(50)
  • Data Type/Format: char, 50

    Two fields (extended character set, allowing hyphens, letters with diacritical marks or accents, etc.)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Two text strings; second may be null
citycharacter varying(50)
  • Data Type/Format: char, 50

    (Extended character set, allowing hyphens, letters with diacritical marks or accents, etc.)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Text string
statecharacter varying(2)
  • Data Type/Format: char, 2
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 2
  • Allows Null: blank
  • Value: blank/null or 2-character USPS state code
provincecharacter varying(30)
  • Data Type/Format: char, 30

    Extended character set, allowing hyphens, letters with diacritical marks or accents, etc.

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 30
  • Allows Null: blank
  • Value: Null or text string
countrycharacter varying(2)
  • Data Type/Format: char, 2
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 2
  • Allows Null: blank
  • Value: ISO 3166 International Standard for country codes Alpha-2 code (officially assigned)
postalcodecharacter varying(20)
  • Data Type/Format: char, 20 numeric-plus: nnnnn-nnnn or nnnnn

      extended character set, allowing hyphens, symbols, letters with diacritical marks or accents, etc.

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 20
  • Allows Null: blank
  • Value: Null or nnnnn-nnnn or nnnnn
ssnencrypted SSN
  • Data Type/Format: text, 11 - Data is encrypted
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: blank
  • Allows Null: blank
  • Value: Null or numeric string with hyphens
ssn_hash



Note: the ssn_hash field hasn't been populated since December 2017, it appears. 

  • Data Type/Format: text
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length:
  • Allows Null:
  • Value: 
ssn_type
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
student_college_id
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
birthdate
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
marital_status
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
reg_dom_partner
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
born_before_23_year
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
married_or_rdp
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
us_veteran
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dependents
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
parents_deceased
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
emancipated_minor
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
legal_guardianship
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
homeless_youth_school
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
homeless_youth_hud
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
homeless_youth_other
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dependent_on_parent_taxes
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
living_with_parents
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dependency_status
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
cert_veteran_affairs
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
cert_national_guard
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_medal_honor
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_sept_11
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_police_fire
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
tanf_calworks
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ssi_ssp
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
general_assistance character
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
parents_assistance character
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dep_number_household
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ind_number_household
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dep_gross_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ind_gross_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dep_other_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ind_other_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dep_total_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ind_total_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_method_a
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_method_b
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_bogfw
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
confirmation_parent_guardian
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
parent_guardian_name
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ack_fin_aid
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
confirmation_applicant
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
last_page
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ssn_last4
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
tstmp_submit
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
tstmp_create
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
tstmp_update
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
tstmp_download
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
term_code
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
cryptokeyid integer
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ip_address

campaign1

campaign2

campaign3

ssn_exception

college_name

preferred_firstname

preferred_middlename

preferred_lastname

preferred_name

ssn_no

no_perm_address_homeless

no_mailing_address_homeless

determined_homeless

elig_method_d

mainphoneintl