Making SalesForce Events Billable

Relationships into Accounts, Contacts, & Opportunities

There is a reason there is not yet a built-in Invoicing procedure in SalesForce.   It is complex. 

The Events table is the best one to use as the basic record on an invoice for those who do billable hourly work such as consultants, attorneys, and contractors.    Events can be entered by the consultant on a Palm on site, then synchronized to show up on the Salesforce Calendar. 
thbnail_iiic.gif (1015 bytes)

Tasks could be billable, but they do not always have a date associated with them.   They can be undated, and open until completed.  So they are not the best records for invoicing purposes.   The payer of the invoice will usually want to know the date a task was done, so it is basically an Event.

We will assume that we always want to send the bill to an Account.  This would be the address of the company record.  An individual Contact would work for the Account, but would not pay the bill.  

In Salesforce, an Event can be associated with either an:

Account - which contains the Billing_Street and other address information

Contact - someone who works at the Account location. 

Opportunity - through to the Account location


This flexibility also makes it complex, and more difficult to create a standard Invoice or report.  You will need to standardize on one or make a multiple IF statement.   An invoice needs a connection between the event itself, and who it is billed to.


This is the easiest relationship to use when printing an invoice.  You only need to filter on the EVENTS.Account_No field, which links to the billing address information in ACCOUNTS.


It is best in Salesforce to have some Account to link a Contact to.  Although a Contact can be created without an Account, this is not advised, as we will need all the consistency we can get.   For every Contact you are going to bill, make an Account for that person.  Otherwise this is how you might put in a friend or relative you won't bill.


This relationship links from EVENTS through the Contact_Id to CONTACTS, and in turn through the Account_Id into ACCOUNTS to reach the billing address information.


This relationship links from EVENTS through the What_Id field to OPPORTUNITIES, and in turn through the Account_Id into ACCOUNTS to reach the billing address information.


This is the most complex relationship which links from EVENTS through the What_Id field to OPPORTUNITIES, and in turn through the Contact_Id into CONTACTS, and in turn on Account_Id into ACCOUNTS to reach the billing address information.

So how can a single program or function accomplish all the above relations?  This would be difficult, as it would have to work for all the above relations.   In Database Management 101, we learn that tables should link on one field.  But, we have two fields that link directly or indirectly (through CONTACTS or OPPORTUNITIES) from ACCOUNTS into EVENTS, the Who_Id and the What_Id.  So who knows what to do?

So we need to standardize the relations, through the creation of an additional table.  A procedure will run through the Events table, and using IF statements, populate that table with the address information from ACCOUNTS.  Then a report can be run on this table.

In order to link into the ACCOUNTS table from both CONTACTS and OPPORTUNITIES, we will need open the ACCOUNTS table 3 times, each under a different alias name.  Here is the Data Session visible through SalesForceFox.

wpeD.jpg (20044 bytes)

Because the EVENTS table links using either the Who_Id field into CONTACT, and the What_Id into ACCOUNTS, you cannot sort simultaneously by two fields at once.  The EVENTS table can either be in order by Who_Id or What_Id at any given time.  This is why we need an integration table for invoicing.

Possible Connections from Events to Accounts

wpe10.jpg (167206 bytes)

wpeF.jpg (169487 bytes)

wpeE.jpg (173020 bytes)

wpe13.jpg (161114 bytes)

These are usually personal, non-billable events,
such as a birthday. 

Identifying Unlinked Events

Sometimes people neglect to link the Events to an Account.  Thus they remain unbillable.  Unbilled events are lost money for you. You have just worked for free.  This is the case when an event has been entered on a Palm handheld, then synchronized into SalesForce.  A report which identifies these is helpful.  It is advisable for the consultant to put the Account Name as the first word of the Event description.

wpe11.jpg (166119 bytes)
EVENTS unlinked


This may occur when both the Opportunity and the Contact is filled in for an Event.  In this case, it would be advisable to use the CONTACT->ACCOUNTS link, as the Opportunities sometimes could involve other Accounts.

wpe12.jpg (168683 bytes)
Both Routes to the ACCOUNTS table


Showing the Starting Time and Ending time

In order to list the Starting Time and Ending time on the Invoice, we will use the Due_Date and the Duration.  The Due_Date is actually the Date and Time of the end of the Event.  We must subtract the Duration of minutes from the Due_Date/Time in order to get the Starting Date/Time.  This is another reason we need an integration table for invoicing.

Selecting the Data into a Query

We will take the approach of selecting the data from the EVENTS and doing the integration up front, so we have a Query table to invoice from.  This is the standard approach taken in most reporting programs.  A special program scans down the EVENTS table and adds the Account_no for linking into the ACCOUNTS table for the address.

Formatting the Report

Salesforce can output the data to Word on a record basis, but for reporting, there is not a way to put a logo, or to make the address of the Account in a label format.  So a special report writer must be used.  SalesForceFox uses the Report Designer included in Microsoft FoxPro, which is very flexible.

wpe2.jpg (62861 bytes)

Marking the Event as Billed

In order to prevent re-billing the same Event, we need a field to mark the Event as billed.  We can use the Query file for this as well.  One option is to use the Location field to put in the date billed.  A new field for this purpose would be a helpful addition to SalesForce.

This provides a way to invoice from the data entered into Salesforce.  It avoids double entry of the same date, time, and work done into Salesforce and an accounting system.  This enables the user to focus more on doing the work, rather than recording it.  It makes Salesforce more of an all-in-one solution.


More Tips - SalesForce Help

Rick Shaddock
800-319-3190 is a division of CICorporation.US and not part of