Mary_l - I was able to get this done and working finally. It is a "round about" solution but it works. 1) We currently have a view in the Sharepoint db (_PROF)that gathers the necessary information that AD shares with this db, ie. Preferred Name, Department, Phone, etc.... this is used as a secondary data source in the form. 2) I then created a view in our Sharepoint database (_SITE) that joins Sites and UserInfo - and am using this as a secondary data source in InfoPath as well - I needed this view because capitalization was not consistent in the first view I mentioned. 3)I added 3 fields to my SQL db and converted the main data source to include these in the form - Domain, UserId, and DomainUserId. 4) Dropped the new fields as text boxes on the form and used conditional formatting to hide them when they are not blank. 5) I used the following jscript behind the form:
//******************************************************************
function XDocument::OnLoad(eventObj)
{
//Only load data if the fields are empty.
if (XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:PIRRequest/@RequestedBy").text == "" )
//Create a WScript.Network object, which provides access to user data
var objNetwork = new ActiveXObject("WScript.network");
//Retrieve the Domain & UserName and write into Domain & UserId fields
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:PIRRequest/@Domain").text =
objNetwork.UserDomain;
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:PIRRequest/@UserId").text =
objNetwork.UserName;
}
//******************************************************************
6) Created a rule in the UserId field to set the DomainUserId text field value - a value that concatenates Domain and User Id text field values (concat(@Domain, "\", .).
7) Created a rule in the DomainUserId field that set the value of the RequestedBy (where I wanted a full name) text field to - function - @tp_Title[@tp_Login = @DomainUserId]
@tp_Title (ex: Smith, Natalie) from the sharepoint view
@tp_Login (ex: Domain\nsmith) from the sharepoint view
@DomainUserId will have the concatenated version (Domain\nsmith)
8) Created rules in the DomainUserId field that set the values of RequestorDept and Phone text fields as well.
functions - @Dept[@PreferredName = @RequestedBy]
functions - @Phone[@PreferredName = @RequestedBy]
@Phone, @PreferredName, and @Dept from the other sharepoint view w/ AD information
Like I said "round about" way to the solution....but it was all I could get to work especially with my limited knowledge of coding and limited timeframe! Part of the process (like the concatenation part) could have been shortened if I could have included this vbscript:
*****************************************************************
Sub XDocument_OnLoad(eventObj)
Set oWshNet = CreateObject("WScript.Network")
sDomain = oWshNet.UserDomain
sUserName = oWshNet.UserName
Set objField1 = XDocument.DOM.selectSingleNode("/my:myFields/my:fieldName")
objField1.text = sDomain & "\" & sUserName
End Sub
****************************************************************
However, my form was already locked into jscript and I wasn't even trying to figure that one out!
If you need any clarification, let me know. Thanks.