Tips and Tricks from R.J., the SalesLogix Consultant

SalesLogix CRM Web Development: PreSorting Data Grids

The SalesLogix Data Grid Control doesn't have a query builder or SortBy property available. So, how do you sort the data for the grid to create a more relevant display of information for the user?

Option 1: One Column Sort

Easy enough for the user to click on the column header. Here's how to do it programmatically:
Create a C# .Net Code Snippet and call this as an OnLoad action for the form.

namespace Sage.BusinessRules.CodeSnippets {

public static partial class MyFormNameEventHandlers
{ 

public static void SortPublicationsStep( IMyFormName form,  EventArgs args)
{

  System.Web.UI.WebControls.GridView grid = (System.Web.UI.WebControls.GridView)form.grdMyDataGridName.NativeControl;
SortDirection direction = SortDirection.Descending;
grid.Sort("MyFieldName", direction);

}

}

}

As always, case SenSiTiviTY is critical to the code.

Option 2: Two or more Column Sort

1. Create a Business Rule C# Code Snippet for the Entity that the form is displayed under (Account, Contact, et al.). In this case I wanted a two column sort on the Box Account Contacts form. First, sort on Status; second, sort on the Last Name field. Instead of going after the grid control, we must go after the data itself. Then return and bind the data to the grid, which will be displayed to the user correctly sorted.

#region Usings
using System;
using Sage.Entity.Interfaces;
using Sage.Form.Interfaces;
using Sage.SalesLogix.API;
using Sage.Platform;
using Sage.Platform.Repository;
#endregion Usings

namespace Sage.BusinessRules.CodeSnippets {

public static partial class AccountBusinessRules
{

public static void GetContactsSortedStep(IAccount account, out System.Collections.Generic.IList<Sage.Entity.Interfaces.IContact> result)

{

/Build Query for basic Account specific data.
IRepository<Sage.Entity.Interfaces.IContact> repository = EntityFactory.GetRepository<Sage.Entity.Interfaces.IContact>
/();
IQueryable qry = (IQueryable)repository;
IExpressionFactory ef = qry.GetExpressionFactory();
Sage.Platform.Repository.ICriteria criteria = qry.CreateCriteria();
// Contact's Account ID = Current Account's AccountID
criteria.Add(ef.Eq("Account.Id", account.Id.ToString()));
//Sort Order, two columns
criteria.AddOrder(ef.Asc("Status"));
criteria.AddOrder(ef.Asc("LastName"));
result = criteria.List<Sage.Entity.Interfaces.IContact>();

}

}

}

Note that the criteria.AddOrder method's are doing the sort, and they are Additive, so keep adding sort's as needed. ef.Desc works for Descing Sorts.

Back on the form, change the DataSource's GetBy method from GetByProperty My1ToManyRelationship to GetByMethod TheNameofTheBusinessRuleCreatedAbove.

You don't even need a load action to call this, as the datasource is automagically refreshed by SalesLogix CRM when you go to the Next Account. Or click on the tab and the data gets loaded into the grid.

There you go, SalesLogix Web Client Data Grid Sorting made easy.

 

Explanation and Script for SalesLogix CRM Associations

The SalesLogix Associations table was developed from Day 1 (15 years ago!) to hold one pair of relationships between either two contacts or two accounts. Many of you have tweaked this to hold more than one pair of relationships (e.g., "Your Mother could also be your Boss?!" or "An Ace Hardware member store might be owned by Ace Hardware itself?," etc.); and in some cases even Opportunity, et al. relationships (e.g., Master Project, Contract, Opportunity and related Projects, Contracts, or Deals).

To help clarify the fields in the Associations table I developed this SQL Query for you to run against your SalesLogix Associations table. Remember: the Associations tabs (or "Add Edit Screen") in SalesLogix dynamically switch who the "To" and "From" are on the fly, you should always look at the "To" records (as you are assumed to be on the "From" record).

“To Be, or not to Be” is how I remember this: To B:
To = B ack
From = Forward
TOID = BACKRELATION = BACKNOTE
FROMID = FORWARDRELATION = FORWARDNOTE

/*

SalesLogix Associations Table
ToID is the 'Parent', corresponds to the BACK relation and BACK notes.
FromID is the 'Member\Child', corresponds to the FORWARD relation and FORWARD notes.

TO: GM\General Motors is the Owner, World Headquarters for the Following Subsidiaries.
FROM: Chevrolet is the Division, Operating Subsidiary of GM.

*/

SELECT

TOID], AT.ACCOUNT as [To Account], [BACKRELATION]
[FROMID], AF.ACCOUNT as [From Account], [FORWARDRELATION]

FROM [sysdba].[ASSOCIATION] ASS
INNER JOIN sysdba.ACCOUNT AF ON ASS.FROMID = AF.ACCOUNTID
INNER JOIN sysdba.ACCOUNT AT ON ASS.TOID = AT.ACCOUNTID

Hopefully, this explanation and script will answer some of your questions regarding SalesLogix Associations. If you have further questions, contact me via the button below.

 

SalesLogix CRM Installation Reminders

Nine Killers to a Successful SalesLogix (SLX) Install

1. Install Media is not local to the machine. Network traffic, routers, and firewalls can raise havoc reading large install files across the Ether.
2. Logged in as anybody who is not a Local Administrator (it doesn’t matter if you are god of the domain admin, you must be a local to the machine admin).
3. Install the Network Client for Admin/Architect installs. I am pretty sure an Admin/Architect install is missing crucial pieces, like the SLX OLE DB Provider Client.
4. Install the Correct SQL Server Client for both the install machine (32 or 64 bit) and target SQL Server: 2005, 2008, 2008 R2. That’s six separate installs from Microsoft, so make sure you install the correct one.
5. If you are on a Windows 7 box, UAC must be turned off.
6. Turn off the antivirus and antispyware stuff.
7. Turn off other applications.
8. Install the latest Windows hotfixes and updates, restart the machine (did I mention you can’t install this on a Mac OS or Linux box?). IE 7 or better is suggested.
9. Old SLX applications should be uninstalled. Not required, but personally I feel better scrubbing the SalesLogix program files and removing all registry settings as well. Then restart the machine.

Your mileage will vary. I can still remember 2X CD's and getting bad reads off of the install disks.

Working in SalesLogix CRM on a Solid Platform

Data in SalesLogix CRM can change, sometimes leaving a user in a state of flux.

A Few Classic "Trouble" Areas

1. Account Ownership and visibility for New Accounts. You diligently enter in new account and contact information, even being nice to your admin and entering in a LeadSource, the correct Team, a complete Address, and the Mobile Phone for the contact. Then you click OK/Save and the account disappears! This started cropping up in my first SLX installation 15 years ago in February, 1997.
   
   What happened? The user filled in the correct Team/Owner for the new account, except and they (the user) weren’t a member of that team. So when they looked through Groups, Lookups, back arrow to the account, etc., it was never found. The punch-line? They entered in the account information again, creating a duplicate. Ouch.
    
2. My group’s "current" record changed! Or my group no longer has the same records in it?! If you have a field in the group Conditions or Sort tabs that will change as you work on the view, what did you expect? You have a group that’s based on Status = "Active" and you change the Status value to "Aardvark." When the group is refreshed, that record won’t be in the group anymore! Or you are sorting the group based on the ModifyDate field, when you change a field value and hit the Save icon. SalesLogix automagically changes the Modify User and Modify Date fields to the Current User, time-stamped to Now. The screen is refreshed and the group has changed, additionally, where you are in the group has changed.
    
3. The opportunity is "Closed," but now it isn’t? I’ve had users working on the same record at the same time as someone else (or even better, logged in as themselves in two different sessions of SalesLogix). Whatever is the last changed value will store to SalesLogix, so it’s best to keep everyone else off of the record - including yourself - that you are working on. From a workflow perspective, you may need to look at the database record to see if it has changed prior to saving the data. This requires some complex scripting to get rid of the concurrent access to a single SalesLogix record.

Bottom Line: Don’t change horses in the middle of a stream.

The Down and Dirty of SalesLogix CRM Picklist Lists

We often get requests for a list of values in the SalesLogix CRM Picklists. There are some neat Crystal Reports and dump-to-Excel-workbooks kinds of utilities out there, but here’s how to do it the old fashioned way: via SQL Statement and T-SQL.

SQL Statement (from R.J. Ledger):

--Query Picklist

Select p1.TEXT as PickListName,p2.TEXT as PickListText,
p1.USERID as PickListOwner,p2.userid as PickListRecordOwner,UI.USERNAME,p2.ITEMID
From sysdba.PICKLIST p1
Inner Join sysdba.PICKLIST p2 ON (p1.ITEMID = p2.PICKLISTID)
Left Join sysdba.UserInfo UI ON (p2.UserId = UI.USERID)
-- Where p2.userid <> 'ADMIN' -- restrict to User entries only (the bad ones!).
Group By p1.TEXT,p2.TEXT,p1.USERID,p2.userid,p2.ITEMID, UI.USERNAME
ORDER BY P1.TEXT, p2.TEXT
 
T-SQL (Stored Proc if needed)

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [sysdba].[usp_GetPicklistsByPicklist]
AS
BEGIN
--- run as SQL Only start HERE!
--- We need to make containers for the Cursor Info
DECLARE @PicklistID char(12), @PicklistName varchar(64)

DECLARE PKL_CURSOR Cursor FAST_FORWARD --- We need to name the cursor!
FOR – and run the SQL to populate the cursor.
Select ITEMID as PICKLISTID, TEXT
From PICKLIST
WHERE PICKLISTID = 'PICKLISTLIST' – did you know that’s the master picklist?
ORDER BY TEXT

OPEN PKL_CURSOR

Fetch NEXT FROM PKL_CURSOR INTO @PicklistID, @PicklistName
While @@FETCH_STATUS = 0
BEGIN –get the picklist values for each picklist.
SELECT @PicklistName as "PickList", ID, TEXT, SHORTTEXT
FROM sysdba.PICKLIST
WHERE PICKLISTID = @PicklistID
ORDER BY ID, TEXT

Fetch NEXT FROM PKL_CURSOR INTO @PicklistID, @PicklistName
END
--- housekeeping.
CLOSE PKL_CURSOR
DEALLOCATE PKL_CURSOR
--- run as SQL Only then END HERE!

END

I didn’t include the output from either statement as it’s several thousand lines long.
Try it, you’ll like it.

How to Fix SQL Server Connection Issues

Many SalesLogix LAN installers are having trouble connecting the Network Client to the SLX OLE DB Provider. Since SQL Server 2005 was released, any client access to SQL Server requires the SQL Server Native Client to be installed on the client machine. This harkens back to the SQL Server 7 days of the SQL Server Client install from the SQL Server SERVER CD (which warned you that you were trying to install a Server product on a Desktop machine before giving you the option of only installing the SQL Server Client).

For some reason, installers are not installing the SQL Server Native Client. Trust me, this is required. Period. Full Stop. Do not pass Go.

Depending on the client machine’s operating system - running in either 32 bit or 64 bit mode -you will download the x86 or 64 bit version of the Native Client, in turn based on which SQL Server database version you are accessing!

Here are the three flavors of the SQL Server Native Client:

1. SQL Server 2005 Native Client download page
2. SQL Server 2008 Native Client download page
3. SQL Server 2008 R2 Native Client download page

There is a huge difference between SQL Server 2008 and 2008 R2, so do not download the wrong version of the Native Client and do not mistake the server’s operating system for the SQL Server version being accessed!

All of this is in the SalesLogix Implementation Guide, so RTM (yea, like most of us I rarely do).

Happy connecting!

 

Write SalesLogix Tables Using External Applications

If you are writing to a SalesLogix database through the SLX OLE DB PROVIDER using an application (e.g., VB6, VB, VB .NET, C# .NET, et al.) and the results are showing up locally but not on the remotes, then you will need to look at any DateTime strings being updated or inserted through direct SQL.

Your update/insert SQL statement will successfully store to the local/main database, but will not sync to the SLX remotes. This is because the SLX OLE DB Provider rejects the SQL Update/Insert statement for the creation of a TEF file (the sync file) if the DateTime string is not correctly formatted.

The Quasi ISO Date string format for a direct SQL statement insert/update must be (that means nothing else will work - no exceptions, full stop, no sync, etc.):

"YYYYMMDD hh:mm:ss"

Today's date would look like:

'20120119 09:20:00'

This does not sync, but will update the local database, because the DateTime format will be wrong:

“UPDATE ACCOUNT SET USERFIELD1 = ‘AARDVARK’, MyDateTimeField = ‘” & NOW() & “’ WHERE ACCOUNTID = ‘A6UJ9A…..”

Bottom Line: it doesn’t matter how pretty and elegant your object oriented class structure is in your application, if the DateTime string format for a direct SQL statement does not follow the SLX Sync rules. Your statement will get rejected by the Sync Server, and the update/iInsert will not occur on your remote offices' and users' databases. Period.

Full Stop.

 

Standardizing SalesLogix CRM Contacts' Titles and Departments

Titles and Department names in your SalesLogix CRM system have to be spelled perfectly (as do Contact and Account names) for inclusion in correspondence and consistent searches. For example, how do you get a list of all the Finance or Science Departments? Or a list of Chief Financial Officers for a lead mining effort or business intelligence analysis?

The Title Department Field

Title: Store the exact title of the contact's business card and use it in all correspondences. Take off the Picklist on the Add Contact screen and the Contact Detail Screen.

TitleStandarized: Use this field to store Picklist exact spelling values for the Function that is represented by the title. Search on this field for reports, groups, queries, and business intelligence cubes.

Examples:
Banks hand out titles instead of raises. 3rd Vice President of Consumer Finance works out to Janitor in other organizations, so spell out the title perfectly. Store Financial Analyst in the Standardized Title.
Chief Financial Officer
Senior VP of Treasury and Finance Operations Comptroller work out to a Standardized title of CFO.

The Contact Department Field

Department: Spell out the actual department name to the letter.

DepartmentStandardized: Use this field to store Picklist exact spelling values for the Functional Department. Search on this field for reports, groups, queries, and business intelligence cubes.

Examples:
Spell out the "Mitchell Kuhlmann Department of Biological and Environmental Sciences" perfectly. Fill in Biology for the Standardized Department.
Accounting and Finance: Finance
Billing and Disputes: A/P or Accounts Payable Receivables and Collections: A/R of Accounts Receivable

Adjust: Groups, Lookups, Forms, EMail Templates, Indexes, Reports, Data Cubes, and SPEEDSEARCH Indexes, as needed.

By coming up with a standardized approach to a Contact's Title and Department, you can improve queries, reporting, analysis and customer relationships. Now that's a winner!

Ask the SalesLogix Guru about Account Names

SalesLogix User Question

Can you please help me clarify the Account table in SalesLogix? It currently shows more than four fields that contain the client name.

R.J., the SalesLogix Guru, Answer

ACCOUNT: This is the Company Name, which is also stored in the AccountSummary and Contact tables, as well as History and Activity records (AccountName field).

DESCRIPTION: Not used too often.

ACCOUNT_UC: Don't use. SalesLogix will store the Upper Case (UC) Account field value for searching.

AKA: "Also Known As." This is not used too often, and can be displayed as, "PKA," "DIP," "Nickname," and "Slang Name." I've often created an Account Alias table to handle all of the variants (E.g., Notre Dame, The University of Notre Dame, Fighting Irish, ND, Fightin' Irish, Golden Domers, etc.)

DIVISION: This is really the Division Of field (E.g., Account = Chevrolet Motor Car Division Division, (of) = General Motors). Some systems will store the Account.ParentID's Account field here.

Speaking of names, think about a Nickname field and a Phonetic Name field for your Contacts. For example:

Robert James Samp, Jr.
Nickname: RJ

How to Retire SalesLogix Users that have Subordinate Users

The SalesLogix Administrator program has a wonderfully cost effective feature for users that are no longer with the firm: Change the user license type to "Retired"!

The Notes History links stay intact (after all, it was that specific user who actually made the phone call or attended the meeting 7 years ago… right?). And retired users don’t cost you a license (or Annual Maintenance fees) - saving your firm the big bucks.

Most of you know that these users need to have Opportunities, Leads, Accounts, Activities, Tickets assigned to someone else, so the ball isn't dropped on managing Customer-centric tasks. This is SOP for any employee transition.

Other administrators have found out that if other users (subordinates) report to this retired user, then SalesLogix slaps you on the wrist and does not let you retire the user. SLX Business Rule: Retired users may not have anyone reporting to them! What’s worse is that subordinate user may actually be retired themselves, and it isn’t readily apparent who reported to this soon to be retired user\manager.

We’ve found that using SQL Queries through Microsoft SQL Server, SQL Query Analyzer, is a fast way to ferret out and correct the situation.

Here are the SQL statements to be run:

-- find a user and whom they report to
-- UserInfo table has the basic data for a specific user
-- their manager is in the UserSecurity table.
SELECT US.*, UI.* FROM sysdba.USERINFO UI
INNER JOIN sysdba.USERSECURITY US ON UI.USERID=US.USERID
WHERE
-- UserSecurity Type field can filter user types if needed
-- US.TYPE = 'R' AND
UI.LASTNAME LIKE 'My%'
ORDER BY DIVISION

Based on the above query we copy the UserID and their ManagerID from the query results.
-- UserID = U6UJ9A00002C
-- ManagerID = U6UJ9A00001U

-- Since we are retiring this user, let’s set the user’s manager to Admin.
-- Set the ManagerID to ADMIN, nice and safe.
UPDATE sysdba.USERSECURITY
SET MANAGERID = 'ADMIN'
WHERE USERID = 'U6UJ9A00002C' and MANAGERID = 'U6UJ9A00001U'

-- Now find user's that report to that user (where the user’s ManagerId = the UserId of this user):

SELECT US.*, UI.* FROM sysdba.USERINFO UI
INNER JOIN sysdba.USERSECURITY US ON UI.USERID=US.USERID
WHERE US.MANAGERID = 'U6UJ9A00002C'
ORDER BY DIVISION
 
-- now update all of these user’s
-- to report to Admin as that manager is going to be retired....
UPDATE sysdba.USERSECURITY
SET MANAGERID = 'ADMIN'
WHERE MANAGERID = 'U6UJ9A00002C'

Et voilà! Mission accomplished. RMB on the user in the SalesLogix Administrator and change user type to "Retired".