As you may know the “add to group” functionality is not currently implemented in the web client. There is a way to create a new ad-hoc group by selecting records from an existing group but no way to add records to an existing group (not to mention that the interface is a bit hard to use). In our case this was a crucial piece because the customer wanted to rely on the ability to add records to the “SyncSalesLogix” group to have them picked up by the Lotus Notes sync. Fortunately there is enough functionality in the API to build it ourselves. I created an “Add To Adhoc Group” smart part and uploaded it to the MSDN Code Gallery in case it is of interest to anyone else.

It should work with all entities for which the LookupView component works, though I only tested it with Accounts, Contacts and Opportunities. It is available as both source code and bundle-based installation and released under the open source Microsoft Public License (which is actually the only one available for MSDN Code Gallery).

One thing I should mention – it does not work very well for the Admin user. So make sure you test it out with a regular user.

You can achieve a substantial improvement of the performance of SlxWeb by making sure the static data gets cached, and the compressible data gets compressed. There are some instructions on how to do that in the Saleslogix documentation but unfortunately they are incomplete and inaccurate so here are a few steps you want to make sure you take:

• Obvious first step – make sure the <compilation> tag in web.config has debug=false (which it is by default, but we often turn it to true while developing). Leaving it to true will turn off some of the caching options.
• Enable compression in IIS – in addition to the steps outlined in the doc (right click on “Web Sites”, go to Service, check “Compress static files” and “Compress application files”), run the following:
  • Start a command prompt and go to \inetpub\adminscripts
  • To ensure aspx and axd (web resources) are compressed, and to ensure the DLL aren’t (which would mess up mail merge), run (on one line):cscript adsutil.vbs SET W3SVC/Filters/Compression/gzip/HcScriptFileExtensions asp aspx axd
  • Also run this one (same thing for the deflate algo): cscript adsutil.vbs SET W3SVC/Filters/Compression/deflate/HcScriptFileExtensions asp aspx axd
  • To ensure Javascript and css are compressed, run: cscript adsutil.vbs SET W3SVC/Filters/Compression/gzip/HcFileExtensions js css htm html txt
• Go to the properties of the “jscript”, “css” and “images” directory, go to Http Headers, turn on the content expiration
• Restart IIS. Test with fiddler or a network capture tool to make sure it is working.

I should mention that these apply to IIS 6 only. Thankfully I have only had to set up SlxWeb on one Windows 2000 server so far.

I admit it, I am a unit-testing junkie. I think it all come down to my immense laziness – I will go to great length to avoid the extra work of having to manually test my programs.

Until now the options for unit-testing in Saleslogix were very limited. I did go through the effort of writing a few automated tests in VBScript but for the most part it was not really justifiable – it is hard to maintain the separation of concern essential to unit testing and usually if I go to the trouble of forcing the concepts of encapsulation and polymorphism into vbscript I end up introducing more bugs than what I catch with unit testing.

Not so with the New and Improved Saleslogix. I want to demonstrate how unit testing in the Saleslogix web client can make our work faster, easier, and help us produce better quality code through a simple problem I faced today on the Insert Opportunity screen – the “OnCreate” opportunity rule would crash with a NullReferenceException. This of course has a very selfish goal – the more people we have excited about the benefits of automated unit testing in Saleslogix, the more Sage will be inclined to make their code testable (and maybe encourage them to use automated testing themselves!)

You could troubleshoot the issue by copying the rule to your own assembly, sprinkle it with logging statements, associate it in the architect, redeploying the web site, try the form again, then try and figure out where the error is. Once you have compiled the rule into your own assembly you can actually also open it in the Visual Studio debugger which will save a lot of time but you still have to go through those build, deploy, login, and trigger steps. I don’t know about your machine but it takes me about 1 1/2 minute to do a full build and deploy, then the debugger has to compile the site to open it which also takes 2 to 3 minutes, after which it still takes 30 to 50 seconds to log into the site and get to the right page. By that time my coffee is getting cold. And if you want to make a change to the rule and try again, you have to restart from the beginning… argh!

Enter unit testing. When unit testing you will still be loading the NHibernate configuration, dynamic methods etc, but you won’t have the overhead of the web server, javascript, painting the screens, or even rendering any output – you are cutting straight to the code you need to test.

So getting back to my problem. I can prepare a tiny method in my test class containing just one line of code: EntityFactory.Create(), right click it and run the test (in this picture it is shown as a call to a builder class, but this is just a wrapper around the EntityFactory):

Of course I had set up the dynamic method to call up my assembly (same thing that you would normally do through the AA interface but since we are just testing right now it is quicker to do it directly):

This fails (of course – as expected) and gives me the traceback I needed. OK, line 145! Let’s set a breakpoint and re-run it (note that you would have the same thing if you opened the web site in the debugger – this is just a LOT faster):

Oh well, the option must not be set, let’s set it in SQL and re-run the test (if you were testing on the live system you would have to restart the web server to get it to re-read the options at this point):

That’s right, 5.14 seconds! How long does it take you to restart IIS and relog into the web client? And perhaps the best part – I can now keep the test in the collection and it will automatically catch any similar problem in the 7.2.3 upgrade.

Hope this will convince some of the power in unit testing. You will want to check this post on how to set your environment up for unit testing: Unit Testing SLX – 7.2.2 Update. If you need any help send me an email or a comment.

In a previous post I examined how to get access the entity data (basically the ORM layer, as well as the dynamic methods piece) using the Saleslogix assemblies from outside of the web client. Obviously this is vital for unit testing, but also has some interesting application for external application. In this next installment I would like to look at the Groups API. In addition to being useful in unit testing and external application I feel the Groups API is poorly documented so a bit of exploring would help.

First off remember that most of the group access is done via a COM component called GroupTranslator. The goal of that component (which I presume was written in Delphi) is to translate the Group blob stored within the database (in the Plugin table) to an XML description and vice versa. It is not terribly reliable and Sage is notoriously slow about releasing fixes for it, but it is what it is. For the general cases it is probably still better than rolling out your own translator.

Next take a look (with Reflector) at the API offered in the 7.2 client. GroupInfo is the main one – it is full of useful static methods for manipulating the groups. Unfortunately they are not documented and some of them look very buggy so we have to thread carefully when dealing with it. It also has a few instance methods but watch out – it makes heavy use of globals so I would avoid messing too much with several groups at the same time. Another one we have to deal with is GroupContext – this has some information about the group that the web user is currently using (sadly it has a few pitfalls as we will see below). Very often when you use a method to retrieve the group’s data it seems to set the current group in GroupContext (as a global). So watch out for that. Sometimes you have to break down and examine the group’s XML yourself (as returned by the group translator) but I prefer to avoid that – my hope is that eventually the GroupInfo API will be fixed to be more reliable. Here are a few of my favorites:

• GroupInfo.GetGroupIdFromNameFamilyAndType – don’t you hate having to figuring that one out in SQL on the LAN client?
• GroupInfo.GetGroupInfo – static method to build a group info object, knowing the plugin id.

• GroupInfo.GetGroupDataReader – I looked at the code and I am pretty sure this won’t release the connection correctly, so I would stay away from that one for now (too bad, it sounds yummy, and does not have the global reliance of the next one)
• GroupInfo.GetGroupDataTable – almost as good as GetGroupDataReader, and does not have the connection problem. Only works when paging is enabled which can only be done using the last 2 overloads. Be careful if you use those because they will mess with the current (global) group context (not sure what the exact effect would be).
• GroupInfo.GetGroupKeyFieldIDs – not bad to get a group’s data. One of the rare data retrieval methods that doesn’t affect the current global group. Unfortunately this is currently broken so do not use it.
• GroupInfo.GetGroupIDs – I am not sure what the difference in purpose is with the previous one. But anyway, this uses the GetGroupDataReader method, so it will leak connection – do not use.
• GroupInfo.GetGroupList – gets you a list of groups for a specific entity. Watch out retrieving some of the properties like IsAdHoc – some are very very slow. So it will be easier to access the DB directly in most cases I think.
• GroupInfo.AddAdHocGroupMember (and AddAdHocGroupMembers) – to add to an adhoc group (works fine)
• GroupInfo.CreateAdhocGroup – create a new adhoc group (this works well)
• GroupInfo.AddLookupCondition – to add a condition to a dynamic group (didn’t try it but it looks OK)
• GroupInfo.SaveAsNewGroup – save group to database (should work fine)
• GroupInfo.getGroupSQL – this is a private method but I just had to mention it anyway. For example to retrieve the “where” part of the SQL:
  

  MethodInfo method = typeof(GroupInfo).GetMethod("getGroupSQL",
      BindingFlags.Instance | BindingFlags.NonPublic);
  String sql = (String)method.Invoke(currentGroup,
     new object[] { "WHERE", currentGroup.GroupXML, false, 1, 1, null });

  Unlike the GroupInfo.GroupSQL property, this one actually works. The first parameter (where I put “WHERE”) is the part of the SQL that you want to retrieve. You can use WHERE, FROM, SELECT, ORDERBY. WHERE seems to expand all parameters, even things like :UserID. The second parameter is whether you want to do paging or not. Usually false. The next 2 parameters are related to paging, but make sure you do NOT set them to 0. Last parameter is the column to sort by, but this is ignored unless you are using paging. If you use “ALL” as the SQL part, you will get the whole SQL for the group, but none of the parameters won’t be expanded.

• GroupInfo.GetGroupLayoutsNodes – you can use this to get the columns from the group.
• GroupInfo.WhereSQL – SQL condition for the group. Equivalent to getGroupSQL(”WHERE”). Works well.
• GroupInfo.FromSQL – the part after the FROM keyword. Equivalent to getGroupSQL(”FROM”). Works well.
• GroupInfo.GroupSQL – access the actual group SQL (same as in the LAN client). Doesn’t return the condition correctly (always returns it as “1=2″).

As a practical example here is an ugly little wrapper class with a working “GetGroupEntityIds” method:

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Sage.SalesLogix.Client.GroupBuilder;
using System.Xml;
using Sage.Platform.Orm;
using System.Data;

namespace SSSWorld.Slx72.Utility
{
    /// <summary>
    /// Helper methods for groups.
    /// </summary>
    public class GroupHelper
    {
        /// <summary>
        /// Return all entity ids on that group.
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public static String[] GetGroupEntityIds(String groupId)
        {
            String sql = GetGroupKeysSQL(groupId);
            using (SessionScopeWrapper session = new SessionScopeWrapper())
            {
                using (IDbCommand command = session.Connection.CreateCommand())
                {
                    command.CommandText = sql;
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        List<String> ids = new List<String>();
                        while (reader.Read())
                        {
                            ids.Add(reader.GetString(0));
                        }
                        return ids.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// Retrieve the full group SQL.
        /// </summary>
        /// <returns></returns>
        public static String GetGroupSQL(String groupId)
        {
            GroupInfo groupInfo = GroupInfo.GetGroupInfo(groupId);
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT ")
                .Append(GetGroupSQLPart(groupInfo, GroupSqlPart.SELECT));
            BuildGroupFromClause(sqlBuilder, groupInfo);
            return sqlBuilder.ToString();
        }

        /// <summary>
        /// Retrieve the SQL appropriate for reading the group entity ids.
        /// </summary>
        /// <returns></returns>
        public static String GetGroupKeysSQL(String groupId)
        {
            GroupInfo groupInfo = GroupInfo.GetGroupInfo(groupId);
            XmlNodeList layoutNodes = groupInfo.GetGroupLayoutNodes();
            XmlElement layoutNode = (XmlElement)layoutNodes[0].ParentNode;
            String mainTable = layoutNode.GetAttribute("maintable");
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT A1.")
                .Append(mainTable)
                .Append("ID ");
            BuildGroupFromClause(sqlBuilder, groupInfo);
            return sqlBuilder.ToString();
        }

        public static String GetGroupSQLPart(GroupInfo groupInfo, GroupSqlPart part)
        {
            MethodInfo getGroupSQL = typeof(GroupInfo).GetMethod("getGroupSQL", BindingFlags.Instance | BindingFlags.NonPublic);
            return (String)getGroupSQL.Invoke(groupInfo, new object[] { part.ToString(), groupInfo.GroupXML, false, 1, 1, null });
        }

        /// <summary>
        /// Which part of the SQL do you want to select
        /// </summary>
        public enum GroupSqlPart
        {
            /// <summary>
            /// After the WHERE (WHERE keyword not included)
            /// </summary>
            WHERE,
            /// <summary>
            /// After the ORDER BY (ORDER BY keyword not included)
            /// </summary>
            ORDERBY,
            /// <summary>
            /// After the SELECT (SELECT keyword not included)
            /// </summary>
            SELECT,
            /// <summary>
            /// After the FROM (FROM keyword not included)
            /// </summary>
            FROM
        }

        #region Private Methods

        /// <summary>
        /// Append the FROM and subsequent clauses to the SQL builder.
        /// </summary>
        /// <param name="sqlBuilder"></param>
        /// <param name="groupInfo"></param>
        private static void BuildGroupFromClause(StringBuilder sqlBuilder, GroupInfo groupInfo)
        {
            sqlBuilder.Append(" FROM ")
                .Append(groupInfo.FromSQL);
            String where = groupInfo.WhereSQL;
            if (!String.IsNullOrEmpty(where))
                sqlBuilder.Append(" WHERE ").Append(where);
            String orderBy = GetGroupSQLPart(groupInfo, GroupSqlPart.ORDERBY);
            if (!String.IsNullOrEmpty(orderBy))
                sqlBuilder.Append(" ORDER BY ").Append(orderBy);

        }

        #endregion
    }
}

Another problem with GroupInfo is that almost all of its methods will want to call GroupContext.GetGroupContext for one reason or another, and GetGroupContext is hard-wired to HttpContext, so not very testing-friendly. This can be fixed in IL though I have not bothered yet.

That’s it for now – this turned out to be a lot harder than I thought it would be. It was actually much harder than on the LAN client because of the poor (or rather, non-existent) documentation and the fact that most of the methods shipped do not actually work. I certainly do not want to turn this post into a rant, but I still have to mention how truly appalling that is. The good side of this coin is that we know the Saleslogix devs are hard at work on the next version and from what I have seen it will probably include a major overhaul of the group interface (and the API, presumably) which might explain why they are not focused on fixing this one. Next installment will be how to get to this stuff from outside of the web client but I thought this short overview of the API warranted a post by itself.

How to programmatically find if a smart part is present on a page (for example, if the behavior of one smart part depends on whether another smart part is loaded… in my case I wanted to show a button on my smart part ONLY if some other custom smart part had been added to the page):

• If the smart part you need to find is on any workspace but DialogWorkspace, you can use FindControl (or FindControlRecursive – google for the code). Remember the smart part will only show up if it is displayed in the particular mode (for example if it is a Detail mode smart part it won’t be anywhere in List mode)
• If the smart part is on the DialogWorkspace it is a bit trickier. If it is not currently displayed, it won’t be there as a control, but instead it will be in the private variable _mySmartParts of the DialogWorkspace.

So here is my FindSmartPart function:

/// <summary>
/// Find a smart part under the specified work item.
/// If the smart part is not on the page, return null.
/// </summary>
/// <param name="parent"></param>
/// <param name="smartPartId"></param>
/// <returns></returns>
private Control FindSmartPart(UIWorkItem parent, String smartPartId)
{
    foreach (var ws in parent.Workspaces)
    {
        if (ws.Value is DialogWorkspace)
        {
            // in this case peek in _mySmartParts
            FieldInfo field = typeof(DialogWorkspace).GetField("_mySmartParts", BindingFlags.Instance | BindingFlags.NonPublic);
            if (field == null)
                throw new InvalidOperationException("Field _mySmartParts not found in DialogWorkspace");
            Dictionary<object, ISmartPartInfo> smartParts = (Dictionary<object, ISmartPartInfo>)field.GetValue(ws.Value);
            if (smartParts != null)
            {
                foreach (object smartPart in smartParts.Keys)
                {
                    Control c = smartPart as Control;
                    if (c != null && c.ID == smartPartId)
                        return c;
                }
            }
        }

        foreach (var smartPart in ws.Value.SmartParts)
        {
            Control c = (Control)smartPart;
            if (c.ID == smartPartId)
                return c;
        }
    }
    return null;
}

Well, nobody said it would be pretty – but, it gets the job done.

As a follow up from Using Source Control with SLX 72, here is how Subversion allowed the upgrade from 7.2.1 to 7.2.2 without having to use the AA bundle installation process (well, having to use it only once):

1. Start with a “vanilla” 7.2.1 source tree, and create a branch for it.
2. Create a Vanilla-7.2.2 branch and apply all the 7.2.2 bundles for it.
3. Now use those 2 branches to upgrade your custom source tree:
   
4. Subversion will apply the differences and mark all problem files with a “conflict” symbol:. You need to review those manually to merge the changes. In most cases I just accepted the new version (the file marked as “right”)
5. This takes care of the “Model” part. For the actual portal source files (the web.config etc) I just redirect the portal source files to the “Vanilla” version.

I can’t really comment on whether or not this is easier than upgrading using the bundler because I was never able to successfully do that (except for the vanilla database)!! But at least it was faster.

Or, how to really get rid of “THTTPRequestThread” errors

Warning – the method described below is a last resort solution and should be used only for demonstration and development purposes – NOT ON PRODUCTION! You should first attempt to use the troubleshooting technique described here by Ryan Farley.

If all else fails, and you are in a time crunch, and slxwebmm.dll JUST WON’T CONNECT, then you may be tempted to squash it completely using the following technique. This is something you can use to disable the mail merge functionality without also disabling the rest of the ActiveX related functionality (e.g. exporting groups to Excel).

1. Download and install ISAPI Rewrite. There is a free “lite” version which does enough.
2. Open the ISAPI rewrite manager you just installed, click on Edit and add the following 2 lines:

   RewriteEngine On
   RewriteRule /mmserver/slxwebmm.dll /mmserver/slxwebmm.ashx

3. Configure your /mmserver virtual directory to enable ASP.NET (it probably is already).
4. Download this zip file and extract it to your /mmserver virtual directory, wherever it may be located.

What this fake slxwebmm handler does is simply regurgitate a canned request I stole from a working server. Obviously it is not going to do mail merges for you But it will prevent the THTTPRequestThread errors and let you do other things like export to Excel.

A follow up to my post, Unit Testing SLX. The post was written on Saleslogix 7.2.1 and there have been several very, very good improvements in the last service pack. So much in fact that unit testing and external access might actually be viable. Most importantly, transactions are now well supported, and inserts actually work. I have not tried on a production application yet but it should actually now be possible to use the SLX 7.2 libraries for external database access from our own components (eg integration services, external web services, etc).

The setup is the same as before, so please read the original posts for details on that. Here is an example test using a transaction:

using (IDBConnectionWrapper con = new DBConnectionWrapper())
{
    String histId = (String)con.GetField("top 1 historyid", "history", "1=1 order by newid()");
    IHistory history;
    using (TransactionScope tx = new TransactionScope())
    {
        history = EntityFactory.GetById<IHistory>(histId);
        history.UserDef1 = new Random().Next().ToString();
        history.Save();

        using (ISession sess = new SessionScopeWrapper(false))
        {
            IDbCommand cmd = sess.Connection.CreateCommand();
            cmd.CommandText = "SELECT USERDEF1 FROM HISTORY WHERE HISTORYID='" + histId + "'";
            sess.Transaction.Enlist(cmd);
            Assert.AreEqual(history.UserDef1, (String)cmd.ExecuteScalar());
            cmd.Dispose();
        }
        tx.VoteRollBack();
    }
    // make sure the transaction rolled back
    Assert.AreNotEqual(history.UserDef1,
        (String)con.GetField("USERDEF1", "HISTORY", "HISTORYID=?", histId));
}

In order to setup the test harness I have created a NUnit SetupFixture class. It has a Setup method which runs before every test of the project (actually before each test within the same namespace as the setup fixture, which works well for this case):

/// <summary>
/// A setup fixture helper.
/// </summary>
[SetUpFixture]
public class TestSetupBase
{

    /// <summary>
    /// Setup helper (this does the setup with the default files)
    /// </summary>        
    [SetUp]
    public virtual void Setup()
    {
        // setup using the default file
        this.Setup("dynamicMethods.xml");
    }

    public virtual void Setup(String methodsFile)
    {
        // ...
    }

    /// <summary>
    /// Teardown helper.
    /// </summary>
    [TearDown]
    public virtual void TearDown()
    {
       // ...
    }
}

If anyone is interested feel free to download it. You’ll have to adjust the references to point to the various Saleslogix assemblies, log4net, NUnit, and NHibernate. To reuse the library in a separate project I can create a TestSetup class in the same namespace as my tests, inheriting from the one in TestSupport (and optionally passing a different path to the dynamicMethods.xml file, since this should be the one from the site deployed by AA) and making sure to link to the Sage.Entity.Interface and Sage.SalesLogix.Entities from the deployed web site (so that they contain the custom entities). Also make sure that the app.config file defines a connection string named “ConnectionString”.

Next step – test the group builder side. This should be pretty interesting.

Here are a few more caveats with regards to dates in Saleslogix and DST – experiences from the switch last week-end.

The first versions of Saleslogix 6.2 (6.2.0 to 6.2.1, I think) were plagued with a bug which I call “Saleslogix DST paradox” – if you modified an account during the DST evening, the value saved in the database would appear to be in a different timezone than the one it was actually saved in. For example, if you save the account at 2008-03-08 23:00:00, the GMT value saved to the database (for CST we would be at GMT+6 in winter) would be 2008-03-09 05:00:00. At first glance this appears to be a summer date, thus CDT, thus GMT+5. Therefore Saleslogix reads it as 2008-03-09 00:00:00, which is the paradox. In those versions of Saleslogix this caused the account to be un-saveable. This is no longer the case, thankfully.

However, third party tools are still affected! Here is how you can find out:

• Pick an account in your database, and set its modifydate to 2008-03-10 05:01:00 via SQL: update account set modifydate = ‘2008-03-09 05:01:00′ where accountid = ‘AMAZCA10001J’ (replace with your favorite accountid obviously)
• Now in the Saleslogix Administrator, run: select modifydate from account where accountid = ‘AMAZCA10001J’. This will return a modifydate (for Central Time) of: 3/9/2008 12:01:00 AM.
• What is the GMT value of 3/9/2008 00:01:00? This is still CST (CDT only comes into effect at 2am), so GMT-6, so the GMT value is really 06:01:00. This is the first problem.
• Now try: select * from account where accountid=’AMAZCA10001J’ and modifydate=’20080309 00:01:00′. Nothing in the result? Oops?
• Now try: select modifydate, accountid from account where accountid =’AMAZCA10001J’ and modifydate=’20080308 23:01:00′. Anything odd? Yes, we got something different than what we were selecting.

How does Saleslogix do it?

The Saleslogix (network) client itself is not affected by the bug anymore. It turns out, they do the GMT conversion on the client side, instead of having the provider handle it! If you run the Saleslogix profiler you’ll notice they slide in a direct SQL statement (the ones that show in pale green) to retrieve the Modifydate. And to save it they use a timestamp parameter which apparently is not affected by the GMT conversion.

On the web client side, they just gave up on optimistic concurrency locking altogether and run a straight “update account where accountid=…”.

What does this mean for us?

When using the Saleslogix provider, we can’t really rely on the Modifydate as a timestamp value (for optimistic concurrency). But what alternative do we have?

• We could keep an integer timestamp. The problem is it wouldn’t be used or updated by the Saleslogix client itself. So it would really be rather pointless.
• We could do like Saleslogix and implement the conversion on the client side… sounds like a major headache, though.
• We could put some special handling for modifydate when it falls on a DST week-end (not sure how to slide that in)
• We could do like the web client and screw the optimistic concurrency. Not a great loss if you ask me, you usually want the last saved value to stick anyway – I think that is where I am going.