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.