Basic invoicing management application is created for one of my customers on their SharePoint Online. Customer, invoices and payments are stored in three separate lists as required.
For one customer, there can be one or more invoices.
For one invoice, there can be one or more payments.
The next business objective was that management wanted to look at the analytics of how many invoices are pending to be paid as of now.
Hence, it was necessary to derive following data-points per invoice.
- Total payment received per invoice which can be calculated by summing up received amount from Payment list item against specific invoice.
- Total Pending payment which would be subtraction between Invoice amount and total payment received.
- Deriving a Boolean value to indicate whether the invoice payment is pending or not. Business logic was to ignore pending payments in the range of 0 to 50 as those are the typical bank charges.
- To derive necessary data in the invoice list, I implemented a solution using Microsoft Flow on SharePoint lists. Flow on invoice list calculates all values based on entries in payment list for this invoice.
Flow on payment list triggers flow on invoice list whenever payment entry is added or modified.
Please refer complete details of the solution below.
Flow of Invoice list;
Create Flow whose trigger action would be HTTP Request.
This flow would be called with invoice id as parameter
It would fetch all entries from payment list for this invoice and would calculate necessary values and would populate data in invoice list
Flow of Payment list;
Create a flow which would trigger on adding a new payment
Create a flow which would trigger on modifying a payment
These flows would trigger HTTP action to call a flow on invoice list created in step above
Refer some useful parts from Invoice Flow below.
Invoice list was finally connected to Power BI to generate expected analytics as mentioned below.
This was further enhanced with some additional MS Flow + Power BI implementations that we will share in future posts. Stay tuned for more such interesting reads.