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.

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.

Billy & SharePoint Synchronization

Billy is based in San Francisco, CA and appears to have around 40,000+ users. Billy has two versions: one for free and a premium version that includes all the features of the free version plus: multiple users, quotes and estimates, custom designed invoices and recurring invoices for $29.00 per month. This post follows the same outline as the FreshBooks & SharePoint Synchronization post.

Customer Synchronization

Customer are set up under Contacts as in Xero and FreshBooks. Unlike FreshBooks and Xero, Billy does not provide for importing clients. It does provide the capability to export Contacts to Excel.

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 Billy. You can setup up a product or a service and use them to track projects but then you can’t use those fields for other billing purposes. Bottom line this is not a job/project oriented application.

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. You could put task into the Description field on the Billy’s Sales Invoice but this a text box so it can’t be repeated in a drop down or combo list.

Time Tracking

The post “SharePoint Task Plan – Tracking Time” discusses time capture alternatives. Billy has neither a timesheet nor payroll capability.

Billing

Billy’s Quotes and sales Invoice features do not support importing of data. Their related reports can be exported into Excel so you could update SharePoint with Quote and Invoice information.

Employee Synchronization

The posts “Example Employee List from QuickBooks” and “Setup Users by Importing” discuss synchronization between QuickBooks and SharePoint Online. Billy does not capture employee information.

Document Management

Billy has no document management capabilities.

Overall Import/Export Capabilities

As mentioned above, Billy has no import capabilities but does provide report export capabilities. Like FreshBooks and Xero it does have an Application Program Interface (API) that can be used by third parties to provide integration, but I am not aware of SharePoint to Xero real time updating capability. See my post for “Real Time Updating Between SharePoint and QuickBooks” for more information.

Bottom Line

Like Xero, if you decide to run your business mostly using SharePoint Online, you can use Billy to do your basic accounting and financial statement preparation. You would also need to use a third party payroll application.

Office 365 Nonprofit

I am adding a blog post on Office 365 Nonprofit since these organizations also face many of the same challenges as small businesses such as:

  • No or very limited technical staff.
  • Limited financial resources.
  • Minimal staff overall.
  • Staff/Volunteers performing multiple roles.
  • Effectively managing projects.
  • Multiple software packages to manage operations that could be consolidated.

As with small businesses, I believe that nonprofits can achieve significant operational efficiencies and cost savings by adopting Office 365 Nonprofit. I also want to familiarize blog visitors with the Microsoft’s Nonprofit offerings in case they know of an organization that would benefit from SharePoint online.

The SharePoint online nonprofit features are very similar to the those available for the business versions of SharePoint Online. This link provides information on Office 365 Nonprofit plans and pricing. Organizations that you know may qualify for free versions of Office 365! Eligibility requirements vary by country and organization size and the latter link provides further information and pricing for non-free plans.

As I mention on the My Consulting Services page of this blog, I provide my consulting services for free to organizations that qualify for the either the donated version of Office 365 Nonprofit or Office 365 Nonprofit Business Premium. This is my way of giving back to our local nonprofit community.

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.

PowerAppsViewEdit

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.

Example SharePoint Survey Application

SharePoint comes with an easy to use survey application that can be used for just about any type of survey (e.g. customer, employee, vendor, etc.). This blog post illustrates using the survey application for an internal employee survey. We use Monster’s “Employee Survey: Use this Sample to Build your own Questionnaire” to illustrate setting up a SharePoint survey.

Select the app by going to Site Content>Your Apps>Apps You Can Add and on the second set of apps is the Survey app. Double click on the icon and the app will be added to your SharePoint site. In this case, we added it to the Human Resources subsite.

Once added, the Employee Survey app looks like this:

The survey name and description is set through the app General Settings page illustrated below.

Notice the Survey Options settings. I decide for this illustration that I don’t want user names showing up in survey results. If I had set it to Yes, only users with the proper permissions could see the user names. Also I want to limit the user to one survey response. Setting “Allow multiple responses?” to No means the user cannot access the survey once they fully complete it the first time.

Below is the screen shot of the actual Employee Survey web page. You can’t tell by looking at the screen shot, unless you are already familiar with SharePoint surveys, but there only two questions. The first question is “Sample Employee Survey*” and the second is “Comments:”.  The * meaning required to be completed.

The numbered questions you see under “Sample Employee Survey*” are sub-questions. I set them up as sub-questions because I want to use apps built in Rating Scale capability for answering the questions. You set the scale range and the Range Text you want to use. Notice there is “Branching Logic” in the settings. I’m not showing that in my screen shot because I’m not using it, but that capability allows you to control the next question the user sees based on the answer to the current question.

Instead of using the built in Rating Scale, I could have set each sub question up as a separate question and required a 1 to 5 number response (or anything else). I felt it was important to allow employees a submit comments so I set the Comments question as multiple lines of text.

The Survey app comes with a “Show a graphical summary of responses” and a “Show all responses” view. Screen shots are shown below.

Notice that the Created By is hidden since opted not to include user names in the survey.

Setting up this Employee Survey took me about 5 minutes and it furthers show has SharePoint Online can be used to monitor and improve your business.