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

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

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

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

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

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

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

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

Sometimes all you need is a small change to convert an irritated customer. This was the case recently when one of our clients submitted a ticket about the "Check for Duplicates" button on the Insert Contact/Account screen.

It seems that even though they filled out fields on the form and pressed the "Check for Duplicates" button, the screen is displayed but no duplicates are found. I guess the argument could be made that this is a training issue, because all you need to do is check the fields you're interested in searching on and press the button again. Therein lies the source of the irritation. The client felt that filling out the form and pressing "Check for Duplicates" should do the trick.

A little sleuthing on our part resulted in a simple change involving two lines of code, also known as 'the ointment.'

Where and how to apply the ointment:

The change is applied to the code file ContactCheckForDuplicates.ascs.cs in the Sage SalesLogix portal. At first it would seem that all you need to do is check all the boxes and you're good to go, but alas, the form doesn't search when it is first opened anyway. This issue is easily solved by adding a line to the OnPreRender method as shown in the code snippet below.

    protected override void OnPreRender(EventArgs e)
    {
        try
        {
            if (Visible && DuplicateProvider != null)
            {
                LoadMatchFilters();
                LoadSourceEntity();
                Mode.Value = "Load";
                LoadPotentialMatches();
            }
        }
        catch (Exception exp)
        {
            throw new ApplicationException(GetLocalResourceObject("LoadErrorMSG").ToString());
        }
    }

The bolded line is a setting that is used by the next line "LoadPotentialMatches()" to perform the search.

Next we have to check all the check boxes to give it something to search on. This is done with a change to the "LoadMatchFilters()" method as shown in the snippet below.

            item.Value = propertyFilter.PropertyName;
            //item.Selected = propertyFilter.Enabled;
            item.Selected = true;
            chklstFilters.Items.Add(item);

Again, the bolded line is the change to make. Be sure to comment out the line above it. 

Now granted this is not an elegant hack, but our goal is to minimize costs and apply a little soothing ointment to a minor irritation.

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

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

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

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

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

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

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

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

In Microsoft Dynamics CRM 2011 it is possible to pull data from a sub-grid on a parent form when a page loads to read the values.

Place the script below inside a Web Resource and call the function "calcPortfolio" on a "OnLoad" event

function calcPortfolio()
{
                var portAmount = 0.00;
                //name of the sub-grid
                var gridControl = document.getElementById("SubGridName").control;                 var ids = gridControl.get_allRecordIds();                 for(i = 0; i < ids.length; i++)                 {                                               var cellValue = gridControl.getCellValue('SubGridField', ids[i]);                                 // cellValue has a datatype of Currency
                                cellValue = parseFloat(cellValue.replace(/[$,]+/g,""));                                 portAmount = portAmount + cellValue;
               }                 var Name = Xrm.Page.data.entity.attributes.get("fieldOnParentForm");
                Name.setValue(portAmount);

}

Adding a database view into the SalesLogix web architecture can be a tricky process, due mostly to the fussiness of NHibernate. What follows is a step-by-step guide to help you through the process.

Problems at the end of the process can be avoided by following these rules when designing your view.

1.    The view name cannot start with the letter I.
2.    The view name and fields must be capitalized.
3.    The view fields must not have spaces in them.
4.    The view key field must be of type char(12).  You can accomplish this with a simple Cast or Convert SQL function (in MS SQL Server).

IMPORTANT: If the key field does not have a database type or char(12) you will get the error "Year, Month, and Day parameters describe an un-representable DateTime" at runtime.

Once your view is working the way you want, the next step is to enable it in DB Manager. 

1.    Open the Architect or Administrator tool and launch DB Manager.  2.    Right click on your view and select Properties.
3.    Click the “Enable” button, and click OK.
4.    Right click on the key field of your enabled view and select properties.
5.    Verify that the “Field Type” box reads “SLX Standard ID”.

I have seen situations where the view's key field is not populated in the RESYNCTABLEDEFS table. This can be easily checked with the query:

select * from ResyncTableDefs where TableName = ‘my view name’

If it is missing, then simply add it with the query:

Update ResyncTableDefs set KeyField = ‘my key field name’ where TableName = ‘my view name’

We are now ready to add our view as an entity.

The process is the same as adding any other table from the database. Note that any relationships that you create from an entity based on a database view should have the Cascade property set to "None" or "Not Applicable" since the underlying database view cannot be directly changed.

There are many situations where a user needs, or would like to have, access to web based information that is not stored in SalesLogix. We would like to integrate access to other information from within SalesLogix so the user sees a single, seamless application. A couple of approaches for this will be described that use techniques both dependent, and independent, or SalesLogix framework.

The first, and simplest, approach is to show external data on a tab in one of the main views. We've all seen this demonstrated a thousand times. All you need to do is all the SalesLogix "Dynamic Web Content" control to a form and populate the "Dynamic URL" property.

The "Dynamic URL" property supports a syntax that allows you to dynamically populate a query string on the URL with the current entity's properties in a codeless way. The syntax for referencing an entity's properties is ${.}. Here is an example from the Application Architect help file that displays the location of the current entity on a Google map.

http://maps.google.com/maps?q=${Address.Address1},+${Address.City},+${Address.State}+${Address.PostCode}&sll=37.0625,-95.677068&sspn=23.875,57.630033&hl=en&ie=UTF8&t=h&z=16&iwloc=addr

This works great if there is no variation in the resource or query portions of the URL.

Another approach would be to display the data in a new browser tab or window. There are a number of ways to do this, and we will describe one that uses JavaScript in the client browser to accomplish this task.

Our solution is to add a button to a quick form, and use the onClientClick event of the button to execute a piece of JavaScript to open a new window/tab. This seems straightforward enough. The only tricky part is to inject the JavaScript code onto the page in an ASP/SLX appropriate way. To do this we will add the following code to the form Load Actions.

Sage.Entity.Interfaces.<ISomeEntity> myentity = this.BindingSource.Current as Sage.Entity.Interfaces.<ISomeEntity>;

string sURL =
string.Format("http://www.mycompanywebsite.com.com/DataSite/GetMeInfo?frm={0}&id={1}", FormName, MyEntity.Id.ToString());

<MyButtonControlID>.OnClientClick = "window.open('" + sURL + "', '', 'directories=no,location=no,menubar=no,pageXOffset=0px,pageYOffset=0px,scrollbars=yes,status=no,titlebar=no,toolbar=yes');";

ClientBindingMgr.RegisterSaveButton(<MyButtonControlID>);

This approach has the advantage of showing the URL in a new browser window, and gives us the freedom to build the complete URL in code without the restriction of having a predefined URL template, as in the first example.

The disadvantage of this approach is that it still requires a round trip to the server, and the associated performance hit, just to execute the code. Ugly!

Our third and final approach is to get everything to run in the client browser and still live within the confines of ASP/SLX.

As you might suspect, there are tricks to getting this to work also. The first is to use the non-standard HTML attribute pin="pin" in the script tag in order to get ASP to leave our code on the page. Here is an example. We could have just as easily put our code in an external file, but we would still need the non-standard "pin" attribute in the script tag.

    <script pin="pin" type="text/javascript">  

    function showWin( someURL )

    {

        // put any preprocessing here

        window.open(someURL, '', 'directories=no,location=no,menubar=no,pageXOffset=0px,pageYOffset=0px,scrollbars=yes,status=no,titlebar=no,toolbar=yes');

    }

    </script>

Next, we need to use a control that will execute our JavaScript function, showWin(), directly without posting to the server first. Our solution to this depends on using a SmartPart, and placing our control on it. For instance, just add a simple anchor tag <a href=”javascript:showWin()” >Show the window</a> to the appropriate place on your page.

These three approaches give varying degrees of flexibility when linking to the outside world. Each has a place in the developers' toolbox to accommodate the variety of situations that may arise.

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

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


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

SQL is often overlooked for programming capabilities. The concatenation function below saved me a lot of time in application programming to simplify presenting a 1-Many data relationship.  This function has been extremely useful for me in report writing, data presentation , etc.

 
The function takes a group of related records and returns a csv of the multiple rows
 
Example…
 
 
Account(Parent Table) ParentID     Name
1    ABC
2    DEF
 
 
Contacts(1-Many Child Table)
ParentID    ContactID    LastName
1    1    Johnson
1    2    Doe
2    3    Smith
2    4    Adams
2    4    Jones
 
Query using the function…
Select Name, ConcatContacts(ParentID) as Contacts from Account
 
Returns
Name    Contacts
ABC    Johnson, Doe
DEF    Smith, Adams, Jones
 
 
 
SQL Script to create function
 
/********Script Function Begin***********/
 
USE [CMRX_SLX626Prod_JM]
GO
 
/****** Object:  UserDefinedFunction [dbo].[ConcatContacts]    Script Date: 06/17/2011 14:48:01 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
 
CREATE FUNCTION [SYSDBA].[ConcatContacts](@ID CHAR(12))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
 
SELECT @Output =        CASE @Output WHEN '' THEN LastName ELSE @Output + ', ' + LastName
END
FROM CONTACTS
WHERE PARENTID = @ID ORDER BY CONTACTID
 
RETURN @Output
END
 
 
 
GO
 
 
/********Script Function End***********/