In SharePoint, you can create task lists in which tasks can be assigned to users. Each task is a new item in the task list, which means the users has to switch between items in that list to handle their tasks. The SharePoint tasks also has default metadata, such as Title, Start date, End date, Assigned to, etc.

What if you just want a simple task list in which you can quickly handle your tasks without any of that SharePoint task metadata? Ofcourse, you can use a simple choice column within your SharePoint list, but then you can’t relate the tasks to another field of your SharePoint list which is want I wanted to achieve.

In this blog, I will show you how to achieve this in a (custom) SharePoint list with the help of PowerApps

For this example, I will be configuring an onboarding process in SharePoint where new employees will be registered and only tasks related to their job titles will be added (I don’t want to see any other tasks, because they are not relevant to the selected job title).

SharePoint configuration

First, I will need to configure the lists in SharePoint, which are:

• Onboarding: This is the list where the new employees will be added
  • Name: Name of the employee
  • Job title: Job title of the employee (lookup to the JobTitles list)
  • Start date: Date on which the employee will start
  • Tasks: The tasks that are assigned (more on this column later)

• JobTitles: This list contains all company job titles
  • Title

• OnboardingTasks: This list contains all tasks that need to be handles for a specific job title
  • Job title: Job title of the employee (lookup to the JobTitles list)
  • Tasks: This is a choice column which contains all tasks for the given job title

Onboarding – Tasks column

Because we want to keep track of the task handling for each new employee, we need to store the task that comes from the OnboardingTasks lists into the Onboarding list item. This will be done using a choice field (just like the original field inside the OnboardingTasks list).

However, I don’t want to configure this column with the all the values that are in the OnboardingTasks list field, because that means that I have to change this field everytime the field in the OnboardingTasks list changes. For this reason, I configured this column to allow manual entries. Also, make sure that the ‘Allow multiple selections’ option is selected.

Adding the tasks to your SharePoint item

SharePoint has no clue of the OnboardingTasks, so we need to configure our forms to make sure it does by customizing the forms with PowerApps.

First thing we need to do is to hide the Tasks field from the Form, because we won’t be using it in our PowerApp.

After that, we need to add a Gallery control to the canvas. I used the Blank vertical template for this. Make sure you don’t add the Galery into your Form, otherwise it won’t work! When I first played around with this, I couldn’t get it to work, because I did add it to my Form. After quite a while trial-and-erroring, I found out that when the Gallery wasn’t added to the Form, it worked perfectly.

Now that we’ve added the Gallery to our canvas, we need to make sure it pulls tasks from the OnboardingTasks list and only the ones that are related to the selected Job title. To do this, we must add the OnboardingTasks list as a new Data source. After that, we can select this Data source in our Gallery. Because we want a quick and easy way for users to handle their tasks, I added a Checkbox control into my Gallery. After adding a header label, resizing and positioning, the Form looks like this:

We still don’t see the tasks because we haven’t configured the Items property for our Gallery correctly. Now, it shows all items that are in our OnboardingTasks list (that’s why there are 4 check boxes visible: there are 4 enties in the list), but we only want to show the choices from the Tasks field for the related Job title. We therefor have to modify our Items property of the Gallery to only show these items by using this function:

Sort(LookUp(OnboardingTasks,Job_x0020_title.Id=DataCardValue2.Selected.Id,Tasks),Value,Ascending)

I will breakdown this function below:

• Sort(): This is not really necessary, but I think it’s more user friendly to show all items in an alphabetical order, which is what this function does. It sorts the source (which is the output from the Lookup() function) in a specific way (which is Ascending in this case).
• Lookup(): This will perform the actual lookup to the Tasks that are related to the selected Job title.
  • OnboardingTasks is the source from where I want to perform the lookup
  • Job_x0020_title.Id=DataCardValue2.Selected.ID is the formula I use to only get values that are related to the selected Job title (DataCardValue2 is the Job title dropdown menu in my Form)
  • Tasks is the field from which I want to get the values from

After adding this to the Items property of our Gallery and setting the value for the Checkbox to Value, the Job title related Tasks are now added to our Form:

Saving the tasks into your item

Because the checklist is not a part of the Form, the tasks won’t be saved into SharePoint automatically. We have to save them separately to SharePoint using the Patch() function after the Form is submitted succesfully by adding the following function to the OnSuccess property of the Form:

Patch(Onboarding,{ID:SharePointIntegration.SelectedListItemID},{Tasks:Filter(Gallery1.AllItems,Checkbox2.Value=true).Value})

• Patch(): The Patch() function writes data back into a given Data source
  • Onboarding is the Data source in which we want to write the data
  • {ID:SharePointIntegration.SelectedListItemID} is the record we want to update. Please note that SharePointIntegration.SelectedListItemID is only available in the EditForm and the ViewForm, not in the NewForm. If you want to use this in the NewForm, you can use SharePointForm1.LastSubmit.ID to get the ID of the last submitted item.
  • {Tasks:Filter(Gallery1.AllItems,Checkbox2.Value=true).Value} is the field we want to update and the data we want to submit. In our case we want to write data back to the Tasks field. The data we want to submit is the value of each checked check-box so we need to use the Filter() function to filter out all checked values:
    • Gallery.AllItems is the source we want to perform our filter on
    • Checkbox2.Value=true is the formula which we want to use in our filter. Checkbox2 is the checkbox control we added earlier to our gallery. Since we only want checked items, we only want items which has a value of true.

When we Save and Publish our Form and try to edit an item in SharePoint, we can see that the selected values are saved into SharePoint:

Showing all handled tasks

Since the Gallery we added in the previous step is not integrated in the Form, the handled tasks will not be shown when you re-open your item:

To make sure it does, we need to configure the Default property of our check-box with the following condition:

If(Checkbox2.Text in LookUp(Onboarding,ID=SharePointIntegration.SelectedListItemID,Tasks).Value,true,false)

What does lookup does is checking if the Tasks field in our Onboarding list contains (using the in operator) the Text value of the check-box. If this is the case, the Default value of the check-box will be set to true, which is checked.

After adding some styling to the check-box, the final result looks like this: