Example Employee List from QuickBooks

My Setup Users by Importing post talks about exporting employee information from QuickBooks into SharePoint to setup users. These users can then be chosen throughout SharePoint to assign tasks, etc.  However, you may not want to set up all employees in SharePoint since they either will be viewing the company site as visitors or not viewing the site at all.

For example, QuickBooks’ Sample Larry’s Landscaping & Garden Supply company would likely have employees that do not need to access its SharePoint site, thus don’t need to be assigned as users, but information about them is needed for human resource and service delivery management.

One way of capturing this information is to develop an appropriate SharePoint list.  This is an example of an Employee Information List using the 3 employees that are in QuickBooks’ Larry’s sample company:

Employee List
Employee List

This list was very easy to setup since I set up the required list information from QuickBooks using a custom report, exporting it to Excel and importing it into SharePoint.

I established the list on the main company SharePoint site since it is needed by Human Resources and Service Delivery which are two separate SharePoint sub sites under the main sub site.  See my Customer List post for more information.

I also added a column, not show above, called Employee Full Name.  This column is First Name plus Middle Name plus Last Name.  I did this so I can easily lookup an employees throughout SharePoint.

I would recommend using either QuickBooks or your payroll system as the source for employee information.  Those applications require more data about employees then you probably would need in a SharePoint list and SharePoint makes it easy to import the data.

More specifics uses of the Employee list are in other posts.


What am I paying Microsoft?

I subscribe to Office 365 Personal since I’m a one man band. I chose the one year payment option so that costs me $69.99 per year.  I also require Access so that I can develop SharePoint Access apps so that is another reason why I went with the Personal version.

As I mention on the blog’s About page, SharePoint is viable for small business because of the SharePoint Online version. I subscribe to Plan 2 because it comes with Excel Services, PowerPivot, and PowerView.  You can do business intelligence reporting using these services and Excel.  The Sales Pivot Chart post shows the use of Excel Services.  This costs me $8/user/month. There is a $5/user/month plan (Plan 1) but I normally don’t recommend it since you will likely use Excel Services.

So I’m paying Microsoft $166 a year.

I recommend that most organizations subscribe to one of the Office 365 plans that bundle Office and SharePoint together.  Office 365 Enterprise E1 provides cloud versions of Office, meaning they don’t run on a PC, for $8/user/month.  Most users do not need a desktop version. Those that do can get a desktop version or the Office 365 Personal version. If you have a large number of users that require Office than Office 365 Enterprise E3 probably makes the most sense for $20/user/month.

Keep in mind that not all of your employees may require user licenses.  If all they are going to be doing is viewing the SharePoint site and not editing, then you can set them up as Visitors and they don’t cost you a thing.

Import/Export QuickBooks Customers

In the Customer List Post, I discuss setting up the central Customer Information list based on the QuickBooks Sample Larry’s Landscaping & Garden Supply company.  Since the customer information that I needed was already in QuickBooks here is how I imported into SharePoint:

  1. I developed a QuickBooks Memorized Report that contained the fields that match the Customer Information List fields.
  2. Ran the report and exported it to Excel.
  3. Opened my SharePoint Customer Information list in Edit mode.
  4. Cut and paste all the rows in Excel into the SharePoint list.  Note: I did all the rows at once.

Pretty simple and fast.

Since we are going to be tracking prospects on our company’s SharePoint site and then changing Is Prospect? to No once they become a customer, we are going to have to get their company information into QuickBooks.  Unless your getting a lot of customers at once, it is probably easiest to enter the data into QuickBooks.   However, you can use the Add/Edit Multiple List Entries feature in QuickBooks (Customers>New Customers & Jobs>Add Multiple Customer: Jobs). All you need to do is:

  1. Open your Customer Information List in Excel
  2. Save the spreadsheet.
  3. Use the above name Multiple List Name feature in QuickBooks.

Also pretty simple and fast.

SharePoint for Customer Management

SharePoint (SP) is ideal for gathering and managing customer information.  When I use the term customer, I’m also including prospective customers.  My Lead Tracking – Sales Networking post shows how SharePoint can be used for tracking perspective customers/leads. In this post I discuss an approach to setting up a customer list so that customer information can be shared throughout your SharePoint site.

First I want to discuss SP Site Content Types.  Think of them as an “object” (e.g. customer) and then the content that makes up or defines that object.  Content Types can be used through out the SharePoint site it is defined in and all of its sub sites. We are going to want to know about the object customer pretty much throughout our company site, so it probably should be defined at the highest level site or our root site.

In looking at Sample Larry’s Landscaping and Garden Supply in QuickBooks, I identified the content that makes up their customers and needed universally in Larry’s.  I defined a Site Content Type called Customer Information that is used for the Customer Information List.  The items or fields that make up the customer are shown in the list below:

Customer List

Noticed that I decided to keep universal information about Customers in this list.  This is because the information on the list will be used throughout the site.  Meaning Sales & Marketing, Accounting, etc. will be using this list and adding their own information on their respective SharePoint pages.  We want to keep control of certain information universally, for example of Customer name, so that we can gather all the information about a customer throughout the site.

Under “new item or edit list” in the screen shot, I have setup 3 views of the list.  Active Customers present just the active customers, Active Prospects the active prospects and All Items include all organizations even those that are Not-active.

I imported the customer data from QuickBooks into SharePoint since the customers already existed in QuickBooks.  Once you get your customers setup in SharePoint, you are probably going to want SharePoint to be your source application.  This is because you will be doing more with customers in SharePoint.  For example, setting up a lead tracking capability and then marking the lead as a customer once you get them.  Also QuickBooks has an Add/Edit Multiple List entries capability that makes the import into QuickBooks easy.

QuickBooks Sample Companies

Because so many small businesses use QuickBooks, I will use the sample companies “Sample Larry’s Landscape and Garden Supply” and “Sample Rock Castle Construction” that QuickBooks provides in its products. I will also address how to import and export out of both SharePoint and QuickBooks to keep duplicate data entry to a minimum.  I will also discuss how the two products can compliment each other.

Setup Users by Importing

Microsoft provides an easy way for adding your staff to SharePoint.  An Admin can access the Bulk Add Users page seen below.

Bulk Add User

This page provides you the opportunity to import an Excel csv file with the information needed to set your users up.  There is even an example Excel worksheet that shows you the fields required.

User Import Spreadsheet

Most small business have the information needed to produce this spreadsheet in their accounting or payroll application.  For example, if your employees are in QuickBooks it is a snap to produce this spreadsheet.  The only fields required are Display Name and User Name but I recommend you import as many of the fields as you can.

Once users are imported you can assign them roles and to the appropriate SharePoint Groups.

Even if you have a small staff, I recommend importing the information since it will save you time during setup.

HR Documents Custom List

The post Improving HR Communications shows how using the HR sub site’s Document Library is used to allow employees to accesses the companies HR documents.  Another approach is to set up a SharePoint Custom List for the HR documents.  The screen shot below shows this alternative:

HR Document Custom List

Notice that the list is much different than the document library.

  1. I named the list “Human Resources Documents”.
  2. Category and Sub Category items are added to group the documents rather than the folder approach in the Document Library.
  3. A Description item was added so the employee can understand what is in the document.  Note:  I could also have added Description to the Document Library.
  4. The paper clip is used as the link that opens the document.
  5. I sorted the list by category and sub category so that if there are a lot of different documents the employee could easily go down the list. Of course the search function can also be used to find a specific document.

If there were many Employee Benefits documents, I could have set up a separate list called “Employee Benefits”.

The “new item” and “edit this list” in the screen shot do not appear if the employee only had Read permissions.

Choosing either the Document Library or Custom List approach is really a matter of preference and the types and volumes of documents involved.

Improving HR Communications

Setting up a Human Resources SharePoint sub site where managers and employees can view HR policies, procedures, and documents at any time can increase staff morale and reduce HR time and effort.  These documents do not have to be repeatedly mailed to staff or sent out every time there is a new update.  Employees can research their own questions without contacting HR and HR can guide staff to documents for answers to their questions. Let’s look at an example HR sub site.

I set up the following sub site under the company’s main site. (Click on your browser’s Zoom function to enlarge.)

HR Main page

I set up two folders in the Document Library that employees can access to review HR forms and documents.  I then setup subfolders to further segregate the information.  Whenever you generate a SharePoint Team Site a Document Library is established.  As its name indicates, this is where you can store documents related to the site.  In this case HR documents.

The Policies folder has the following subfolders for their related documents:

HR Policies Subfolde

The employee benefits folder has the following subfolders that employees can access at any time:

HR Benefits Subfolder

Also notice that I added a Newsfeed to the site telling employees that an updated Health Plan description is now ready for them to review.  With SharePoint, employees can sign up for alerts when updates occur.  HR does not have to send out an email advising them of the update or send out the new document.

An alternative to an HR sub site would be to have an HR folder on the main company page and then add the appropriate subfolders below that folder.  This could be a better alternative for companies with few employees and minimal HR documents.

Folders are a great way of grouping information on a similar subject but there are couple of things to keep in mind.

  1. You do not see the parent folder name when drilling down to the subfolder. In the example above, you can’t tell that Education & Training is under Employee Benefits. If it is important for staff to see the parent name, a work around would be to place the parent name before the folder. For example you could use the name Employee Benefits – Education & Training.  Of course if you have subfolders within subfolders the name can become un-manageable.
  2. When you open a folder you don’t see the folder name as shown in the screen shot of the documents within the Vacation & Personal Leave folder below.
  3. If a user does a search using the Find a file, which appears on all SharePoint pages, they go straight to the document and also see the folder path.  This is illustrated in the second screen shot below.

Leave Policy Doc exampleLeave Search

My experience and advise is that users should use the search capability as the first resort.  It is quicker and avoids the guessing of where documents maybe.  In another post I will discuss alternatives to folders, but if folders work nicely for your company, go ahead and use them.

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