Migrating Users

October 12, 2010

The time may come when you need to transfer users from one SLX database to another.

In WG Admin, you can export some of the user fields into a tab-delimited file (i.e., go to the Users view, right-click, and select Export…).

You can also import users … from a .csv file with an entirely different column-format than the export. So you can’t simply export from one (source) database, and use the same file to import into the target.

That import file must have not only the correct format, but a specific name (which you cannot change, though you can move/copy it to a different location). You’ll find the blank version of it in C:\Documents and Settings\All Users\Application Data\SalesLogix\SaleslogixUserImport.CSV, and the column list here.

To get the complete set of importable user data out of the source database, for users on a particular Team, run the following query against the database in SSMS:

SELECT ISNULL(usr.UserName, ''), -- USERNAME
USERTYPE = CASE usy.Type
     WHEN 'A' THEN 'Add On'
     WHEN 'C' THEN 'Concurrent'
     WHEN 'M' THEN 'Remote'
     WHEN 'N' THEN 'Network'
     WHEN 'R' THEN 'Retired'
     WHEN 'T' THEN 'Web Only'
     WHEN 'V' THEN 'WebViewer'
     ELSE 'Not Recognized' END, -- USERTYPE
ISNULL(usy.UserTemplate, ''), -- USERTEMPLATE
ISNULL(usr.Prefix, ''), -- PREFIX
ISNULL(usr.FirstName, ''), -- FIRSTNAME
ISNULL(usr.MiddleName, ''), -- MIDDLENAME
ISNULL(usr.LastName, ''), -- LASTNAME
ISNULL(usr.Suffix, ''), -- SUFFIX
ISNULL(usr.Email, ''), -- EMAIL
ISNULL(usr.Title, ''), -- TITLE
ISNULL(usr.Region, ''), -- REGION
ISNULL(usr.Division, ''), -- DIVISION
ISNULL(usr.Department, ''), -- DEPARTMENT
ISNULL('ADMIN', ''), -- MANAGER
     -- ISNULL(usy.ManagerID, ''), -- MANAGER
ISNULL(usy.IsManager, ''), -- ISMANAGER
ISNULL(wrk.Address1, ''), -- WORKADDRESS1
ISNULL(wrk.Address2, ''), -- WORKADDRESS2
ISNULL(wrk.City, ''), -- WORKCITY
ISNULL(wrk.State, ''), -- WORKSTATE
ISNULL(wrk.PostalCode, ''), -- WORKPOSTALCODE
ISNULL(wrk.County, ''), -- WORKCOUNTY
ISNULL(wrk.Country, ''), -- WORKCOUNTRY
ISNULL(usr.Phone, ''), -- WORKPHONE
ISNULL(usr.Direct, ''), -- DIRECTPHONE
ISNULL(usr.Fax, ''), -- FAX
ISNULL(usr.Mobile, ''), -- MOBILEPHONE
ISNULL(usr.PagerTerm, ''), -- PAGERNUMBER
ISNULL(usr.PINNumber, ''), -- PAGERPIN
ISNULL(adr.Address1, ''), -- HOMEADDRESS1
ISNULL(adr.Address2, ''), -- HOMEADDRESS2
ISNULL(adr.City, ''), -- HOMECITY
ISNULL(adr.State, ''), -- HOMESTATE
ISNULL(adr.PostalCode, ''), -- HOMEPOSTALCODE
ISNULL(adr.County, ''), -- HOMECOUNTY
ISNULL(adr.Country, ''), -- HOMECOUNTRY
ISNULL(usr.Home, ''), -- HOMEPHONE
ISNULL(usr.AccountingUserID, ''), -- ACCOUNTINGID
ISNULL(pro.AssetID, ''), -- ASSETID
ISNULL(pro.AssetDesc, ''), -- ASSETDESC
HireDate = CASE WHEN pro.HireDate IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.HireDate)
END,
BirthDate = CASE WHEN pro.BirthDate IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.BirthDate)
END,
ISNULL(pro.Spouse, ''), -- SPOUSE
ISNULL(pro.SSN, ''), -- SSN
ISNULL(pro.EmployeeNumber, ''), -- EMPLOYEENUMBER
USERDATE1 = CASE WHEN pro.USERDATE1 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE1)
END,
USERDATE2 = CASE WHEN pro.USERDATE2 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE2)
END,
USERDATE3 = CASE WHEN pro.USERDATE3 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE3)
END,
USERDATE4 = CASE WHEN pro.USERDATE4 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE4)
END,
USERDATE5 = CASE WHEN pro.USERDATE5 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE5)
END,
ISNULL(pro.USERTEXT1, ''), -- USERTEXT1
ISNULL(pro.USERTEXT2, ''), -- USERTEXT2
ISNULL(pro.USERTEXT3, ''), -- USERTEXT3
ISNULL(pro.USERTEXT4, ''), -- USERTEXT4
ISNULL(pro.USERTEXT5, ''), -- USERTEXT5
USERNUM1 = CASE WHEN pro.USERNUM1 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM1)
END,
USERNUM2 = CASE WHEN pro.USERNUM2 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM2)
END,
USERNUM3 = CASE WHEN pro.USERNUM3 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM3)
END,
USERNUM4 = CASE WHEN pro.USERNUM4 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM4)
END,
USERNUM5 = CASE WHEN pro.USERNUM5 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM5)
END,
CONVERT(varchar(max), ISNULL(pro.NOTES, '')), -- NOTES
ISNULL(usr.WindowsSID, ''), -- WINDOWSSID (Don't import if the target
    -- database is on a different network than the source database)
ISNULL(usr.UserCompany, '') -- USERCOMPANY
FROM sysdba.UserInfo usr
LEFT OUTER JOIN sysdba.UserProfile pro
ON usr.UserID = pro.UserID
LEFT OUTER JOIN sysdba.UserSecurity usy
ON usr.UserID = usy.UserID
LEFT OUTER JOIN sysdba.SecRights rig -- for Team export
ON usr.UserID = rig.AccessID -- for Team export
LEFT OUTER JOIN sysdba.SecCode sec -- for Team export
ON rig.SecCodeID = sec.SecCodeID -- for Team export
LEFT OUTER JOIN sysdba.Address adr
ON usr.HomeAddressID = adr.AddressID
LEFT OUTER JOIN sysdba.Address wrk
ON usr.AddressID = wrk.AddressID
WHERE sec.SecCodeDesc = 'MyTeam' -- Team name,
     -- for Team export
AND usy.Type <> 'W' -- ADMIN user
AND usy.Type <> 'P' -- OTB Template (All Access, etc.) users

Right-click over the result set in SSMS, and Select All; then right-click again, and Copy.

Open the SaleslogixUserImport.CSV file, and Paste the result set into it, below the header row. Then Save the workbook in its original name and format.

Then, in WG Admin, go to Insert -> Import Users -> From CSV File…, select the SaleslogixUserImport.CSV, and import it.

You can’t import Retired users directly, so if you need to import those (for data integrity) the easiest way is to first import all of the non-retired users (in a single .CSV file). If you then create a second .CSV file with just the Retired users, but change all of their USERTYPE column values to “Network” (say), then any of those users who don’t have licenses available will automatically get converted into Retired ones by the import.

The above won’t bring the UserSecurity.ManagerID across (since that won’t be the same UserID in the target database as it was in the source). So unless you want to get a lot fancier (i.e., building a UserID lookup table, to transform those values), you’ll need to update that field manually in the target database.

If you want to get all of the users (not just those on a particular Team) out via the above SQL, just delete the SecRights and SecCodeID table joins, and the “MyTeam” WHERE-clause.

Also, if the target database is on a different network than the source, don’t import the WindowsSID field. That field gets populated when you click “Use Windows Authentication” for the User in WG Admin.

Advertisements

CustomSettings Table

October 9, 2010

If you need to check whether a given User is a member of a particular Team, Ryan Farley has provided a very useful IsMember function:

' sGroupType = "T" for team, and "D" for department
Function IsMember(ByVal sUser, ByVal sGroup, _
    ByVal sGroupType)

    Dim objRS

    If sGroupType = "T" Then sGroupType = "G"
    Set objRS = CreateObject("ADODB.Recordset")
    With objRS
         Set .ActiveConnection = _
             Application.GetNewConnection
         .Open "select accessid from secrights where " _
             & "seccodeid = " _
                   & "(select seccodeid from seccode where " _
                   & "(seccodedesc = '" & sGroup & "') " _
                   & "and (seccodetype = '" & sGroupType _
                   & "')) and (accessid = '" & sUser & "')"

         If Not (.EOF Or .BOF) Then
            IsMember = (.Fields(0).Value & "" = sUser)
         Else
             IsMember = False
         End If
         .Close
    End With

    Set objRS = Nothing

End Function

When using that function, you’ll typically hard-code the Team name in your scripts. But suppose that your client’s SLX administrator then changes that name—that would break your code.

You can eliminate that possibility by storing the Team name in the CustomSettings table, which is then visible in WG Admin, on the Tools > Options > Custom tab.

On that tab, click Add, and then enter the Category (“Teams”), Description (“My Sales Team”), Version (“1”), and Type (“Data”).

The Validation field on that tab takes a TABLE.FIELD string, which will cause the unique values for that field to be loaded into the Value dropdown. So if you want to validate against the existing Teams (and Users) in the database, enter SECCODE.SECCODEDESC for the Validation string, and then select the Team (e.g., “My Sales”) you want to validate against in your code, from the Value dropdown.

You’ll also need to build a simple function to retrieve that value from the CustomSettings table, based on its Category and Description values, to return the DataValue field value. That is, a wrapper around this:

strValue = GetField("DataValue", 
"CustomSettings", "Category = '" & strCategory _
& "' AND Description = '" & strDescription & "'")

With that implemented, if the client’s SLX administrator ever changes the team names (for whatever reason), you can just change the Value for this entry on the Custom tab, and your code will continue to work as it should.


Program XML/HTTP with VBScript

October 5, 2010

Program XML/HTTP with VBScript:

' amazon_price.vbs
' This VBScript prompts for an ASIN, and returns the price.
' Usage: double-click amazon_price.vbs, enter ASIN.

Sub AmazonQuery(ASIN_In)
  Dim SelectedText
  Dim MSXML
  Dim XMLURL
  Dim Loaded

  ' Make sure that some text is selected
  If ((Len(ASIN_In) = 0) Or (ASIN_In < " ")) Then
     WScript.Echo "Please enter an ASIN."
     Exit Sub
  End If

  ' Set Associate ID and Developer Token
  AffiliateTag = "insert associate tag"
  DeveloperToken = "insert developer token"

  ' Create an instance of the MSXML Parser
  Set MSXML = CreateObject("MSXML.DOMDocument")

  ' Set MSXML Options
  MSXML.Async = False
  MSXML.preserveWhiteSpace = False
  MSXML.validateOnParse = True
  MSXML.resolveExternals = False

  ' Form the request URL
  XMLURL = "http://xml.amazon.com/onca/xml3" + _
     "?t=" + AffiliateTag + _
     "&dev-t=" + DeveloperToken + _
     "&page=1" + _
     "&f=xml" + _
     "&mode=books" + _
     "&type=lite" + _
     "&AsinSearch=" + ASIN_In

  ' Issue the request and wait for the response
  Loaded = MSXML.Load(XMLURL)

  ' If the request is loaded successfully, continue
  If (Loaded) Then
     ' Look for the ErrorMsg tag
     Set XMLError = MSXML.SelectNodes("//ErrorMsg")

     ' If it exists, display the message and exit
     If XMLError.length > 0 Then
        WScript.Echo MSXML.SelectSingleNode("//ErrorMsg").text
        Exit Sub
     End If

     ' If there's no error, use XPath to get the product name
     ' and the price
     WScript.Echo "The price of " + _
       MSXML.SelectSingleNode("ProductInfo/Details/ProductName").text + _
          " is " + _
       MSXML.SelectSingleNode("ProductInfo/Details/OurPrice").text
  Else
     WScript.Echo "The service is not available."
  End If

End Sub

strASIN = InputBox("Please enter a product ASIN.")

AmazonQuery(strASIN)