Normally when data is deleted from a database, it is simple enough to have a programmer write a DELETE statement in SQL to wipe out a bunch of records from a table. This technically works okay, but has some issues that one should be aware of.

SalesLogix maintains the schema of the database in its own system tables and uses this data to keep track of data structure and relationships. When data is removed using a straight database call, any child records to the records being deleted still remain in the database leading to orphaned data.

Even though this orphaned data may not be visible anywhere, from a data quality standpoint, this is not a good idea and eventually leads to useless records piling up in the database. Luckily, SalesLogix provides a purge data wizard that allows for purging a set of data and their child records - based on conditions that you might want to specify.

To purge data, launch the Tools --> Maintenance --> Purge Data Wizard

It will walk you through a series of steps that help you specify the exact data set that needs to be purged.

Depending on which record you are on, the wizard will automatically check the box that matches that record type. For example, if you launch the wizard from Contacts, the default Contacts button is checked as in the screenshot below:

The wizard will allow you to select one of the following methods of selecting the dataset for deletion:

• Based on the value in a single field
  • Allows for selection of a field, an operator and a value that will be used as a condition for the deletion.
• Based on the values in two fields
  • Same as above, but allows for 2 conditions to be created.
• Entire current lookup or group
  • If a group can be created using multiple conditions and joins, then this group can be used as the basis for the record deletion.
• Active record
  • Only the record that is currently being worked on will be deleted
• Currently selected records
  • This allows you to highlight a set of records in the group view and only have those highlighted records be deleted.

From experience, I have found it best to delete based on a group. This is because:

• The data in the group can easily be exported to Excel before deletion. This helps in case the list has to be passed around for review.
• The group can be shared with SalesLogix users so everyone can review it before it is deleted.
• The group lets you make sure you have the conditions right before you attempt a Delete.
• In most cases there are multiple conditions to be specified and the "group builder" comes in handy to build a more complex group based on a set of business rules.Considerations for Purging/Deleting Entity Data in SalesLogix

When a SalesLogix report is run, you normally see two different kinds of filter options/parameters. The purpose of this post is to explain what they are and how they are different.

Normally when a report is run, the first set of filters is a set of options that appear on the report selection page. It looks like this:

These options allow you to dynamically specify the data that should show up in the report. For example, if the report is a contact based report and needs to be filtered to a certain group, or a certain record that the user is currently working on, this is where you would do it. It also allows you to save these criteria if you would like to reuse them the next time the same report is run.

Note that these filters are externally passed into the Crystal report from SalesLogix and are not a part of the Crystal report parameters collection.

The second set of parameters you could run into are the actual Crystal report parameters that are built into the report:

These parameters are designed into the report and allow you to specify additional parameters like date ranges or other options that you may way to have a user specify to control what data is shown, or even how it is displayed.

These filters get applied in addition to the SalesLogix filters mentioned above. The combination of both sets of parameters is what gives you the final result in the report.

A key point to note here is that the SalesLogix filters are meant to allow for filtering data at the entity level so that one may filter by Entity group, Date or User. The crystal parameters usually complement these filters by allowing complex formula based filtering to be used to change display formats on the fly and reformat a report to work differently based on the parameters specified.

SalesLogix allows you to access your data via the new SData webservice feeds that are hosted as a part of the SalesLogix web client. Normally to enable your SData feed, all you have to do is check the "Generate Feed" option on the entity that you wish to create the feed for. The feed is normally not enabled by default, so make sure to turn it ON when you create your entity so that you can use it for external SData based data integrations.

If your "Path Name" field contains an invalid character, then a validation message will force you to correct this before saving. So if you use an underscore character like in the screenshot below, this is what the error will look like:

Recently we ran into an issue for a client who happened to use numbers in their extension tables. The extension table was named Account2. When the SData feed was created, interestingly, it does remove the number from the name, adds an "s" at the end, and calls the feed "accounts". Obviously the system doesn’t like this because the name is the same as the SData feed for the Account entity. So I renamed the feed to "AccountTwo", which was a perfectly valid name. This caused an error indicating that I have invalid characters in the SData feed even though I didn’t have any. 

On further examination I realized that the application was using the "Display Name" of the Entity to create the SData feed. Since the display name of the entity has the number 2 in it, it kept complaining about the invalid number. Changing the DisplayName temporarily to a different name like "AccountTwo" allowed me to create my SData feed. Then I could change the DisplayName back to what it previously was,  and things worked just fine.

We frequently get questions about the reporting functionality in SalesLogix. So here is some information that should give you a pretty good idea about how the reporting works and how it interfaces with the SalesLogix database.

SalesLogix actually uses the Crystal Reports software to design and output reports. So in terms of wanting to know the flexibility of the reporting software, just look at the functionality of Crystal Reports and that should tell you how easy/hard it is to design reports. 

The first step is obviously to create the report in Crystal Reports. In doing this, the most important thing to be aware of is to create an OLEDB connection to the SalesLogix application server.  Instead of creating a report to directly connect to the SQL or other back end database, you need to make sure that the SalesLogix OLEDB database driver is used. This causes the report to run in the context of every SalesLogix user and therefore causes the user's security to be enforced so that they only see what they are supposed to see. 

If you bypass this step and connect directly to the database, then you run the risk of having a user have access to all the data in the system. 

We recently ran into an issue in SalesLogix version 7.5 sp3 where the mail merge did not work after an install. Clicking on the write->email menu would bring up the following message:
 
The Following HTTP status error was reported in THTTPRequestThread.Execute: Retrieving the COM class factory for component with CLSID{1BBD3B34-C697-449A-AE6B-37A18D338C0F} failed due to the following error: 80040154
 
The issue was traced back to the mail merge dlls not being registered on the server even though the installation process seemingly went through successfully.
 
To resolve this, un-register and re-register the following DLLs
SLXMMGUI.dll SLXMMEngine.dll SLXMM.dll
 
If these files are missing, then you will need to find them from a working installation and just copy them over and register them.
 
Sometimes this issue also occurs if the following registry key is missing:
HKEY_CLASSES_ROOTCLSID{1BBD3B34-C697-449A-AE6B-37A18D338C0F}
 
You will need to export this registry key from a working installation and re-import it to the affected server.
 
Finally rebuild and redeploy the SalesLogix website to republish all changes to the website.

Recently we were tasked with importing data for a client who acquired a company that also used SalesLogix. There  were a couple of Account based tables that needed to be imported for historical reference ONLY.  
We were surprised to note that whoever originally did the SalesLogix implementation for the acquired company decided to create the table with a flat structure. We literally had 100-300 fields per table! 
 
Our thought was to be able to simply bundle up the table from the old environment and apply it to our production system. Eventually the data in these tables would be old enough and we could simply drop the tables from SalesLogix.
 
We decided to use our own  ETL tool,  Starfish, to import the data from the old system to the new. The thought of mapping several hundred fields was depressing. Luckily Starfish has an "auto map" feature that came in handy to automatically link all fields that had the same name. This saved a ton of effort in having to manually map fields as well as reduced any mistakes I could make with a screen full of fields to map.


For more details on the mapping options in Starfish, click on the following link to go to the starfish documentation site:
 
http://www.starfishetl.com/support-20/help-20/mapping/mapping-screen-overview

I recently ran into a client issue where several SQL views were built to support generation of some dashboard and reporting outputs. When retrieving this data, there was a need for filters based on the standard SalesLogix Opportunity EstimatedCloseDate field.
 
When testing it, it was observed that the date filter did not work correctly on the start date of the date range. So if an opportunity had a close date of '5/4/2011' it would only be pulled up in SalesLogix if the start date was '5/3/2011'.  
Our first thought was that GMT was being applied, and therefore we needed to verify if the EstimatedCloseDate field was properly defined as a standard date field and not a DateTime field.
 
A query of the SECTABLEDEFS table pointed out that the field was correctly specified as a "D" indicating that this is a Date ONLY field and no GMT formatting should apply.

 
So the next step was to use the SalesLogix profiler. The SalesLogix profiler executable is located in the program filesSalesLogix folder and shows you the actual query that is being processed by SalesLogix. We noticed that the profiler was reporting that a GMT adjustment is being made when the query is processed against the SQL view that retrieves the opportunity information.   
When we next checked the SECTABLEDEFS table for the definition of the SQLView, it turned out that the DATETIMETYPE was set to NULL by default when the View was created. This needed to be set to "D". Without this, the SalesLogix would assume that the field needs to have GMT applied to it. Changing the DateTimeType field to a "D" for the EstimatedCloseDate field used in the SQLView finally resolved the issue.  
Bottom line - make sure to set your Date fields to the appropriate type values  in SECTABLEDEFS table because SalesLogix does not automatically set them when they are initially created.

Every so often we come across a client who wishes to upgrade from a SalesLogix instance setup on SQL 2000. This discussion usually comes up when we start the process of analyzing hardware requirements for the upgrade. Here are a few key points that come up in order to help with understanding the issues you could potentially run into.

Server considerations:

SalesLogix 7.5.3 supports SQL 2005 & SQL 2008. Refer to the SalesLogix compatibility checklist for more details. Most of our clients are on SQL2005, which makes it easy to detach such a database and attach it to a SQL 2008 environment.  
If you are on an older version like SQL 2000, then you will have to attach it to a sql2005 instance which should upgrade it to SQL2005 format. Then repeat the same process to attach the database to SQL 2008.
 
If you don’t have a SQL 2005 environment, it is always possible to setup the free SQL2005 express edition and use that for the intermediate step. Keep in mind though that there is a 4GB database limit on the express edition. For larger databases, you will need a standard 2005 SQL Server edition.  
You could also use the SQL2008 migration tools for this, but you will need the SQL2000 environment running in parallel with the SQL 2008 environment so that the migration tools can properly analyze and migrate data to SQL2008.

Considerations for client computers:

If the implementation involves a sync server and remote users, then you will need to consider the SQL edition that will be installed on their computers:
 
The standard install files provided by SalesLogix  provide you with a SQL 2005 express edition installation which is convenient if the SQL server on the host is also on SQL 2005.  
If you plan to install SQL2008, then you will need to make sure you have a process to download and roll out SQL2008 express editions to users. The standard remote install looks for a SQL express instance called "SALESLOGIX". So to keep things consistent, it would be best to have the SQL2008 instance be setup with the same instance name.
 
On the client machines that run the windows/LAN client or remote clients, you will also need to get the appropriate SQLNative client drivers installed to match the SQL server that has been chosen.