Power Platform Community Forum Thread Details

Hello,

I am working on a project to automate some audits. I’m new to PowerApps, Flow, and Common Data Service so I’m not sure this idea would even work but figured I would throw it out there and see if any of you could point me in the right direction.

Vendor File

Each Friday the vendor emails a .xlsx file containing the benefit deduction amounts that should be deducted from each associate’s paycheck.

EmployeeNo	Paycode	Amount	Date
A123	QRS	21.2	6/7/2019
B123	TUV	100	6/7/2019
C123	LMN	12	6/7/2019
D123	LMN	12	6/7/2019

Internal File

The file is loaded into our system and a report (again .xlsx format) is generated prior to checks being cut.

EmployeeNo	Paycode	Amount	Date
A123	QRS	21.2	6/7/2019
B123	TUV	100	6/7/2019
C123	LMN	12	6/7/2019
E123	AAA	5.1	6/7/2019
F123	LMN	1000	6/7/2019

I need to compare these two files to make sure each row matches. In the example above, I would need to know that Employee E123 was loaded into the internal system but has the wrong amount (currently 5.1, should be 12). I would also need to know that Employee F123 is in the internal system with an amount of 1000 – but was not included in the vendor file.

Here is my rough plan – Please let me know if this would work…..or if you have a better idea/way to get this done.

Have all of the files emailed to a shared email box
Use Flow to identify these emails based on subject line. The Flow would then copy the attached .xlsx file to a Document Library on a SharePoint site.
Set up Flow triggered by a new file being added to SharePoint to load the file into a CDS entity
Figure out how to Join (or Compare) the Vendor Entity to the Internal Entity. These records would be added to a Errors Entity
Use Flow to email out a .xlsx file with the errors that need to be reviewed.
Use the results from these audits (or entity comparisons) to update a PowerBI dashboard so leadership can see the audits are being completed.

I had also considered making this into PowerApp but am a bit unclear on how many Plan 2 licensees we would need to purchase to use that solution.

Any ideas or feedback would be greatly appreciated!

Categories:

Building Power Apps