Ever wished you could do this to you SharePoint Lists?

Manage Overdue Invoices in SharePoint

If (like us) you store client invoices in a SharePoint document library, then why not use SharePoint Highlighter to help you easily identify those where payment is overdue?

We are going to add a countdown to the library to show exactly how many days old each invoice is, as well as flags for 30, 60, and 90+ days invoices.

Here's how:

To do this we are going to add 2 SharePoint Highlighter columns to the library: One for the countdown and one for the flags. Let's do the countdown first.

Countdown

Add a new column and make it a Pentalogic Highlighter column called "Days". Now in the Highlighter configuration section choose Countdown.

 

To set the parameters for the countdown we want to show the number of days between the Invoice Date and Today. Actually this is a count-up rather than a countdown! So set Start to "Invoice Date", and End to "[Today]".

 

Now decide how you want the time difference to be displayed.  You will see that Highlighter can be pretty accurate and show times down to the second! But in this case I think simply whole days will do just fine.

 

For this column we are not going to activate any of the rules.  This is because we just want a simple count, with no conditional highlighting or color coding. So simply click OK and we should be done with this stage.

 

Flags

First, add a new Highlighter column to your library called "Overdue". In the Highlighter set up choose Highlighting.

Under Display choose Show Icons, and base it on the "Invoice Date" column.

 

 

What we want to achieve here is too complex to get from the "Auto-create" so let's go straight for the advanced rules – click on "Add Custom"

TIP – the important thing to remember about Highlighter rules is that in deciding how to treat a record. Highlighter will always apply the first rule that that record agrees with, and Highlighter will only ever apply one rule to each record (see Rule Precedence).

So when applying date based rules as we are here, you need to think through carefully what order to set your rules up in.  Although if you get it wrong it's no great disaster as Highlighter gives you the option or re-ordering the rules.
So in this case we want to

  • Display a red flag for anything over 90 days old
  • Display a yellow flag for anything between 60 and 90 days old
  • Display a blue flag for anything between 30 and 60 days old
  • Display no flag for anything less than 30 days old, as these invoices are not yet due for payment.
  • So to achieve this, this is how we will set up the rules:

As you can see, the first rule applies to the oldest items.  So Highlighter will look at an item, if its invoice date is "less than" in other words "earlier than" Today -90 days, it will give it a red flag.

If the item doesn't qualify on that rule it will move on to see if the item is less than/earlier than today – 60 days, and so on.

The final step in this is to clear the default icon, so that if none of the rules apply, nothing is displayed.

And you're done.  Improved credit management with color coded aged debt management in SharePoint.