Home → New Client Setups → VLO to CM7 Conversions → Script
1.1. Script
API_ID = ind_API_Id
FIRSTNAME = ind_FirstName
MIDDLENAME, or MIDDLE_INITAL (if name is not provided) = ind_MiddleName
LASTNAME = ind_LastName
SURN_SUFFIX = ind_Title & ind_Suffix
SEX = ind_Gender
BIRTHDATE = ind_Birthdate
REGIS_Date = ind_Register
VOTER_ID = ind_VoterId
PHONE_NUM = Home Phone Number (If not already on the record)
MOBILE_NUM = Mobile Phone Number (If not already on the record)
EMAIL & EMAIL_MATCH_TYPE = I = Home Email Address (If not already on the record)
EMAIL2 & EMAIL_MATCH_TYPE = I = Home Email Address (If not already on the record)
EMAIL3 & EMAIL_MATCH_TYPE = I = Home Email Address (If not already on the record)
ADDRESS = Home Address Line 1 (DEFAULT ADDRESS, if no address on record, do not overwrite client preferred, unless this is NCOA in which case the provided NCOA, which is a different field than these would be the preferred)
CITY = Home Address City
STATE = Home Address State
ZIP = Home Address Zip5
ZIP4 = Home Address Zip4
MADDRESS = Mailing Address 1 (IF DIFFERENT THAN HOME & DEFAULT IF DIFFERENT, if no address on record, do not overwrite client preferred, unless this is NCOA in which case the provided NCOA, which is a different field than these would be the preferred)
MCITY = Mailing City
MSTATE = Mailing State
MZIP = Mailing Zip5
MZIP4 = Mailing Zip4
AI_COUNTY = iad_County (Both Home & Mailing)
PREC_NO1 = iad_PrecinctCode (Both Home & Mailing)
PREC_NAME = iad_PrecinctName (Both Home & Mailing)
CONG_DIST = iad_Congress (Both Home & Mailing)
ST_UP_HOUS = iad_Senate (Both Home & Mailing)
ST_LO_HOUS = iad_Legislature (Both Home & Mailing)
Ind_LastVoted = CASE WHEN VTR_GEN18 <> '' THEN 'General 2018'
WHEN VTR_PRI18 <> '' THEN 'Primary 2018'
WHEN VTR_OTH18 <> '' THEN 'Other 2018'
WHEN VTR_GEN17 <> '' THEN 'General 2017'
WHEN VTR_PRI17 <> '' THEN 'Primary 2017'
WHEN VTR_OTH17 <> '' THEN 'Other 2017'
WHEN VTR_GEN16 <> '' THEN 'General 2016'
WHEN VTR_PRI16 <> '' THEN 'Primary 2016'
WHEN VTR_OTH16 <> '' THEN 'Other 2016'
WHEN VTR_GEN15 <> '' THEN 'General 2015'
WHEN VTR_PRI15 <> '' THEN 'Primary 2015'
WHEN VTR_OTH15 <> '' THEN 'Other 2015'
WHEN VTR_GEN14 <> '' THEN 'General 2014'
WHEN VTR_PRI14 <> '' THEN 'Primary 2014'
WHEN VTR_OTH14 <> '' THEN 'Other 2014'
WHEN VTR_GEN13 <> '' THEN 'General 2013'
WHEN VTR_PRI13 <> '' THEN 'Primary 2013'
WHEN VTR_OTH13 <> '' THEN 'Other 2013'
WHEN VTR_GEN12 <> '' THEN 'General 2012'
WHEN VTR_PRI12 <> '' THEN 'Primary 2012'
WHEN VTR_OTH12 <> '' THEN 'Other 2012'
WHEN VTR_GEN11 <> '' THEN 'General 2011'
WHEN VTR_PRI11 <> '' THEN 'Primary 2011'
WHEN VTR_OTH11 <> '' THEN 'Other 2011'
WHEN VTR_GEN10 <> '' THEN 'General 2010'
WHEN VTR_PRI10 <> '' THEN 'Primary 2010'
WHEN VTR_OTH10 <> '' THEN 'Other 2010'
WHEN VTR_GEN09 <> '' THEN 'General 2009'
WHEN VTR_PRI09 <> '' THEN 'Primary 2009'
WHEN VTR_OTH09 <> '' THEN 'Other 2009'
WHEN VTR_GEN08 <> '' THEN 'General 2008'
WHEN VTR_PRI08 <> '' THEN 'Primary 2008'
WHEN VTR_OTH08 <> '' THEN 'Other 2008'
Else '' END
PARTY_CODE = ind_pty_GUID as forignkey w/ std_Party and just nvarchar/text in ind_RegisteredParty
CASE WHEN 'A' THEN 'American Independent'
WHEN 'B' THEN 'Constitutional'
WHEN 'C' THEN 'Consumer'
WHEN 'D' THEN 'Democratic'
WHEN 'E' THEN 'Inferred Democrat'
WHEN 'F' THEN 'Reform'
WHEN 'G' THEN 'Green'
WHEN 'H' THEN 'Liberal'
WHEN 'I' THEN 'Independent'
WHEN 'J' THEN 'Other'
WHEN 'K' THEN 'Independent'
WHEN 'L' THEN 'Libertarian'
WHEN 'N' THEN 'Independent'
WHEN 'O' THEN 'Independent'
WHEN 'P' THEN 'Peace and Freedom'
WHEN 'Q' THEN 'Socialist'
WHEN 'R' THEN 'Republican'
WHEN 'S' THEN 'Inferred Republican'
WHEN 'T' THEN 'Right to Life'
WHEN 'U' THEN 'Independent'
WHEN 'V' THEN 'Conservative'
WHEN 'W' THEN 'Natural Law'
WHEN 'Y' THEN 'Other'
WHEN 'Z' THEN 'Independence Party'
ELSE '' END
Donor Tags (Green Color w/ Type of "Donor"):
DON_ANML Donor Tag: Animal Welfare
DON_ARTCUL Donor Tag: Arts/Culture
DON_CHARIT Donor Tag: Charitable Org
DON_CHILD Donor Tag: Childrens Charites
DON_ENVIR Donor Tag: Environment
DON_ENVWLD Donor Tag: Environment
DON_HEALTH Donor Tag: Health
DON_INTAID Donor Tag: International Aid
DON_OTHER Donor Tag: Other Charitable
DON_POLCONS Donor Tag: Political (Conservative)
DON_POLIT Donor Tag: Political
DON_POLLIB Donor Tag: Political (Liberal)
DON_RELIG Donor Tag: Religious
DON_VET Donor Tag: Veterans
FEC Donor Tag: FEC/Political
Issue Tags (Orange Color w/ Type of "Issue")
PRFL_2NDAMEND Issue Tag: 2nd Amendment
PRFL_ACTIVE_MIL Issue Tag: Active Military
PRFL_ANML_RIGHTS Issue Tag: Animal Rights
PRFL_BORDER_SECURITY Issue Tag: Border Security
PRFL_CHOICELIFE = 1 - Issue Tag: Pro Life
PRFL_CHOICELIFE = 2 - Issue Tag: Pro Choice
PRFL_CLINTON_SUPPORT Issue Tag: Clinton
PRFL_EDUCATION Issue Tag: Education
PRFL_ENVIRONMENT Issue Tag: Environment
PRFL_EVANGELICAL Issue Tag: Evangelicals
PRFL_GUN_CONTROL Issue Tag: Gun Control
PRFL_HEALTHCARE_REFORM = 1 = Issue Tag: Healthcare (Pro ACA)
PRFL_HEALTHCARE_REFORM = 2 = Issue Tag: Healthcare (Oppose ACA)
PRFL_IMMIGRATION_REFORM Issue Tag: Immigration Reform
PRFL_LABOR Issue Tag: Labor
PRFL_LGBT_SUPPORT = 1 OR 2 Issue Tag: LGBT
PRFL_MARIJUANA_REFORM Issue Tag: Marijuana Reform
PRFL_MARRIAGE_EQUALITY = 1 Issue Tag: Marriage Equality
PRFL_MIL_SUPPORT Issue Tag: Military Support
PRFL_OBAMA Issue Tag: Obama
PRFL_PERSUADABLE_VOTER Issue Tag: Swing Voter
PRFL_POLITICAL_IDEOLOGY = C Issue Tag: Conservative
PRFL_POLITICAL_IDEOLOGY = M Issue Tag: Moderate
PRFL_POLITICAL_IDEOLOGY = L Issue Tag: Liberal
PRFL_SANDERS_SUPPORT Issue Tag: Bernie Sanders
PRFL_TAXES Issue Tag: Taxes
PRFL_TEAPARTY = 1: Issue Tag: Tea Party
PRFL_TRUMP_SUPPORT Issue Tag: Trump
PRFL_VETERAN Issue Tag: Veterans
Voter (Purple Color w/ Type of "Voter")
ABSENTEE Voter Tag: Absentee (Tag Description: Permanent Absentee)
4/4 Voter (Tag Description: Both General and Primary Elections in 2018 & 2016)
3/4 Voter (Tag Description: Both General and Primary Elections in 2018 & 2016)
2/4 Voter (Tag Description: Both General and Primary Elections in 2018 & 2016)
1/4 Voter (Tag Description: Both General and Primary Elections in 2018 & 2016)
0/4 Voter (Tag Description: Both General and Primary Elections in 2018 & 2016)
SUM(
CASE WHEN VTR_GEN_18 <> '' THEN 1 ELSE 0 END +
CASE WHEN VTR_PRI_18 <> '' THEN 1 ELSE 0 END +
CASE WHEN VTR_GEN_16 <> '' THEN 1 ELSE 0 END +
CASE WHEN VTR_PRI_16 <> '' THEN 1 WHEN VTR_PPP_16 <> '' THEN 1 ELSE 0 END
)
4/4 General (Tag Description: Voted in General 2018, 2016, 2014, 2012)
3/4 General (Tag Description: Voted in General 2018, 2016, 2014, 2012)
2/4 General (Tag Description: Voted in General 2018, 2016, 2014, 2012)
1/4 General (Tag Description: Voted in General 2018, 2016, 2014, 2012)
0/4 General (Tag Description: Voted in General 2018, 2016, 2014, 2012)
SUM(
CASE WHEN VTR_GEN_18 <> '' THEN 1 ELSE 0 END +
CASE WHEN VTR_GEN_16 <> '' THEN 1 ELSE 0 END +
CASE WHEN VTR_GEN_14 <> '' THEN 1 ELSE 0 END +
CASE WHEN VTR_GEN _12 <> '' THEN 1 ELSE 0
)
4/4 Primary (Tag Description: Voted in Primary 2018, 2016, 2014, 2012)
3/4 Primary (Tag Description: Voted in Primary 2018, 2016, 2014, 2012)
2/4 Primary (Tag Description: Voted in Primary 2018, 2016, 2014, 2012)
1/4 Primary (Tag Description: Voted in Primary 2018, 2016, 2014, 2012)
0/4 Primary (Tag Description: Voted in Primary 2018, 2016, 2014, 2012)
SUM(
CASE WHEN VTR_PRI_18 <> '' THEN 1 ELSE 0 END +
CASE WHEN VTR_PRI_16 <> '' THEN 1 WHEN VTR_PPP_16 <> '' THEN 1 ELSE 0 END
CASE WHEN VTR_PRI_14 <> '' THEN 1 ELSE 0 END +
CASE WHEN VTR_PRI_12 <> '' THEN 1 WHEN VTR_PPP_12 <> '' THEN 1 ELSE 0 END