Jason Buss' Blog

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.

Using the Reporting API enhancement in SLX 7.5.2 web

Jason Buss — Wed, 17 Feb 2010 16:17:00 GMT

Sage has added an API enhancement to facilitate running Crystal reports in the SalesLogix web client. To get the functionality in 7.5.1, it was necessary to install a reporting update bundle, but the functionality has incorporated into 7.5.2 with no additional installation needed.

The enhancement adds a number of functions to facilitate running reports in the web client.

Functions include:

ShowReportByName() ShowDefaultReport(), SetContactReport(), ShowReportByID(), ShowReport()

Running a report is very easy. Using a button as an example, simply add one of the functions to the onclientclick property of the button, such as:

ShowReportByName('Contact:Contacts By Lead Source - Sample');
ShowDefaultReport();
SetContactReport('Contact:Contact Summary - Sample');ShowDefaultReport();
ShowReportByID('p6UJ9A0004SD')
ShowReport('Contact:Contact Address Book - Sample', 'CONTACT', 'CGHEA0002670');

(Thanks to John Perona for posting this info in the Sage partner forums)

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.

Message box dialogs in the SalesLogix web client

Jason Buss — Thu, 14 Jan 2010 13:58:00 GMT

In the web client, there's not an easy way to present the user with a multiple choice dialog. One quick and dirty way to do this is to create a new form to function as a dialog.

First, I created a basic ticket form. This will be used in the ticket area, so I created a new form under this entity. On this, I added a column and a couple of rows. I dropped a label control on the form and set the Row and Column span properties to 2. In the remaining areas I dropped a button control on each. Finally, set the caption properties for the buttons and the label. You don't have that much control over the placement of the buttons, so to keep them together, I went to the columns property of the form and set the left column to a width of 90% and the right column to a with of 10%

We will need to add our new dialog to the list of available smartparts for the Ticket entity. In the portal manager, Under Sage SalesLogix and Pages, open the Ticket Detail page. On the Smartparts tab, add the new dialog and set the Target Workspace property to "Dialog Workspace".

To launch the form, we simply need to call the DialogWorkspace Javascript API:

ScriptManager.RegisterClientScriptBlock(this.Page, GetType(), "InvokeYesNoQuestion", "DialogWorkspace.show({ id: 'TicketYesNoDialog', width: '400', height: '100', top: '300', left: '300'});", true);

There we have it!

Using this method has a couple of downsides:

This dialog is very much "single use". You would have to create a new form for every dialog you would want to launch.
When launched from a code snippet, the process doesn't stop when this form is opened. Any logic stemming from a choice on this dialog must be performed on the form itself. There is no way to pass the result of what button is selected to the originating script.

Well, there you go. Not perfect, but a relatively simply method for launching a yes/no dialog in the SalesLogix web client

Thanks for reading!

Area/Category/Issue headache in SalesLogix Web

Jason Buss — Wed, 06 Jan 2010 14:39:00 GMT

Ah, the SalesLogix Web Client... Having worked with SalesLogix for over a decade, I feel as though there's not a whole lot I can't do with the LAN client. The Web client however.... Well, it seems like I encounter challenges with it every day.

I have a project that I'm currently working on that requires me to revert values from the Ticket-level ACI control if certain conditions are not met (including what ACI values are selected) when the values are modified. This has proved to be a challenge, to say the least. Apparently, I can't seem to get the current values of the individual Area Category and Issue entity objects on the On Change event of the dplArea control. Instead, it returns the previous values (before the change) making it rather difficult to evaluate what HAS BEEN selected. Ironically, it would make reverting to the previous values much easier since I wouldn't have to use the IChangedState interface, but I can't evaluate things in the first place.

Unfortunately, I don't have any solutions... no happy endings. At least, not yet. I hope to follow this post up with a solution to my little problem in the near future. In the meantime, I'm going back to working the issue, along with the growing desk-shaped bruse on my forehead.

Thanks for reading! Please stay tuned! :)

Become a Fan of Customer FX on Facebook / Twitter

Jason Buss — Wed, 18 Nov 2009 06:28:00 GMT

If you've been following our blogs, such as mine or our others written by Ryan Farley, Kris Halsrud and George Jensen (among others), we now have feeds set up on both Twitter and Facebook! These feeds are a great and easy way to stay on top of new information relevant to SalesLogix, SageCRM and all the great content that comes out of Customer FX.

We here at Customer FX strongly believe that information should be shared freely, and our blogs provide tons of useful information regarding a wide array of topics to help you through your own CRM implementation. We are also extremely excited about our new Open Source Initiative using Git Extensions, which provides a whole new way to share... not just our knowledge, but our source code as well!

So, please follow our feeds on both Twitter and Facebook. Thank you very much for helping us to become one of the best online sources for information on SalesLogix, SageCRM and more!

Jason

Type/Subtype issue in SLX Web 7.5.1

Jason Buss — Wed, 11 Nov 2009 11:33:00 GMT

Before I get back to Sage CRM topics, I wanted to mention an issue I bumped into an issue in a couple of SLX 7.5.1 databases.

Account Type/Subtype combination are generally pretty easy to set up. You just need to create a picklist and name it using the "Code" value for an item in the Account Type Picklist. (For "Lead" for example, create a picklist named "Account Lead" for example). This works fine in most cases, however I discovered that on the insert contact/account screen, it doesn't quite work correctly.

When you select a type value on this screen, the correct subtype does not come up. If you change type to a different value at that point, you'll get the sub type list from the first item you selected. Account Type and Subtype show up in three areas; Account Detail, Insert Account and Insert Contact Account. For all three, a business rule called "GetSubTypePickListName()" is called on the LoadAction of the form. This works fine in Account Detail and Insert Account, but not on Insert Contact/Account. To fix this on this view, I wanted to move that business rule from the load action of the form to the ChangedValue action of the Type Picklist itself.

This would be easy to do on the first two forms if it were necessary, since you have an actual view in Application Architect to work with. On the Insert Contact/Account view, you have to modify code within the smart part itself so it gets a little more complicated.

First of all, we need to find the smartpart. This can be found in the Project Explorer under Portal Manager-> Sage SalesLogix-> SupportFiles-> Pages. Right-Click Insert Contact/Account in this area, and click "Edit Page".

Once the code is open, we basically need to make three changes:

In the OnLoad method, comment out or remove the line that reads pklAccountSubType.PickListName = account.GetSubTypePickListName();
In the OnWireEventHandlers method, we need to add a line to include a change action event to the Type picklist control. I threw in a line pklAccountType.PickListValueChanged += pklAccountType_ChangeAction(); under the first line in that method that reads base.OnWireEventHandlers();
Finally, we need to create the new method we've referenced in Step 2. I basically took the code from the OnLoad event, and stripped out everything except the reference to the business rule, and code to set up the Entity objects within that method:

    protected void pklAccountType_ChangeAction(object sender, EventArgs e)
    {
        IContact contact = (BindingSource.Current as IContact);
        IAccount account;

        if (contact != null)
        {
            if (!Page.IsPostBack)
            {
                contact.Account = account;
            }
            else
            {
                account = contact.Account;
            }
            if (account != null)
            {
                pklAccountSubType.PickListName = account.GetSubTypePickListName();
            }
        }
    }

That should be it. Please note, however; that the code I listed above may require some tweaking. (The DB I originally modified is not currently available to me, and I put the code example here largely from memory. I thought I had run into an issue when defining the Account Entity object, so I'll update this post with the final code once I get a chance to double check what I had originally done.)

Thanks! I hope you find this post helpful.

SageCRM - Building Workslips Part 1

Jason Buss — Wed, 28 Oct 2009 12:37:00 GMT

Over the next few weeks, I'll be posting a walk-through which will outline building a Workslips module for SageCRM. In this part, I'll cover creating a new table, and defining fields in that table.

We're going to start with creating new table structure to hold basic Workslip information. First we have to define the Workslip table. In SageCRM, got to Administration->Advanced Customization->Tables and Databases and then click the "Create Table" button

This will open the for where we enter the table details:

We'll set the following properties:

Table Name: Workslip
ID Field Name: Workslip_WorkslipID (Primary key for table)
Top Level Entity: Yes
Company Id Field: Workslip_CompanyID (Foreign key to Company table)
Table Caption: Workslip
Column Prefix: Workslip (All fields defined in this table will be prefixed by "Workslip_")
Person Id Field: Workslip_PersonID (Foreign key to Person table)
User Id Field: Workslip_UserID (Foreign Key to User table)
Solo Options: Filter by Company/Person

Click the "Save" button to create the new table.

Now that the table has been created, we have to define the fields in the table. Under Administration->Customization, we should now see the new Workslip Entity under the Primary Entities. Click "Workslips" and make sure you are on the "Fields" tab. Click the "new" button to add fields.

In SageCRM, the individual field definitions contain format settings that define which control is used, and how the control will ultimately be rendered in the client. We're just going to add a couple fields for now.

The first field we will add is a memo-style, description field:

The field type of "Multiline Text" will display a multi-line memo field. Set the width and height to define the size of the control once it is added to a form.

Next, we will add a Workslip Number field:

This is just a standard text field, with Max Length and Width fields defined as well.

Additional fields can be added as required. Definitions of other field types can be found here. System fields, such as the foreign keys for Company, Person and user numbers have been already created for you. I'll be adding some additional fields, and in the next post for this walkthrough, we'll start getting into creating Lists and Screens.

Thanks for reading!

FieldTypes in Sage CRM

Jason Buss — Wed, 14 Oct 2009 12:03:00 GMT

I wanted to take this post to outline some of the common field types that are available within Sage CRM, for anyone new to development within Sage CRM. These are defined under the Fields tab under Administration/Customization/{EntityName}.

*Note - not only to field names need to be unique, field Captions must be unique as well. It's a bit of a pain, since you cannot have two separate fields on two different views that have the same caption.

Most of these properties are the same, but I've noted differences where they exist:

Text: Standard Text field

Column Name - Field name in the Entity Table
Caption - Caption Displayed in Sage CRM
Max Length - Field Length of within the Entity Table
Entry Width - With of the field as it is displayed in Sage CRM
Default - (Optional) default value for the control.

CheckBox: Standard Boolean field

Column Name
Caption
Default - (Optional) Checked or Unchecked

Phone Number/Email Address/WWW URL: Specialized entry fields including formatting for specific data

Column Name
Caption
Max Length
Entry Width
Default

MultiLine Text: Memo field

Column Name
Caption
Entry Width - Since a memo field does not have a fixed width, the width and Height properties are used to define how the field appears on the form
Entry Height
Default

Selection: Picklist Items

Column Name
Caption
Selection Height
Lookup Family - Used to define the picklist used in the control
Lookup Width px - (Optional)
Default

Integer/Numeric: Standard field for numeric input

Column Name
Caption
Entry Width
Default

Date & Time: Field to display date and time information with formatting

Column Name
Caption
Default

Team Select/User Select: Control allowing selection of Teams or Users in Sage CRM

Column Name
Caption
Selection Height
Default
Search SQL (User Select only) This optional property allows you to define a SQL statement to search for particular users

That's all for now, but in my next post, I'm going to go step by step through building a new tab based on these types of fields. Stay Tuned!

Adding/Modifying system menu items in Sage CRM

Jason Buss — Wed, 30 Sep 2009 11:35:00 GMT

By using the Systems Menu functionality, you have the ability to customize the Administration and Main menus in Sage CRM. As an example, I'm going to add a custom menu item to the main Sage CRM menu to launch the view to create a new Person record in Sage CRM.

System menus are managed under Administration|Advanced Customization|System Menus. Once there, select "Main Menu".

Caption - The Text displayed under the menu button
Action - Set to "other" for this example
System Act - This is the functionality launched by the button. For this example, I'm using "newperson"
Bitmap - The icon that displays on the button

Click "Add" to add the new menu item to the main menu, and click "Save" to commit the change.

That's all there is to it. You now can directly create a new Person record from the main menu. There are many different functionality options that can be launched via a new main menu item. It'd be worth the time to review the other items under the System Act property to see what else is available.

Hope you find this post helpful. Thanks for Reading!

SageCRM Tip - Editing screen definitions - Screen contents missing from screens

Jason Buss — Thu, 17 Sep 2009 10:39:00 GMT

On the last SageCRM project I worked on, I ran across a little issue with the CRM interface which was causing me a bit of a headache. I was working with a couple of screens I had defined for a custom entity. These screens were similar, so I was doing a lot of comparing one screen to another. Every once and a while, I would notice that the screen contents would disappear from the screen I was reviewing, and I would end up having to rebuild the screen.

After rebuilding this screen for the Nth time, I figured out what was going on: If you change your mind and do not perform any changes to the screen, you MUST click either "save" or "cancel" to navigate off of the Screen Definition page. Leaving the Screen Definition by simply navigating off the page causes the defined contents to be stripped from the screen.

Of course, even after I figured that out, I still had to rebuild the view a few more times before leaving the view properly became second nature! Anyway, I hope this brief post saves you some headaches.

Thanks for reading!

Displaying external data in SageCRM with ASP

Jason Buss — Wed, 26 Aug 2009 10:30:00 GMT

In my last blog post, I outlined how to display data from external database in SageCRM 200. This week, I'm going to do the same thing, but instead of connecting to an external datasource through SageCRM, I'm going to display the data using a custom ASP page. If you recall from my last post, we had to create a view so that we could include the required channelID field to be able to display the data in the Team CRM area. One of the benefits of using a custom ASP page is that we can pull directly from the source table without having to add additional data elements. Also, we have the ability to display this view almost anywhere in SageCRM. With the other process, we couldn't add the page to Companies (for example) without establishing a relationship between the external data and company data.

First of all, we need to create a simple ASP page displaying the information we wish to see. I'm going to pull in the same data as I did in my last example:

<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Open"Provider=SQLOLEDB;UID=sa;PWD=CFXPASS;Data Source=crmDev;Initial Catalog=Northwind;"
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT orderdate, requireddate, shipaddress, shipcity from Orders"
rs.Open sql, conn
%>

<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
    response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
       <td><%Response.Write(x.value)%></td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
rs.close
conn.close
%>
</table>

</body>
</html>

Save this file as "Northwind.asp". Once the file is saved, it needs to reside in the CustomPages folder of the CRM installation. Typically, this is found in "...\CRM\InstallName\WWWRoot\CustomPages".

Now that the asp page is saved and in the correct location, go to the same location we added the other Northwind tab. This was under Administration->Advanced Customization->System Menus. Once there, edit the "Channel" tab group to add the new tab.

Set the following properties:

Caption : in this case "Northwind Orders ASP"
Action : Set this to customfile. This will let SageCRM know that this a file in the CustomPages folder
Custom File : Enter the name of the custom ASP page. (Northwind.asp)

Click Add and then Save to add the new tab. That's all there is to it!

This isn't meant to be an ASP tutorial, so the formatting is pretty simple. However, you can see how simple it is to add a custom ASP page to Sage CRM.

Thanks for reading!

Displaying external data in SageCRM 200

Jason Buss — Wed, 19 Aug 2009 08:41:00 GMT

In this post, I will outline a relatively simply process for displaying data in SageCRM from an external data source. To keep this simple, we're not going to worry about any sort of security, and we'll save more advanced formatting for another post.

First of all, we need to create a reference to an external data source. For this example, I'm using the Standard "Northwind" database installed with SQL. To create a connection to an external datasource, click the "Administration" button, then "Advanced Customization" and finally "Tables and Databases".

Once in this area, click the "New Database Connection" button which will presented a Database Details view where you enter your connection information

I've entered the following info:

Database Driver: There are a number of options here, but since I'm connecting to a SQL database, I selected Microsoft SQLServer
Server Name: The name of the SQL server containing the external database.
Database Name: The name of the External Database.
Database Description
Username/Password: For the sake of this example, I'm simply using the standard sa username/password for this server.

Click "Save" after entering this information. The Northwind database will now display in the list of available Databases, however we still need to include a table. For this example, I'm going to use the "Orders" table in Northwind, but we need to perform a step on the database before we can display information from this table on the Team CRM tab.

To display on the Team CRM tab, the table needs to contain a field named chan_channelid. This field is used by the Team CRM tab to determine if the current user has access to see this data. For this example, we want all users to see this, so we want this field to contain a value of 5. We could just add a new column and insert that value across the entire table, however to avoid modifying the original source data, I'm going to create a view instead.

To create the view, I use the following syntax in SQL Query anaylizer:

create view vOrders as select 5 as chan_channelid, * from orders

I now have a view named "vOrders" which I can use in SageCRM

Back under Administration|Advanced Customizations|Tables and Databases, click the "New Table Connection" button to bring up the table details view:

On this view, we need to fill in the following information:

Table Name: This is the name of the view we created earlier
Table Caption: This is the caption by which the table (view) will be identified in SageCRM (Note: This value cannot contain spaces)
Database: This pull-down should contain the Northwind database that we created the connection to earlier.
ID Field Name: For this example, we're going to leave this value blank for now.

Click "Save" on this view. The data from the Northwind Orders table will now be available in SageCRM

Now that we've created connections to the Northwind table and Orders database (via a view), we need to create a list view containing Orders data. Go to Administration|Customizations. You should find the entry for "NorthwindOrders" listed in the Secondary entities pull-down. Once that is selected, click the "Lists" tab, and click the "New" Button:

For the New List Definition, we simply need to enter:

Name: This is the descriptive name of the List view
Table/View: the name of our view (vOrders)

Click Save, and then click the "Customize" button next to the newly created view to define the columns to display:

On this view, add whichever fields you want to display and click "Save" to finish configuring the new list view.

Now that we have our list view created, the only step remaining is to add the list view to the Team CRM area. To add a new tab to Team CRM, go to Administration|Advanced Customization and click on "System Menus". The tabs under Team CRM are defined in the "Channel" tab group, so click "Customize" next to that entry:

Here, we need to set three values:

Caption: This will display on the tab under Team CRM
Action: Run Block
Block Name: This is the Caption we defined for the list view.

Click Save, and we are done!

If we wanted to do additional formatting, say on the date fields or column captions, I would make those sort of changes in the view we created. Otherwise, this is a pretty simply method to display external data within SageCRM. In future postings on SageCRM, I'll try to get into adding data related to existing entites and possibly dig more into user security topics.

I hope you find this helpful, or at least interesting. Thanks for reading!

Using Picklist data revisited

Jason Buss — Wed, 12 Aug 2009 11:00:00 GMT

Back in January, I posted about the SalesLogix picklist data structure, and outlined how to use the data found in the picklist table to populate other controls such as comboboxes and listboxes. You can find that article here.) If you are performing customizations to version 7.2 or later of SalesLogx, this processes can be greatly simplified by using Application.Picklist instead of directly querying the database.

By using Application.Picklist, you no longer need to understand how SalesLogix stores picklist data. This also serves to clean up your code, since you don't have to look at the picklist table.

As an example, here is code populating a ListBox control with the values from the "Contact Type" picklist.

Dim x
Set objList = Application.PickLists.Item("Contact Type")
ListBox1.Items.Clear
For x = 0 To objList.Count - 1
ListBox1.Items.Add(objList.Item(x))
Next
Set objList = Nothing

That's all there is to it. Much simpler than looking at the picklist data directly via a query. Remember though, Application.picklists is ONLY available on implementations of SalesLogix version 7.2 or greater.

Thanks for reading!

Using SQL views to incorporate external data into SalesLogix cusomizations

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.

Events based on history in version 6.x SalesLogix

Jason Buss — Wed, 08 Jul 2009 10:43:00 GMT

Prior to version 7, there was limited access to those activity and history areas in SalesLogix. In a recent project, I had to perform a series of tasks upon completing an activity with a particular Type/Category/Result combination. This had to be done in a 6.2.1 system. In order to do this, I had to modify the OnCompletedActivity active script. I wanted to briefly outline the modifications I made to this area for anyone who is running an older version of SalesLogix.

Basically, I needed to look at a completed todo activity with a certain Category, and then update the associated contact and/or opportunity status values depending on the Result of the completed activity. I created a subroutine named "ToDoActivityOperations" which I call via the Sub Main in the OnCompletedActivity Script

Sub ToDoActivityOperations
Dim strSql, objRS

     Set objRS = objSLXDB.GetNewRecordSet

(After creating a recordset, I open a sql statement retuning ContactID, OppID, and result from the history table that have the correct activity type)

     strSql = "SELECT OPPORTUNITYID, CONTACTID, RESULT, TYPE, CATEGORY FROM HISTORY " & _
              "WHERE ACTIVITYID = '" & vActivityID & "' and type = '" & "262147" & "'"

     objRS.Open strSql, objSLXDB.Connection

(In my next step, I check to see if any records were returned. Then I get the values from the history record)

     If objRS.RecordCount <> 0 Then
          strOpportunityID = objRS.Fields("OPPORUNITYID").Value & ""
          strContactID = objRS.Fields("CONTACTID").Value & ""
          strResult = objRS.Fields("RESULT").Value & ""

(Based on the result value, I call additional subroutines I've written to update Opportunity and Status record values
          If lcase(result) = "rejected" Then
             If strOpportunityID <> "" Then UpdateOpportunityStatus "Closed - Rejected", strOpportunityID
             If strContactID <> "" Then UpdateContactStatus "SRL", strContactID
          Elseif lCase(result) = "accepted" Then
             If strOpportunityID <> "" Then UpdateOpportunityStatus "SAL", strOpportunityID
             If strContactID <> "" Then UpdateContactStatus "SAL", strContactID
          Else

          End If
     End If
     objRS.Close
     set objRS = nothing

End Sub

That was all there was to it. It would be nice to be able to edit activity and history views, but short of upgrading to the current version of SalesLogix, this at least allows some degree of control whenever activities are completed.

I hope you find this helpful. Thanks again for reading!

Populating AutoComplete Strings

Jason Buss — Wed, 01 Jul 2009 12:27:00 GMT

I recently had a project which required me to populate the strings collection in an AutoComplete control from data contained in the database. I wanted to take a second to outline how I populate the control's strings, as well as create a reusable script to simplify the process in the future.

The FillAutoCompleteString method I've created is pretty simple. It accepts parameters for the control name, as well as Select, from and where clauses to build a SQL statement. (The Where parameter is optional, but you do need to pass at least a blank string.)

The method simply loops through a recordset based on the SQL parameters provided, and writes each item to the autocomplete control.

Sub FillAutoCompleteString(ByRef objControl, ByVal strSelect, ByVal strFrom, ByVal strWhere)
Dim objNames
Dim sSql

    objControl.Strings.Clear

    Set objNames = CreateObject("ADODB.Recordset")
    With objNames
         Set .ActiveConnection = Application.GetNewConnection
         .LockType = 1 'adLockReadOnly
         .CursorLocation = 3 'adUseClient
         .CursorType = 0 'adOpenForwardOnly
         sSql = "select " & strSelect & " from " & strFrom & " product"
         If Len(trim(strWhere & "")) > 0 Then
            sSql = sSql & " where " & strWhere
         End If
         .Open sSql

         While Not (.BOF or .EOF)
            objControl.Strings.Add(.Fields(strSelect).Value & "")
            .MoveNext
         Wend
        .Close
    End With

    Set objNames = Nothing

End Sub

I put together a simple test form to check out the functionality. After adding a "Select" and "From" value, clicking the Fill AutoComplete Strings button will add those items to the Autocomplete control. The user will now be presented with a list of potential items, based on the characters they've entered.

Obvously, querying the database in this way can be slow if dealing with an exceptionaly large database, so keep that in mind. Otherwise, feel free to use this code in your own implementations. Thanks for reading!

Using the Forms collection in the SalesLogix LAN client

Jason Buss — Wed, 24 Jun 2009 17:56:00 GMT

From time to time, you may find that you want to reference a control or script on a form other than the one you are currently on. This can be done easily using the Forms collection in SalesLogix.

The first thing we need to do is to loop through all open forms, checking for the desired form. For this example, we'll look for the Contact Detail form. (Note: we're looking for the value contained within the "Name" property of the form we are looking for.):

    Dim frm
    Dim x

    For x = 0 to Application.Forms.Count - 1
        If Application.Forms(x).Name = "frmContactDetail" Then
            Set frm = Application.Forms(x)
            Exit For
        End If
    Next

Once this code runs, we should have an object (frm) containing a reference to that form. If the form is not found, the code will return null, which we should look for before trying to manipulate anything

If Not frm Is Nothing Then
End If

Any references should be done within that IF statement. To access properties, you just need to include the form object (frm.{control}.{property}). For scripts, you'll need to access the forms Scripts collection (frm.scripts.{sub or function name})

That's all there is to it. All in all, pretty easy, right?

Keep in mind, this does require that the form you want to reference actually be open. If you want access to information to information on an uninitiated form, you'll need to access the appropriate tables directly.

Hope you find this post helpful. Thanks again for reading

Utilizing Spell-Check functionality in SalesLogix 7.5 LAN

Jason Buss — Thu, 11 Jun 2009 10:14:00 GMT

If you've ever wanted to implement some sort of Spell Check functionality within your SalesLogix database, you're in luck! The Standard Spell Check vbScript makes it simple to add this functionality to your custom forms. *Note: Microsoft Word must be installed on the local machine.

For this example, I'll set up Spell Check functionality in a standard Memo field. There are two ways to implement this sort of functionality:

1: Using the Standard Memo popup. This method will launch an generic edit view for the memo field which includes "Spell Check" as a right-click menu option.

First of all, we need to create a form. For this example, we simply need an Account form containing a Memo control, and a button to launch the Standard memo popup.

In the code for this form, we will first want to include the System:Spell Check. Once the script is included, add the following code to the Button's click event:
Sub Button1Click(Sender)
InvokeStandardMemoPopup Memo1, "Spell Check This form", False
End Sub
This subroutine takes three parameters. The first is the name of the Memo control, followed by a Caption for the form we are launching. The final parameter is a boolean designating if a time/date stamp should be added to the invoked form.
Upon clicking the button, you are now presented with the standard memo popup view.

If you right click in the view, you will see a "Spell Check" item included in the menu.

That was pretty easy to set up. However, if you don't want to present an additional view to the end user, you can add the functionality directly into your memo control.

2: Adding spellcheck functionality to a memo control.

We'll use the same form as before, but we will need to add A PopupMenu control to the form. We'll need to set up the PopupMenu items to include "Spell Check" as a menu item. Double click the PopupMenu's Items property and add these items:

We'll also need to add the following code to the PopupMenu control's "OnClick" event:
Sub PopupMenu1Click(Sender, ByRef MenuItem)
    StandardOnPopUpMemoClick Memo1, MenuItem.MenuIndex
End Sub

The last step is to add this code to the Memo control's "MouseDown" event:
Sub Memo1MouseDown(Sender, Button, X, Y)
    If Not Sender.ReadOnly And Button = 1 Then
        StandardMemoMouseDownEvent Memo1, PopupMenu1
    End If
End Sub
This code checks to see that the memo control is not read-only, and that the right mouse button has been clicked. Then you just pass the Memo control and PopupMenu control to the StandardMemoMouseDownEvent subroutine.

There you go! You've just used the Spell Check script included in SalesLogix to add Spell Check functionality to your custom form. I hope you find this article helpful! Thanks for reading!

Setting conditions for Crystal Reports in SalesLogix LAN

Jason Buss — Wed, 27 May 2009 11:08:00 GMT

I wanted to quickly outline a process for passing a condition to a Crystal report in two simple steps. Say you've created an account report that you want to launch via a button on the account detail, limited to the current account record.

Step 1, Setting up the button:

The button is going to do two things: Set the current accountid to a global variable, and launch the report.

Sub Button1Click(Sender)
Application.BasicFunctions.GlobalInfoSet "gAccountID", Application.BasicFunctions.CurrentAccountID
Application.BasicFunctions.DoInvoke "CrystalReportPreview", "Account:New Account Report"
End Sub

Step 2, pass the current ID to the launched report:

In order to pass the ID to the report, we need to create a VB Script which grabs the Global Variable, then passes it to the report using the ReportAddCondition function found under Application.BasicFunctions. This script is attached to the report via the report properties set under manage|Crystal Reports.

The new script is named Account Report Add Condition, and contains:

Sub Main
Dim AccID

    AccID = Application.BasicFunctions.GlobalInfoFor("gAccountID")
    Application.BasicFunctions.ReportAddCondition "AccountID", "=", AccID, "String", ""
    Application.BasicFunctions.GlobalInfoClear "gAccountID")

end sub

Three things are happening here. First of all, we're assigning a local variable to the ID contained in the Global Variable. Next, we add the condition to the report, and finally, we clear the global variable. Once the script is saved, go to the menu item "Manage|Reports", select the report you want to add the condition to and view the properties of the report. On the Execution tab, add the script to the When Open event.

That's all there is to it! You now have a button on account detail which launches your new report for the current opportunity record.

User feedback using the Animate Control

Jason Buss — Wed, 13 May 2009 09:52:00 GMT

In my last blog post, I described how to use the Progress Bar control to provide feedback to a user when running time intensive processes. In addition to the Progress Bar control, the Animate control can also be used.

Animate Control

The Animate control is very simple to use. A number of animations are inlcuded to cover standard operations such as moving or coping and deleteing items, and some other additional animations.

After adding the control to the form, choose one of the standard animations from the "CommonAVI" property of the form. I also set the visble property, and the Active property to false at design-time, so that I can turn both properties on in code to make visible and start the animation.

I've used my code example from last week, and included a call to a control named "Animate1" on the form.

Sub UpdateAccounts

Dim rs

Set rs = CreateObject("ADODB.Recordset")
With rs, pos

        pos = 0

         Set .ActiveConnection = Application.GetNewConnection
         .CursorLocation = adUseClient
         .CursorType = adOpenStatic
         .LockType = adLockOptimistic
         .Open "select * from Account where status <> 'Open'"

         ProgressBar1.Max = .RecordCount
         ProgressBar1.Position = pos

         .MoveFirst

         Animate1.Visible = True
         Animate1.Position = True

         While Not (.Bof or .Eof)

             .Fields("modifyuser").Value = Application.BasicFunctions.CurrentUserID
             .Fields("modifydate").Value = Now
             .Fields("status").Value = "Open"

             .Update

             pos = pos + 1
             ProgressBar1.Position = pos

             .MoveNext
         Wend
         .Close

         Animate1.Visible = False
         Animate1.Active = False

End With
Set rs = Nothing

End Sub

In addition to the available preset animations, you can also use the Animate control to disaply an AVI file instead. To do this, you'll want to set the "CommonAVI" to aviNone, and then enter the path and file name for the AVI file you would like to display. The Animate and Visible properties will function the same regardless of the animation source.

(the clock.avi under c:\windows is a simple avi displaying a running count from 1 to 10.

That's all there is to it. The Animate control is a very simple way to spruse up your views when performing time-consuming updates. Thanks for reading!

Providing interface feedback - Progress Bars

Jason Buss — Thu, 07 May 2009 11:14:00 GMT

When performing lengthy updates in the SalesLogix client, it's a good idea to provide the user with some degree of feedback so that it doesn't appear as though SalesLogix has locked up. The Progress Bar control, can be used for this purpose:

Progress Bar

The main properties in this control you will want to set are the "Max" and "Position" properties. "Max" defines the upper limit of the bars position. "Position" defines the current position of the bar. Basically, we just need to set "Max" to the count of records modified, and then need to increment the "Position" value by one for each record we update.

In this simple example, I'm simply running through the Account table, and setting the status value from each record to "Open" when it doesn't already have that status.

Sub UpdateAccounts
Dim rs

Set rs = CreateObject("ADODB.Recordset")
With rs, pos

        pos = 0

         Set .ActiveConnection = Application.GetNewConnection
         .CursorLocation = adUseClient
         .CursorType = adOpenStatic
         .LockType = adLockOptimistic
         .Open "select * from Account where status <> 'Open'"

         ProgressBar1.Max = .RecordCount
         ProgressBar1.Position = pos

         .MoveFirst

         While Not (.Bof or .Eof)

             .Fields("modifyuser").Value = Application.BasicFunctions.CurrentUserID
             .Fields("modifydate").Value = Now
             .Fields("status").Value = "Open"

             .Update

             pos = pos + 1
             ProgressBar1.Position = pos

             .MoveNext
         Wend
         .Close

End With
Set rs = Nothing
End Sub

That's all there is to it. Users will now be able to visually follow the progress of the update. I hope you found this code sample helpful!

Adding items to the Opportunity Snapshot (LAN)

Jason Buss — Wed, 29 Apr 2009 12:51:00 GMT

The opportunity snapshot on the Opportunity Detail view is a handy place to display summary information, but customizing it can be a bit confusing. The Snapshot is simply a browser control, displaying HTML which is being generated by a method on the Opportunity detail screen. You can see the HTML code in that method (LoadSnapShot), but it is so broken up and pieced back togther to add variable data, that it can be a little tough to navigate. Hopefully this basic rundown helps explain how the snapshot is put together, and how simple it can be to add new information.

First, let's take a look at the conditions involved in generating the snapshot (Opportunity Detail view, LoadSnapshot method). Primarily, the Opportunity status is checked to determine which version of the snapshot is to be displayed. I've cleared out the html in this code, so we can see how things are setup based on status.

The first thing checked is to see if the current status is either Open or Inactive (I've cleared out localization code as well to make this more readable):

If strStatus = "Open" Or pklStatus.Text = "Inactive" Then

In this area, they set up the header information for the HTML
    If Not blnMCEnabled Then
Here they display pricing information, depending if Multi Currency is enabled or not.
    Else
    End If

Finally, they show summary information for the opportunity which is not reliant on currency. This is usually fields like Days Open, Current Stage, etc... This is repeated for both Closed statuses as well as a catch all for any other status value.

ElseIf strStatus = "Closed - Won" Then
'Header Info
    If Not blnMCEnabled Then
'MultiCurrentcy enabled
    Else
'MultiCurrentcy disabled
    End If
'Summary Information
ElseIf strStatus = "Closed - Lost" Then
    If Not blnMCEnabled Then
    Else
    End If
Else 'The code below is a "default" if the user addes a picklist value that doesn't equal the above conditions out of box.
    If Not blnMCEnabled Then
    Else
    End If
End If

That's all there is to the code in terms of WHERE you want to edit, but there is a lot of broken up HTML to navigate at that point; However, If you search for label values in the Snapshot code, and do a little copying/pasting, it's not do difficult to add some additional information to this area.

I'm going to add a "Total Margin" field in the Value section of the snapshot. Total Margin will be defined as (TotalSales - TotalFixedCost) / TotalSales. We'll want to calculate margin based off of both SalesPotential for Open Opportunities, and ActualAmount for Closed Opportunities.

First of all, we need to get the total cost value. I'm going to add this code near the top of the LoadSnapShot method on Opportunity Detail. This will give me a variable name dTotalCost containing the total cost value for all products on the current opportunity:

Dim dTotalCost

    Set objCostRS = objSLXDB.GetNewRecordSet
    strSql = "select sum(p.fixedcost) as totalcost from opportunity_product o inner join product p on o.productid = p.productid" & _
             "Where o.opportunityid = '" & strCrntOppID & "'")
    objCostRS.Open strSql, objSLXDB.Connection

    If objCostRS.RecordCount <> 0 Then
        dTotalCost = objCostRS.Fields("totalcost")
    Else
        dTotalCost = 0
    End If

objCostRS.Close
Set objCostRS = Nothing

Now that we have cost, we have to calculate the margin. I do this in two places actually. First right outside the first If statement that checks the opp status, and I also set it inside the If statement if status is "Open". I do this because margin will be based off of the Actual Close Amount if the Opp has any closed status, and off of Sales Potential if the status is Open or Inactive.

(My added code is in bold, you will probably find this right around line 370 on the Opp detail form code)

            If IsNull(objRS.Fields("WSALE").Value) Then 'DNL
               lngWeighted = 0
            Else
               lngWeighted = objRS.Fields("WSALE").Value 'DNL
            End If

            If cDbl(intActualAmount) = 0 Then
                dMargin = 0
            Else
                dMargin = (cDbl(intActualAmount) - cDbl(dTotalCost)) / cDbl(intActualAmount)
            End If

            Application.BasicFunctions.GlobalInfoSet "CurrentStatus", strStatus 'DNL
            If Application.Translator.Localize(strStatus) = Application.Translator.Localize("Open") Or Application.Translator.Localize(pklStatus.Text) = Application.Translator.Localize("Inactive") Then 'DNL

               If lngSalesPotential = 0 Then
                    dMargin = 0
               Else
                    dMargin = (cDbl(lngSalesPotential) - cDbl(dTotalCost)) / cDbl(lngSalesPotential)
               End If

Now that we've calculated the Margin value, we need to insert it into the snapshot. Because of how the snapshot is being built, we'll need to add it in eight different places, twice for each Opportunity Status (Open or Inactive, Closed Won, Closed Lost, other). If you know that multicurrency functionality is not an important aspect of your database, you can add it only once for each status).

                  vHTML = vHTML + "<td class=indent>" & Application.Translator.Localize("Sales Potential = ") & FormatCurrency(lngSalesPotential, 2)
                  vHTML = vHTML + "</tr><tr>"
                  vHTML = vHTML + "<td class=indent>" & Application.Translator.Localize("Weighted = ") & FormatCurrency(lngWeighted, 2) 'Calculated Field
                  vHTML = vHTML + "</tr><tr>"

You can justcopy the last two lines here, and change the copied line to use our new margin values to add it to the snapshot.

vHTML = vHTML + "<td class=indent>" & Application.Translator.Localize("Margin = ") & FormatCurrency(dMargin, 2) 'Calculated Field
vHTML = vHTML + "</tr><tr>"

You'll then want to go through the code and add this new line in each of the MultiCurrency sections for each status.

Adding a single new value is simple enough, but if you try to get more complicated with it, keep a close eye on <td> and <tr> tags before and after any code you enter to avoid breaking the Snapshot all together.

I hope this post is helpful in explaining the basics of how the snapshot is put together, and encourages people to experiment with modifications to this area. Thanks for reading!

Building new Main Views in SalesLogix LAN - Part 3

Jason Buss — Wed, 22 Apr 2009 19:11:00 GMT

In my last couple blog posts about creating main views, we built our Insert and Detail views, as well as the Mainview container. We also covered creating New menu and Navbar items. The last thing we need to do to make our new main area usable is to create our first group.

Groups:

Each main area of SalesLogix (Account, Contact, Opportunity, etc..), has defined a number of default groups. At least one group must be created for each area in order to view any new data that has been added to our new main table.

Groups must be created in the SalesLogix client. To create our first group for SLXProjects, first go to the List view for SLXProjects. Right click somewhere in the list area, and select new group to launch the SLX Query Builder.

I name the group and set visible fields in the Layout tab. Since I want this group to display all records in the SLXProjectTable, I don't have to bother setting options in the "Conditions" tab, since all records will be shown by default. I created new global joins from SLXProject to Account and Userinfo so that I can display username and associated account values as well.

With our group created, we can now view new SLXProject records.

Adding additional tab views

Now that we have the main areas set up, we'll probably want to add additional tabs to SLXProject as well. To create a new tab, simply create a new data view using the new SLXProject table as the base table of the form.

To add a SLXProject family to save our new views, simply add a "SLXProject" picklist item to the Active Form Types picklist found in manage:picklists in SalesLogix Architect.

Once saved, the new views shows up under "More Tabs".

This should give you the basics of creating a new Main View in the SalesLogix LAN client. Hopefully, this demonstrates how simple it can be to expand the functionality of SalesLogix to meet your own specific business needs.

Thanks or reading!

Building new Main Views in SalesLogix LAN - Part 2

Jason Buss — Wed, 15 Apr 2009 12:00:00 GMT

In my last Main Views article, I created the detail view as well as the mainview. For this next part, I'll go through creating an insert view, as well as toolbar and menu items for the new SLXProject entity.

Creating the Insert View:

The insert view for this new area will be created as a manage view, and the data will be written to the database using ADO. We will start by creating a launch script for this view. After creating a new active script in architect, I added the following code:

Option Explicit

Sub Main
Dim objMv

    Application.BasicFunctions.DoInvoke "Form", "System:Insert SLXProject"
    Set objMv = Application.MainViews.Add("System:Quote Details", 1, True)
    With objMv
         .Refresh
         .Show
    End With

End Sub

The Insert view itself is a simplified version of the detail view we already created. Being a manage view, the controls on the insert view are not mapped to fields in the new SLXProject table.

I've also added code to two events on the insert view.

The WhenOpen event is being used to set defualts on the form. In this case, creating the ID for the new record, and defaulting the leader field to the currently logged in user:

Sub AXFormOpen(Sender)
Form.CurrentID = Application.BasicFunctions.GetIDFor("SLXProject")
lkeLeader.LookupID = Application.BasicFunctions.CurrentUserID
End Sub

On the WhenClick Event of the OK button on the form, I am creating a new ADO recordset and saving the data entered on the form:

Sub Button1Click(Sender)
Dim objRS

     Set objRS = Application.CreateObject("ADODB.Recordset")
     With objRS
         Set .ActiveConnection = Application.GetNewConnection
         .CursorLocation = adUseClient
         .CursorType = adOpenStatic
         .LockType = adLockOptimistic
         .Open "select * from slxproject where 1=2"

         .AddNew
         .Fields("slxprojectid").Value = Application.BasicFunctions.GetIDFor("c_test")
         .Fields("accountid").Value = CurrentID
         .Fields("createuser").Value = Application.BasicFunctions.CurrentUserID
         .Fields("createdate").Value = Now
         .Fields("modifyuser").Value = Application.BasicFunctions.CurrentUserID
         .Fields("modifydate").Value = Now
         .Fields("name").Value = txtProjectName.Text
         .Fields("description").Value = memDescription.Text
         .Fields("leader").Value = lkeLeader.LookupID
         .Fields("isactive").Value = "T"
         .Fields("accountid").Value = ""
         .Update

         .Close
     End With
     Set objRS = Nothing

     Form.ModalResult = mrOK
End Sub

Creating Menu/Toolbars:

I also have created menu and toolbar plugins to view SLXProject data, as well as launching the insert view.

The menu plugin contains two menus. The SLXProject NavMenu is referenced in the toolbar plugin below to allow for right-click functionality to insert a new record from the navbar. Both menus launch the Active script, which in turn loads the insert view.

(Note the popup menu property, referencing the SLXProject NavMenu.)

We're not quite fully functional yet. In the next article, I'll go through finalizing the plugins we've created so far, and setting up groups and other suplimental views.

Thanks for reading!