Search code examples
vbams-projectproject-server

Reading the list of values of an Enterprise Custom Field using VBA

I'm trying to read the list of available items in an Enterprise Custom Field programatically, using MS Project 2010 in an EPM landscape. I'm customizing the Enterprise Global, thus I'm sticking to VBA. Some fields are text-fields, whereas some are bound to a Lookup Table (in PWA's Server Settings).

I can read each field's current value using: 

?ActiveProject.ProjectSummaryTask.GetField(FieldNameToFieldConstant("_MY_FIELD_NAME_"))

but I cannot get a list of available values that _MY_FIELD_NAME_ may have, case it is bound to a Lookup Table.

I have found several references to: 

Application.CustomFieldValueListGetItem(FieldID,Item,Index)

But none of them work. This is a rather common unanswered question being asked around.

I could not find a solution for this yet; has anyone (please) ?

Thank you for your time, any help is most welcome.

Solution

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

    I'm posting it as it may help somebody 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 MSDN about 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

    The function below dumps the allowed values per CEF received. It's a starting point for more elaborated use-cases:

    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

    The same issue as posted in MSDN.

    Pedro