Date Fields and SQL Views in SalesLogix

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.

Date Fields and SQL Views in SalesLogix

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.

Allen D'Souza's picture
Allen D'Souza
Senior CRM Consultant

Allen is a senior CRM consultant at Technology Advisors Inc. He spends most of his time designing solutions using InforCRM, StarfishETL, and SQLServer. He also specializes in data migrations from-and-to Microsoft Dynamics CRM and Allen has been involved in large implementations for prominent players in the real estate, telecom, manufacturing, and publishing industries. Prior to working at Technology Advisors, Allen worked as a Microsoft application developer in the Accounting space. Outside of work, he likes to meditate, play his guitar, and eat chocolate.

Related Articles

February 27, 2018

When a sales rep closes an opportunity, she should always indicate the reason for the lost sale in the CRM system. Why? Management can help teams fill gaps and ultimately improve win rates by properly addressing the underlying issues behind these lost sales. Let’s look at some best practices for tracking lost sales in your CRM.

December 18, 2015

I decided to try out the new Microsoft developer tools that come with Microsoft Visual Studio 2015 to get a feel for the software. When I downloaded the product, I received a web installer which in turn downloaded the necessary components and installed them. It was a somewhat slow install process. Once a good portion of the install completed, I was presented with the following error message: