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

Wave Accounting & SharePoint Synchronization

I’m adding a short post on Wave since it has gotten good reviews and deserves to be discussed with the other accounting systems.

Wave is based in Toronto CanadaWave’s Accounting, Invoicing, and Receipt Scanning are free. Wave charges for Payroll, Credit Card and Bank payment processing.

Here is a Wave Review 2018 by Cloudwards that provides more information on the product. The reviews I have read give Wave high marks and classify it as very good for single owners/very small businesses. I believe that if Wave is used in conjunction with Office365/SharePoint the combination could meet the needs of many small business and save those businesses money on their accounting software.

Customer Synchronization

Customer are set up under Sales in Wave. Wave provides the capability of importing clients via a CSV file. The customer data in Wave is limited but if you are doing your customer management in SharePoint this would not be a factor. See my post on Customer Management for further information.

Project Synchronization

The post “The Case for a Project List” – I suggest you should set up projects in both SharePoint and QuickBooks. I also point out that although you can import from QuickBooks to SharePoint, there are multiple steps involved. It is easier to go from SharePoint to QuickBooks.

There is no concept of Job or Project in Wave. You can set up a product or service, but they are single items. Bottom line — this is not a job/project oriented application. However, you could do your project management and bill preparation in SharePoint/Office365 and record the invoices and payments in Wave.

Task Plan Synchronization

As in Xero, there is no concept of Task. You could use the product/service field to track tasks but then you can’t track a project if you use those fields for that purpose. Again, this is not a drawback if you are using SharePoint Task Management capabilities.

Time Tracking

The post “SharePoint Task Plan – Tracking Time” discusses time capture alternatives. Wave has a timesheet capability as part of payroll but there is no import capability. Entering the data in two places is necessary if you are tracking hours for payroll and project or customer management.

Billing

Wave’s Estimates and Invoice features do not support importing of data. Their related reports can be exported into Excel, so you could update SharePoint with Estimate and Invoice information. As mentioned above, you could decide to undertake these functions in SharePoint.

Employee Synchronization

The posts “Example Employee List from QuickBooks” and “Setup Users by Importing” discuss synchronization between QuickBooks and SharePoint. I did not look at Wave’s employee data capabilities.

Document Management

Wave has no document management capabilities.

Overall Import/Export Capabilities

Wave has limited import capabilities but does provide report export capabilities. Unlike FreshBooks and Xero it does not have an Application Program Interface (API) that can be used by third parties to provide integration.

Bottom Line

Like Xero and Billy, if you decide to run your business mostly using SharePoint and Office365, you can use Wave to do your basic accounting, financial statement preparation, payroll and collections. Its added advantage is that the software is free.