How to use Custom Task Field value in Custom Resource Field Formula?

Question

I'm looking for a way to calculate the workload of resources using Microsoft Project. The workload needs to be calculated based on the urgency and complexity of the tasks assigned to the resource. I've managed to create custom Task Fields named Urgency and Complexity. I've also managed to create a custom Resource Fields named Workload. However, I couldn't find a way to use the value of Urgency and Complexity to calculate the value of Workload. Anyone here knows how I can solve this?

Answer 1

Sorry you are mixing fields and short of creating some code to do the math, you cannot use task and resource fields to calculate assignment fields (pairing of tasks and resources).

You may have luck posting to the Microsoft Forum for programming - but you need to have some skills in code development. The folks there will guide you - but it's unlikely they will write the entire code for you.

See: https://social.technet.microsoft.com/Forums/projectserver/en-US/home?forum=project2010custprog

If you post there, please explicitly state what version of Project (2013, 2010, 2007) you are using.

Answer 2

MS Project has the ability to level according to task priority.

1. Double click on a task to pull up the Task Information dialogue.
2. In the "General" tab, look for "Priority." The default setting is 500. Change this to reflect the relative priority of the task. It sounds like you'll need to devise a way for priority to encompass both urgency and complexity.
3. Go to the Resource tab in the ribbon.
4. Click on "Leveling Options." Leveling Options is listed towards the right above "Clear Leveling" and "Next Overallocation." This opens the Resource Leveling settings dialogue.
5. In the lower portion of the dialogue there is a "Resolving Overallactions" section.
6. Set the "leveling order" to "priority, standard." The default is standard. Priority, standard will level tasks based on priority first, then according to the standard process.

Answer 3

I finally solved my problem. Many thanks to @JulieS for pointing me to the Microsoft Project forum. One particular user in that forum is very helpful. Here's the thread that I created to ask this question: How to use Custom Task Field value in Custom Resource Field Formula?

Anyway, it turns out that you can't mix task fields, resource fields, and assignment fields in Microsoft Project. At least, not normally. To be able to do that, the only possible way is to use VBA. Here's a run down of the things I'm doing to solve my problem.

First of all, I added two new task fields named Urgency and Complexity. The value of Urgency is set to use lookup tables with 3 values: "1 - Normal", "2 - Medium", "3 - Important". I also set the value of Complexity to use lookup tables with 3 values: "1 - Low", "2 - Medium", "3 - High". The numbers in the front of each value is used for calculating the workload.

Now that both task fields are defined, I simply add this VBA code:

Sub Calculate_Workload()

Dim Reso As Resource
Dim Job As Task
Dim Task_As As Assignment
Dim Reso_As As Assignment

'Reset Workload to 0 (zero)
For Each Reso In ActiveProject.Resources
    Reso.Number1 = 0
Next Reso

'Calculate Workload
For Each Job In ActiveProject.Tasks
    If Not Job Is Nothing Then
        For Each Task_As In Job.Assignments
            Dim Task_Urgency As Integer
            Dim Task_Complexity As Integer
            Dim Task_Workload As Integer
            Task_Urgency = Left(Job.Text3, 1) 'Urgency
            Task_Complexity = Left(Job.Text4, 1) 'Complexity
            Task_Workload = ((Task_Urgency - 1) * 3) + Task_Complexity
            Task_As.Number1 = Task_Workload
            Set Reso = ActiveProject.Resources(Task_As.ResourceID)
            For Each Reso_As In Reso.Assignments
                If Reso_As.TaskID = Job.ID Then
                    Reso_As.Number1 = Task_As.Number1
                    Reso.Number1 = Reso.Number1 + Reso_As.Number1
                End If 'TaskID
            Next Reso_As
        Next Task_As
    End If 'Nothing
Next Job

End Sub

The code above basically does these steps:

1. Reset the workload for each resource (Reso.Number1).
2. Retrieve the value of Urgency (Text3) and Complexity (Text4), i.e. the number in front of the value, for each task.
3. Calculate the workload for each task based on the value of Urgency and Complexity.
4. Set the value of Workload (Number1) for task assignment (Task_As.Number1) and resource assignment (Reso_As.Number1) so that it can be shown in the Task Usage or Resource Usage view.
5. Sum all resource assignment workload to calculate the total amount of workload for each resource (Reso.Number1).

Now, every time there's a change to Urgency or Complexity, I simply run the VBA code and voila! I can now use the Resource Usage view to see the amount of workload for each assignment and the total amount of workload for each resource.

That's it. Hope this is useful.