Jason Buss' Blog : SQL

Updating text within Textblob fields in SalesLogix

Jason Buss — Thu, 19 Apr 2012 15:16:00 GMT

I had a request to post on how you could replace characters within a Blob field in the SalesLogix database. Blob fields are database fields that hold any sort of binary information (such as text, images, audio, etc..), but SalesLogix uses them primarily for holding large amounts of text, since you don't have to define a field length. The Longnotes field in the History table is a good example.

Updating these types of fields is simple. You can perform a SQL update statement like you would on any other type of field in the database. The only caveat is that you need to perform a cast on the blob field so that it is evaluated as a VarChar, otherwise you will get an error regarding the Blob datatype.

The update will only be performed for the length you specify in your cast, therefore you'll need to cast it large enough to encompass the largest amount of data you may have in that field.

If you wanted to replace any instance of the "pipe" symbol ( | ) with the "at" symbol ( @ ) for example, you could perform the following update:

 update HISTORY set LONGNOTES = REPLACE(cast(LONGNOTES as varchar(5000)), '|', '@')

Note that I am casting the Longnotes field to a VarChar with a length of 5000. In most cases, that should be plenty of space to include all text, but your situation may require a larger cast.

This statement can be ran directly in the SQL Server Management Studio or Query Analyzer, or if you have a LAN system and need to consider synchronization, you can run this in the Execute SQL functionality under the Tools menu in the SalesLogix Workgroup Administrator. Since the data is being updated directly in the SQL database, this will work for both LAN and Web implementations.

Keep in mind that the History.Notes field contains the first 255 characters of the History.Longnotes field for display purposes, so you would want to perform the same update on both fields.

Thanks for reading!

Simple Insert Trigger Example

Jason Buss — Tue, 01 Mar 2011 19:01:00 GMT

I previously posted about using an update trigger to enforce data rules in SalesLogix. Since I was working on another trigger now, I also wanted to post a simple example of an Insert Trigger.

Basically, I have a table called TriggerTest containing a State and also three of Manager fields. When inserting a record for a particular state, I needed to also populate the Manager fields with the current managers defined for each state. I'm using a table called CRMManagers to store a list of each state and the three manager values for that state: (State, Manager1, Manager2, Manager3)

Here's how I created the trigger:

 --First, I create the trigger, naming it trUpdateManagers against the 
--TriggerTest Table.
Create Trigger dbo.trUpdateManagers on dbo.TriggerTest
--The trigger is created for Insert, which will fire whenever a row is 
--inserted to the TriggerTest table.
for Insert 
as 

--Now I Declare an ID and State variable and populate them with the new
--values inserted into the TriggerTest table.
Declare @ID nchar(10)
Declare @STATE nvarchar(50)
--When setting the ID and State variables, I'm referencing "Inserted" 
--which is an internal SQL table which basically contains a copy of 
--the currently inserted row.
select @ID = [ID], @STATE = STATE from inserted

--Next I Declare variables for each of the Manager Values
Declare @MGR1 nvarchar(50)
Declare @MGR2 nvarchar(50)
Declare @MGR3 nvarchar(50)

--And now set the Manager variables with the appropreate values 
--based on the State inserted to TriggerTest
Select 
    @MGR1 = [Manager1]
,    @MGR2 = [Manager2]
,    @MGR3 = [Manager3]

from dbo.CRMManagers where STATE = @STATE

--Finally, I run an update statement to udpate the newly inserted
--TriggerTest record with the Manager values.
update dbo.TriggerTest set Manager1 = @MGR1, 
Manager2 = @MGR2, Manager3 = @MGR3 where ID = @ID

That's about it. Pretty simple!

Running Sql Server Integration services package from a C Sharp application

Jason Buss — Wed, 19 Jan 2011 16:04:00 GMT

If you've ever created a SSIS package and wanted to provide a way for end users to run that package without having to run the Business Intelligence Development studio, you can compile the package into a dtsx file for them to run. This would require them to manually alter connections and variables, which can get complicated for an average end user. Alternatively, it is pretty simple to put together an application which can be used to run the package. This would allow you to handle modifications to package variables in a much more user-friendly manner.

I have a relatively simple SSIS package which queries data from a view based on a date range, and exports the returned data to a CSV file.

First of all, you will need to add a reference to the Microsoft.SQLServer.ManagedDTS.dll as well as a reference to the Microsoft.SqlServer.DTS.Runtime namespace. Once that is done, you can reference the namespace to create objects referencing the package, and any other items you need to reference such as connections and variables.

This is a simple method I created in Visual Studio 2010 which calls the provided compiled DTS package and updates the connections and variables. (see the attached ZIP file to see the full application)

 
using Microsoft.SqlServer.Dts.Runtime;

namespace CFX_ExportPortalEmailLeads
{
    public partial class frmExportData : Form
    {

        private void cmdRun_Click(object sender, EventArgs e)
        {
            Execute_Package(String.Format("{0:yyyy-MM-dd}", dtpStartDate.Value), String.Format("{0:yyyy-MM-dd}", dtpEndDate.Value));
        }

        private void Execute_Package(string StartDate, string EndDate)
        {
            string pkgLocation = txtUsePackage.Text;

            Package pkg;
            Microsoft.SqlServer.Dts.Runtime.Application app;
            DTSExecResult pkgResults;
            Variables vars;
            Connections conns;
            
            app = new Microsoft.SqlServer.Dts.Runtime.Application();
            pkg = app.LoadPackage(pkgLocation, null);

            //Set Connection Settings
            conns = pkg.Connections;
            conns["DestinationConnectionFlatFile"].ConnectionString = txtOutputFolder.Text + "\\ExportFile.csv";
            conns["SourceConnectionOLEDB"].ConnectionString = "Data Source=DataServer;User ID=sa;Password=1234567;Initial Catalog=DTSDatabase;Provider=SQLOLEDB;Auto Translate=false;";
            
            //Set SSIS Variables
            //**Note** - Variables MUST be package level variables in the SSIS package, otherwise an error will result when trying to set the variable  
            vars = pkg.Variables;
            vars["StartDate"].Value = StartDate;
            vars["EndDate"].Value = EndDate;
            
            pkgResults = pkg.Execute(null, vars, null, null, null);

            if (pkgResults == DTSExecResult.Success)
                MessageBox.Show("Package Ran Successfully");
            else
                MessageBox.Show("Package Failed");

        }
             
    }
}

As you can see, this method is pretty simple, but provides a much simpler user interface than the compiled SSIS package.

Thanks for reading!

Advanced Pivoting in MSSQL

Jason Buss — Thu, 06 Jan 2011 15:13:00 GMT

Using pivot queries in SQL is a great tool for rotating data, but what if you need to do something more complicated with multiple data sources and multiple columns? Recently, I had a request to pivot some data from multiple similar tables and to rotate that data into a columnar format.

I hadn't played much with pivot queries in MSSQL, so initially I thought I could accomplish it simply using that type of query. However, with the number of columns to include in the pivot, and the potential for an unknown number of columns, I found that I had to create a couple of stored procedures to process the data. Here's a simplified version of what I did:

I started out with three different tables Test1, Test2 and Test3. Each table contains the same number of fields:

PrimaryKey
ForeignKey
Description
Amount

The goal was to rotate and combine the values so that we would end up with a dataset containing a column for each Description and Amount row in each table. This becomes complicated because you have no way of knowing just how many columns you would eventually end up with.

As a first step, I created a view to join the data from all three tables:

 CREATE VIEW [dbo].[vUnionTestTables]
AS
SELECT     PrimaryKey, ForeignKey, 'Test1' AS FromTable, description, amount
FROM         dbo.Test1
UNION
SELECT     PrimaryKey, ForeignKey, 'Test2' AS FromTable, description, amount
FROM         dbo.Test2
UNION
SELECT     PrimaryKey, ForeignKey, 'Test3' AS FromTable, description, amount
FROM         dbo.Test3
GO

After the view is created, I created a stored Procedure that does that uses temporary tables to process the current data and stuff the data into additional tables created by the procedure:

 CREATE procedure [dbo].[sp_TablesSnapshot] @DataPart varchar(30) as
DECLARE @FromShort char(3)
If @DataPart = 'Test1' set @FromShort = 'T1'
else
If @DataPart = 'Test2' set @FromShort = 'T2'
else
If @DataPart = 'Test3' set @FromShort = 'T3'
declare @PivotTableX TABLE
(
    ID Int,
    RECORDID VARCHAR(30),
    FROMTABLE VARCHAR(50),
    DESCRIPTION NCHAR(100),
    AMOUNT NVARCHAR(4000),
    Processed NCHAR(1)
)
declare @RowCount Int
Set @RowCount = (Select Count(ForeignKey) from dbo.vUnionTestTables)
Declare @I INT
Set @I = 1
WHILE (@I <= @RowCount)
BEGIN
    DECLARE @PrimaryKey INT, @ForeignKey INT, @FromTable VARCHAR(3), @Description VARCHAR(100), @Amount NVARCHAR(4000)
    SELECT    @PrimaryKey = PrimaryKey
            , @ForeignKey = ForeignKey
            , @FromTable =
                Case FromTable
                WHEN 'Test1' Then 'T1'
                WHEN 'Test2' Then 'T2'
                WHEN 'Test3' Then 'T3'
                END
            , @Description = description
            , @Amount = Amount
            
    FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY ForeignKey) AS [RowNo] FROM dbo.vUnionTestTables) t1
    WHERE t1.RowNo = @I
    And
    FromTable = @DataPart
    Insert into @PivotTableX (ID, RECORDID, FROMTABLE, DESCRIPTION, AMOUNT, Processed)
    values (@PrimaryKey, @ForeignKey, @FromTable, @Description, @Amount, 'F')
    Set @I = @I + 1
END
declare @PivotTable TABLE
(
    ID Int,
    RECORDID VARCHAR(30),
    FROMTABLE VARCHAR(50),
    DESCRIPTION NCHAR(100),
    AMOUNT NVARCHAR(4000),
    Processed NCHAR(1)
)
insert into @PivotTable (ID,RECORDID,FROMTABLE,DESCRIPTION,AMOUNT,PROCESSED) select distinct * from @PivotTableX where ID is not null
Declare @MaxCount int
declare @CountTable table
(
    RecordID int,
    FromTable nchar(3),
    idCount int
)
insert into @CountTable
(RecordID,FromTable,idCount)
select RECORDID, FromTable, COUNT(ID) from @PivotTable
GROUP by RECORDID, FromTable order by RECORDID
Set @MaxCount = (select MAX(idcount) from @CountTable where FromTable = @FromShort and RecordID is not null)
Declare @ColumnsErString varchar(5000)
Declare @ColumnsMeString varchar(5000)
Declare @ColumnsQmrString varchar(5000)
Declare @BuildColumnsString varchar(5000)
Set @BuildColumnsString = 'CREATE table ' + rtrim(@FromShort) + 'Temp (' + ltrim(rtrim(@FromShort)) + '_ForeignKey varchar(30), '
Set @I = 1
WHILE (@I <= @MaxCount)
Begin
    If @I <> 1 set @BuildColumnsString = @BuildColumnsString + ', '
    Set @BuildColumnsString = @BuildColumnsString + rtrim(@FromShort) + 'Desc' + ltrim(str(@I)) + ' nchar(100), ' + rtrim(@FromShort) + 'Amt' + ltrim(str(@I)) + ' nvarchar(4000)'
    Set @I = @I + 1
End
Set @BuildColumnsString = @BuildColumnsString + ')'
Exec(@BuildColumnsString)
Declare @TableIDStep varchar(30), @RecIDStep varchar(30), @RecID varchar(30)
Declare @CurrentFrom varchar(3), @CurrentDesc nchar(100), @CurrentAmt nvarchar(4000)
Declare @InsertStr varchar(5000), @InsertStep int, @CurrentStep varchar(30)

Select @RecIDStep = min(ID) from @PivotTable where FROMTABLE = @FromShort
Set @TableIDStep = 1
Set @InsertStep = 1
while @TableIDStep <= @MaxCount
begin
    while @RecIDStep is not null
    Begin    
        select
            @RecID = RECORDID,
            @CurrentFrom = Fromtable,
            @CurrentDesc = replace(DESCRIPTION,char(39),char(39)+char(39)),
            @CurrentAmt = AMOUNT
        from @PivotTable where FromTable = @FromShort and ID = @RecIDStep
        
        If @CurrentStep <> @RecID Set @InsertStep = 1
        Set @CurrentStep = @RecID
        Declare @CheckInsertStr nvarchar(100)
        Declare @CheckInsertParam nvarchar(100)
        Declare @ReturnCount int
        select @CheckInsertStr = 'select @ReturnCount = count (*) from ' + ltrim(rtrim(@FromShort)) + 'Temp where ' + ltrim(rtrim(@FromShort)) + '_ForeignKey = ' + @RecID
        select @CheckInsertParam = '@ReturnCount int OUTPUT'
        exec sp_ExecuteSql @CheckInsertStr, @CheckInsertParam, @ReturnCount output
            
        If @ReturnCount = 0                         
            Set @InsertStr = 'insert into ' + @CurrentFrom + 'Temp (' + ltrim(rtrim(@FromShort)) + '_ForeignKey,' + @CurrentFrom + 'Desc' + ltrim(STR(@InsertStep)) + ',' + @CurrentFrom + 'Amt' + ltrim(STR(@InsertStep)) + ') values (' + CHAR(39) + @RecID + CHAR(39) + ',' + CHAR(39) + ltrim(rtrim(@CurrentDesc)) + CHAR(39) + ',' + char(39) + ltrim(rtrim(isnull(@CurrentAmt, 0.00))) + CHAR(39) + ')'
        Else
            Set @InsertStr = 'update ' + @CurrentFrom + 'Temp set ' + @CurrentFrom + 'Desc' + ltrim(STR(@InsertStep)) + ' = ' + CHAR(39) + ltrim(rtrim(@CurrentDesc)) + CHAR(39) + ',' + @CurrentFrom + 'Amt' + ltrim(STR(@InsertStep)) + ' = ' + CHAR(39) + ltrim(rtrim(@CurrentAmt)) + CHAR(39) + ' where ' + ltrim(rtrim(@FromShort)) + '_ForeignKey = ' + @RecID
    
        
        exec(@InsertStr)
        
        update @PivotTable set Processed = 'T' where ID = @RecIDStep
        Select @RecIDStep = MIN(ID) from @PivotTable where FROMTABLE = @CurrentFrom and ID <> @RecIDStep and Processed <> 'T'
        Set @InsertStep = @InsertStep + 1
    End
    Set @TableIDStep = @TableIDStep + 1
End

Finally, a second stored procedure is used to drop the temp tables if they already exist, and fill them with values derived from the previous stored procedure:

 CREATE procedure [dbo].[sp_BuildRotatedTempData] as

DECLARE @RC int
DECLARE @DataPart varchar(30)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T1Temp]') AND type in (N'U'))
DROP TABLE [dbo].[T1Temp]
EXECUTE @RC = [dbo].[sp_TablesSnapshot]
   'Test1'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T2Temp]') AND type in (N'U'))
DROP TABLE [dbo].[T2Temp]
EXECUTE @RC = [dbo].[sp_TablesSnapshot]
   'Test2'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T3Temp]') AND type in (N'U'))
DROP TABLE [dbo].[T3Temp]
EXECUTE @RC = [dbo].[sp_TablesSnapshot]
   'Test3'

Now it's just a matter of running the second procedure each time the data changes to get a current snapshot of the data in the current format. It is then a simple matter of creating a view joining the each of the newly created temp tables to their parent table to return everything in a columnar format.

I hope you find this helpful. I've attached the scripts to create the Stored Procedures so that you can modify them for your own purposes.

Thanks for reading!

Building a query-based lookup in SalesLogix Lan / Using SLX_Common to build query strings

Jason Buss — Mon, 20 Sep 2010 16:12:00 GMT

I've previously posed about a number of useful functions available to you within the SLX_Common script in the LAN version of SalesLogix. I'd like to go a little further in-depth about a couple of functions which are helpful for creating new SQL Query strings.

In a recent project, I needed to build a query interface similar to what you find in standard opportunity products view. The SLX_Common script holds a couple of functions that I used in building this interface. In this posting, I'm going to go through building a simple account lookup view using this functionality.

Before I get to any code, we need to build a view containing our search fields and a grid to hold the search results. This view will not be bound in anyway, so I'm going to create this as a manage view.

First of all, I'm going to get rid of the right panel and the associated buttons. In addition, I'm going to add a datagrid as well as three textboxes to hold search values, and three combo boxes to hold operators for each field. For the purposes of this post, I'll use the Account, Type and Status fields as the lookup fields. Finally, I've added a Search button.

For Type and Status, I actually set them up as picklists, with their corresponding lists attached. The comboboxes have had their Items property populated with standard operator values (Starts With, Contains, Equal To, Not Equal To, Less Than or Equal To, Greater Than or Equal To, Less Than, Greater Than). While some of these operators don't really make sense for a Alpha-numeric comparison, I've included them in this example. The available operators and their order are important, which you will see a little later.

For the Grid itself, we need to include some columns. Since this grid will not actually be bound, it doesn't really matter which fields we use, but we do need to provide fields so that we can apply formatting for fields that would need it, such as dates, boolean values, phone numbers, etc... I've used the userfield 1-6 fields in the account table, setting the caption property of each column to what I would like to display in the datagrid, along with any necessary formatting.

As you can see, the accountid field is included as well. I don't want to actually show this field, so it will be necessary to go into the columns collection for the grid and make that column invisible. While I am in there, I'm also going to set the other columns to read-only, as well as disabling the editor.

Now that our view is set up, we can start adding in come code to make this lookup work!

The first bit of code I'm adding is to define a global variable for each search parameter, which will tell us which fields to include in the search. To populate these global varables, I created a subroutine call ApplyFilterOption which I attach to the OnExit event of each of the parameter fields:

  option explicit
Dim bIncludeAccount
Dim bIncludeType
Dim bIncludeStats
Sub ApplyFilterOption(Sender)
    bIncludeAccount = (Trim(txtAccount.Text) <> "")
    bIncludeType = (Trim(pklType.Text) <> "")
    bIncludeStatus = (Trim(pklStatus.Text) <> "")
End Sub

With this code, if a user enters a value in "Account", for example, we'll know to include "Account" in the where clause of our SQL Statement.

Now we're ready to do the actual query. I've added another couple of functions, to build the actual query string that will search the database. First, I created a function called SearchAccount. This function is simply a string consisting of a SQL Statement for querying the Account table.

Function SearchAccount
    Dim sSql
    sSql = "SELECT ACCOUNTID AS USERFIELD1, ACCOUNT AND USERFIELD2, " & _
           "TYPE AS USERFIELD3, STATUS AS USERFIELD4, " & _
           "MAINPHONE AS USERFIELD5, WEBADDRESS AS USERFIELD6 " & BuildFilter
End Function

As you can see, there is no where clause yet. This will be built by another function I added called BuildFilter.

Function BuildFilter
    Dim sWhere
    sWhere = ""
    If bIncludeAccount Then
        sWhere = AddToWhereClause("ACCOUNT", GetSQLConditionClause(cmbAccount.ItemIndex, txtAccount.Text), sWhere)
    End If
    If bIncludeType Then
        sWhere = AddToWhereClause("TYPE", GetSqlConditionClause(cmbType.ItemIndex, pklType.Text), sWhere)
    End If
    If bInlcudeStatus Then
        sWhere = AddToWhereClause("STATUS", GetSqlConditionClause(cmbStatus.ItemIndex, pklStatus.Text), sWhere)
    End If
    BuildFilter = sWhere
End Function

In the BuildFilter Function, I am using two different functions from the SLX_Common Script; AddToWhereClause and GetSqlConditionClause. The GetSqlCondition function takes two parameters; The index from the combo box (this is why the order of the operators within the items property of the combobox control is so important) and the value to search for. This is called from within the AddToWhereClause function to append any search parameters from my view into the SQL statement I built.

All we need to do now is set the SQL property of the results grid to the results of the SearchAccount function. Since we aliased the returned values in that statement with the same fields we used to define the grid, any formatting we set in the grid will carry forward.

Sub cmdSearchClick(Sender)
    dgSearchResults.SQL = SearchAccount
    dgSearchResults.Refresh
End Sub

We should now be getting a result set in our grid. The final step will be to navigate to whichever record from the results the user double-clicks, and to close the search view. This can be done very easily in a doubleclick event on the grid:

Sub dgSearchResultsDblClick(Sender)
    Application.BasicFunctions.ShowDetails "Account", dgSearchResults.GetCurrentField("UserField1")
    frmTestAccountLookup.ModalResult = mrOK
End Sub

So There we go! We've just created a simple Account lookup view using a Manage view, and functionality from the SLX_Common script.'

I hope you found this informative. There's a lot more we could do with this view. Right-click functionality would be nice to have, and you'll probably need to disable the navigation functionality if there are no results returned. If you are implementing functionality similar to this, I'd encourage you to play with my code an try to figure out what else you can do to make this more complete of a solution. Thanks for reading!

Useful Functions from the SLX_Common script (SalesLogix LAN)

Jason Buss — Mon, 19 Jul 2010 11:58:00 GMT

The SLX_Common script included in SalesLogix contains a number of simple, yet useful, functions that you can use within your own functionality to save yourself some time by not having to reinvent the wheel each time you need some simple functionality.

GetItemIndex(Control, Value)

This function returns the index for an item within a control such as a ComboBox, CheckListBox, ListView, ect. Basically, any control with an "Items" property.

SLXIsNumeric(Value), NulltoZero(Value), CheckForNull(Value), CheckForNullDate(Value)

This series of functions can be used to evaluate a number of different datatype for null or blank values.

There are a number of functions to provide some basic calulations commonly performed against currency values:

CalculateAdjustedPrice(Price, Discount)
CalculateDiscountPercentage(AdjustedPrice, PriceBase)
CalculateExtendedPrice(AdjustedPrice, ExchangeRate)
CalculateLocalPrice(AdjustedPrice, ExchangeRate)
CalculateExtendedPriceLocal(Quantity, PriceLocal)
ExtendedPricetotal(objRS)

ExtendedPriceTotal will total up all records from the passed recordset, where the fieldname is "Extended")

There are also a couple of scripts to help with SQL statements:

GetSQLConditionClause(Index, strFltText), GetSLXConditionClauseEx(Index, strFltText, strField)

The Index number passed corresponds with the type of query to run... (0 = Like with wildcards, 1 = Like, 2 = Equals To, 3 = Not Equal To, etc...)

AddToWhereClause(Field, Clause, Where), AddToWhereClauseEx(Field, Clause, Where, Condition)
AddCommaDelimatedTextToWhereClause(intCauseIndex, strWhere, strText, strField)

etc...

This is just a short list of some of the functionality available in this particular script. Other useful functionality is available in this, as well as many other scripts which are included within SalesLogix. In addition, if you're relatively new to VB scripting, these scripts provide some invaluable examples for how Subroutines and Functions can be built within the SalesLogix client.

Jason

SQL Function for parsing name values

Jason Buss — Tue, 25 May 2010 15:13:00 GMT

In a recent project, I had to create a SQL Server Integration Services package to basically copy contact information (which was being stored in the Account.Account field) from the Account table into the Contact table. Since the Contact name was being stored in it's entirety within the account field, I needed a way to parse the Contact information to populate the individual contact name fields in the contact table.

I created a scalar function called ParseName to accomplish this: (I've attached a zip containing the .sql file as well)

 CREATE function [dbo].[ParseName](@NameString varchar(100), @NameFormat varchar(20))
returns varchar(100) as
begin

--ParseName parses a Name String into its component parts 
--and returns it as requested.
--This function assumes the data source maintains a standard format of 
--'Prefix Firstname Middlename Lastname Suffix'
--
--@NameString is the raw value to be parsed.
--@NameFormat is a string that defines the output format.  Each letter in the string
--represents a component of the name in the order that it is to be returned.

--    P = Full prefix
--    p = Abbreviated prefix
--    F = First name
--    f = First initial
--    M = Middle name
--    m = Middle initial
--    L = Last name
--    l = Last initial
--    S = Full suffix
--    s = Abbreviated suffix
--    . = Period
--    , = Comma
--    [ ] = Space

--Example1: select dbo.Parsename('Doctor Bobo Lee Brown Esquire', 'L, p. F m. s.')
--Result: 'Brown, Dr. Bobo L. Esq.'

--Example2:    (Full contact name stored in SLX Account table)
--                select 
--                    dbo.Parsename(ACCOUNT, 'F') as FIRSTNAME
--                ,    dbo.Parsename(ACCOUNT, 'L') as LASTNAME
--                from ACCOUNT
--
--Returns:      FIRSTNAME, LASTNAME
--                --------------------
--                'Bobo', 'Brown'

Declare    @Prefix varchar(20)
Declare    @FirstName varchar(20)
Declare    @MiddleName varchar(30)
Declare    @LastName varchar(30)
Declare    @Suffix varchar(20)
Declare    @TempString varchar(100)

--String Prep - We want to remove any periods, double spaces and commas
set    @NameString = rtrim(ltrim(replace(@NameString, '.', '. ')))
while  charindex('  ', @NameString) > 0 set @NameString = replace(@NameString, '  ', ' ')
while  charindex('.', @NameString) > 0 set @NameString = replace(@NameString, '.', '')
while  charindex(',', @NameString) > 0 set @NameString = replace(@NameString, ',', '')

--Get Prefix and strip it out of the string...  Add additional prefixes as needed
set  @TempString = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))
if   @TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor') set @Prefix = @TempString
if   len(@Prefix) > 0 set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))

--Get Suffix and strip it out of the string... Add additional suffixes as needed
set  @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
if   @TempString in ('Jr', 'Sr',  'II', 'III', 'Esq', 'Junior', 'Senior') set @Suffix = @TempString
if   len(@Suffix) > 0 set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))

--Strip Lastname out of the string
set  @LastName = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
set  @NameString = rtrim(left(@NameString, len(@NameString) - len(@LastName)))

--Check Last name to see if it has two parts...  Add additional values as needed
set  @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
if   @TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE')
     begin
     set @LastName = @TempString + ' ' + @LastName
     set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))
     end

--Get FirstName and strip it out of the string
set    @FirstName = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))
set    @NameString = ltrim(right(@NameString, len(@NameString) - len(@FirstName)))

--Anything left is MiddleName
set    @MiddleName = @NameString

--Build output string
--You will need to add Long/Short versions of Prefixes and Suffixes to the appropreate section
set    @TempString = ''
while len(@NameFormat) > 0
begin
set @TempString = @TempString +
    case ascii(left(@NameFormat, 1))
        when '80' then case @Prefix
            when 'Dr' then 'Doctor'
            when 'Rev' then 'Reverend'
            else isnull(@Prefix, '')
            end
        when '70' then isnull(@FirstName, '')
        when '77' then isnull(@MiddleName, '')
        when '76' then isnull(@LastName, '')
        when '83' then case @Suffix
            when 'Jr' then 'Junior'
            when 'Sr' then 'Senior'
            when 'Esq' then 'Esquire'
            else isnull(@Suffix, '')
            end
        when '112' then case @Prefix
            when 'Doctor' then 'Dr'
            when 'Reverend' then 'Rev'
            else isnull(@Prefix, '')
            end
        when '102' then isnull(left(@FirstName, 1), '')
        when '109' then isnull(left(@MiddleName, 1), '')
        when '108' then isnull(left(@LastName, 1), '')
        when '115' then case @Suffix
            when 'Junior' then 'Jr'
            when 'Senior' then 'Sr'
            when 'Esquire' then 'Esq'
            else isnull(@Suffix, '')
            end
        when '46' then case right(@TempString, 1)
            when ' ' then ''
            else '.'
            end
        when '44' then case right(@TempString, 1)
            when ' ' then ''
            else ','
            end
        when '32' then case right(@TempString, 1)
            when ' ' then ''
            else ' '
            end
        else ''
    end
set @NameFormat = right(@NameFormat, len(@NameFormat) - 1)
end

Return @TempString
end

GO

This function will allow you to parse contact values and return them in any way you would like. It's not all inclusive in terms of available prefixes/suffixes, etc. so, please feel free to use/modify this function as needed for your situation.

Thanks for reading!

Recombining Parsed data in SQL

Jason Buss — Tue, 30 Mar 2010 13:10:00 GMT

A couple of weeks ago, I posted about using User Defined functions and Cross Apply to parse data in SQL server. The second part of the import I was working on required me to take that parsed data, perform some manipulations, and then recombine that data. Surprisingly, I found that putting that data together was much more difficult then pulling it apart in the first place. After much experimentation, this is what I came up with...

As you may recall from my previous post, I created a Table-Valued function called Parsedata to pull apart the data from a field. The next step was to join each parsed data value, and join it to another table holding those values as well as an ID for each value. I then needed to combine the ID's from that second table.

My Original Data Looked Like:

ID Value
---------------
1   ABC, DEF
2   HIJ, KLM
3   NOP, QRS

First, I created a view using the ParseData function: (This is what I did in my previous blog post)

Create View [dbo].[vDataSplitSource] as
select Table.ID, s.Value from Table
cross apply
dbo.ParseData(Table.Value, ',') s

After parsing the data, I get a view containing:

ID    Value
--------------
1     ABC
1     DEF
2     HIJ
2     KLM
3     NOP
3     QRS

That view functions as my source. My target is a hash table which contains IDs for each value, similar to:

ItemID   Value
----------------
11       ABC
12       DEF
13       HIJ
14       KLM
15       NOP
16       QRS

I create a simple view joining my parsed data to the hash table data:

Create View [dbo].[vJoinedData2] as
select distinct a.ID, b.Value
from vDataSplitSource a inner join HashTable b
on a.Value = b.Value

Now I have a view containing values from both the view and the table.

For the next step, I created a new Scalar-Valued Function to actually put the data back together. In this particular case, my result set needed to not only have commas as delimiters, but also needed commas at the beginning and the end of the string as well. The function requires that you pass the ID for each row as well:

Create FUNCTION [dbo].[CombineData] ( @c INT )
    RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @p VARCHAR(MAX) ;

           SET @p = ',' ;
        SELECT @p = @p + rtrim(ltrim(Value)) + ','
          FROM vJoinedData2
         WHERE Id = @c ;
    RETURN @p
    END

Finally, It was just a matter of calling the function to return the data as I needed it:

select id, cast(dbo.CombineData(id) as nchar(40)) as RetValue
from vJoinedData2

This last data returns:

ID    RetValue
--------------
1     ,11,12,
2     ,13,14,
3     ,15,16,

Unfortunately, I found that the whole process runs very slowly. I actually used the final statement here as a view, and then joined that view to another view by IDs which proved to be unmanageable when working with larger datasets. Ultimately, I ended up stuffing the final returned data into a temporary table and joining to that instead.

Anyway... It look me a while to figure out this whole processes, and most examples I found didn't exactly conform to my particular requirements, so I hope that someone out there can find this useful.

Thanks for reading!

Jason

Using SQL User defined functions and Cross Apply to parse SQL data

Jason Buss — Mon, 08 Mar 2010 09:40:00 GMT

Recently, I worked on an import where I spent a considerable amount of time working out how to effectively parse data from a SQL table, for insertion into another table.

The table I was evaluating basically looked like this:

ID, Value
------------------------------
"1", "ABC, DEF, HIJ"
"2", "KLM, NOP, QRS"
"3", "TUV, WXYZ"

I needed a row for each distinct value. To parse the data, I created a user defined function with the following code:

CREATE FUNCTION dbo.ParseData
(
   @RowData nvarchar(2000),
   @SplitOn nvarchar(5)

)
RETURNS @RtnValue table
(
   Id int identity(1,1),
   Data nvarchar(100)
)
AS
BEGIN
   Declare @Cnt int
   Set @Cnt = 1

   While (Charindex(@SplitOn,@RowData)>0)
   Begin
       Insert Into @RtnValue (data)
       Select

           Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

       Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
       Set @Cnt = @Cnt + 1
   End

   Insert Into @RtnValue (data)
   Select Data = ltrim(rtrim(@RowData))

   Return
END

Now, this function works great when manually defining the values to parse, such as select * from dbo.ParseData('123,456,123,456', ','). This would return

Id, Data
-------------
"1", "123"
"2", "456"
"3", "123"
"4", "456"

as I expected, but when I try to use the function with my original table, I was getting an error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Split", or the name is ambiguous.

To get around this, I was able to use "Cross Apply" in my sql statement, to run the UDF against each row in my table. Using Cross Apply result in this statement:

select ID, s.data from [table]
cross apply
dbo.ParseData([Table].Value, ',') s

Which Returns:

Id, Data
-------------
"1", "ABC"
"1", "DEF"
"1", "HIJ"
"2", "KLM"
"2", "NOP"
"2", "QRS"
"3", "TUV"
"3", "WXYZ"

Now I have my data parsed with the table ID, ready to be joined to my source data for import.

-Jason

Using SQL triggers to enforce data rules in SalesLogix.

Jason Buss — Fri, 05 Feb 2010 13:20:00 GMT

In a recent project, I used triggers to enforce some data rules in the Ticket area of SalesLogix. We did this because our client was using both the LAN as well as the Web clients. We were having some difficulty seeing changes in the LAN client when values were set in the Web, and vice-versa.

If you're not familiar with them, triggers are code that are executed automatically in response to an event occurring on a SQL table. SQL supports triggers for insert, update and delete operations. In this case, I only had to deal with update triggers, since I needed to set certain fields based on a user either checking a checkbox, or setting a particular ticket status.

I have a extended table off of Ticket (Ticket_Ext), which contained a boolean (ProblemClear) and a date field (ProblemClearDate). I had to do the following, based on if the ProblemClear field was checked:

If True and ProblemClearDate is Empty, set ProblemClearDate to the current date.
If True and ProblemClearDate is NOT empty, do nothing.
If False, clear the ProblemClearDate field.

In addition, I had a couple rules for when the Ticket Status value was changed:

If the value is 'Closed', and ProblemClear was not checked, set ProblemClear to 'T' and set ProblemClearDate to the current date
If the value is not 'Closed' and ProblemClearDate is not empty, clear ProblemClearDate

For the most part, the syntax creating the triggers is pretty simple. Basically, we're just writing a standard Update query. The thing you have to keep in mind, however; is that triggers are defined at the table level and not for specific fields. In order to evaluate specific fields, you have to utilize specialized system tables 'Inserted' and 'Deleted' that are in SQL. Whenever a field is updated, a temporary record in the 'Inserted' table is created, and 'Deleted' is populated when a field value is deleted. There is no corresponding 'Updated' table, so if a field value is updated, then an entry is added to 'Deleted' and then to 'Inserted'. There is an update function which returns a true value if the field passed to the function had indeed been updated.

Here's the code I wrote to build these triggers based on the rules listed above:

Create TRIGGER uptClearFlag
ON TICKET_EXT
FOR UPDATE AS
If update (ProblemClear)
Begin
    Update ticket_ext set ProblemClearDate = GetDate()
    where ticketid in (select ticketid from inserted)
    and (ProblemClear = 'T') and ((ProblemClearDate = '' or ProblemClearDate is null))

    Update ticket_ext set ProblemClearDate = Null
    where ticketid in (select ticketid from inserted)
    and (ProblemClear = 'F') and (ProblemClearTime is not null)
End
go

Create TRIGGER uptTicketStatus
ON Ticket
FOR UPDATE AS
If update (StatusCode)
Begin
    Update a set a.ProblemClear = 'T'
    from ticket_ext a
        inner join ticket b on a.ticketid = b.ticketid
        inner join picklist c on b.statuscode = c.itemid
    where a.ticketid in (select ticketid from inserted)
    and (c.text = 'Closed')

    Update a set a.ProblemClearDate = null
    from ticket_ext a
        inner join ticket b on a.ticketid = b.ticketid
        inner join picklist c on b.statuscode = c.itemid
    where a.ticketid in (select ticketid from inserted)
    and (c.text <> 'Closed')

End

go

As you can see, I join my SLX Tables to the 'Inserted' system table so that I can find the correct record that I want to update. It also bears mentioning that triggers would not Sync to remotes, so they are not really an option in that case unless you created the same triggers on each remote database as well.

-Jason

Using SQL views to incorporate external data into SalesLogix customizations

Jason Buss — Wed, 05 Aug 2009 11:17:00 GMT

Did you know that you can include SQL views in SalesLogix? This can be a very handy method to include data that is not normally part of SalesLogix. It is important to note, that these changes are not sync-aware. However if you are running SalesLogix LAN in a network only environment, this can be a helpful way to include data from external sources.

First of all, we need a view. This could be anything, as long as the view is created in your SalesLogix database and is owned by the SYSDBA user. For this example, I'll add a simple view of accounts/primary contacts from another SLX database in Query analyzer:

create view vAccountViewTest as

    select
        a.accountid,
        a.account,
        c.lastname,
        c.firstname from

        SalesLogix2.sysdba.account a
    inner join
        SalesLogix2.sysdba.contact c

    on a.accountid = c.accountid

    where c.isprimary = 'T'

Once this view is created, it will show up in the database manager:

If you get the properties of that view, you simply need to "Enable" the view for use in SalesLogix:

Now that the view has been enabled, you can use it in SalesLogix, just as you would use any other table in the system, for example, in a datagrid:

That's basically it! I hope you found this post helpful.

Understanding SalesLogix Picklists data structure

Jason Buss — Tue, 06 Jan 2009 22:08:00 GMT

Picklists in SalesLogix are a very useful tool, but did you know you could also you values from an existing picklist to populate other list-based controls such as ComboBoxes, CheckListBoxes, ListViews... even Radio groups? You simply need an understanding of the structure of picklists in SalesLogix:

There is a table named "Picklist" within the salesLogix database. There are a number of fields in that table, but the most important fields in this table are TEXT, ITEMID, and PICKLISTID. SalesLogix stores both the individual picklist items in this table, along with the definition for the picklist itself. The TEXT field contains the name of both the Picklist as well as each picklist item, however; a list item's picklistid value will equel the itemid of the picklist name record. Instead of an ID in picklistid, the main record of the list contains the value 'PICKLISTLIST'.

So, say you wanted to return the results of the Contact Type picklist, you could run the query:

select text as [Contact Type] from picklist
where picklistid in
(select itemid as picklistid from picklist
where text = 'Contact Type' and picklistid = 'PICKLISTLIST')
order By text

Then using that as the SQL in the process I defined in my last post (Here) you can populate the control of your choice with those values. This would also be very helpful if you wanted to use SLX Picklist data in an external application which has access to the SalesLogix data.

Pretty neat!