UFB: Data Entry Revamp

Inspiration

The United Food Bank (UFB) currently has a website from which potential volunteers can download and print PDF volunteer forms. They would then fill out these hard-copy forms and bring them to UFB when they volunteer. The UFB staff would then need to manually input all of this information to the Microsoft Access database.

This project will automate the process of managing volunteer information by giving the volunteers the opportunity to submit their information online via the UFB website. This information would then be automatically pulled into the Microsoft Access database.

This project will accomplish the following:

• Minimize handwriting errors
• Minimize duplications in the system
• Free up volunteer/staff time that could be spent on other tasks:
  • 12 hours per week of volunteer (donated) time
  • 8 hours per week of staff (paid) time

What it does

Volunteers will input their information into the forms on the WordPress website, and it will be automatically stored in a temporary database on the WordPress site. A connection is created between this database and an Excel spreadsheet that will query the site at given time intervals. From here, Microsoft Access will be used to import this Excel file and append the data to the permanent database of volunteers. The temporary database on the WordPress site is cleared, and the permanent database is queried to get an updated list of all the emails. This list will be used to avoid duplicate submissions in volunteer data by alerting the user if they already have an account and only allowing the volunteer forms to be completed for a volunteer with a unique email address.

How we built it

In order to build this project, we had to figure out how to pass data between WordPress, Microsoft Excel, and Microsoft Access. Because there was no clear way to directly export the form data submitted in WordPress to the database in Microsoft Access, we used an Excel Internet Query to export the data to Excel as an intermediate step. Once this Excel Internet Query file was exported, it was set up to automatically update at given intervals to reflect the current entries in the WordPress database. Shortcuts were created in Microsoft Access, which allowed the changes in this Excel sheet to be quickly imported/appended to the Microsoft Access database. We then created a query to fetch all the emails in the database and used another shortcut to export these to a text file. Once this text file was updated with the current emails in the database, it was used to check future volunteer submissions on WordPress to avoid duplicate volunteer entries into the system.

Challenges we ran into

The challenge of our chosen nonprofit United Food Bank is that they are still manually entering volunteer information and hours into their Microsoft Access database. The challenge we faced was to figure out how to transfer the right data fields to auto populate correctly into the corresponding fields of their Access database. Once we managed to find a way to streamline this, the biggest challenge we ran into was that our testing website kept crashing and it delayed our progress significantly.

Accomplishments that we're proud of

Some of our milestone accomplishments were:

• Storing form data on a temporary WordPress database
• Moving data from WordPress to Excel
• Syncing up the Excel sheet to communicate with WordPress forms so it can be imported into Access
• Importing of Excel file into MS Access database
• Exporting a list of all the emails in the MS Access database which could later be used to avoid duplicate volunteers
• Step by step documentation with appropriate screenshots for nonprofit to follow procedure to implement system on their own

What we learned

As first time hackers, we learned a lot. Specifically, we learned:

• How to communicate well with a team
• The process of creating a solution and the learning practices along the way
• How to divide up tasks and listen to all the perspectives

What's next for UFB: Database Revamp

We hope that this project will help the United Food Bank to better manage their volunteer database. As a team, we plan on keeping in touch and working together again at another hackathon.