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.