This guide will help administrators that need to adhere to the new 2022 HUD requirements identify and migrate their HMIS data into the new AHS Template. The AHS Template standardizes HMIS data collection, ensures reporting compliance, and reduces the burden on Administrators from having to update forms and reports when HUD changes compliance specifications.
Note: If at any point while attempting this migration you find yourself getting stuck or struggling to find time, you can opt to hire Bonterra technical staff support. Please reach out to your Account Manager to explore this pricing option.
Legacy AVS Forms/Database – A legacy version of Apricot that included HMIS Data elements and Aggregate reports. This legacy version included a Household, Client Profile, Program Enrollment, HMIS, HMIS Income and Benefits to track HMIS Data Elements. It also included Aggregate Reports for the APR, CAPER, and Data Quality Reports. To determine if you are on a Legacy AVS database, please reach out to your Account Manager to confirm.
Table of Contents:
Identify current HMIS Forms/Fields: Identify the current forms in your Legacy AVS Apricot Database that you will need to pull your HMIS data elements from in preparation for migration. The AHS Template that you will be migrating your data into contain a handful of different forms (noted below) and require the data elements to be mapped from the Legacy AVS forms/elements.
AHS Template Forms
Forms Completed for Participants by Staff
Households (Tier 1)
HUD Enrollment Household (Tier 2)
Individuals (Tier 1, Main Profile)
Program Enrollments (Tier 2)
HMIS Assessments (Tier 2)
Income and Benefits Assessment (Tier 2)
Services Provided (Tier 2)
Bed-Night Date (Tier 2)
Ongoing Street Contact for Emergency Shelters, Street Outreach, and Services Projects (Tier 2)
RHY Form (Tier 2)
SSVF – Veteran Income Assessment (Tier 2)
Coordinated Entry Assessment (Tier 2)
Coordinated Entry Event (Tier 2)
Administrative Forms Completed by Admins
Organization (Tier 1)
Continuum of Care (Tier 1)
Category (Tier 1)
Options (Tier 2)
Project (Tier 1)
Bed and Unit Inventory Information (Tier 2)
Grant (Tier 2)
Note: All forms in Bold above are required for basic reporting compliance. In addition, the italicized forms may be required depending on the type of HUD Project/Funding Streams you have. Category, Options, and Continuum of Care are forms that store data that is populated in different parts of the AHS Template Forms within Dynamic Dropdown fields (i.e., Funder on Grant Form, or CoC Code on Program Enrollment). These forms don’t need to be filled out as they are already filled by default.
Identify Custom Forms/Fields: Identify if you have any custom fields/forms that you want to migrate into the new AHS Template setup.
Custom Form Example #1:
You may have a client who is a part of your Case Management Program and your Rapid Re-Housing Program. As part of your Case Management Program, you may need to record Case Notes on a custom Tier 2 form that is currently a part of your Legacy AVS configuration. This Case Note form would need to be re-built under the Individual (Tier 1) Profile and the data migrated into it.
Custom Field Example #2:
You may have a custom field on your Client Profile to track Household Income Level. Since this is not a HMIS Data Elements this would need to be added as a custom field to the Individual (Tier 1) Profile, so data could be migrated into it as well as the HMIS Data Element fields. Note: In order to add a custom field to one of the AHS Template forms a new section will need to be created to house the custom field or fields. All other sections are locked to maintain HMIS Data Element compliance.
It also may be determined that you only want to migrate Clients that are being reported to HUD through HMIS into the new AHS Template Forms. In this case only the HMIS Data Elements relevant to the AHS Template Forms would need to be identified and migrated (See Pre-Migration Checklist, Step 1).
Note: If this is determined, you may have to enter client profile information in two separate Tier 1 Profiles, your current Legacy AVS profile (Client Profile or Custom name), and the new AHS Profile (Individual). This is so you can track HMIS data elements using the AHS Template Forms while at the same time continuing to track other programs and services utilizing your existing Legacy AVS forms setup. If having two Tier 1 Profiles is not desired, then identifying the Custom Forms and Fields that will be needed to capture other program data will be essential in getting all client information moved over to the new AHS Template Forms setup.
Complete AHS Administrative Forms
Before migration occurs, the AHS Administrative forms need to be completed to assist in populating administrative fields on multiple Tier 2 forms.
The AHS Administrative Forms below need to be completed before migration takes place:
Organization (completed first)
Continuum of Care (if applicable)
Bed and Unit Inventory Information (if applicable)
Identify Report Updates: If moving all data (including Custom Forms/Fields) to new AHS Template Forms, identify current reports that need to be updated.
If there are any current reports that are not related to HMIS reporting that need to be run post migration of data, these need to be identified and updated based on the new Profile that will be used in the AHS Template Forms (Individuals), and any other associated forms needed to run reports.
Update Permissions for new AHS Template Forms
General Understanding Needed of VLOOKUP / Merging of Data in Excel
For parts of this migration effort, you will need to be able to use VLOOKUP or be able to manage Queries and Data Merges in excel. Part of the migration process will require you to link (using the Link Import type), two different Tier 2 records together. This can only be accomplished by using the Record ID from each of the separate Tier 2 records. This will be explained later in a different section as part of the actual migration process.
Run Reports for existing data
In order to move data from the Legacy AVS forms to the AHS Template Forms, we need to create reports that will pull all of our existing data out and prepare it for import into the AHS Template.
By utilizing the previously identified Legacy AVS forms that contain our HMIS Data Elements we can then create Standard Apricot Reports to pull that raw data out. It generally is best practice to create a report that contains all the fields identified on each template that will be used to import data into the AHS Template Forms. This maintains a 1:1 mapping when moving from Legacy AVS Forms to AHS Template Forms.
When generating any report containing HMIS Data Elements that will be migrated, you must include the Record ID of the record and store it in the Migration ID field of the corresponding AHS Template Forms.
Additionally, to support the Tier 2 to Tier 2 linking effort that will be discussed later, it is also imperative to include the Program Enrollment Record ID on each of the additional Tier 2 forms that will need to be linked to it. This can be stored in the Program Enrollment ID field on each of these forms.
The Program Enrollment Record ID should be able to pull into a report by using the linking relationship options in the Form Settings area of the report section (below). By clicking on the Program Enrollment option in the screenshot this will force the section to only show the HMIS Assessments that are linked to the Program Enrollments. This will help ensure the correct Assessments get associated to the correct Program Enrollments later when they get linked together in the AHS Template Forms
NOTE: If you do not have the Legacy AVS forms or you have customized them and do not have any linking relationships between the HMIS Assessment (or any of the Tier 2 forms that need to be linked) and the Program Enrollment, you will need to devise a method to associate the correct Assessments with the Program Enrollments.
Generate AHS Templates
For this step you will need to utilize the Prepare for Import function to go in and generate all the different AHS Templates. (See article here about generating templates and importing data into Apricot.)
Once templates have been generated, the data that was pulled using the reports generated in the previous step will need to be copied into the newly generated templates in preparation for import.
Note: There are certain fields on multiple Tier 2 forms that may not necessarily be included in your HMIS Data Elements but will still need to be imported as part of the migration process in order for Compliance Reporting (HMIS Reports) to run correctly. Below are the list of forms and fields that need to be considered as you are running reports and copying the data into the AHS form templates:
Project Name (Auto Populated) – This is the name of the Project that a Participant is enrolled in. This field will Auto-Populate from the Project record that is linked to when filling out the AHS Template forms manually. Since we are migrating data, this will need to be filled in on the templates to make sure this data gets included in each Program Enrollment record that is imported.
Migration ID – Record ID of the Program Enrollment from the Legacy AVS data.
All Fields Under the “For Administrative use” section.
Project Type (auto populates from linked Project) – HMIS Project Type
Emergency Shelter Tracking Method – If Project Type is Emergency Shelter, required.
Funder populated from Select Funder from Project Grant Info dropdown – Funding Source from HUD (i.e., HUD: ESG – Rapid Rehousing)
Client Location HUD Assigned CoC code for the client’s location – CoC Code
Continuum Project? Populated from link to Projects T1 – Is the project a Continuum Project?
Project ID – This is the Record ID from the Project Record that the Enrollment is linked to. During migration this can be populated by using the Project information that was completed during the Pre-Migration checklist.
Age at Enrollment – Age of Participant at time of enrollment.
Household ID – Record ID of HUD Enrollment Household. This will need to be populated after Enrollment data and HUD Enrollment Household data has been imported and the Record ID generated.
AHS/AVS – Flag field that is used to determine which form logic to use based on reporting requirements. This information can also be pulled from the Project Record that was completed during the Pre-Migration checklist.
All Other Services/Referral Eligible Fields – These fields are flags for whether a Participant is eligible for the corresponding Service or Referral from the respective HUD Funding Types/Streams.
All other Tier 2 Forms
These forms include information that is auto populated from the Program enrollment form that these records are linked to.
By clicking on the green gear in the corner of the field (in Administrator mode) you will be able to tell which field from the program enrollment the field is auto populated from.
It is recommended to populate these fields after the Program Enrollment form has been migrated and linked to each of these Tier 2 forms.
Import Data (not Links, this will come later)
Once the templates have been generated and filled with data from the reports that were run in step 1, we are now ready to import our data into the new AHS Template forms.
During the import process it is highly recommended to follow the best practices below:
When assigning import data to a program, it would be best to make sure the data is assigned to the same program that it was originally assigned to in the Legacy AVS forms it was pulled from. This will ensure that users who were able to access the data before will still be able to access the same data after migrating to the new AHS Template Forms.
If you run into an issue with Data Validation (i.e., there is a choice in a field that doesn’t match what is in the AHS Template Forms), pause the migration and correct the issues within your import file. The data values that get imported into the AHS Template Forms need to match the choices that are available in the AHS Template Forms. This is integral in maintaining HMIS Reporting Compliance. You cannot change what the available choices are in the AHS Template Forms.
Import Links (How to import links in Apricot)
After completing the import of data into the AHS Template Forms, it is now time to import the Links that are used to make references between different forms in the AHS Template Forms.
Below is a list of the different links that will need to be made between various Tier 1 and Tier 2 forms within the AHS Template Forms:
Individual (Tier 1) to Households (Tier 1) – Links Individuals into a Household
Project (Tier 1) to Program Enrollment (Tier 2) – Links a participant’s enrollment to a specific Project record.
Program Enrollment (Tier 2) to Category (Tier 1) – The field that contains this link on the Program Enrollment form is the “Select Funder (Grant ID in parentheses)”. This link associates the Program Enrollment to the specific funding source (stored in the Category Tier 1 form for Funding Source).
Program Enrollment (Tier 2) to Continuum of Care (Tier 1) – The field that contains this link on the Program Enrollment form is the “Select CoC Code”. This link associates the Program Enrollment to the Continuum of Care record which contains the applicable CoC Code. Note: This link only needs to be imported when: Relationship to Head of Household field = “Self (head of household)”
Program Enrollment (Tier 2) to HUD Enrollment Household (Tier 2) – The Program Enrollment form is linked to the HUD Enrollment Household form by two different linking fields. When to use each link is describe below:
When Relationship to Head of Household = Self (head of household) the link that is used is the “Create Enrollment Household”.
When Relationship to Head of Household equals anything other than Self (head of household) the link that is used is the “Select Enrollment Household”.
Note: The HUD Enrollment Household form is a new form that doesn’t exist in the Legacy AVS Forms. This form is used to capture the Household ID for a Household that is enrolled into a Project/Program together. This could be a Household of 1 or a Household of 5, either way this form needs to be generated as part of the migration process to the AHS Template Forms.
For example, if we had an enrollment for a Household of 1, that participant would be listed as Self (head of household), we would then create and link a new HUD Enrollment Household form using the “Create Enrollment Household” link.
If we had a Household of two, we would follow the same process for the Head of Household in step a, but for the additional individual we would need to select the Relationship that they have to the Head of Household (can only be one HoH for the Household), and then link to the existing HUD Enrollment Form (that was created by the Head of Household) using the “Select Enrollment Household”.
There should only be one HUD Enrollment Household form per Project enrollment for a Household. If a Household is enrolled into the same project again (different dates) or a different HUD funded project than an additional Program Enrollment should be generated for each participant in the Household and an additional HUD Enrollment Household record will need to be generated and linked to by each participant in the Household.
It would be best practice to generate a HUD Enrollment Household record per unique HUD project program enrollment for a Head of Household. For example, if a Household of 1 had two separate program enrollments, one for a HUD project and one for a non-HUD funded program/project, we would generate one HUD Enrollment Household record to link to the Program Enrollment that is associated to the HUD Project. If the above participant had an additional HUD project program enrollment, then we need to generate an additional HUD Enrollment Household form for that specific enrollment.
Note: When working with a Household that has multiple participants, only one HUD Enrollment Household record is needed per Household enrollment into a HUD project.
Program Enrollment (Tier 2) to HMIS Assessment (Tier 2) – Links Program Enrollment to the HMIS Assessments, using the “HMIS Assessment” link field.
Program Enrollment (Tier 2) to Income, Non-Cash Benefits and Health Insurance Assessment (Tier 2) – Links Program Enrollment to the Income, Non-Cash Benefits, and Health Insurance Assessment, using the “Income, Non-Cash Benefits and Health Insurance Assessment” link field.
Program Enrollment (Tier 2) to RHY Start/Exit Assessment (Tier 2) (If applicable) – Links the Program Enrollment to the RHY Assessment, using the “RHY Start/Exit Assessment” link field.
There is an additional link to the Program Enrollment form from the following Tier 2 forms:
Income and Benefits Assessment
Ongoing Street Contact for Emergency Shelters, Street Outreach…
SSVF - Veteran Income Assessment
Coordinated Entry Assessment
Coordinated Entry Event
These links only need to be generated if the data was migrated from the Legacy AVS Forms to the AHS Template Forms. Bold forms are required to be linked.
Linking Tier 2 Forms to other Tier 2 Forms
Unlike Tier 1 forms the only way to reference a Tier 2 record for linking is by its Record ID. Since this is the case, we will need to run a report(s) on the data that was imported to the AHS Template Forms to pull out the Record IDs for each Tier 2 form we want to link.
When running reports, we need to create two separate sections (in one report), one section that contains data for one of the Tier 2 forms, and the other section containing data for the other Tier 2 form that will be linked. (i.e., Program Enrollment data section, and HMIS Assessment Section). This process will need to be repeated per Tier 2 to Tier 2 link that needs to be imported.
To successfully associate the HMIS Assessment with the correct Program Enrollment we will need to pull the following fields in each report section:
Program Enrollment section – Record ID, Migration ID
HMIS Assessment section – Record ID, Program Enrollment ID
Once the report has been created with the above sections, we will then need to export the report as an .xlsx file that has two tabs, one for each section.
Now we to need to associate the two tabs together to get our new Record IDs associated in the same table. We will accomplish this using VLOOKUP or Data Merge in excel and will be using the Migration ID / Program Enrollment ID (these two IDs should be the same) relationship to do so. By using VLOOKUP on the Program Enrollment Tab, we can pull in the new Record ID values from the HMIS Assessment tab based on the Migration ID column (Program Enrollment tab) that matches the Program Enrollment ID column (HMIS Assessment tab). After copying our VLOOKUP formula to all rows, we should have a list of all our new HMIS Record ID values associated to our new Program Enrollment Record ID values.
From here we can copy these values to our linking import template in the appropriate column and import our links.
Note: The forms used in the descriptions above are examples, but this process will work the same for all other Tier 2 forms that need to be linked to the Program Enrollment form (except for the HUD Enrollment Household form, see previous section)
Cleanup and Additional Information
For all the linking fields that use a dropdown field (Dynamic Dropdown), these linking fields will require an additional data import to have textual information show when a form is loaded post import. The link will still exist without the data import but when you view the record the field will appear as blank, since no textual data has been imported. Standard linking fields do not require the additional data import.
When you have completed importing all your HMIS data and links to the associated forms, we highly recommend disabling/removing permissions to the Legacy AVS forms that you were using to collect this data. This is to ensure staff are not continuing to enter any new HMIS data in the old forms and creating duplicated information in your database. Note: This only applies if you are moving all your data (HUD/HMIS data and other program data into the new AHS Template Forms).
Now that you have migrated your HUD/HMIS data from the Legacy AVS Forms to the new AHS Template, you have access to the updated Compliance Reporting feature which makes running HMIS required reports easier and reduces the burden on administrators to continually update and maintain these when HUD releases compliance updates.
When you have completed your data migration it will be imperative for you to test the Compliance Reports and verify that your data is being pulled in and displayed accurately. Depending on the results of running the Compliance Report(s) it may be necessary to review the data migration process and go back and make corrections where necessary.
You’ve made it to the end of this guide. You should now be set up with HUD-compliant reporting and data in all the right places. Congratulations! You deserve a pat on the back.
NOTE: If at any point while attempting this migration or testing your new Compliance Reports you find yourself getting stuck or struggling to find time, you can opt to hire Bonterra technical staff support. Please reach out to your Account Manager to explore this pricing option.