none
Project Professional 2010 Cascading Lookups RRS feed

  • Question

  • Hello,

    I need to customize Project Professional 2010 in the following manner:

    We have a custom field called Team (Lookup) and another called Team Tasks (Lookup). When the user is creating a new task or editing an existing one in Project Professional 2010, once they select the Team, I have to filter the Team Tasks to only display the Tasks that belong to this Team. Then the user can select the Task name from the drop down.

    My question is whether it is possible or not to implement this cascading Lookup functionality in Project Professional 2010. If it is possible, I would appreciate directing me to any resources or tips that might be helpful.

    So far, using an add-in, I was able to capture the "Change" event for the first Lookup (Team) but I am unable to filter the second Lookup (Team Tasks).

    Thank you in advance.

    Thursday, January 31, 2013 12:30 PM

Answers

  • Hello Moezz.

    You can't filter a look-up list, but you can add/delete items from the list. However, if you delete items from the list, then any rows that use those values are set to blank. The solution below isn't ideal, but its the best I could think of at the moment.

    So you need two columns for look up you want to filter - an input column (which will contain the list of filtered values, dependent on the preceding column), and a persistent column (which will store the values long term, and allow them to be reinstated into the input column after selection).

    In the code below, I've used the following columns:

    1. text11 - This is the control column. The options available in text12 will be dependent on the value of this column
    2. text12 - This is the input column, where the user enters the value they want
    3. text13 - This is the persistent column, which stores all the values to enable them to be reinstated.

    There are two modules:

    1. FilterLookupValuesOnText12 - This should be run after you change the value on the first column (text11)
    2. ReinstateValuesOnText12 - This should be run after you change the value on the second (text12)

    Sub FilterLookupValuesOnText12(subj_Task As task)
    'Adds custom values to teh text12 lookup selection,
    'depending on the value of text11
    Dim x As Integer
    'clear out the existing look-up values
    On Error GoTo AllDeleted
        For x = 1 To 20 'Assumes there will be no more than 20 values in the list at any one time
            Debug.Print x & " - " & CustomFieldValueListGetItem(pjCustomTaskText12, pjValueListValue, 1)
            CustomFieldValueListDelete pjCustomTaskText12, 1
        Next x
        
    AllDeleted:
    On Error GoTo ErrorHandle
        Select Case subj_Task.Text11
        
            Case "Option 1"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1A"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1B"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1C"
                
            Case "Option 2"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2D"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2E"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2F"
                
            Case "Option 3"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3G"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3H"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3I"
        
        End Select
    Exit Sub
    ErrorHandle:
    MsgBox "An error has occurred.", vbCritical, "Look-up List Filtering"
    End Sub

    Sub ReinstateValuesOnText12(subj_Task As task)
    'reinstates all the look-up options and values to text 12
    Dim x As Integer
    Dim t As task
    subj_Task.Text13 = subj_Task.Text12
    On Error GoTo AllDeleted
        For x = 1 To 20 'Assumes there will be no more than 20 values in the list at any one time
            Debug.Print x & " - " & CustomFieldValueListGetItem(pjCustomTaskText12, pjValueListValue, 1)
            CustomFieldValueListDelete pjCustomTaskText12, 1
        Next x
        
    AllDeleted:
    On Error GoTo ErrorHandle
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1A"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1B"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1C"    
            
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2D"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2E"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2F"
            
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3G"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3H"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3I"
                
                CustomFieldValueListAdd pjCustomTaskText12, ""
    For Each t In activeProject.Tasks
        let t.Text12 = t.Text13
    Next t
    Exit Sub
    ErrorHandle:
    MsgBox "An error has occurred.", vbCritical, "Look-up List Filtering"
    End Sub

    The use case is that the user selects a value in text11, which then result in text12 look-up options being filtered (with the side effect of clearing values from text12 for other tasks).

    The user then selects a value for text12, and on exiting the screen, the full list of look-up options is reinstated, and all the values which were previously cleared from the text12 column are also reinstated.

    However, this comes with ugliness - it assumes that the user will populate text11 then immediately populate text12. If they get distracted, or don't know the value for text12, they will then appear to lose their values. Like I say, it's not ideal, and could end up tying users in knots.

    The much simpler way of doing it is to simply have one field, and concatenate the values for the two columns together in the permitted combinations to force users to only choose valid combinations. So for the example above, you'd have a single field with the following look-up values:

    • Option 1 - Sub-option 1A
    • Option 1 - Sub-option 1B
    • Option 1 - Sub-option 1C
    • Option 2 - Sub-option 2D
    • Option 2 - Sub-option 2E
    • Option 2 - Sub-option 2F
    • Option 3 - Sub-option 3G
    • Option 3 - Sub-option 3H
    • Option 3 - Sub-option 3I

    Uses fewer fields, and doesn't rely on VBA code which is a bit flaky. It's then impossible for a user to select an invalid combination of the two values.

    Hope this helps,
    Andrew



    • Edited by Andrew Simpson Friday, February 1, 2013 1:17 PM
    • Marked as answer by Moezz Sunday, February 3, 2013 12:46 PM
    Friday, February 1, 2013 1:16 PM
  • You mentioned an Add-in? If you are using .Net then why not display a Form in the Team change event and show only tasks for that team? Save the selected value in the Team task field.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Marked as answer by Moezz Sunday, February 3, 2013 12:46 PM
    Friday, February 1, 2013 8:24 PM
    Moderator

All replies

  • Hello Moezz.

    You can't filter a look-up list, but you can add/delete items from the list. However, if you delete items from the list, then any rows that use those values are set to blank. The solution below isn't ideal, but its the best I could think of at the moment.

    So you need two columns for look up you want to filter - an input column (which will contain the list of filtered values, dependent on the preceding column), and a persistent column (which will store the values long term, and allow them to be reinstated into the input column after selection).

    In the code below, I've used the following columns:

    1. text11 - This is the control column. The options available in text12 will be dependent on the value of this column
    2. text12 - This is the input column, where the user enters the value they want
    3. text13 - This is the persistent column, which stores all the values to enable them to be reinstated.

    There are two modules:

    1. FilterLookupValuesOnText12 - This should be run after you change the value on the first column (text11)
    2. ReinstateValuesOnText12 - This should be run after you change the value on the second (text12)

    Sub FilterLookupValuesOnText12(subj_Task As task)
    'Adds custom values to teh text12 lookup selection,
    'depending on the value of text11
    Dim x As Integer
    'clear out the existing look-up values
    On Error GoTo AllDeleted
        For x = 1 To 20 'Assumes there will be no more than 20 values in the list at any one time
            Debug.Print x & " - " & CustomFieldValueListGetItem(pjCustomTaskText12, pjValueListValue, 1)
            CustomFieldValueListDelete pjCustomTaskText12, 1
        Next x
        
    AllDeleted:
    On Error GoTo ErrorHandle
        Select Case subj_Task.Text11
        
            Case "Option 1"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1A"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1B"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1C"
                
            Case "Option 2"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2D"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2E"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2F"
                
            Case "Option 3"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3G"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3H"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3I"
        
        End Select
    Exit Sub
    ErrorHandle:
    MsgBox "An error has occurred.", vbCritical, "Look-up List Filtering"
    End Sub

    Sub ReinstateValuesOnText12(subj_Task As task)
    'reinstates all the look-up options and values to text 12
    Dim x As Integer
    Dim t As task
    subj_Task.Text13 = subj_Task.Text12
    On Error GoTo AllDeleted
        For x = 1 To 20 'Assumes there will be no more than 20 values in the list at any one time
            Debug.Print x & " - " & CustomFieldValueListGetItem(pjCustomTaskText12, pjValueListValue, 1)
            CustomFieldValueListDelete pjCustomTaskText12, 1
        Next x
        
    AllDeleted:
    On Error GoTo ErrorHandle
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1A"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1B"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 1C"    
            
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2D"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2E"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 2F"
            
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3G"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3H"
                CustomFieldValueListAdd pjCustomTaskText12, "Sub-option 3I"
                
                CustomFieldValueListAdd pjCustomTaskText12, ""
    For Each t In activeProject.Tasks
        let t.Text12 = t.Text13
    Next t
    Exit Sub
    ErrorHandle:
    MsgBox "An error has occurred.", vbCritical, "Look-up List Filtering"
    End Sub

    The use case is that the user selects a value in text11, which then result in text12 look-up options being filtered (with the side effect of clearing values from text12 for other tasks).

    The user then selects a value for text12, and on exiting the screen, the full list of look-up options is reinstated, and all the values which were previously cleared from the text12 column are also reinstated.

    However, this comes with ugliness - it assumes that the user will populate text11 then immediately populate text12. If they get distracted, or don't know the value for text12, they will then appear to lose their values. Like I say, it's not ideal, and could end up tying users in knots.

    The much simpler way of doing it is to simply have one field, and concatenate the values for the two columns together in the permitted combinations to force users to only choose valid combinations. So for the example above, you'd have a single field with the following look-up values:

    • Option 1 - Sub-option 1A
    • Option 1 - Sub-option 1B
    • Option 1 - Sub-option 1C
    • Option 2 - Sub-option 2D
    • Option 2 - Sub-option 2E
    • Option 2 - Sub-option 2F
    • Option 3 - Sub-option 3G
    • Option 3 - Sub-option 3H
    • Option 3 - Sub-option 3I

    Uses fewer fields, and doesn't rely on VBA code which is a bit flaky. It's then impossible for a user to select an invalid combination of the two values.

    Hope this helps,
    Andrew



    • Edited by Andrew Simpson Friday, February 1, 2013 1:17 PM
    • Marked as answer by Moezz Sunday, February 3, 2013 12:46 PM
    Friday, February 1, 2013 1:16 PM
  • You mentioned an Add-in? If you are using .Net then why not display a Form in the Team change event and show only tasks for that team? Save the selected value in the Team task field.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Marked as answer by Moezz Sunday, February 3, 2013 12:46 PM
    Friday, February 1, 2013 8:24 PM
    Moderator
  • Thank you Andrew and Rod for your suggestions. I really appreciate your help.

    Our company does not want the suggestion of one single list since the list might be very large.

    I will be trying the other two suggested solutions (clearing the lookup then repopulating and opening another form).

    Thank you again.

    Sunday, February 3, 2013 12:46 PM