Main Views

June 28, 2010

Since SLX 6.2, the Account, Contact, and Opportunity entities have been implemented as Main Views.

If you want to create your own custom Main View (e.g., for hospital Cases, or company Projects) entity, these are the steps:

  1. In the Picklist Manager, on the Plugin Families tab, add a “MyEntity” value to the Active Form Types, Active Script Types, Main View Types, Menu Types, Report Families, and Toolbar Types picklists
  2. Create a new table (e.g., MyEntity), not associated with any other tables
  3. Create a new MyEntity Detail Main View, bound to the MyEntity table; set the EntityNamePlural and EntityNameSingular properties; and save it under the MyEntity plugin family
  4. Create an Insert New MyEntity Data Form, bound to the MyEntity table
  5. Create a MyEntity Details form, and assign it to the ViewName property of the DetailsView control, on the MyEntity Main View
  6. Create a new lookup (Manage -> Lookups) against the MyEntity table, to be used as the QuickFindLookup on the MyEntity Main View. (For lookups, the Search field is the field searched on, e.g., the field which the “Starting With” value is applied to; the LookupID is what it returns [e.g., to the LookupID field of a LookupEdit control], as an SLX Standard ID; and the Name field is “the value that would be displayed in a LookupEdit when the lookup returns [like a contact name, address info, etc.”])
  7. Create tabs for the Main View (tabs are just Data Forms, bound to the MyEntity table, with the default panel and buttons removed)
  8. Add those tabs to the DefaultTabs list property of the Main View TabsPane control
  9. On the Standard Menus plugin, add a new item to launch the Insert New MyEntity form
  10. In the Standard Menus plugin, create a new MyEntity NavMenu entry. (The OTB right-click popup items for the Sales Toolbar [!] can be found in the same Standard Menus plugin, below the main menu items; e.g., Contacts NavMenu)
  11. On the Sales Toolbar, add an item to jump to the MyEntity Main View (i.e., an Action value of Function, with an Argument of MyEntity:MyEntity Detail). Set its Popup menu property to the MyEntity NavMenu
  12. On the Standard Toolbar, add a new item (with icon) to Insert New MyEntity, which launches the MyEntity:Insert New MyEntity form
  13. Add an item to the Lookup main menu (in the Standard Menu plugin) to launch your MyEntity lookup via a script, using the LookupItemWithConditionByID function (example)

You can’t change the bound-table for a Data Form after it’s created, so if you want to reproduce the normal Attachments or Contacts tabs for the MyEntity entity, you can’t just save them under the new Family. What you’ll have to do instead is create a new Data Form, bound to the MyEntity table, and then copy-and-paste the code and the controls from the existing OTB tab, onto your new one.

In 7.5, the Activities tab (and also the Summary tab) are still part of the compiled functionality of SLX; so if you want to have an Activities tab for the MyEntity entity, you’ll have to build it from scratch.

Of course, if you’re associating Activities with MyEntity records, you’ll also need to modify the Activity Detail View form, and add a MyEntityID field to the Activity table, data-bound from the Activity Detail View. You can do a similar thing for the MyEntity Notes/History tab, adding the MyEntityID field to the History table.

If your MyEntity Main View is displaying “%n of %n records” at the top, or if the All MyEntities group in the (F8) groups view contains no records, check to make sure that your lookup has been assigned to the QuickFindLookup property of the Main View, that it exists on the database in question (i.e., if you’re in QA or production, it should have been bundled and applied), and doesn’t include fields which don’t exist in the MyEntity table.

Also, for the menu/toolbar additions above, it’s much preferable to do those in new plugins, which simply add/remove options from the OTB Standard Menu, Standard Toolbar, and Sales Toolbar. That way, when it comes time to upgrade to the next version, you don’t have to search through the OTB menus/toolbars to try and figure out what you’ve added or deleted from them.


Views on Remotes

June 26, 2010

If you have a database view on your SalesLogix Remote clients, which you’re using for running Crystal reports off of, and which pulls data from the UserSecurity table, you may have encountered this:

“Crystal Report Runtime Error: This field name is not known.”

The way around that is to create the view using the T-SQL setuser function, in Management Studio, on each Remote database (you cannot run this SQL in WG Admin through the SLX Provider):

setuser N'sysdba'
GO

CREATE VIEW sysdba.USERREQV
AS
SELECT sysdba.USERINFO.USERID, sysdba.USERINFO.USERNAME,
sysdba.USERSECURITY.ENABLED, sysdba.USERSECURITY.TYPE
FROM sysdba.USERINFO INNER JOIN
sysdba.USERSECURITY
ON sysdba.USERINFO.USERID = sysdba.USERSECURITY.USERID

GO
setuser
GO

Without the setuser function, the code will execute, and the view will populate as if nothing was wrong; but you won’t be able to see the fields in your Crystal report, in design mode. (Same thing if you try running it in WG Admin, without that function.)


SQL Server Collation

June 25, 2010

When you’re installing SQL Server, and you get to the screen where you have to choose the Collation Settings, you’re probably just accepting the default of “Dictionary order, case-insensitive, for use with 1252 Character set,” right?

Guess what? They’re serious about that being case-insensitive, i.e., any SQL statements you may run against that server and its databases aren’t case-sensitive … so any UPPER or LOWER functions used in your WHERE-clause comparisons are just being ignored.

That’s fine, except for when you want to be doing case-sensitive string comparisons.

So if you’ve set up a server instance/database like that, and you need to run a case-sensitive query (e.g., in the WHERE clause of a user-defined function or a stored procedure), how do you do that? With the T-SQL COLLATE function and a case-sensitive (CS) collation name, like so:

SELECT Account FROM sysdba.Account
WHERE Account LIKE 'a%'
COLLATE SQL_Latin1_General_Cp1_CS_AS

I haven’t tested whether the SLX Provider can handle the COLLATE function—and would be quite pleasantly surprised if it can….


SLX Remote Users and MSXML 6.0 SP2

June 25, 2010

If you’re upgrading SalesLogix for Remote users, and doing that on SQL Server Express Edition 2005, and having to reinstall SSEE because they’ve lost the sa password (etc.), guess what?

Before you do that, if Microsoft MSXML 6.0 SP2 exists on the system (in Control Panel, Add/Remove Programs), you have to uninstall that, or the SSEE installation will fail, with an error message (displayed via a hyperlink to the setup log file, not via a dialog box) that DOES NOT give you much help in figuring out what actually went wrong.

And you can’t uninstall MSXML 6.0 SP2 from Control Panel (Add/Remove Programs), either. Instead, you have to download the Windows Installer CleanUp utility, and run that separately.


SLX URLs

June 24, 2010

SalesLogix URLs are very neat things, allowing you to jump to any ACO record in SLX even from applications outside of SalesLogix.

And the nice thing about the OTB code in the “System:SLX View Launcher” script which implements that, is that it will even handle your own custom entities/mainviews, without needing any modifications to the code.

But suppose you want to be able to open Attachments in the same way, e.g., by writing the “slx://attachment/attachmentid” URL into an Excel hyperlink, to open the Attachment directly from Excel?

It’s easy, and just requires a small change in that same script (with some rewriting of the existing code to make it clearer):

slashpos = instr(1, strTaskName, "/")
strTableName = Mid(strTaskName, 1, slashpos-1)
strID = Mid(strTaskName, slashpos+1, len(strTaskName))
If  UCase(strTableName) = "ATTACHMENT" Then
     strFilename = Application.BasicFunctions.GetAttachmentPath _
          & GetField("Filename", "Attachment", "AttachID = '" _
          & strID & "'")
     strDescription = GetField("Description", "Attachment", _
          "AttachID = '"  & strID & "'")
     OpenSelectedFile strFilename, strDescription
Else
     Application.BasicFunctions.ShowDetails strTableName, strID
End If

To use the OpenSelectedFile functionality, you’ll need to Include the “System:Attachment Support” script (which itself Includes the “SLX Database Support” script, and thus the GetField procedure). The first GetField call is (obviously) retrieving the Filename from the Attachment table, for the AttachID in question.


Parameterized Queries

June 23, 2010

When you’re working with data-bound fields, the SLX Provider handles names or other data with apostrophes—like “O’Reilly”—or fields with Null values in them, without you needing to worry about it.

If you’re doing explicit string SQL Inserts or Updates, however—or using fields that could have apostrophes/nulls in them in your WHERE clauses—you’re probably used to passing the field values through Replace functions, and either testing for Nulls or kludg-ily concatenating empty strings onto the end of each field.

If you use parameterized queries instead of SQL strings, you can go back to not worrying about stuff like that.

Sage actually uses exactly that method in the OTB “System:SLX Lead Support” script, but it’s full of a lot of other unrelated code. In bare-bones terms, parameterized queries in VBScript look like this:

Dim strCommand
Dim objParams
Dim objCmd
Dim objRs

...

Set objCmd = CreateObject("ADODB.Command") 'DNL
Set objCmd.ActiveConnection = Application.GetNewConnection

objCmd.CommandType = adCmdText
objCmd.Prepared = True
strCommand = "SELECT Lastname FROM Contact " _
     & "WHERE AccountID = ? AND IsPrimary = ?"
objCmd.CommandText = strCommand

objCmd.Parameters.Refresh
Set objParams = objCmd.Parameters

objParams.Item(0).Value = strAccountID
objParams.Item(1).Value = blnIsPrimary

Set objRs = objCmd.Execute

...

Set objRs  = Nothing
Set objParams = Nothing
Set objCmd = Nothing

It works exactly the same way for Inserts and Updates, just (obviously) without the objRs recordset.

If you’re getting weird errors, the Very First Thing to check is that the number of question-marks in the command string is equal to the number of parameters you’ve added.

And if you’ve ever wondered, the “DNL” that you see all over the place in SalesLogix OTB code stands for “Do Not Localize”—i.e., for strings which are just used internally and cannot be translated into other languages without having hugely detrimental effects on the program, as opposed to UI strings which can/should be localized into the user’s preferred language.

So it’s not just some developer’s initials. 🙂


Data Forms

June 23, 2010

Say that you’re doing the same-old, same-old thing of launching a data form as an add/edit view off of a grid on a tab. But in addition to the data-bound fields for the record you’re adding/editing, you also need to populate/update a one-to-many table—for example, adding/updating one record per user on a given team, in that 1:M child table.

You could do that SQL update in the grid’s OnAddedRow or OnEditedRow events, by passing the record’s ID value back down in a global variable; but conceptually that’s not where the SQL belongs. Rather, you’d like to be doing the 1:M update in the data form itself. But the problem is that any code you put into the OK button’s click event will run even when the form fails validation (so the form doesn’t close on its own, but just sits there, mocking you). And the AXFormValidate doesn’t return a value until after it’s finished running; and again, even when it returns False, the OK click event code still runs.

The way around that is to iterate through the controls on the form, looking for ones which have their Required property set, but no value entered by the user. If that validation fails, then exit the click event without running the 1:M SQL:

Function ValidateForm

     Dim i
     Dim blnValidated

     blnValidated = True
     On Error Resume Next
     For i = 0 To ControlCount - 1
          Select Case TypeName(Controls(i))
               Case "PickList", "Memo",  "LinkEdit", "DateTimeEdit", _
                  "Edit", "LookupEdit"
                    If Controls(i).Required And _
                       Controls(i).Text = "" Then
                         blnValidated = False
                         Exit For
                    End If
          End Select
     Next
     On Error Goto 0

     ValidateForm = blnValidated

End Function

Sub cmdOKClick(Sender)

     ...

     If Not ValidateForm Then
          Exit Sub
     End If

     ...

     ' SQL for doing 1:M Insert/Update

     ...

End Sub

It’s kludgy, but because you’re iterating through the controls looking for blank Required ones rather than hard-coding that test, it doesn’t make the form any less maintainable: As long as any new control’s Type is listed in the Select Case statement, you don’t even need to know that that validation function is there.