none
Reading the LIST of Lookup Values for Enterprise Custom Field using VBA (2010) RRS feed

  • Question

  • Hello everybody,

    My goal is to read from VBA the _list_ of allowed values that a given Enterprise Custom Field may have (MS Proj Pro 2010).

    I'm making a UserForm that simplifies user input.

    I can read/write the C.E.F. successfuly with 

        ActiveProject.ProjectSummaryTask.GetField(FieldID:=FieldNameToFieldConstant(MY_FIELD_NAME)

        ActiveProject.ProjectSummaryTask.SetField(FieldID:=FieldNameToFieldConstant(MY_FIELD_NAME), Value:="VALUE")

    However I need to read WHICH VALUES are valid for a given EF so that I can build a combobox.

    I've researched this topic extensively and I'm almost starting to believe that VBA cannot do it in 2010.

    Can anyone help me prove me wrong on this?

    Thanks (or: please :-)

    Pedro

    Thursday, September 19, 2013 10:27 PM

Answers

  • I'm excited to share I've found what I was looking for!

    I'm posting it as it may help someone else.

    So I took a few steps back and read in detail all the labels in the "Custom Fields" dialog in MS Proj. I've digged in MSDN the terms that made sense to me, such as LookupTable and OutlineCodes. I was pushing the wrong button! The code below illustrates this.

    Sub TestGOC()
    'pfonseca Sep 2013
    'This Sub exhausts my Immediate Window. Run with caution (or dont run at all)
        
        Exit Sub ' safety plug
        For i = 1 To Application.GlobalOutlineCodes.Count
        Debug.Print "GOC" & "(" & i & ")" & " " & Application.GlobalOutlineCodes(i).Name
            For j = 1 To Application.GlobalOutlineCodes(i).LookupTable.Count
                Debug.Print "..." & "(" & j & ")" & " " & Application.GlobalOutlineCodes(i).LookupTable(j).Name
            Next j
        Next i
    
    End Sub
    

    Sub DumpListValues(pCEFName As String)
    'pfonseca Sep 2013
    
        For i = 1 To Application.GlobalOutlineCodes.Count
            If Application.GlobalOutlineCodes(i).Name = Trim(pCEFName) Then
                For j = 1 To Application.GlobalOutlineCodes(i).LookupTable.Count
                    Debug.Print "(" & j & ")" & " " & Application.GlobalOutlineCodes(i).LookupTable(j).Name
                Next j
            End If
        Next i
    
    End Sub

    Thank you John! (and others that may have spent cycles on this)

    Pedro

    • Marked as answer by Pedro.Fonseca Friday, September 20, 2013 12:04 PM
    Friday, September 20, 2013 12:04 PM

All replies

  • Pedro,

    I couldn't find a way to get the count value for a value list, but the following code should get you pointed in the right direction. You will need to modify it to call out the enterprise custom field you are using. For testing I just print the list values in the immediate window. You can write them to an array for inclusion in your combobox.

    Sub GetEntValLst()
    Dim t As Task
    Dim Val As String
    Dim i As Integer
    For i = 1 To 100
        On Error Resume Next
        Val = CustomFieldValueListGetItem(FieldID:=pjCustomTaskText1, Item:=pjValueListValue, Index:=i)
        If Err > 0 Then Exit For
        Debug.Print Val
    Next i

    End Sub

    Hope this helps.

    John

    Friday, September 20, 2013 2:01 AM
  • John, thank you for looking into this.

    I've tried CustomFieldValueListGetItem before too, but my finding is that enterprise custom fields cannot be read by this method. I may be doing something wrong as I always get "1101-The argument value is not valid."

    I've modified your code slightly in order to read a new LOCAL CF renamed as "test1" and based on "Text1".

    It works...but I'm really chasing ENTERPRISE CF lookup values. 

    The methods I posted in my question work fine even for ECF (getting and setting). I can read ECF by their name, which is very convenient. I cannot however get the list of the allowed values for an _E_CF.

    This is the code I'm using, based on yours:

    -- START Sub GetEntValLst() Dim t As Task Dim Val As String Dim i As Integer Dim fName As String fName = "test1" For i = 1 To 100 On Error Resume Next

    'Clng and Trim are here for testing, they should not be necessary Val = CustomFieldValueListGetItem(FieldID:=CLng(Trim(FieldNameToFieldConstant(fName, pjTask))), Item:=pjValueListValue, Index:=i) If Err > 0 Then Debug.Print Err.Number & "-" & Err.Description Exit For End If Debug.Print Val Next i End Sub -- END

    Thank you very much,

    Pedro


    • Edited by Pedro.Fonseca Friday, September 20, 2013 10:04 AM added a comment to the code
    Friday, September 20, 2013 10:02 AM
  • I'm excited to share I've found what I was looking for!

    I'm posting it as it may help someone else.

    So I took a few steps back and read in detail all the labels in the "Custom Fields" dialog in MS Proj. I've digged in MSDN the terms that made sense to me, such as LookupTable and OutlineCodes. I was pushing the wrong button! The code below illustrates this.

    Sub TestGOC()
    'pfonseca Sep 2013
    'This Sub exhausts my Immediate Window. Run with caution (or dont run at all)
        
        Exit Sub ' safety plug
        For i = 1 To Application.GlobalOutlineCodes.Count
        Debug.Print "GOC" & "(" & i & ")" & " " & Application.GlobalOutlineCodes(i).Name
            For j = 1 To Application.GlobalOutlineCodes(i).LookupTable.Count
                Debug.Print "..." & "(" & j & ")" & " " & Application.GlobalOutlineCodes(i).LookupTable(j).Name
            Next j
        Next i
    
    End Sub
    

    Sub DumpListValues(pCEFName As String)
    'pfonseca Sep 2013
    
        For i = 1 To Application.GlobalOutlineCodes.Count
            If Application.GlobalOutlineCodes(i).Name = Trim(pCEFName) Then
                For j = 1 To Application.GlobalOutlineCodes(i).LookupTable.Count
                    Debug.Print "(" & j & ")" & " " & Application.GlobalOutlineCodes(i).LookupTable(j).Name
                Next j
            End If
        Next i
    
    End Sub

    Thank you John! (and others that may have spent cycles on this)

    Pedro

    • Marked as answer by Pedro.Fonseca Friday, September 20, 2013 12:04 PM
    Friday, September 20, 2013 12:04 PM
  • Pedro,

    Okay, I'm glad you figured it out and thanks for sharing your results.

    John

    Friday, September 20, 2013 2:32 PM