Recently I had a client making a lot of requests about how to group expenses on their custom invoice.
The requests looked something like this…
“Can you sum up all Per Diem expenses using these accounts… 5010, 5050, 7050”
“Can you sum up mileage expenses using these accounts… 5025, 5033”
“Lodging expenses use these accounts, they need to be summed up as well:
lodging 5027
Meals 5028
Lodging/Meals combined 5030”
Custom Invoices in Vision are just another custom report. They are one of the more difficult reports to create b/c of the complexity of the invoice. An invoice in Vision actually is comprised of 25 or so sub reports all on one master report. Each sub report has its own underlying SQL statement, formatting, etc… That’s why customizing the invoice is so expensive!
In any case, here is what a grouped/summarized piece of the invoice looks like when it comes out. Notice it’s grouped by employee, then also by expense category (so this section probably represents a week or two of transactions… which would take up a lot of space on an invoice if not grouped).
Keep in mind it’s easy enough to create sql statements to group on particular account number values in an invoice… once you’ve mastered creating custom invoices in Vision 7 that is.
However, what happens when you want to add more categories or an account gets changed? If you have the account values hard coded into the statements, then every time an account number changes or one gets added, or even if the client wants to add a grouping category, it’s going to cost them money to update the report. While some consultants may enjoy that type of relationship with their clients, I prefer to build things that last.
Thus, instead of putting actual account values in sql statements or formatting the report around groups that would most likely change, I did it using a custom infocenter. That infocenter looks like this:
Here’s how it all works:
1. When clicking on the “Magic Button” a stored procedures goes and gets all accounts from any transaction that has appeared on an invoice and populates the “expense groups” grid with those accounts.
2. The user assigns any or all account to an “Invoice Group” by selecting from a drop down.
3. The user then identifies the type of unit that will be used when describing that group… like “Miles” for a mileage account or “Days” for a per diem account.
4. They click the “Magic Button” again which populates the “expense sort” grid on the right, where they can put the order in which they want those expense groups to appear on the invoice for that particular employee. The “Expense Sort” grid on the right will only populate with actual expense group categories used in the grid on the left.
5. Clicking on the “Magic Button” at any time will update both grids with any new accounts used in expense section on invoices, as well as update the Expense Sort grid with any new categories created or assigned.
6. Any expense item that gets billed to an invoice will show up in this matter.
Now… you might be saying to yourself “why didn’t they just use expense categories for all this?” Well, what if the expense categories you use internally are different from what your client wants to see on invoices? Or what if you have different requirements for different clients?
This allows you to set up multiple expense category configurations and assign that set of configurations to any project… that’s the last part:
7. Assign this template for invoice expense grouping to any project, and the invoices will use that grouping.
Feel free to comment with questions or contact me directly.
Thanks for stopping by!