This is a simple web tool that allows for the uploading of a series of four excel spreadsheets which are then consolidated and linked to provide a unique table of clients and a table of duplicate client evaluations (based on same client ID and timestamp).
Data should be in the following formast:
VISPDAT:
| osUsers_UserID | cmClient_Name | Assessment_ClientID | Assessment_AssessmentID | VulnerabilityIndex_VulnerabilityID | VeteranStatusVal_ItemDesc | VISPDAT_Type | TypeVal_ItemDesc | VulnerabilityIndex_AssessmentDate | osOrganization_Organization | osUsers_UserName | VISPDAT_ScoreGeneral | VISPDATFamily_ScoreFamily | VISPDAT_ScoreHistory | VISPDAT_ScoreRisks | VISPDAT_ScoreSocialization | VISPDAT_ScoreWellness | VISPDAT_ScoreTotal | VulnerabilityIndex_WhereSleepMostFrequently | VulnerabilityIndex_YearsHomeless | VulnerabilityIndex_MonthsHomeless | HmisDataAssessment_HMISTimesHomelessLast3Years | VISPDAT_MentalDisabilities | cmClient_DisablingCondition | $GB1000000019 |
FVISPDAT:
| osUsers_UserID | cmClient_Name | Assessment_ClientID | Assessment_AssessmentID | VulnerabilityIndex_VulnerabilityID | VeteranStatusVal_ItemDesc | VISPDAT_Type | TypeVal_ItemDesc | VulnerabilityIndex_AssessmentDate | osOrganization_Organization | osUsers_UserName | VISPDAT_ScoreGeneral | VISPDATFamily_ScoreFamily | VISPDAT_ScoreHistory | VISPDAT_ScoreRisks | VISPDAT_ScoreSocialization | VISPDAT_ScoreWellness | VISPDAT_ScoreTotal | VISPDATFamily_SleepingLocation | VulnerabilityIndex_YearsHomeless | VulnerabilityIndex_MonthsHomeless | VISPDATFamily_MHIssueDiffToStayHoused | cmClient_DisablingCondition | $GB1000000018 |
PSH:
| cmClient_ClientID | custom_HPL_VISPDAT_PSH_Sep29_2017_AssessmentDate | custom_HPL_VISPDAT_PSH_Sep29_2017_Type | custom_HPL_CEAssessmentsMay17_2017_CEAssessmentDate | cmClient_FirstName | cmClient_LastName | cmClient_Name | cmClient_VeteranStatus | custom_HPL_VISPDAT_PSH_Sep29_2017_OrgID | custom_HPL_VISPDAT_PSH_Sep29_2017_InterviewLocation | custom_HPL_CEAssessmentsMay17_2017_AssessCommunID | custom_HPL_CEAssessmentsMay17_2017_EntryPointID | custom_HPL_VISPDAT_PSH_Sep29_2017_ScoreFamily | custom_HPL_VISPDAT_PSH_Sep29_2017_ScoreTotal | custom_HPL_VISPDAT_PSH_Sep29_2017_SpecialPopulationTotalScore | v_FamilyMemCount_NoFamMembers | CUSTOM_cmClient_NumberOfRooms | custom_HPL_VISPDAT_PSH_Sep29_2017_YearsHomeless | custom_HPL_VISPDAT_PSH_Sep29_2017_MonthsHomeless | custom_HPL_VISPDAT_PSH_Sep29_2017_SpecialPopulationYearsHomeless | custom_HPL_VISPDAT_PSH_Sep29_2017_SpecialPopulationMonthsHomeless | custom_HPL_VISPDAT_PSH_Sep29_2017_SpecialPopPeriodsHomelessLast3Yrs | custom_HPL_EarliestEnrollment_July11_2017_EnrollDate | custom_HPL_VISPDAT_PSH_Sep29_2017_WhereSleepMostFrequentlySingle | custom_HPL_VISPDAT_PSH_Sep29_2017_WhereSleepSingleOther | custom_HPL_VISPDAT_PSH_Sep29_2017_SleepingLocationFamily | custom_HPL_VISPDAT_PSH_Sep29_2017_SleepingLocationFamilyOther | custom_HPL_CE_Status_Dec18_2017_ServiceCodeID | custom_HPL_CEAssessmentsMay17_2017_ClientID | custom_HPL_EarliestEnrollment_July11_2017_ClientID | CUSTOM_cmClient_ClientID | CUSTOM_cmClient_UniqueID | custom_HPL_VISPDAT_PSH_Sep29_2017_ClientID | custom_HPL_CE_Status_Dec18_2017_ClientID |
RRH:
| cmClient_ClientID | custom_HPL_VISPDAT_RRHTH_Sep29_2017_AssessmentDate | custom_HPL_VISPDAT_RRHTH_Sep29_2017_Type | custom_HPL_CEAssessmentsMay17_2017_CEAssessmentDate | cmClient_FirstName | cmClient_LastName | cmClient_Name | cmClient_VeteranStatus | custom_HPL_VISPDAT_RRHTH_Sep29_2017_OrgID | custom_HPL_VISPDAT_RRHTH_Sep29_2017_InterviewLocation | custom_HPL_CEAssessmentsMay17_2017_AssessCommunID | custom_HPL_CEAssessmentsMay17_2017_EntryPointID | custom_HPL_VISPDAT_RRHTH_Sep29_2017_ScoreTotal | custom_HPL_VISPDAT_RRHTH_Sep29_2017_ScoreFamily | custom_HPL_VISPDAT_RRHTH_Sep29_2017_SpecialPopulationTotalScore | v_FamilyMemCount_NoFamMembers | CUSTOM_cmClient_NumberOfRooms | custom_HPL_VISPDAT_RRHTH_Sep29_2017_YearsHomeless | custom_HPL_VISPDAT_RRHTH_Sep29_2017_MonthsHomeless | custom_HPL_VISPDAT_RRHTH_Sep29_2017_SpecialPopulationYearsHomeless | custom_HPL_VISPDAT_RRHTH_Sep29_2017_SpecialPopulationMonthsHomeless | custom_HPL_VISPDAT_RRHTH_Sep29_2017_SpecialPopPeriodsHomelessLast3Yrs | custom_HPL_EarliestEnrollment_July11_2017_EnrollDate | custom_HPL_VISPDAT_RRHTH_Sep29_2017_WhereSleepMostFrequentlySingle | custom_HPL_VISPDAT_RRHTH_Sep29_2017_WhereSleepOtherSingle | custom_HPL_VISPDAT_RRHTH_Sep29_2017_SleepingLocationFamily | custom_HPL_VISPDAT_RRHTH_Sep29_2017_SleepingLocationOtherFamily | custom_HPL_CE_Status_Dec18_2017_ServiceCodeID | custom_HPL_CEAssessmentsMay17_2017_ClientID | custom_HPL_EarliestEnrollment_July11_2017_ClientID | CUSTOM_cmClient_ClientID | CUSTOM_cmClient_UniqueID | custom_HPL_VISPDAT_RRHTH_Sep29_2017_ClientID | custom_HPL_CE_Status_Dec18_2017_ClientID |
Login to the admin interface and click on "Sheet uploads" then "Add Sheet Upload." Load the appropriate Excel file into each slot then click Save. After saving the sheet upload, click the checkbox next to the uploaded sheet and choose "Ingest uploaded sheets" from the Action dropdown menu, then click Go.
That's it! You can then view your data.
Note that each time the Ingest function runs, all data is wiped out and replaced with the data from the newly uploaded sheets. The newly uploaded sheets are deleted after being ingested.
The site is secured with an SSL certificate so it should be safe to send data to/from the tool. The cert needs to be updated approximately every three months and I may be able to automate this with a cron job.