Sharepoint Online 2013 - Retrieving a task by its related list item?

Question

For the ticketing system I've been working on, a task will be created via workflow whenever a new ticket (discussion) is created, which is assigned to the specific personnel assigned to that particular category of tickets.

Right now, we have a requirement to update the task's details when the original ticket is updated (i.e. when the ticket is "Closed" in the forum, the task will also be marked as completed). In other words, the workflow that triggers on ticket update will have to call the task which is tied to said ticket.

This information appears to be stored within the RelatedItems column within the Tasks list. As an example, running a GET call using the REST API to the following URI -

<site collection>/<site>/_api/web/lists/getbytitle('Tasks')/items?$select=RelatedItems

(Note: 'Accept' and 'Content-Type' set to 'application/json; odata=verbose')

yields the following results for RelatedItems:

{
    d: {
        results: [1]
            0:  {
                __metadata: {
                    id: "fd7c1b83-a0e5-4c19-851c-744df77b799c"
                    uri: "<site collection>/<site>/_api/Web/Lists(guid'6c2b2b63-b64d-46b9-a845-288e6ec8c746')/Items(39)"
                    etag: ""2""
                    type: "SP.Data.TasksListItem"
                }-
                RelatedItems: "[{"ItemId":5,"WebId":"1a6ccde0-95a3-4134-a787-4afb8a437fa5","ListId":"82410f3d-d778-4638-b7c6-f24d89fe84a4"}]"
            }
        }
}

This means that the task shown has a related item (the ticket (Discussion)) with ID=5, located in the list (category/Discussion Forum) with GUID=82410f3d-d778-4638-b7c6-f24d89fe84a4.

What I am trying to do now in the ticket update workflow is to pass said ticket's ID (and List GUID) as filter values to the Tasks list, which should return the task that was created by the ticket creation workflow for that ticket. However, I have not been able to filter the Tasks lists by RelatedItems using those values (the GET call simply throws an error that "The field 'RelatedItems' of type 'RelatedItems' cannot be used in the query filter expression.").

Is there a way to retrieve the task from the Tasks list based on the ID and List GUID of its related item (ticket)? I realize that one could query the entire Tasks list through the workflow and compare to each task's RelatedItems field until a match is found, but that is likely to be impractical when we have multiple active tickets (and tasks) in the system.

As before, we are mostly restricted to OOTB SharePoint Online 2013 and Sharepoint Designer 2013. Thanks in advance for your advice.

Answer 1

Another option would be to make the REST call using the Workflow Instance ID

[site]/_api/web/Lists/getbytitle('Tasks')/Items?$filter=(WF4InstanceId%20eq%20guid%277f02790a-4244-43c6-8d71-fdd272248cd5%27)

Answer 2

You can retrieve workflow tasks based on the WorkflowItemId which is the item the workflow is related to.

[site]/_api/web/lists/getbytitle('Workflow Tasks')/items?$select=*,WorkflowItemId&$filter=Status eq 'Not Started' and WorkflowItemId eq 25

add a filter for Status to restrict to currently running workflow.

Answer 3

So it turns out that I was able to find a workaround for my problem.

The first thing I did was to create a new site content type TicketTask, which was derived from the out-of-box Task content type (under the 'List Content Types' group). In other words, Task is the parent content type of TicketTask.

I then added two custom site columns to TicketTask, LinkedTicket and LinkedList, which are used to store the ticket's ID and a reference to it's parent list (e.g. list title). The content type is then associated to my Tasks list (via List Settings).

Within the ticket creation workflow, I used the "Create List Item" action to 'manually' construct the TicketTask in my Tasks list, passing the values for ticket ID and parent list title to the two custom columns.

Thus, every newly created ticket now has a TicketTask, with the reference to its original ticket in a much more easily accessible form. To get the task from the ticket, I can do the following:

<site collection>/<site>/_api/web/lists/getbytitle('Tasks')/items?$filter=(LinkedTicket eq <ticket's ID>)

And to get the ticket from the task, I can do this:

<site collection>/<site>/_api/web/lists/getbytitle('<LinkedList>')/items(<LinkedTicket>)

This works well enough for my situation, although it still feels a bit clunky. Hopefully, someone has a more elegant solution.