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 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.

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.

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 – Tracking Time

The post SharePoint Task Plan discusses using SharePoint’s great project team features to develop a sophisticated project task plan. The project task plan includes tasks, subtasks, budgeted hours, budgeted $, assigned staff, task dates, a related Calendar, and Gantt Chart for planning and executing the project. This is all great for planning and proposing the project, but how can you track actual results when your project time and expenses are being captured in either QuickBooks or a time tracking application? This post discusses the options to capture actual time charges against the project. These options are:

  1. QuickBooks. – You can enter the time in QuickBooks by first exporting the tasks as Service Items as discussed in the Exporting Task Lists to QuickBooks post and then use QuickBooks’ time sheet to capture the billable and not billable time as depicted below:

    You can then run either a QuickBooks’ “Job by Time Summary” or a “Job by Time Detail” report and post the actual hours back to the SharePoint Task Plan or to an Excel spreadsheet (Note: I discuss an idea for the former in a separate post). However, some organizations do not use the QuickBooks timesheet either because of # license and permission issues or because in QuickBooks the decision on whether hours are billable or not billable must be made during time entry. This decision usually resides with the project or job supervisor and often occurs when the project bill(s) is being prepared. QuickBooks doesn’t easily handle either writing off hours or deferring hours to a later bill.

  2. Third party SharePoint Timesheet software. There are a few applications available for purchase. However, most of these are geared towards larger businesses, require some modification, and are expensive to license. Also without some custom development they do not get the hours over to QuickBooks for billing.
  3. Third party non SharePoint Timesheet software. There are many third party timesheet alternatives that integrate with QuickBooks. The issue is that they do not integrate with SharePoint and like option 1 above, require posting the hours back to SharePoint or like 2 above require customization to post back.
  4. Custom Develop a SharePoint Timesheet. I would not recommend this alternative for small businesses because of the development and maintenance cost associated with it.
  5. Use a SharePoint List for the Timesheet. This would provide a basic timesheet using SharePoint’s out of the box functionality. It would require some type of posting back to QuickBooks. Thuan’s post on The Soldier of Fortune blog titled “Building Timesheet Management Solution in Office 365 Without Code” goes through such a solution. His solution has the user picking from a list of all projects and then picking a task from all tasks. This could work for organizations that charge at the project level but may be problematic in organizations with many projects and tasks since there are no edit controls. If billing is undertaken in QuickBooks, then a manual posting back is required.
    Below is screen shot of a timesheet alternative based on Thuan’s post. The timesheet is a SharePoint list based on Sample Jim’s Family Store Irrigation Plan SharePoint task plan. Because the list is based on the SharePoint task plan specific to that project, the tasks that can be charged are only the ones specific to the project. The drawback to this approach is that a user would need to go to each project that has a task plan to charge hours to a specific task.

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.

Job/Project Setup Alternatives

In my post We got the Job! Now What?  I discuss when you may want to setup a job/project in SharePoint.  Let’s say that Larry’s Landscape & Garden Supply has just been award a new irrigation design project for one of its existing customers – Jim’s Family Store and they want to set up a project for one or more of the reasons discussed in that post. There are several alternatives:

Jim's Family Store Irrigation Task Plan

  • Set up a dedicated project site under the Service Delivery site as depicted below:

Jims Family Store Project Subsite

As you can see, the dedicated project subsite provides the full SharePoint functionality that comes with any SharePoint Team site. This alternative is best suited for more sophisticated and complex projects. The dedicated project Task Plan approach has the advantage of not requiring a separate site, but if you look at the Quick Launch menu on the left of the Service Delivery subsite in the first screen shot, it is getting pretty complicated. It displays Documents, Customer Landscaping Schedule, task templates, projects, etc. A lot of projects and template task plans could make the site and its navigation cumbersome.

Another alternative is to set up a SharePoint Subsite under Service Delivery dedicated to task templates and project task plans. This would keep the Service Delivery site less complicated and easier to navigate.



We Got The Job! Now What?

We were awarded the school irrigation plan project! We know that SharePoint has some great project management tools.  Do we go ahead and set up the project in SharePoint?  The answer to the question is it depends and we will be discussing various scenarios in this and other posts.

QuickBooks’ Sample Larry’s Landscape & Garden Supply is going to bill the school as they complete each phase of the project.  The amount billed is going to be what they bid for a given phase but they want to capture actual time to determine the accuracy of the bid. Pretty simple.  In this case, I would recommend exporting the tasks to QuickBooks as we discussed in that post and setting up the job just in QuickBooks. I would also add the Sales Price column to the export as discussed in the post.

I wouldn’t bother setting up the project in SharePoint since my project activity is almost entirely in QuickBooks. Like the irrigation proposal and estimate templates,  I would have both a SharePoint report template for this type of plan and a workflow for report review and approval. I probably want to easily review other completed irrigation plans during the project. Consequently, I would place the report in an appropriate SharePoint document library.

I would setup a project in SharePoint under these scenarios:

  • A customer with multiple projects.
  • Substantial project documentation (e.g. status reports, multiple deliverable documents, etc. – again I would have templates & workflows wherever possible).
  • Substantial or complex project reporting.
  • Complex project management requirements.
  • Large project teams.
  • Recurring or long term service contracts.
  • Staff needing to access project financial information but providing access to QuickBooks is not preferable.
  • Report across projects.