Query an Infopath field, run a script and populate another field

DerekB — Tue, 14 Apr 2009 16:15:07 GMT

Hello,

Here's what I'm trying to do. I have my form automatically populating the requester's First Name, Last Name, Phone Number, E-Mail Address and user ID (I'm pulling this from the Sharepoint Authentication Service). The one thing I cannot do is to get the user's manager information, as this is not currently stored in my Active Directory environment. We do have a Novell E-Directory where we can get this information from (this uses LDAP). I had one of our scripters write a VBscript that can pull this information (just have to run it, it popus up a box to enter an ID then it gives you the Manager information), but I don't know how to get it to work within Infopath.

Basically, it should (hopefully work like this):

The user loads the form
The form populates the following fields: Requester_First_Name, Requester_Last_Name, Requester_Phone_Number, Requester_Email and Enterprise ID (this is working)
A script runs to take the Enterprise_ID and populates the Manager_EMail field

The script I have looks like this:

'Enable Strict Mode
Option Explicit

'Set Error Handling Options
On Error Resume Next
'On Error Goto 0

'Variable declarations
Dim strEdsUserName : strEdsUserName = "CN=xxxxxx,OU=Admins,OU=Services,O=BMGC"
Dim strEdsPassword : strEdsPassword = "xxxxxxx
Dim strEdsSearchBase: strEdsSearchBase = "<LDAP://eds-ldap-1.bmogc.net/O=BMGC>"
Dim strFilter, strAttributes, strQuery, strBase, strUserName
Dim objCommand, objConnection, objRecordSet
Dim strManagerWorkForceID, strGivenName, strSn, strMail

strUserName = Inputbox("Enter the user name to search.")

'Establish a connection to the directory service
Set objCommand = CreateObject("ADODB.Command")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Properties("User ID") = strEdsUserName
objConnection.Properties("Password") = strEdsPassword
objConnection.Properties("Encrypt Password") = False
objConnection.Properties("ADSI Flag") = 0
objConnection.Open "Active Directory Provider"
objCommand.ActiveConnection = objConnection

'Search #1
'Find out what the specified user's 'managerWorkForceID' attribute is
strFilter = "(cn=" & strUserName & ")"
strAttributes = "managerWorkForceID"
strQuery = strEdsSearchBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
Set objRecordSet = objCommand.Execute

'If the record set is empty, we didnt' find a user with the specified user name, so exit
If objRecordSet.EOF Then
WScript.Quit
End If

'Find the user's managerworkforceID
Do Until objRecordSet.EOF
        Dim strName
        For Each strName in objRecordSet.Fields("managerWorkForceID").Value
            strManagerWorkForceID = strName
        Next

objRecordSet.MoveNext
Loop

'If the managerWorkForceID is empty, we don't have any criteria to perform the next search on
'so exit
If strManagerWorkForceID = "" Then
WScript.Quit
End If

'Search #2
'Search for the user who has a 'workForceID' attribute equal to the 'managerworkForceID'
'found above, then pull that user's first name, last name and smtp address
strFilter = "(workForceID=" & strManagerWorkForceID & ")"
strAttributes = "givenName,sn,mail"
strQuery = strEdsSearchBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
Set objRecordSet = objCommand.Execute

Do Until objRecordSet.EOF
        Dim givenname, sn, mail
        For Each givenname in objRecordSet.Fields("givenName").Value
            strGivenName = givenname
        Next

        For Each sn in objRecordSet.Fields("sn").Value
            strSn = sn
        Next

        For Each mail in objRecordSet.Fields("mail").Value
            strMail = mail
        Next

objRecordSet.MoveNext
Loop

WScript.Echo strGivenName & " " & strSn & ", " & strMail

' Clean up.
objConnection.Close
Set objConnection   = Nothing
Set objCommand      = Nothing
Set objRecordSet    = Nothing

Code

Query an Infopath field, run a script and populate another field