Calculated Field from another Sharepoint List

Question

Relative newbie to Sharepoint Lists so please bear with me.

I have 2 Lists that I need to work together to provide a bit of help to my Service Desk colleagues.

List A (Tickets) FIELDS:

1. Ticket Number (TN00001 format),
2. Master Ticket (Tick box), Master
3. Ticket Number (TN00001 format),
4. Priority (Integer),
5. Time Spent (In Days),
6. Service Indicator (Integer - Priority x Days),

A Ticket in List A can be made a "Master Ticket" with Master Ticket field. Other tickets in the same list can be linked to a Master Ticket by inserting a Ticket Number (TN00001 format) into the Master Ticket Number field. Hope this makes sense.

List B (Problem Tickets) FIELDS:

1. Problem Ticket Number (PR00001 format),
2. Master Ticket Number (TN00001 format),
3. Combined Service Indicator (Integer)???

Problem Ticket = a combined record for tickets that recurr because we haven't resolved the root cause. Problem Tickets have a one-to-one

My goal here is to calculate the combined figure for the Service Indicator field for a problem ticket. e.g. If there are 5 tickets in List A, each with a service indicator of 20 then the Problem Ticket linked to the Master should have Combined Service Indicator of 100.

Is there any way to achieve this in Sharepoint's List settings page (without writing code)?

Answer 1

If I were you I would split up your Tickets list into two lists (Master tickets & Problem tickets). Then use a lookup from the problem list to the master list to create that relationship. That way it's not all in one record for each one and you have a central place to manage the master tickets.

For your Combined Service Indicator it could be a bit tricky. You could have a field in your master ticket list that you could change via SPD workflow.

By running a list workflow on the Problem ticket (child) list on creation of new items, you could check for a match between your lookup value and the value of the item in the Master ticket (parent) list. After you have that match you can run a calculation on the field in your master list to add that value to the Combined Service Indicator.

I say it could be tricky because you would really only want this to run on creation of a new item. Otherwise every time you edit the item it would run that calculation. Another problem comes up with this as well. When you complete your ticket in the problem list, it won't subtract that amount this way.

A way to get around this, would be to have a workflow run on creation and change of items and add a couple fields to use as flags in your workflow.