Prior to vaccination clinics, many LeadingAge and CAST members have been overwhelmed trying to pre-register hundreds of residents and employees with pharmacies, including pharmacies partnering with aging services providers.

Completing the consent forms can be labor intensive and time consuming for organizations trying to vaccinate a large number of their residents and/or employees. In many cases, providers need to pre-register the people willing to be vaccinated by completing a pre-registration Excel template and sending it to pharmacies. The pharmacies then prepare the appropriate number of doses and vaccination consent forms, in hard copies, for individuals to complete before taking the vaccine.

However, this process can be automated and streamlined to save providers significant time. Providers already have most, if not all, of the data that pharmacies need to complete the forms in different information systems. Examples include residents’ electronic health records (EHR) and staff’s information stored in members’ human resources’ (HR) and payroll information systems.

Automating the Process without an EHR

LeadingAge CAST reached out to information system vendors and members for advice on the best ways to automate this process. CAST asked which methods are the most effective and easiest to implement by a broad array of providers, including those that do not have EHRs, like housing members.

In response, we heard that some providers are exporting resident data in Excel, from the electronic EHR or tenant’s directory for resident pre-registration, and from payroll or HR software for staff. They then manually copy data fields one by one to complete the consent forms, which is still inefficient.

In addition, some vendors have re-created the consent forms within their EHRs as custom forms, but some pharmacies accept only their own pharmacy-branded consents.

Microsoft Mail Merge Solution

Through the CAST LinkedIn group, Majd Alwan, Ph.D., CAST executive director and LeadingAge senior vice president of technology and business strategy, learned that Brandon Claps, CIO of Loretto, has created a solution using Microsoft Mail Merge. In this interview, Claps shares his experience to make it easier for other providers to automate the process.

Majd Alwan: Hi Brandon. Thank you for making the time to sit with me to discuss what you have done at Loretto to facilitate the process of pre-registering staff and residents/patients and pre-filling their consent forms prior to vaccination. First, would you please tell me what information systems you have in place?

Brandon Claps: Sure! My pleasure. Thanks for the opportunity.

For staff data, we leveraged our payroll system, ADP. As for the resident data, we currently have SOS as an electronic health record (EHR). We also have our own data warehouse, which is a structured query language (SQL) database.

We pull data out of all of our information systems, including ADP and SOS, daily. We store them in our data warehouse for ease of querying the data, generating real-time data reports, creating data visualization dashboards, and potentially other data analysis capabilities. We also use Qlik’s Data Analytics Software.

Alwan: Great! Could you please walk me through the process that you have used, what you did, and how you did it?

Claps: We exploited the Mail Merge capabilities within Microsoft Office to automate the pre-population of data. We received the pre-registration Excel template and the consent form in a locked pdf format, not an editable one, from our pharmacy partner Pharmscript. We identified the data fields required within our data warehouse, and we created a data mapping table that mapped the pharmacies’ required data fields to the relevant fields in our database.

We then exported the data as an Excel file and used the mapping table to auto-fill the pharmacy’s pre-registration Excel sheet. We used the conditional filling feature within MS Mail Merge. For example: If SourceRace=White, then DestinationRace=W, if for example White should be completed as W in the pharmacy’s form etc.

Alwan: I heard from some providers that auto-populating the Excel template they received from their pharmacy partner created an error, especially when the Excel template was designed with restricted value, like drop-down menus. Did you face that problem, and how did you solve it?

Claps: No. The Excel template we received did not have that restriction, and we did not have any issues. Usually drop-down menus’ variables have hidden values attached to them, rather than what we see on the screen. One would need to expose those hidden values before creating the mapping table. Then one would fill the destination fields with the appropriate hidden value when exporting the data and creating the destination file through Mail Merge.

Alwan: That is very helpful. Now, tell me how you auto-populated the consent forms, which is obviously harder, since you have a locked template that is not even editable.

Claps: We first used Adobe Acrobat to convert the pdf consent form into an MS Word document that maintained all the branding logos, disclaimers, etc. We also created another mapping table for the consent data fields, similar to the process described earlier for the pre-registration form. We then used MS Mail Merge to create the pre-filled consent documents, which we saved as pdf files and printed for hand signature.

Alwan: I am familiar with the process; I actually used a similar process a long long time ago, in parsing and auto-filling branded and formatted forms. I saved them as Rich Text Format (RTF) instead, as RTF files were much easier to parse and auto-fill, at the time. I’m dating myself here…. So this must have saved your staff a ton of time getting ready for the vaccination clinic, right?

Claps: It did – It really demonstrated the value of having a data warehouse and showed the organization a tangible return on the investment we have made in information technology (IT) and data infrastructure a while back!

Alwan: Let me ask you one last question. I know that once the consent forms are pre-filled, one can potentially send the completed form electronically, via email or text messages, for electronic signatures by all relevant parties through an e-signature service like DocuSign. One would then distribute executed signed copies to all for documentation. Have you explored that option?

Claps: Great question! We did talk about it, but we only had two days between receiving the pre-registration and consent templates, and the actual vaccination clinics, so we did not have enough time to implement it. It is doable, if one has more time.

Alwan: Thank you so much, Brandon, for your willingness to share your expertise and experience on this issue. I am sure your peers and colleagues will find this extremely helpful.

Claps: My pleasure! Happy to help any time. Thanks to you and LeadingAge CAST for all you are doing for aging services providers.

It is worth noting that this solution is easy to implement by a broad array of providers, regardless of setting, including Assisted Living (AL) and housing providers. The solution works well for information systems of any sophistication level, as long as the resident information system, like resident directories or tenant management, can export data in Excel format. All it needs is Adobe Acrobat and Microsoft Office.

If you have any questions, please feel free to contact me at malwan@LeadingAge.org.