SharePoint Customer/Job Timesheet Example

The blog post “SharePoint Task Plan – Tracking Time” discusses capturing time against a SharePoint Task Plan using SharePoint’s standard capabilities. This post presents an approach of capturing job hours at the Customer/Job level. It is based on Thuan’s post on The Soldier of Fortune blog titled “Building Timesheet Management Solution in Office 365 Without Code” but goes a couple of steps further.

We start by using the Customer List and from that setup a Customer/Job List as illustrated in the screen shot below:


The Job Customer field is a drop-down list from the Customer List. The Job Name field is added in by the user as is the Customer/Job Name. The screen shot does not show the Job City, Job State, Job Zip Code and Sale Person fields that are part of this list.

The Customer/Job field is needed for the Customer/Job Timesheet List below. You will also notice that I added Timesheet Activity as an optional field for billing and/or reporting purposes.


I combined Timesheet Year and Month for grouping as opposed to having them separately as in Thuan’s post. The formula being: =TEXT([Timesheet Date],”YYYY”)&”/”&TEXT([Timesheet Date],”MM”) . By establishing the Customer/Job Name field, it stops users from coming up with their own names and making time reporting unmanageable. See illustration below.

I also added views and grouping for Customer/Job/Date and Staff/Date/Customer/Job so you can see the hours and totals associated with those views. The screen shots in this post are using the New look for lists. Under the SharePoint Classic view, Total Hours are displayed by the selected group.

I then went ahead and generated a PowerApps cell phone application, so staff can enter the data when they are away from their computer. A screen shot of the PowerApps is below:

The PowerApps application above took about 5 minutes develop without any coding. See the post “QuickBooks Employee List PowerApps Example “ for more information on PowerApps.

A Customer Level Timesheet can be developed under the approach outlined above. The difference would be that a Customer/Job list would not be necessary, and the Job Name and Customer/Job Name fields would also not exist.

Office 365 Business Center Preview vs SharePoint

Microsoft included Business Center in Office 365 Business Premium. They added “Preview” after the word Center since it’s a new offering. This is the link for more information on Office 365 Business Center Preview. I’m not going to go through Business Center’s pluses and minuses since its new, but at this time it doesn’t even come close to the customer management that you can do through SharePoint.

Office 365 Business Center Preview does have a couple of advantages:

  1. Syncing of Business Center customer data with QuickBooks Online and QuickBooks Desktop. I did not try QuickBooks Online but I was not able to properly sync with QuickBooks Desktop. Also, the Contact features in Business Center have some bug issues that need addressing.
  2. Ability to generate customer invoices in Business Center and sync those with QuickBooks Online and Desktop. The invoicing capabilities/features are very limited at this time and I could not find information on how to sync with the desktop version.

Business Center functions in a closed environment, unlike SharePoint, making it hard to share data. I think Microsoft would have been better off making Business Center a SharePoint application.

Sales Proposal Tracking

The post Sales Proposals in SharePoint discusses the generation of such documents. The question then comes: How do we track our school irrigation proposal once it is submitted? The posts addressing customer management (search tagline CRM) using SharePoint, particularly Lead Tracking – Direct Sales gives an example how sales and marketing activities can be managed using SharePoint. I would recommend that proposal tracking be incorporated into the overall customer relationship management process as displayed by our examples.

For those looking for a simple way to track proposals, here is a possible approach:

Proposal Document Library with tracking1

I added the following to our Proposal Library web page:

  • Date Submitted.  Tracks the date we gave it to the school.
  • Decision Date. The date on which they will make the award.
  • Disposition.  A drop down list to track its status.  The options being Accepted, Cancelled, Pending, and Rejected.  This lets us report on what finally happened to the proposal.
  • Notes.
  • Assigned To.  The QuickBooks Sample Larry’s Landscape and Garden Supply employee responsible for follow up.
  • Proposed $.  I can also sum this column on the web page so I can see the total value of Pending proposals.

Besides keeping the proposal in the document library, we could also add copy as an attachment to the school customer information on the “Customer & Prospect List.”  This allows it to be viewed from different pages.  Of course doing a site search would also bring us to the proposal.

Sales Pivot Chart

SharePoint allows you to add Excel spreadsheets to your web pages.  This includes Excel charts and pivots tables.  Below is an example of a pivot chart that queries the PRA’s Lead Tracking Access app and then displays leads by physician specialty.

Excel Lead Pie Chart

A knowledgeable Excel user can easily develop charts and Excel reports for viewing the overall operations of the business.

Lead Tracking – Direct Sales & Mailings

Direst sales and mail campaigns are heavily used by PRA in marketing and selling its products (software packages) and services (billing, cash collections, document management, etc.).

Sales staff visit targeted physician office on a schedule basis and provide collateral describing the company’s products and services, topics of interest (e.g. current trends, etc.), and Medicare statistics and analysis.  This material is provided to physicians in a set order and offices are visited on a set schedule.  Physician offices not directly visited by a salesperson are placed on a mailing list and receive the same materials.

All leads are tracked via a SharePoint Access app that resides on the Sales & Marketing sub site.  Here is a screen shot of the main lead tracking page (use the browser Zoom function to enlarge):Main Lead Page

The far left side contains the pages composing the lead tracking application.  The scrolling list is composed of all physician sales targets.  A sales person can search the list by name. The main page contains the contact and background data related to the physician practice.  The horizontal list under the contact and background data represents the following:

  • Contacts – people contacts at the physician’s office.
  • Drop Ins – marketing collateral provided to the lead.
  • Papers – background papers provided to the lead.
  • Medicare Programs – Medicare programs participated in by the practice.
  • Medicare Payments- received by the practice over the last 3 years (this data is retrieved from public data provide by the Center for Medicare and Medicaid Services know as CMS).
  • Service Offerings – the PRA products or services the physician may be interested in.
  • Secondary Specialties – of the physician’s practice.

Below are screen shots of each of these pages.  I will not go through the detail of each one.  The point I want to make is that PRA was able to develop a sophisticated lead tracking application customized to their needs using SharePoint in less than a week. The app was developed by a non IT staff member.  The app allows them to operate efficiently in carrying out and monitoring their sales efforts and at no additional costs for software and customization!

I do want to point out the last screen shot on this page.  It depicts one of 2 web pages that sales staff use to route themselves for their schedule visits.  The routing is based on the physician’s office address entered in the main page.  This routing capability saves PRA significant travel expenses and eliminates wasted time driving by the salesperson.


Contact Detail

Drop In


Service Offerings