All You Need to Know About Custom Scripts But Were Afraid to Ask! Part 1

One of the hidden yet powerful features of BillQuick is its ability to support custom SQL scripts that can add, extend and modify the software’s functionality. So in an effort to promote this amazing functionality, I want to share with you what custom scripts are and how they can truly benefit your business.

I will also share three smart, time-saving custom scripts requested by BQE customers. These examples will help you learn the benefits of custom scripts and at the same time help you figure out if your business could benefit from a similar custom script. To avoid making this blog article unnecessarily long, I will divide it into two parts. In this first part, we will cover the basics of custom scripts and then share an actual use case.

So let’s start by getting familiar with some of the terminology related to the custom scripts. There are three types of scripts that can be added to the BillQuick company file stored on a SQL server.

1. Trigger: This is the most common type of custom script that gets executed automatically when you insert, update or delete data in BillQuick.
2. SQL Agent: While triggers are fired upon insert, update or delete, the SQL Agent can execute a script on a user-defined schedule.
3. Stored Procedure: Code added here is typically the fastest to execute and can keep your data views updated and ready for delivery. Stored procedures can also be called by Triggers and SQL Agent.

The custom scripts also have the ability to display a message within BillQuick. As an example, if you want to automatically remind the user to attach a purchase-receipt (say for meals) to an expense entry, you can do so by asking for a script that prompts a user to attach the receipt with the entry being recorded.

What can a custom script do? Pretty much everything a typical computer can do with just a few limitations. It can send a text message to your mobile phone or email you a piece of information. It can copy and update data, submit time on your behalf, and much more. For example, you can add a custom script that will monitor past due invoices and send email to clients automatically, reminding them that their invoices are past due. The task to obtain the latest list of past due invoices can be achieved by a stored procedure and the task of running that procedure monthly and emailing the clients can be easily accomplished by using a SQL Agent.

Finally, custom scripts are free if you know how to write SQL code. If you are not good at writing SQL code, no worries; BQE Consulting Team can develop and add custom scripts to your BillQuick company file for a nominal fee. Here is the best part--BillQuick will automatically upgrade the custom scripts when you upgrade from one major version to another. So, it is safe to say that the functionality added via custom script is “forward compatible”. I have been informed by our Consulting Department that an average custom script takes around 6 hours to code, test and deploy. So it is fast and affordable!

Now that you have a basic knowledge of custom scripts, let us look at an example that demonstrates clever and time-saving implementation of custom scripts.

Auto-Adjust the Cost Rates for Salaried Employees for Accurate Job Costing

Most small businesses are not as meticulous about job costing and many simply don’t worry if their job costs are off by 10 to 15%. However, I believe ignoring this issue can significantly affect job costs and billing, and hence every business should pay attention to resolving it. Let me highlight the problem via a simple example.

If you have a salaried employee earning $800 per week, you can easily calculate the hourly cost rate as $20 using the 40 hour work-week. What if the employee works 50 hours in a given week and still gets paid the same $800? That will dilute the cost rate to $16 per hour. Now all the time entries for that week are overstating the cost rate by 25%. That is significant and should not be ignored! I wish the Service Fee Schedule in BillQuick could handle this dynamic cost rate adjustment, but it can’t. My only option is to change the rate manually using the Batch-Change feature in Sheet View. However, manual editing is time consuming and prone to human errors.

When this problem was shared by one of our customers during a training session, our initial reaction was to add this request to the wish list and let the product Development Team figure out how BillQuick can solve it via a custom script.

So, for each customer, a simple trigger script was developed that calculates the hourly cost rate “on-the-fly” for every salaried employee as they insert, update or delete time entries. Not only will the script calculate the cost rate for the time entry you are adding or editing, it will also update all other time records for the week and update their cost rates accordingly. This obviously results in accurate job costing all the time.

Some of the interesting tweaks (to the originally developed script) requested by customers were:

• Have it distribute cost only to billable time entries
• Have the cost calculation ignore vacation, sick and holiday hours
• Wait for the week to complete and then run the script via SQL Agent at the end of the week automatically
• Have it recalculate the cost rate on bi-weekly basis to correspond with the payroll cycle
• Have it adjust the bill rate too using a predefined multiplier

In the next part, I will cover how we solved the problem of auto adding expense entry when a certain type of time entry was added. I will also share a very interesting story of an engineering company that bills all large format prints printed on their in-house printer without ever entering those expenses in BillQuick. In the meantime, I would love to hear your pain-points that you believe can be solved by custom scripts. Also, if you have added your own scripts, please share those with us and other BQE users.