Identify Sub-Task Level in SharePoint Office 365 Task List

This SharePoint Forum post titled “Determine a sub-task’s level using SharePoint Designer” discusses an approach for identifying a sub-task’s level.

We needed to identify the sub-task level for reporting in our Office 365 SharePoint solution “SB2O”. There will be different types of customers using our SharePoint small business template, so we needed a more flexible approach to identifying levels. This blog discusses our approach for identifying N levels.

Below is a screen shot of a standard Task list titled “Sub Task Level”. The list contains a parent and 5 sub-task levels. The ID column is the Item’s ID. I added a “ParentLevelId” number column to capture the ID of the parent Item and a “Task Level” number column to capture the task level.

I then developed a SharePoint Designer 2010 workflow. Since a top-level task, in this case the “Parent Task”, always has a null Parent ID, we test to determine if the task’s Parent ID is empty. If so, we set the Task Level to 0 and the ParentLevelId to 0. We then stop the workflow.

If it is not, we set an integer variable called “ParentId” to the Parent ID of the current item. We then set a number variable called “TaskLevel” to the Task Level of the Parent plus 1.

We find the Task Level of the Parent by doing a Lookup on our Task List, “Sub-Task Level”, with ID and the ParentId variable to relate the two items.

We then up date the current item as shown below and stop the workflow.

Below are the completed workflows.

Note that the only reason the ParentLevelId and the Task Level number match is because I entered the items one after another and no task rearrangement. Not a likely user scenario.

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 Lead Tracking List Approaches

The posts “SharePoint for Customer Management“, “Lead Tracking – Direct Sales & Mailings” and “Lead Tracking – Sales Networking” talk about how SharePoint Lists can be used instead of a third party CRM package. The main advantages being saving on license fees and a solution geared specifically to your business needs. Those posts illustrate various SharePoint Lists for tracking sales and marketing activities. The “SharePoint for Customer Management” post talks about keeping one unified customer list for both existing customers and prospects and keeping that data on the main SharePoint site as opposed to keeping prospects separately on the Marketing & Sales subsite. The primary reason being the main site level allows the data to be accessed by all subsites. The post also discusses setting up separate views for Active Customers and Active Prospects so you can easily access that data. The post “Lead Tracking – Direct Sales & Mailings” discuss using a SharePoint Access data base application for tracking daily sales and marketing activities.

But suppose you would prefer not to use multiple lead lists and an Access application (while providing great functionality — they do add more complexity) and keep prospect/lead data on the Marketing & Sales subsite. In other words, a your looking for simpler approach. Below is a screen shot of such a Lead list:

Additional columns can be added as necessary, for instance since we are using QuickBooks’ Larry’s Landscape & Garden Supply example company, a column for Frequency (weekly, monthly, etc.) is probably appropriate following the $ Quote. Once a Lead becomes a customer you can then add them to a separate customer list. However, if the Customer List is on the main site and the Lead List is on the Marketing & Sales subsite, this would entail entering some data from scratch into the Customer list. The reason for this is that a subsite column cannot be looked up on the main site. Even if you had the Lead List on the main site, your Lookup column list would show all Leads and not just those that resulted in customers. This is because SharePoint does not provide filtering for Lookup columns. Therefore, you are probably better off keeping the separate Lead List on the main site.

Service Firms – Use SharePoint Online to Stop Losing Potential Customers & Making Costly Initial Estimate Visits

This post is a result of an experience I just had in getting an estimate to repair my pool. It got me wondering why service firms need to send someone to give you an estimate when, in some or many cases, they can ask you to take a picture(s) of what you need done and either provide an estimate from them or use them for follow up questions and then an estimate.

My pool is Pebble Tec and about 16 years old. As you will see in the picture below, it has a crown around it and that crown has eroded in two place. Trying to find someone in the Phoenix area during the summer to do this type of repair is hard for two reasons: 1. They are very busy because it is the peak time. 2. It is a small job. I needed to make a lot a calls to find two contractors that were willing to give me an estimate. The first one told me it would be two weeks before someone could come out and give me an estimate. The second came out the next day, because they already had someone scheduled in the neighborhood, and provided me with the estimate. The second company seemed reasonable so I told them to schedule the work. Consequently, I cancelled the first contractor. After speaking with the second contractor it was evident to me that neither had to come out to give me an estimate, they could have done it from cell phone pictures and saved travel time and transportation. Also they could have figured out if they wanted the job, how long it would take, which employee would do it and when they could schedule it in. I would have been fine with this since it meant I did not need to hang around for the person that would do the estimate. So taking pictures would have made it easier on me and them.

I set up the simple SharePoint custom list below to track the opportunity and respond back to the prospect. When the perspective customer’s call comes in, the information can be entered and they can be asked to email pictures of the needed repairs.

The paper clips are the emailed pictures that are attached to the opportunity. In my case they looked like this:


I decided to do picture attachments instead of setting up a Custom SharePoint Picture column because I think in most instances a potential customer would have multiple pictures…a SharePoint Picture column can only reference one picture (of course you can have multiple columns but that can unwieldy). One drawback to attachments is that PowerApps do not support displaying attachments so you could not view the lead fully on a cell phone or tablet.

QuickBooks Employee List PowerApps Example

Microsoft just released a “Preview” of PowerApps. PowerApps allows users to develop phone and tablet applications for various types of data sources including SharePoint and Excel. Additionally, Microsoft is going to add PowerApps directly to SharePoint Lists so that a PowerApp can be develop for a List just like a SharePoint List View.

I decided to give it a try by developing a Phone App for my blog post Example Employee List from QuickBooks. I wanted the app to show the employee name, email, work phone #, cell phone #, and home phone # so a user could look these up easily from their cell phone and also be able the edit the email address and phone #s if necessary.

Microsoft claims that PowerApps allows users to develop applications without programmer support. Based on my experience, I agree with that statement. A power user that can develop SharePoint and Excel formulas should be able to develop a PowerApps tablet or phone application.

In order to compare my app to a SharePoint View, I defined a mobile View with the same List columns as the app. Below are screen shots of the “Staff Contact List” mobile app and the related SharePoint List View.

PowerApps comes with three different types of screens. A browse screen like the one below that allows you to browse the data.

A Detail Screen that gives you more information about the item and lets you delete or select the item for editing.

An Edit Screen to edit the item. I decided not to allow the user to edit the staff name but all the other items.

Here is the screen shot of the corresponding mobile List View. I called this view “PowerApps.” For those of you that have not used a mobile device to view a SharePoint List, the view below is what you would see when going to that View’s web page.

If you clicked on the Edit icon in this View, you would get all List columns for editing.


The advantages of a PowerApps for mobile and tablet devices are:

  • You don’t need to go the SharePoint page in your browser. You can click on the PowerApp directly from your device.
  • The layout is much cleaner and friendly to use.
  • You can choose the items you want the user to edit or not edit.

Overall my first impression of PowerApps is very favorable and some great applications can be developed by a user in your business that is familiar with Excel formulas. This is a big plus for small business that cannot afford or do not need an IT staff person. Because this a “Preview” version of PowerApps there are still some kinks that need be to worked out but I’m looking forward to full product release.

Project Task Plan Billing Using SharePoint

We setup a task plan for the Jim’s Family Store Irrigation Plan, discussed how we could post time against the project and view those timesheet charges. So let’s stay on the path on not yet posting to QuickBooks and discuss how we use SharePoint for billing the project. Below is a SharePoint Datasheet View of the project timesheet list. It contains the following columns:

  • Task:Title. These are tasks that so far received timesheet charges.
  • Timesheet Year Month. This column later allows summing the hours by month.
  • Timesheet Date. The dates the hours are charged against the task. In this illustration, I want to make the billing decisions by day and this is why task 2.0 “Determine irrigation requirements” is displayed on three rows of the view. I could have gotten more finite and also summarized by employee.
  • Timesheet Hours. The total hours charged on that date.
  • Non – Billable Work. These are hours that are not to be billed to Jim but incurred on the project (e.g. training time).
  • To Be Billed Work. Hours that are to be billed in March.
  • Not Yet Billed. Hours to be billed at a later date.
  • Project Rate. The rate associated with the task. This is for informational purposes.

The project/job manager completes the Non-Billable and To Be Billed columns of the datasheet to compute the actual billing. The Not Yet Billed column is a computed columns. This datasheet view is also exported and linked to Excel. The two pivot tables shown below the view are based on the export and show Actual Work to Budget and Actual Billed Amount to Budget. They are embedded in the billing view web page so the budget impact of the billing is available to the project manager as they enter the hours.

If the decision was not to develop the billing by date but rather by month, the Non-Billable, To Be Billed and Not Yet Billed columns would have been added to the Excel spreadsheet and not contained in the datasheet view. The pivot tables would still present the same data the project manager entered the hours.

Viewing Timesheet Charges

The post SharePoint Task Plan – Tracking Time presents a SharePoint List based timesheet for charging time against a project task plan. Adopting this type of list based timesheet raises the question: How does a project or job manager view all the time tracked to project? The screen shot below presents a view of the timesheet list displaying time charges by year/month and then task and employee. Summary totals are also presented. This view provides an easy way of seeing all time charged to the project. Alternative views would be by task, year/month, and employee or by employee, task, year/month etc. Because it is easy for users to develop their own personal views in SharePoint, a project or job manager can develop a view to meet their particular needs.

The number in parenthesis at the end of the task, indicates the number of entries for that task.  Heading columns, e.g. Task Title, Timesheet Date, Hours, Comments, Staff Member, etc. are not depicted in the screen shot.  They appear at the top of the view.

SharePoint Task Plan

The post Job/Prospect Setup Alternatives discusses the option of setting up a project task plan. SharePoint has great project management tools to assist team members in undertaking projects and communicating to customers of its progress.  So let’s look further at how this could work for the Jim’s Family Store Irrigation Plan project. We will assume that QuickBooks Sample Larry’s Landscape & Garden Supply company took advantage of SharePoint’s great functionality and put together both the proposal as discussed in Sales Proposals in SharePoint Online and a task plan as discussed in Task List Templates in Service Delivery. Larry’s has decided that for project management and customer relations reasons they want to manage and report against the plan.(Note: I could have set up the task plan as described in this post and submitted it as part of the proposal.)

Here is what they did to set it up:

  1. They added the Irrigation Design Tasks template to the Service Delivery subsite by choosing the template under Service Delivery subsite>Site Contents>Your Apps as shown below.

2016-02-29 (1)

  1. They set up a “Load Estimates” list as depicted below and copied and pasted the Project Rate, Estimated Work, and Estimated $ from the Excel spreadsheet in the Proposal Library to the list.

  1. This resulted in all tasks, hours, project rate, and estimated amounts being loaded into the task plan. Larry’s project manager then went in and edited the task plan for Task Start and End dates and assigned employees to the tasks as shown below. You may notice that I didn’t include Estimate $ and Project Rate in the view below since these amounts are fixed for this particular project and we can see them under other views of the task plan. I did include Estimated Work since this helpful in assigning employees to the project.


We now have the completed task plan as depicted below. You will notice that the total hours and project start and end dates are at the top of the columns.

The standard Task Plan app in SharePoint also includes the following views:

  • Calendar View. Depicting the dates of the tasks.

  • Gantt View showing tasks and their relationships.

Additionally, list views are added by SharePoint for:

  • Completed Tasks
  • Upcoming Tasks
  • Late Tasks
  • My Tasks

The diamonds in the Gantt chart indicate one day tasks.  Project deliverables can be shown on the chart by giving them one day start and end dates.

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.