Case Study Summary: Springhouse resolved a business challenge by using the Microsoft Power Platform to help our client prepare complicated government-compliance reports, which previously required exhaustive, manual data sharing.
Background
The Toxic Substances Control Act (TSCA) requires companies that manufacture or import specific chemical substances to report details to the Environmental Protection Agency (EPA). The companies submit information about the manufacturing, processing, and use of those chemicals every four years. The reports must be submitted electronically using the EPA’s Central Data Exchange (CDX).
Challenge
Previously, our client used a database system that produced one PDF form per reported chemical. In their case, they needed to report on 400 chemicals. Someone had to manually enter the information from the hundreds of PDF forms into the CDX system. This was a time-consuming process that had a risk of data entry errors.
Goal
The EPA’s CDX system accepts properly formatted Microsoft Excel workbooks for bulk uploading chemical data. Our customer asked us to replace the database system with a new means of capturing data and exporting it in the required Excel workbook format.
Let’s talk about problem-solving
Recommendation
Springhouse recommended using a combination of Microsoft solutions – Power Apps, Power Automate, Microsoft Office scripts, and SharePoint. Working with these tools that integrate, we proposed developing a data entry application that inputs the required information into a SharePoint list repository. A supporting workflow would then extract the requested information into a CSV file for easy upload.
Approach
Using the Microsoft Power Platform, Springhouse developed its Chemical Data Reporting (CDR) system to produce an Excel workbook file that can be uploaded to the CDX using the bulk upload functionality.
The new CDR system consists of…
- A Power Apps canvas application that serves as the primary user interface for entering chemical data
- SharePoint lists that serve as the repository for the chemical data
- Power Automate workflows to extract the chemical data from the lists and produce CSV files
- Microsoft Office scripts that convert and combine the CSV files into a single Excel workbook correctly formatted for uploading to the CDX
Results
The CDR input mechanism features data validation to ensure accurate information. The system then produces a single Excel workbook, which is accepted by the EPA’s bulk upload process, eliminating the need to manually enter all the chemical data a second time directly into the CDX system.
Applicable Data Sharing Uses for Microsoft Power Platform
The government expects data from a wide variety of sources. For example, hospitals, financial institutions, pharmaceuticals, environmental, safety, and others upload data to fulfill their compliance obligations. Springhouse can adapt a Microsoft Power Platform solution like this to any government relations data-sharing report requirement.