SharePoint List Lookup Column of Active Fields – Solving null when field is changed to Not Active.

Below is our Customer/Prospect Contact List entry form. The user picks the active Customer or Prospect of the Contact person from the drop-down list. The drop-down is a Lookup Column from our Customer/Prospect List, which also includes no longer active accounts.

Naturally we don’t want these non-active accounts on our drop list, so we use a Calculated Column called “Active Customers Prospects” =IF(OR([Customer Status]=”Customer-Active”,[Customer Status]=”Prospect-Active”),[Customer Name],””). The “Active Customer Prospect Name” Lookup field is based on this calculated column.

A complete contact entry looks as follows:

An issue comes up when we make a Customer or Prospect not active. The name does not appear on the drop-down list as desired, but their name also does not appear on the Contact list. See below:

Here is the approach we use to keep the Customer/Prospect Name on the Contact List:

  1. Add the Customer/Prospect Name column to the Contact List. This is now the column we use in our views.
  2. Remove the Customer Prospect Lookup column from all Contact List views. This keeps the drop list on our entry page but keeps it off our views, so the users do not get confused.
  3. Add the Microsoft Flow below that maintains the integrity of the Customer Prospect Name on the Contact List.

We also turn off on the under Contact List Settings>Advance Settings>Quick Property Editing since using this view requires the Customer/Prospect Lookup column to be on the Views and added “Please refresh page to view changes.”

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.

Using Power BI & SharePoint Online to Solve the QuickBooks Online “Type” Void

The post QuickBooks Online & SharePoint Online Synchronization Revisited provides an overview of how Power BI can be used to not only synchronize both online products but also integrate them. The post SharePoint for Customer Management discusses how a SharePoint Online list can be used for customer management. One of the fields in the SharePoint Online Customer list is “Customer Type”. While the QuickBooks desktop version supports various “types” for financial and management reporting, the QuickBooks Online version (as of the time of this post) does not support these capabilities. Below is an illustration on how Power BI, SharePoint Online, and QuickBooks Online can be brought together to full this reporting void. The illustratiom is a Power BI report displaying total customer billings by customer type.

The first consideration is how we are going to sync the SharePoint Online Customer List with QuickBooks Online customers. The post QuickBooks Online & SharePoint Synchronization discusses how customers can be imported and exported using Excel. I’m going to assume that SharePoint Online is being used as either the CRM or lead tracking application. Consequently, the customer data in SharePoint Online is imported into QuickBooks Online via Excel.

Here are the overall steps I undertook to develop the report in Power BI Desktop.

1. Because I already established a connection to my SharePoint Online site, I chose Recent Sources and then the SharePoint connection.

2. Selected the Customer & Prospect List table and then edited the query to only include Customer Name and CompanyType. I also renamed the columns Customer and Customer Type.

           

3. I connected to the QuickBooks Online data source, you can see it in 1, and selected the Customer table. I eliminated all the columns expect for CustomerParent, Customer Full Name, and Id. Renamed Id to CustomerId and CustomerParent to Customer Name. The reason I needed CustomerParent is because QuickBooks Online treats jobs/projects as Sub Customers. The total billings report that I am illustrating is at the Customer level so we need to know the parent name.

4. I then selected the QuickBooks Invoice table and only kept the columns you see below.

5. I then set the relationships between the quires.

6. Just to be on the safe side, I copied the Customer Name from both the SharePoint Online Customer & Prospect List and QuickBooks Online Customer quires and pasted them into Excel so I could compare them via a formula to make sure the names are exactly the same.

7. Lastly I added the three tables you see below to form the report.

For illustration purposes, I kept the report simple. Parameters could have been added for dates, amounts >, etc. Graphs and drill downs could have been used for visual effects. My purpose was to show how Power BI and SharePoint Online can be used to improve financial and management reporting for QuickBooks Online users.

Note: Organizations not using SharePoint Online can use a simple Excel Worksheet with client name and customer type to develop the same report.  Power BI Desktop comes with a connection for Excel data sources.

Preparing a Customer Bill from SharePoint

The post “Project Task Plan Billing Using SharePoint” discusses how a project/job manager can set the project’s monthly billing based the hours charged to the project. The Excel spreadsheet referenced in that post, has all the data to be included in the monthly billing. Based on that data, we can easily produce a spreadsheet similar to the one below to send to the customer. This example is for the billing period April 1, 2016 to April 30,2016. The previous billed amounts were for the month of March.

We can then attach the spreadsheet to a word cover letter or even embed the spreadsheet in the Word document. A simple example is presented below. You can use Excel and Word’s rich features to develop a bill to meet your specific needs. This cannot be done in QuickBooks.

Additionally, you can establish a SharePoint folder in Service Delivery subsite’s document library to keep Jim’s bill and other relevant project documents.

Neat List – Phone Call Memo

A neat list that is called Phone Call Memo is added when you activate the Group Calendar in SharePoint Online. It is a great way to track incoming calls and route them to the appropriate employee for follow up. You can set the Workflow to ensure that other employees in the company are also alerted about the call. The Phone Call Memo list also comes with four standard views:

  • My Unresolved Memos
  • My Memos
  • Memos from Me
  • All Unresolved Memos

All items list view:

The Case for a Project List

In my post Job/Project setup alternatives, I discuss  a couple of alternatives once you get a project and want to monitor its progress and results.  To recap:

  • Set up a project team site if the project is either large and/or complicated.
  • Set up a separate task plan to manage the project.  This is a subset of the project team site.

In the post We Got The Job! Now What?, I discuss maybe not even setting up a project in SharePoint and just setup the job in QuickBooks.  I then list reasons why you may want to set up projects in SharePoint.  The last reason being reporting across jobs/projects. This reason is why you should seriously consider setting up projects up in SharePoint.

So to illustrate another project alternative, I took the customers and jobs for QuickBooks’ Sample Larry’s Landscaping and Garden Supply company and set them up in Project List. Here is screen shot of the list:

Projects List

I added Frequency, Estimated Work, Rate, and Estimated $ as fields not found in QuickBooks.  The frequency and estimated work is need for  customer & employee scheduling.  Since I already know the rate, frequency, estimated work, I can make a projection of total estimated customer revenue.  I added Service Type since I didn’t like the way QuickBooks sets up a separate description for the service being provided.  Service Type also let’s me report across all projects. Noticed that view we are looking at is only Active Jobs.  I also have views for Not Active Jobs and All Jobs.

I could have added similar information as is found in QuickBooks like Project Address, Project Contacts, etc. if I so chose. Based on this Project List, I can easily copy and paste the job information into QuickBooks as outlined in the import/export post.

I also decided to place this list on the main firm website since it will probably be accessed in various subsites.  Not only Service & Delivery for scheduling but also Marketing & Sales for customer relationship management.

Another advantage about having project information on SharePoint is that I can easily customize my list views to meet a particular business need.  The example I use here could not have been done in QuickBooks. It just doesn’t have that flexibility.   In fact, I had to run several custom reports and export them to Excel in order to put the data together the way I wanted it.

You may also want to either attach a copy of the Service Agreement to the project row or hyperlink the service agreement to the Service Agreement Library so it can be viewed directly from the list.

Dashboard & View Ideas – Expiring Service Agreements

I will include various ideas for dashboard and view items as they come up.

Here is one for keeping track of service agreements that are expiring with the next 30 days:

Expiring Agreements

This 30 day view is based on the agreement end date.  I added this to the Service Agreement Document Library web page.  To make it more prominent, it could be added to the main Marketing & Sales web page.

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.

Customer & Employee Scheduling

My post Example Employee List from QuickBooks discusses how to import employee information from QuickBooks into SharePoint.  Since SharePoint is team driven, we need to get employee information setup as easy as possible and without redundant data entry into multiple applications. The import process I outline in that post does that.

Now let’s look at using of our Employee List along with our Customer & Prospect List for showing an example of customer and employee scheduling.

QuickBooks’ “Sample Larry’s Landscaping & Garden Supply” company provides customer services that are repetitive and undertaken at the customer’s location. Effective service delivery can be achieved by optimally scheduling these services based on frequency and customer location.

Our first step is to determine the customers that require repetitive services and their addresses.  The view below shows us that information. In the Customer & Prospect list we captured the type of services customers want (e.g. basic lawn maintenance, tree & bush trimming, etc.). I checked off the services needing scheduling so I can view only those customers.  The list view is sorted by Zip Code and Street 1 Address so I can easily decide how to schedule the customers.

Lawn Customer ListThe second step is to set up a Service Delivery Calendar based on the above view to show which customers will receive the services by day, time and frequency.

Customer Landscaping Calendar  (Note: Please use your browser’s zoom function to enlarge the screen shot.  Also I only schedule a limited amount of customers for illustrative purposes.)

This Calendar provides an easy view of customers by day and duration.  Any Larry’s employee could view this calendar to see when the service is due and who will be undertaking the service.

The screen shot below depicts the information that is entered into the calendar. Also if a user was to double click on a customer name on the calendar they would also see this form with the completed information.

Calendar Service Schedule Form

Upon completing the Calendar, I will have the customers and service staff scheduled for the delivery of services.  The view of the below shows the result of the scheduling.

Customer Landscaping Schedule

Users can easily see all the required scheduling information.  I built in travel time between customers and the time it should take to deliver the service.  Since I also have employee information, I could calculate the amount of time an employee is busy during a given time period.

Scheduling of lawn mower equipment could also be added to this calendar to manage those resources.