Sunday, December 17, 2017

#1 2007-09-17 06:00:07 am

Adam Bell
Administrator
From:: Nova Scotia, Canada
Registered: 2005-10-04
Posts: 4660

Scripting Clickable Bliss' Billable

Introduction to Billable

I decided recently to stop using Excel X to generate invoices for the occasional consulting jobs I do and looked around for an application to replace it. I am (as you might imagine) an AppleScript nut, so when I look for an application to accomplish something I want to do, I look for one that I can script so I can extend its capabilities and personalize it for my intended use if it doesn't meet all my needs.  I found Mike Zomek's  Billable (by Clickable Bliss) which advertises itself as:<blockquote>Billable is an application for Mac OS X that helps you keep track of the billable services you perform for a client and then lets you create and manage invoices based on those services.</blockquote>In it's latest version (version 1.2b2 was tested, but 1.2 is now released) Billable is scriptable. Billable has a very nicely done and rather straight-forward window for defining Clients, assigning services to them, and then automatically preparing an invoice which can contain logos, and is very presentable looking (See the Billable Screenshots for samples, and note that there are excellent Screencasts as well). You define yourself and/or your company in Billable's preferences using your Address Book entry if you prefer,  and there is even space for a logo to be displayed at the top of your Invoices. Nicely done. You then go on to define your clients and their "coordinates" either by importing them from your Address Book, or using a handy Client Editor provided. In the side bar of the Billable Window, each of your clients will appear as a smart folder which can appear as their labeled logo if you like, inside of which are two more folders for each client; one for Invoices and the other for Services. Not shown in the screenshots (that I found) is a view of a Service entry, so I've shown one in Figure 1 below:

http://files.macscripter.net/unscripted/Billable/ServiceWindow.jpg

Figure 1: A Billable Service Window

Notice that at the bottom of the Services window there is a Notes pane. More about that later. It is an important scripting aid because its contents can be read into a script variable, so I will be placing currency values that need exchange rate calculations in Notes with a flag so I can find them, and then running a script to find them all and doing the conversions for entry into the actual cost in foreign currency.

Getting Help

Like any good Apple app., not much direction beyond the screencast is needed, and the Help files are very thorough as well. All the instructions you will ever need are there and the search terms for finding them are fairly obvious. (I hate apps with arcane ways of naming things in help.) There is a Forum as well, and Mike Zomek is quite responsive to email queries for help. If you're not happy with the default layout of an Invoice, for instance (though I thought the default to be quite adequate and used it unaltered), you can create a new template for that client's invoices by editing a copy of the default template found inside the application itself ~/Applications/Billable.app/Contents/Resources/invoice_template.html (assuming that you put Billable in your Applications folder). Don't edit the default -- if you screw it up, no invoices. Templates use a fairly comprehensive set of tags enclosed in double braces like this {{tag}}. Using these is well-explained in the Help file; search on "Customize Invoice" and "Template" for those pages. Templates are not scriptable, however.

Scripting Billable

Why do I need Billable to be scriptable? Because Billable by itself, although quite capable of handling foreign currencies as shown in one of the screenshots, is not set up to deal with more than one at a time. In these days of connectivity I can work from home in Canada for clients that do not live here, but must then manage two things: I must invoice an foreign client in the Client's native currency, and I must also keep track of my taxable income from those clients in Canadian dollars ($Cdn).

Billable has a fairly comprehensive Dictionary, and for the most part it behaves as you would expect it to. Scripting Billable is not rocket science and doesn't require any hacks, GUI scripting, or strange constructions (aside from the occasional requirement to enforce order with "get" inside parentheses before going after properties). I can accomplish most of what you might want to do with it straight up. I say "most" because I didn't see a way to select a particular set of services and print an invoice for them, so I did that by hand in Billable, which provides a handy button in the window and gives you a view pane for seeing the invoice before you print or send it.

To accomplish my goals, I made extensive use of the notes section of the Service windows. I created a Client in the Client Editor that I called "AppleScriptClient" to use for this tutorial and then made up a bunch of services. The two Client Editor window panes are shown in Figures 2 and 3 below:

http://files.macscripter.net/unscripted/Billable/ClientEditorProfile.jpg

Figure 2: Profile Pane of a Service Editor Page for Setting Up or Changing a Client

http://files.macscripter.net/unscripted/Billable/ClientEditorAdv.jpg

Figure 3: Second Pane of Client Editor Page for Setting Up or Changing a Client (note the icon)

For expenses, generally, rather than creating a new service for each, I used notes with a column of figures for individual items to be totaled for the invoice. For Canadian expenses, I headed the notes with "$Cdn" and then the figures as shown in Figure 4 below. Note the button "Quantity" is checked for expenses. I didn't use the Quantity text box except for recurrent expenses that spanned several months (Document Storage, for example). Note that Figure 4 shows a total in the upper pane services list that is not shown below. That is an artifact: I shot the screen after I had run the script, and only modified the entry boxes below and then restored them. Figure 5 shows a typical fee-based service, i.e. consulting time.

http://files.macscripter.net/unscripted/Billable/ServicesQty.jpg

Figure 4: A Typical Service by Quantity Entry Using Notes for Items

http://files.macscripter.net/unscripted/Billable/ServicesT.jpg

Figure 5: A Typical Fee-Based Service

A Script to Do Currency Conversions from Service Notes

I have intentionally left the parts of this script using redundant code rather than using a handler to deal with repeated features so each subsection is stand-alone.

Applescript:


tell application "Billable"
   -- Get the Client (you can use the full name or an abbreviation)
   set C to first item of (get companies whose abbreviation is "scpt")
   (* get needed exchange rate for the day I invoice. Note that I use XE.com's Currency Update Service, which emails me the daily rates for a huge list every day, so I just find the last received and parse it for the currency I want after having set it up to use Canadian dollars as its base. There are lots of other ways to get the rate, so this is just an example*)
   set MidMktExch to my getMktRate() -- CDN to US handler from Eudora mails
   set Agio to 0.02 -- Canadian bank charge for buying and selling.
   set Exch to MidMktExch - Agio
   -- Get the Services that are not hourly, i.e., Quantity type.
   repeat with S in (services of C whose rateType is "Quantity")
       -- Find those services that need conversion to US dollars
       tell S
           if notes contains "$Cdn" then -- always the key in the first paragraph
               -- Telephone is a column of numbers one to a paragraph
               -- starting with the second.
               if summary contains "Telephone" then
                   set Nts to get notes
                   set p to paragraphs 2 thru -1 of Nts
                   set Total to 0 -- causes text in note to coerce to numbers.
                   -- add them up. I have not made this a handler to make clearer what's going on.
                   repeat with N in p
                       set Total to Total + N
                   end repeat
                   -- convert them and update the cost in US dollars
                   set totalFlatFeeCost to Exch * Total
                   (* Update notes. Because notes are appended to the charges in an invoice, I want to make it clear to the client how they were converted. See Figure 6 below*)
                   set notes to Nts & return & "$" & Total & " in $Cdn converted to $" & totalFlatFeeCost & " in $US as of " & (createdAt as string) & " @ $" & Exch
               else if summary contains "Storage" then
                   -- Convert monthly charge to US dollars
                   set Nts to notes
                   set Stor to paragraph 2 of Nts
                   set totalFlatFeeCost to Exch * Stor
                   set notes to Nts & return & "$" & Stor & " in $Cdn converted to $" & totalFlatFeeCost & " in $US as of " & (createdAt as string) & " @ $" & Exch
               else if summary contains "Travel" then -- Cdn Travel
                   set Tnotes to notes
                   set tP to paragraphs 2 thru -1 of Tnotes
                   set Ttot to 0
                   -- again, add them up
                   repeat with p in tP
                       set Ttot to Ttot + (first word of p)
                   end repeat
                   set totalFlatFeeCost to Ttot * Exch
                   set notes to Tnotes & return & "$" & Ttot & " in $Cdn converted to $" & totalFlatFeeCost & " in $US as of " & (createdAt as string) & " @ $" & Exch
               end if
           end if
           -- Now do expenses that were incurred in the US.
           -- Get US$ meals
           if summary contains "Meals" then
               set M to 0
               set Nts to get notes
               set p to paragraphs 2 thru -1 of Nts
               -- Add up the meals
               repeat with aMeal in p
                   set M to M + aMeal
               end repeat
               set totalFlatFeeCost to M
               set notes to Nts & return & "$" & M & " in $Cdn converted to $" & totalFlatFeeCost & " in $US as of " & (createdAt as string) & " @ $" & Exch
               -- Get US Travel Expenses
           else if summary contains "US Travel" then
               -- Add up the costs in notes
               set Unotes to (get notes)
               set uPn to paragraphs 2 thru -1 of Unotes
               set Utot to 0
               repeat with p in uPn
                   set Utot to Utot + (first word of p)
               end repeat
               set totalFlatFeeCost to Utot
               set notes to Unotes & return & "$" & Utot & " in $Cdn converted to $" & totalFlatFeeCost & " in $US as of " & (createdAt as string) & " @ $" & Exch
           end if
       end tell
   end repeat
end tell

to getMktRate()
   set MB_name to "Exch"
   tell application "Eudora" to set LM to last message of mailbox "Exch"
   set tid to AppleScript's text item delimiters
   set AppleScript's text item delimiters to "USD United States Dollars"
   set MMExch to (word 2 of paragraph 1 of text item 2 of LM) as number
   set AppleScript's text item delimiters to tid
   return MMExch
end getMktRate

After this script has been run on the Services for this client, things are changed in the items as shown in Figure 6 below.

http://files.macscripter.net/unscripted/Billable/ServicesQtyAS.jpg

Figure 6: The Service of Figure 4 After the Script Has Run

Finally, I wanted to tally up the Fee-based charges in Canadian dollars for my own tax records. The following script does that, except that it just presents a dialog at the end instead of entering it in a spreadsheet for me.

Applescript:


tell application "Billable"
   -- Get the Client
   set C to first item of (get companies whose abbreviation is "scpt")
   -- get needed exchange rate for converting back.
   set MidMktExch to my getMktRate() -- CDN to US handler normally
   set Agio to 0.02 -- Canadian bank charge for buying and selling.
   set Exch to MidMktExch - Agio
   -- get the services for invoice of C
   set Paid to {}
   set Income to 0
   set Expense to 0
   set I to invoices whose client is C -- easier to do all rather than identify one.
   repeat with inv in I
       -- go through paid up invoices for this client
       if isPaid of inv then set Paid to Paid & services of contents of inv
   end repeat
   repeat with S in Paid
       if rateType of S is "Quantity" then
           set Expense to Expense + (totalCost of S)
       else
           set Income to Income + (totalCost of S)
       end if
   end repeat
   set ClientBilling to Income + Expense
   set CdnIncome to Income / Exch
   set N to name of C
   display dialog "As of " & short date string of (current date) & return & return & "Total Paid is US$" & ClientBilling & return & return & "Canadian Taxible Income is $" & Income with title N
end tell
-- same as before. You'll have to supply your own method here.
to getMktRate()
   set MB_name to "Exch"
   tell application "Eudora" to set LM to last message of mailbox "Exch"
   set tid to AppleScript's text item delimiters
   set AppleScript's text item delimiters to "USD United States Dollars"
   set MMExch to (word 2 of paragraph 1 of text item 2 of LM) as number
   set AppleScript's text item delimiters to tid
   return MMExch
end getMktRate

Clearly these are just examples to illustrate how the process might be done, not universal scripts.

Conclusion

Overall, I've been very pleased with Billable and use it for clients not shown in the ScreenShots. I recommend it to anyone who wants a simple invoicing system that can be customized using its native tags in a template for the invoice, and using AppleScripts to do any calculations you require within a Service entry. The Invoices you produce are PDFs and can be, if you choose, attached to an email for transmission to your client.

You can see the Invoice.pdf that I produced after running the first script above by clicking the thumbnail image below.

http://files.macscripter.net/unscripted/Billable/BillableInvoice.pdf

Figure 7: The Billable Invoice (PDF) After Running the First Script

Happy Scripting and Billing!


iMac running OS X 10.13.1


Filed under: Bell, Review, Billable, Eudora

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)