Customer FX

New Promotions from Sage for SalesLogix and MAS 500

Brianna Tinjum — Thu, 02 Jul 2009 20:28:00 GMT

Don't miss out. Yesterday I received a Sage Business Partner email that announced new Sage promotions for SalesLogix and MAS 500. The email didn't specify how long the promotions last. If you are interested, contact your Business Partner. ...(read more)

OnCompletedActivity an oldy but goody- SalesLogix 6.2 VBScript

Kris Halsrud — Thu, 02 Jul 2009 16:38:17 GMT

We recently had a request to do some customizations for a client who wanted some validation done when completing an activity. The only problem was they were on 6.2. As you might know the Activity screens in SalesLogix 6.2 are part of the application and are not accessible to be modified. Worse, until SalesLogix 7.0 the Application.Activity class was not available nor was the Global Script dealing with activity events.

I had to search the old memory banks (well actually some old emails) but I finally found what I was looking for. In SalesLogix 6.2 there is a VBScript called OnCompletedActivity that runs when a SalesLogix activity is completed. This script allows you, in a minimal fashion, to add customizations to the activity completion events in SalesLogix 6.2. A couple of things to point out:

The event that this runs under is only when converting an activity to history. It does not offer any other hooks. It does however work with completing an unscheduled activity.
This script is a dead end. Once you upgrade to 7.0 this script becomes obsolete. Instead more robust functionality is offered (a good thing).

In case any one out there is still working in SalesLogix 6.2, hopefully this post can help if you need to do some customizations around the activity area.

Jul 20, 2009 - Global Replace - Updating Records

Brianna Tinjum — Thu, 02 Jul 2009 14:39:35 GMT

Attend the Workshop - Global Replace - Updating Records!

Event details:

Monday, July 20, 2009
From 2:00 PM Central to 2:30 PM Central
(7:00 PM GMT to 7:30 PM GMT)

See Workshop Details

Register Today!

Updating SalesLogix Opportunity Summary Crystal Report

George Jensen — Thu, 02 Jul 2009 14:09:00 GMT

So we have about 20 reports left to update as move onto the SalesLogix Opportunity Summary Crystal Report. I reviewed this report in SalesLogix Opportunity Reports (Part 2) dated January 13th of 2009. This is what I wrote about todays report.

Start Excerpt..................

The Opportunity Summary report is a simple little report that provides basic Opportunity information. But this simple report has some issues, First, when you run the report you are asked if you want to display each "Account" on a new page, Here again this parameter does not do what it indicates it should. The parameter should read each "Opportunity" on a new page. There is no reference to the Account in the groupings with in this report. Also, if you look you see the date fields are showing both date and time. One other suggestion for this report would be to add a parameter on whether or not to include History items.

End Excerpt...................

I wrote the review using version SalesLogix 7.2.2, I am adjusting the reports in version 7.5.1. So as in this case I find that some of the issues have been fixed. The version of the report I am working does honor the parameter on whether or not to display the Opportunity on a new page. No problem, there are still some things that can be adjusted on this report.

As with a lot of the SLX reports there is plenty of white space to eliminate, we can do the usual Landscape to Portrait change along with the realignment of fields. I would like to add more Account level detail. I find most of my clients like to know what City and State the Opportunity is for. The date issue still exists. My point of view on Dates is that time is not important unless we are looking at Activities or History where it is important for duration. But knowing the exact time a Opportunity record was created does not seem like it would be very important. Therefore I often change those date fields to format that fits the current use of the field in the report. In this instance I will change the three date field to just display the date. If you look in the upper right hand side of the report you will see the words "With Notes". This, in my opinion, means there should be some option on whether or not to view History or Opportunity Notes.

So lets get to work..........

In order to make adjustments to Crystal Reports in SalesLogix (SLX) you need to have access to a machine with the SLX Architect and Business Objects Crystal Reports installed. You will need your SLX Admin Password from your Administrator.

First thing to do is log into into the SLX Architect. Close the Open Project window that first opens and open the Manage Crystal Reports view via the Manage/Reports menu item. Highlight the Forecast by Account Manager report under the Opportunity family and select the Check Out icon using the tool bar icon on the top of the view. Then select the Launch Crystal Report System icon on the tool bar located all the way to the right.

The next thing I do is select the key combination of CTRL R which should run the report. This automatically establishes the connection to the database so I do not have to create one and then allows me to immediately see the effects of my change after I make it. Now if you look at the Crystal report interface you will see two tabs right under the report name, "Design" is where you make modifications and "Preview" runs the report with data. You can make modification in the Preview tab but when the change is made the report automatically refreshes the data. In some larger reports I have seen Crystal crash because I made to many changes to quickly. Trust me this is an issue if you have not saved in a while. So I have gotten into the habit of making changes under the design tab only and of course saving a lot.

To change from Landscape to Portrait, simply right click on any white space on the report and select "Page Setup" from the list that appears. When the interface opens, select the Portrait choice and close the interface.

If you navigate to the Design tab you will see some of the fields are now running off of the report. So it is a good time to realign and our additional fields. To add in the City and State fields. Since the Address table is not currently attached to the report. To add this table I clicked on Crystals Database menu and selected "Database Expert" from the list that appears. I found and highlighted the Address table in the list on the left and selected the arrow pointing to the right located near the center of the open interface. Then I selected the "Link" tab at the top of the open interface, when the tab opened I found that Crystal made the correct link to the Account table and all I had to do was make it a left join which is done by clicking on the line forming the link between the tables and selecting the "Link Options" button located on the right side of the interface. Selecting OK, I am returned to the report, I searched for the field labeled CityStZip in the Address table, I then dragged the field onto the report and located the field under the Account name in Group Header 1b.

I decided to make three columns of Opportunity related information. I used the Opportunity Description field as the field to align to. To adjust the date fields to display just the date instead of the date and time I held down the CTRL key and clicked on each of the fields I wanted to format and right clicked on one of the fields and select "Format Objects" from the list that appears. Find the Date/Time tab, you can choose whichever style of date you wish too, I use the 03/01/1999 style the most. Once your style is selected you can close the Format Editor and see the result.

I then created a new section under group header 1, this one is C, then added the Opportunity Notes field to the report.

I then worked to get the History Sub report to align within the Portrait space. To use less space I also formated the DateTime field to display using the 3/01/1900 12:00 format. I aligned the fields in the History subreport to the main report. I spent quited a bit of time squeezing all of the fields together and ended up removing the Result field.

So now I need to add the parameter that controls the Notes appearing in the report. Here I am assuming that Notes mean the Opportunity and History Notes fields. I noticed in the list of parameters that a View Notes parameter already existed, So now all I need to do is create the formulas. I created the following formula for suppressing the Opportunity Notes field.

If (IsNull({OPPORTUNITY.NOTES}) or {OPPORTUNITY.NOTES}="")
or {?ViewNotes}= "No"
Then True
Else False

This formula to suppress for the History Notes:

If (IsNull({HISTORY.HISTORYID}) or {HISTORY.HISTORYID} = "")
or {?Pm-?ViewNotes} = "No"
or {PICKLIST.TEXT} = "Database Change"
Then True
Else
False

I also added this for the label "With Notes"

{?ViewNotes} = "No"

Well-here is the report with Notes.

Looking at the "Notes:" after the "History Notes" label, I checked the database and the entry is in the data.

Download the updated Report here and thanks for reading.

Get all of our Free SalesLogix Reports here...

Hey - Did you know?

The American flag was adopted in 1777 and originally called The Stars and Stripes. Over the years, tradition has given meaning to the colors. RED is for hardiness and valor. WHITE is for purity and innocence. BLUE is for vigilance, perseverance and justice.

Why is the flag backwards on a soldiers right arm?

This flag is symbolic of the men who used to march into battle with an actual flag. So steady and strong that the flag would blow behind them. On the arms of our soldiers, the flag faces as if being carried into battle, blowing behind them.

Enjoy your freedom and have a Happy 4th of July. Geo

SageCRM has the ability to connect to external databases

Scott Weber — Wed, 01 Jul 2009 15:17:00 GMT

With SageCRM, you can view data from external sources such as your accounting system...(read more)

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!

Handy code snippet for setting current group in SalesLogix 7.5 web

Kris Halsrud — Wed, 01 Jul 2009 12:08:00 GMT

I ran across this little line of code while looking for something else in Reflector. The Business Rules assembly in SalesLogix 7.5 has a method in the BusinessRuleHelper class called FlushCurrentGroupSetDefault that allows you to essentially set the current group of the entity you are on by passing to it the string of the group name you wish to open, as shown here:

Sage.SalesLogix.BusinessRules.BusinessRuleHelper.FlushCurrentGroupSetDefault("ALLTICKETS");

Finishing SalesLogix Opportunity Detail Crystal Report

George Jensen — Tue, 30 Jun 2009 15:10:00 GMT

As we continue adjusting the Crystal reports that come with a standard implementation of Sage SalesLogix we come to the Opportunity Detail report. I wrote about this report in a blog post on January 6th 2009. Here is the link if you want to revisit the post. This is what I wrote on the "Opportunities Detail" report.

Start Excerpt........................

The first thing I notice when the report opened the report is written to the width of portrait but saved in landscape leaving a lot of white space on the right side. This could be adjusted by tightening up the Opportunity header information and formatting the date fields correctly then resetting the page set up. I like the way the report holds each group together so there is no doubt what section of the report you are on. I see some of the sub report data gets cut off on the right side. This report can be really long so I will just show the first couple section for this one Opportunity.

End Excerpt....................................

Not a lot to write about today. This report is already well structure, it was just not finished to fine level of detail. Basically all I am going to do is take this report from Landscape to Portrait and play with alignments and formats of the fields. I hope you like the outcome.

Download the updated Report here and thanks for reading.

Get all of our Free SalesLogix Reports here...

Please - Keep Smiling.

Creating a New Contact Process in SalesLogix

Brianna Tinjum — Mon, 29 Jun 2009 05:02:00 GMT

Learn how today! Creating contact processes in SalesLogix can be a great way to simplify the tasks your users perform everyday. In today's workshop, "Creating a Contact Process - SalesLogix v6.2 and Above", you will learn how to create simple contact ...(read more)

Missing Group Members Issue Fixed in SalesLogix 7.5.1 Hot Fix 6

Mark Duthoy — Thu, 25 Jun 2009 22:00:00 GMT

In the SalesLogix 7.5.1 web client we found an issue with breaks in group members when there are multiple per-Account group members. ...(read more)

Jul 13, 2009 - Marketing Campaigns - SalesLogix v6.2 and Above

Brianna Tinjum — Thu, 25 Jun 2009 21:32:28 GMT

Attend the Workshop - Marketing Campaigns - SalesLogix v6.2 and Above!

Event details:

Monday, July 13, 2009
From 2:00 PM Central to 2:30 PM Central
(7:00 PM GMT to 7:30 PM GMT)

See Workshop Details

Register Today!

Customer FX June 2009 Newsletter

Brianna Tinjum — Thu, 25 Jun 2009 17:28:00 GMT

If you missed it... Last week I sent out the Customer FX June 2009 Newsletter. If you are not on my mailing list, but have an interest in viewing the lastest news, you can still do so. The newsletter is attached, enjoy! ...(read more)

Further Updating the SalesLogix Open Opportunities by Account Crystal Report

George Jensen — Thu, 25 Jun 2009 14:18:00 GMT

Today we will work with SalesLogix Open Opportunities by Account Crystal Report. The blog post on January 6th 2009 details the minor issues I discovered with this report. Here is the link if you want to revisit the post. This is what I wrote on the "Open Opportunities by Account" report.

Start Excerpt...........................

The Open Opportunity By Account uses the condition fields of Modifyuser and ModifyDate. The data in the report seems in line with what I would expect. I see the same issue with the Est. Close Date field (Formating) and I would work the Sales Potential percent sign into the report using a formula field just to make things look a little better.

End Excerpt..............................

The orginal post was written using a 7.2.2 version of SalesLogix and I am happy to report that the 7.5.1 version of this report was updated fixing the date formatting issue. The percent sign for probability was also addressed by simply adding the percent sign in the label and removing it from the details section of the report. This version of the report is update with a new color scheme and the subtotal are much more to the point. Looks pretty good.

For the heck of it I am going to make some changes to the report just because I can. There is way to much white space on the report because it is formatted to Landscape, I will switch to Portrait and close everything up nice and tight. I will also put the percent sign with probability, why should you have to go to the label to see that it is a percentage you are reading.

To change from Portrait to Landscape simply go to the File menu and select "Page Setup" from the list that appears. To see the fields that are now off to the right in the report you will need to navigate to the reports Design Tab. To get everything to fit on the report I changed the Font size for the details section of the report to 8 and the labels located in the Page Header to 9 (Bold does not work in the 8 font size). I aligned all fields and their labels starting from left to right until they were all again on the report.

Because it is possible to overwrite the Description field on the report I set it to grow by right clicking on the field and selecting "Format Field" from the list that appears. Navigate to the Common tab located all the way to the left on the Format Editor interface, you will see the "Can Grow" check box, set this to checked and close the editor.

To add the percentage (%) sign to the Probability field, I simply created a label with the symbol inside set to 8 font and then I dragged the existing field into the new label. I then went up to the label for the field and removed the percent sign.

I also added in the City, State, and Zip code fields. Now the Address Table is not currently attached to the report. To add this table I clicked on Crystals Database menu and selected "Database Expert" from the list that appears. I found and highlighted the Address table in the list on the left and selected the arrow pointing to the right located near the center of the open interface. Then I selected the "Link" tab at the top of the open interface, when the tab opened I found that Crystal made the correct link to the Account table and all I had to do was make it a left join which is done by clicking on the line forming the link between the tables and selecting the "Link Options" button located on the right side of the interface. Selecting OK, I am returned to the report, I searched for the field labeled CityStZip in the Address table, I then dragged the field onto the report and located the field under the Account name in Group Header 1. I already had relocated the Account Manager and Region fields to the right.

Finally, I made sure to align the total area for both the Account group and the Report Footer. Nothing big here I know but what the heck, it's kind of fun.

Here is the screen shot of the update report.

Download the updated Report here and thanks for reading.

Get all of our Free SalesLogix Reports here...

Please - Keep Smiling.

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 the Picklist assembly to get Picklist details in SalesLogix 7.5 Web

Kris Halsrud — Wed, 24 Jun 2009 11:50:00 GMT

While there are many ways of querying data in the SalesLogix entity model, including using the DataServices, or IQuery to run a direct query to the database, SalesLogix also has provided a Picklist assembly for working specifically with picklists. If you take a look at the entity model you will notice that there is no IPicklist assembly. Working with Picklists are a common enough thing for any SalesLogix developer. Wether it be populating a control with picklist values or doing a lookup to get picklist's text, short text, picklistid, or itemid values, all of these things can be accomplished by using the Sage.SalesLogix.Picklists assembly.

The picklist assembly defines a Picklist class (Sage.SalesLogix.PickLists.PickList). This class exposes the typical properties about a picklist entity including:

Code
CreateDate
CreateUser
DefaultIndex
Id (same as ItemId)
ItemId
ModifyDate
ModifyUser
PicklistId
Shorttext
Text
UserId

It also contains several methods that can be called to return a specific Picklist object or a collection of them.

Lets take a look at one I recently had to use.

In the Support area of SalesLogix (Tickets in particular) there are several fields that store the picklist code rather than the text value. One of these is Ticket Status.

Now if you tried to run a server side script to get the contents of the Ticket Status' picklist such as

if(pklStatusCode.PickListValue == "Open")

It would never return true. This is because since the picklist stores the picklist code instead of the text, what it actually would need to say is

if(pklStatusCode.PickListValue == "k6UJ9A000039")

In this example the ID represents the picklist's ItemId value, which is the unique value for the item in the picklist table. Since picklist ItemID's can be different between databases (They are sequentially generated at the time the picklist entry is created based on the key index of the database at the time) it is not a good idea to hard-code a specific value.

So how can we do a quick lookup to get what the correct ItemID value is? Use the Sage.SalesLogix.PickLists assembly, of course!

Lets take a look at one method in particular, called "GetPicklistItemByName". This method accepts two inputs, the name of the picklist containing the value you want to query and the text value of the item you want to query. It ouputs a Sage.SalesLogix.Picklists.Picklist object. Lets take a looks:

Sage.SalesLogix.PickLists.PickList er = Sage.SalesLogix.PickLists.PickList.GetPickListItemByName("Ticket Status","Closed");

So in this sample I am going to get a Picklist object returned to me that represents the picklist item in the "Ticket Status" picklist where the item's text = "Closed". Now with my object I can then query any of its attributes. Here is full sample:

Sage.SalesLogix.PickLists.PickList er = Sage.SalesLogix.PickLists.PickList.GetPickListItemByName("Ticket Status","Closed");
if( pklStatusCode.PickListValue == er.ItemId)

There you have it. A very easy way of getting the picklist details about a particular item. Spend some time looking at this assembly. There is a lot it can offer when you need to work with picklists.

Updating the SalesLogix Forecast by Account Manager Crystal Report

George Jensen — Tue, 23 Jun 2009 14:22:00 GMT

Well I made it through two very challenging weeks of Annual Training and I am ready to restart our efforts on fixing and adjusting the standard Crystal Reports that come with an implementation of Sage SalesLogix . I wrote a blog post on January 6th of this year. Here is the link if you want to revisit. This is what I wrote on the "Forecast by Account Manager" report.

Start Excerpt ............................

Next is the Forecast by Account Manager, this report uses the condition filter fields of AccountManagerID and Estimated Close. When you run the report you are prompted to answer two report parameters.

There are a couple of issues with the parameters, the first parameter asks you if you want to see the statistics for each Account, it should actually read for each User. You will see in the screen shot that the data is User based, not Account. Next the second parameter asks if you want to display the graph, it displays the graph with either the "Yes" or "No" selection. This issue exists because the suppress formula is for the report footer B which contains nothing for the report. The graph is actually located in Group Header 1 and that is where the suppress formula should written to.

There are two other items that could be adjusted to make the report a little tighter. The Est. Close Date field could be formatted to display just the date. Also, where the Account is displayed in header of group 2, we could set that section of the report to underlay the detail section which would bring the Account name in line with the Opportunity data for that line. Here is the screen shot:

End Excerpt ................................

These are all pretty minor issues. Because of comments I have received during recent phone calls I realize lately I have been negligent on detail so I will try a little harder to make sure these posts meet their intent of instructing. I will start this blog by reiterating some of things I have written in previous posts.

In order to make adjustments to Crystal Reports in SalesLogix (SLX) you need to have access to a machine with the SLX Architect and Buisness Objects Crystal Reports installed. You will need your SLX Admin Password from your Administrator.

To fix the first issue we simply need to adjust the "Prompt Text" property of the "View Statistics" parameter field which can be founded in Crystals Field Explorer. If the Field Explorer is not visible then you should go to the Crystal menu bar and select "View" and then Field Explore from the list that appears. This should open the Field Explorer on the right side of the Crystal Interface. Look for the label Parameter and click on the plus sign, when tree opens right click on the appropriate label and select "Edit" from the menu that appears. To adjust this parameter I added the word "Manager" to the end of the statement and capitalized the word "Account".

The next issue is caused by the parameter being applied to Report Footer b which is an inactive section of the report. To fix this I am going to right click on the gray area just to the left of the report and select "Section Expert" from the menu that appears. Highlight the section labeled "Report Footer b" from the list on the left. On the right you will see the "Suppress" check box with a button labeled by X-2 with a pencil underneath in red font, this button opens the Formula Workshop for the selected section suppression formula. Click on this button, highlight and Cut the existing formula from this section then save and close the Formula Workshop. Now select the "Group Header #1" from the list on the left and select the button across from the Suppress Check box and paste the formula into the Formula Workshop. Now Save and Close the Formula Workshop and go ahead and test the report. I noticed right away the Account Manager and Labels for the report now also disappear when I selected "No" to the parameter, so I added a new sections to the report by right clicking on the Group Header 1 and selecting "Insert New Section Below" from the list that appears. I dragged the fields and labels into this new section leaving only the graph in the area that is suppressed by this parameter.

To adjust the Close Date to display just the date instead of the date and time simply right click on the field and select "Format Field" from the list that appears. You can choose whichever style of date you wish too, I use the 03/01/1999 style the most. Once your style is selected you can close the Format Editor and see the result.

I am going to rescind the last issue I mention in the blog but I am going to make some major adjustments.

First of all I noticed in the database I am working with that the Days Open field is just returning a 0 for each record. Thinking about this a little I decided to to write a new formula but I want to warn you this might need to be adjusted based on your Opportunity Status Values your company uses. Once again we need to go to the Field Explorer, right click on the listing for "Formula Fields" and select "New" from the list that appears. Label the formula "DaysOpen" and select OK, when the Formula Workshop opens you are ready to create your new formula. Below is mine:

if {OPPORTUNITY.STATUS} = "Open" then DateDiff("d",{OPPORTUNITY.CREATEDATE},CurrentDate)
else DateDiff ("d",{OPPORTUNITY.DATEOPENED},{OPPORTUNITY.ACTUALCLOSE})

This one basically says if the Opportunity Status is "Open" then figure the number of days the Opportunity has been open by determining the date difference between the OPPORTUNITY.CREATEDATE and the CurrentDate. If anything else then use the OPPORTUNITY.DATEOPENED and the OPPORTUNITY.ACTUALCLOSE of the Opportunity to determine the number of days the Opportunity was open.

With the formula created, I dragged it into the report replacing the original field and formated the number to display without the decimal point. I also updated the summary fields in the Group Footer 1 and Report Footer to use this new field instead of the original.

With some focus still on formulas I updated the F_Close_Date formula to read:

If {OPPORTUNITY.STATUS}= "Open" then {OPPORTUNITY.ESTIMATEDCLOSE}
else {OPPORTUNITY.ACTUALCLOSE}

Since this formula field is already included in the report all I will need to do is align the field with the others as I make adjustments. But I added a label with a star in it next to this date field. If the Opportunity Status is anything but "Open" the star label is suppressed. This allows me to tell the user that the date they are seeing is the Estimated Close if the status = "Open"

I also created a new formula called Forecast to help replace the "T" value meant to indicate whether the Opportunity has been included on the forecast or not. That formula is:

If {OPPORTUNITY.ADDTOFORECAST} = "T" then "Forecasted"
else "Not Forecasted"

Once created I replaced the field that displayed the "T" value with this new formula field.

I also scrolled through the list of Opportunity fields and added the Description field to the report. From left to right I now added labels and aligned fields as necessary across the report. With all of the mods completed I think the enhancements made this report a better one: Here is a screen shot without the graph.

If I were going to implement this report into my system I would define and create the rules that define how far back to collect records for the report. Currently the report displayes all Opportunities in the system.

Download the updated Report here and thanks for reading.

Get all of our Free SalesLogix Reports here...

Please - Keep Smiling.

User Configuration

Dale Richter — Tue, 23 Jun 2009 14:05:00 GMT

I was working with a Client this week on SAGE CRM.com. Although this product looks similar when looking at the interface. They are several very large differences when you take a closer look. In Sage CRM you have an area for High Level User Configuration. This area is not available when you are using Sage CRM.com. All of these settings have been pre-set and are not accessible because you do not have direct access to the server. The .com package definitely has it place and is a great solution for many...(read more)

SageCRM Web Lead Capture

Scott Weber — Mon, 22 Jun 2009 14:26:00 GMT

All editions of SageCRM come with the ability to create and activate a web lead capture screen. New leads are critical to most companies so why not enable your prospects to communicate their interest in your products and services while they are browsing your web site? Here is a screen shot of the very simple set up: Also provided is a complete HTML template that you can copy and paste into your corporate web site which saves time in getting the form set up. Of course you can modify the form to fit...(read more)

Create New Sales Processes in SalesLogix

Brianna Tinjum — Mon, 22 Jun 2009 05:02:00 GMT

Now is your chance to learn how! Have you always wanted to create your own Sales Processes in SalesLogix but weren't sure how? If so, today is your lucky day. We are holding the workshop, "Creating a New Sales Process - SalesLogix v6.2 and Above", at 2pm ...(read more)

SQL Server SSIS configuration issues

Kris Halsrud — Fri, 19 Jun 2009 14:43:55 GMT

Recently I ran into a problem where I was unable to load a SSIS package into a SQL 2005 Server instance. I received the following error:

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 Books Online.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

After a little bit of Googling I found the problem to be a configuration file that was not set up properly. There is a file called MsDTSSrvr.ini.xml that resides in the folder C:\Program Files\Microsoft SQL Server\XXX\DTS\Binn, where XXX is the numeric equivalent of the SQL server version running. In this file there is an XML markup for the server name. In my case it looked like this:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

Notice the Server name tag used the "." which denotes the default instance. Unfortunately my SQL instance I was attempting to load my SSIS package into was not the default instance.

Changing the XML file to have my instance allowed me to add the SSIS properly. Please note you also need to stop and restart the SQL services in order to have this change take place:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>MYSQLSERVER\MYINSTANCE</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

SalesLogix 7.5 Web Upgrade Role Fix

Mark Duthoy — Fri, 19 Jun 2009 12:00:00 GMT

During a recent trial upgrade from SalesLogix 7.2 Web to 7.5 Web we encountered the following error while browsing through the web client- "No row with the given identifier...(read more)

SageCRM - To Host or Not to Host

Brianna Tinjum — Thu, 18 Jun 2009 20:55:00 GMT

That is the question. If you have been considering SageCRM as a possible CRM system you may know that you have the option of having it hosted or on-premise. It is nice to have options, but how do you know if SageCRM or SageCRM.com is the right choice for you? ...(read more)

Jul 07, 2009 - Creating and Fulfilling Literature Requests - SalesLogix v6.2 and Above

Brianna Tinjum — Thu, 18 Jun 2009 15:34:01 GMT

Attend the Workshop - Creating and Fulfilling Literature Requests - SalesLogix v6.2 and Above!

Event details:

Tuesday, July 07, 2009
From 2:00 PM Central to 2:30 PM Central
(7:00 PM GMT to 7:30 PM GMT)

See Workshop Details

Register Today!

Sage SalesLogix ERP Link Sync

Kris Halsrud — Thu, 18 Jun 2009 13:50:00 GMT

In addition to the client side aspect of ERP Link there is a "server" side component that allows the ERP Link Queries to run and populate the data in SalesLogix. The queries provide the data that you see in SalesLogix for a customer like YTD sales, open payments, open invoices, etc. These data queries can only be executed through the ERP Link Sync utility. If you do not set up and run this utility, you will not see your financial data in SalesLogix!

ERP Link Sync is basically a run command wrapper around the SalesLogix client that allows the client to be launched through the windows task scheduler and execute the data queries load steps.

The machine that you install ERP Link Sync on must have:

SalesLogix client installed
MAS 500 installed
Windows XP SP2 or Windows 2003 Server SP2 are the specified OS platforms.

In order to correctly set up and run ERP Link Sync you must first do the following:

Install ERP Link Sync. There is a setup.exe that is straight forward to install. This needs to be installed on the computer where the scheduled task is run from.
You must log into the computer, where the task will be scheduled, as the Windows user account that will be used to run the scheduled task. Each windows account records the SalesLogix alias login information in the registry settings for that local user. Also you need to ensure that this account has rights to all of the components used when accessing SalesLogix (Sync folders, document paths, etc.) To do this:

Log into the computer as the user account.
Go to Start…Programs…SalesLogix. On the SalesLogix login screen, create the log in alias. The actual alias name you need is what will appear in the “Log in to” box on the main login page.
Go ahead and login to the SalesLogix client. Make sure it opens OK.

You must configure the ERP Link login parameters within the SalesLogix client for the user that will be used to log into the ERP Link Sync. I would recommend you use "Admin" for this user. To do this follow these steps:

Log into the SalesLogix client with the SalesLogix user name you will use to also log into the ERP Link Sync utility with.
Configure the ERP Link Login under Tools…Manage…ERP Link...Set Login Parameters.
Go to Tools…Manage…ERP Link…Load / Refresh Accounts
Ensure you can see the list of accounts. This will ensure your login parameters are OK.

Now you can add your Windows scheduled task. To do this use the following steps:

Go To Start...Control Panel...Scheduled Tasks...Add Scheduled Task
Click Next
Click Browse
Browse to your SalesLogix install directory. A new folder will be added during the installation of Sage ERP Link Sync called ERPLink. (The default would be C:\Program Files\SalesLogix\ERPLink). Select the ERPSync.exe file. Click Open.
Name your Windows Task Job and specify the frequency (typically daily). Click Next.
Specify a time to run the task. I would recommend this be run on off hours. Either early in the day or at the end of the day, or both. Make sure that other jobs are not running at the same time. Remember your SQL maintenance plans! Click Next
On the Login screen you must login with the same Windows account that was used to log in in step 2 above. Click Next.
Check the "Open Advanced Properties..." checkbox. Click Finish.
In the run command you will need to add the appropriate switches after the existing path to the ERPSync.exe. After the closing " in the path that is there you can add the following switches. These are case sensitive:

/B - Sage SalesLogix database alias (required)
/N - Login user account (required)
/P - Login user account password (required)
/G - Account group name to synchronize. This must be a dynamic group. Sage SalesLogix ad hoc groups are not supported by ERP Sync.
/A - Sage SalesLogix account ID to synchronize
/I - Refreshes all products for all companies in Sage SalesLogix which have links to valid products in Sage MAS 500. The switch will not create new products in Sage SalesLogix.

Make sure that the user you use in the /N switch is the same one used in step 3 above. Again I recommend "Admin".

I would also recommend the group to use in the /G switch is not "All Accounts" but rather a group you create of just your customer accounts. There is no need to have the system search through every account in your database. Note that if your group name has spaces you will need to enclose it in quotes.

So the final Run command field should look something like:

"C:\Program Files\SalesLogix\ERPLink\ERPSync.exe" /B=SalesLogix_Eval /N=Admin /P=password /G="My Customers"

That is it. Click OK to close the scheduled task wizard. You should now be ready to run whenever your task was set for.
If you are having trouble getting this to run I would recommend that you take the string in the "Run" field and login to the computer as the Windows user account specified in the "Run As" field. Go to Start...Run and paste in your "Run" string. This should launch the SalesLogix client and begin the ERP Link Sync. If it does not work, go back and check your settings.

SalesLogix ERP Link- An Overview

Kris Halsrud — Wed, 17 Jun 2009 11:51:00 GMT

With ERP Link, SalesLogix has the capability to integrate with MAS 500 in a more robust fashion than is possible with DynaLink. Lets take a look at the components of ERP Link.

Overview of the Components

First of all the actual ERP Link product is mainly contained in a couple of .NET extension in the SalesLogix database. These .NET extensions contain both the integration engine, with the associated rules and template logic, as well as actually containing the screens that a user of SalesLogix sees that displays the back office information.

These .NET plugins are embedded into several standard "shell" SalesLogix form plugins. These shell plugins are empty and simply provide a way for the ERP Link .NET extension screens to be embedded into. This shell approach allows for these custom tabs to be controlled via the normal SalesLogix release mechanism, while at the same time allows a more rich GUI feature set for presenting the ERP Link Data.

The ERP Link module also consists of some traditional SalesLogix form plugins, used to display or configure attributes for the integration.

These components, along with the ERP Link table structure are what is installed with the ERP Link bundle and consist of the entire solution.

As mentioned, the following shell plugins exists. These are simply containers into which the .NET forms embed themselves.:

ERP Link Query Data
ERP Link Order Details (The opportunity sales order grid form)
ERP Link Contract Reference
ERP Link Contact Reference
ERP Link Account Reference

The following are standard SalesLogix plugins used to display or configure data related to the back office integration:

ERP Link Account Financial Data
ERP Link Site Reference
ERP Link Product Status
ERP Link Manage Sites
ERP Link Address Reference
ERP Link Add Edit Site
ERP Link Account Item Sales History
ERP Link Account Sales History
ERP Link Account Financial Details

The following database objects are added for ERP Link:

Table ERPXREFTABLES
Table ACCOUNTREFERENCE
Table ADDRESSREFERENCE
Table CONTACTREFERENCE
Table PRODUCREFERENCE
Table SALESORDERREFERENCE
Table CONTRACTREFERENCE
Table SITEREFERENCE
Table SYSCOLLABORATION
Table SYSAPPLICATION
Table SYSIDENTITY
Table SYSOPTIONS
Table ERPLOGIN
Table ERPPROFILE
Table ERPUSERPROFILE
Table ERPUSERPROFILEMAP
Table ERPCONTEXTCALLS
Table ERPDATARECORD
Table ERPDATAQUERY
Table ERPPRODUCTMAP
Table PRODUCTSTATUS
Table CONTRACTINVOICE
Table CONTRACTPOSTING
Field TICKETACTIVITY.BILLEDAMOUNT
Table ERPTERMS
Table SALESHISTORY
Table ITEMSALESHISTORY

Configuration

Once installed, to configure ERP Link you use a series of wizards that are also contained in the SalesLogix ERP Link .NET extensions. All of these wizards are accessed through a new custom SalesLogix client menu, found under Tools...Manage, called "ERP Link".

In order to configure these wizards the computer you run them on must have the MAS 500 client installed. This is because it attempts to locate a configuration file located in the MAS client install folder that specifies the location of the MAS 500 server.

Set Login Parameters

This screen defines the MAS user account used to connect while doing the integration steps and running the various queries for returning things like inventory levels.

An important note about this screen

The login defined here is per user not system wide, so each user would need to set this up if they will be doing any of the following configurations/loading.

Set Up Wizard

Used to configure the connection to the MAS server and also to define the templates available.

Once you connect through the first screens you arrive at the template configurations.

Templates are a core concept to ERP Link. They allow an administrator to define the attributes that are assigned to an entity an MAS 500 when that entity is created from SalesLogix. Multiple templates can be created for each type of entity insertion. You can then assign a specific template to a user so that they use that template's attributes whenever they create the given entity in MAS 500. While this sounds good there are some issues with this. I will touch on that in a future post.

Each entity template comes with a default template. This is the template all users will use unless they are assigned a different template.

Customer Templates

The first entity template in the wizard is the Customer Entity template. This template is used to define the attributes that are populated when a SalesLogix Account is moved into MAS 500 as a Customer.

When you edit the template, you can see what attributes can be defined. These attributes are intended to fill in fields that are necessary to describe a Customer in MAS that do not have a corresponding SalesLogix Account equivalent.

Each of the attribute fields here are directly linked to the MAS 500 system (based on the defined company code). When you click an ellipse on any of the fields you will be presented with the valid entries from the MAS 500 system.

Contact Templates

The template which is used when inserting a new Contact into MAS 500 from a SalesLogix Contact.

Address Templates

An address in MAS 500 can be associated to a Customer, Contact, and Sales Order or Sales Order Line item. This template is used when creating these entities from a SalesLogix Address record.

Invoice Templates

Invoices are created from Support Contracts in SalesLogix. This template defines those attributes used to create a MAS Invoice from a SalesLogix Support Contract.

User Mappings

On any of the template screens you can select the "users" button to assign users to the currently highlighted template. A user can only be assigned to one Template per entity.

Entity Mapping Wizard

The entity mapping wizard allows you to define specific field data that should be moved between SalesLogix and MAS 500. You may also specify which direction these mappings occur (From SalesLogix to MAS, from MAS to SalesLogix, or bi-directional).

Each of the possible entity creation areas can have the field mappings modified.

Clicking the ellipse next to one of the entities will open the field mappings for that entity creation.

In the mapping's add/edit view you can identify the SalesLogix field or literal value, The MAS field or literal value, the direction which the data flows, data translation functions and conditions to apply to the data.

Also on the Template Select screen, is a button called "Transformation Tables". A transformation table is a named list of mapped values. Transformation tables are valuable to ensure data integrity when mapping values between Sage SalesLogix and Sage MAS.

Transformation tables are also necessary for data in Sage SalesLogix fields that is larger than what is accepted by Sage MAS. For example, the country field in Sage MAS only accepts three characters. The same field in Sage SalesLogix accepts free text. A transformation table is necessary to map the Sage SalesLogix country to a two character code accepted by Sage MAS.

When the Transformation Tables button is clicked, the manage dialog opens. From here you can add, edit, or delete transformations:

Data Query Wizard

Data queries are queries built against the MAS 500 system used to load the results of the query into tables within SalesLogix. These queries are what is used to map the financial history details from MAS to SalesLogix, such as YTD Sales, Open Payments, Open Orders, etc.

There are two types of queries:

Mapped Queries - Data is mapped to specific tables and fields in Sage SalesLogix. The imported data can be used to create groups, mail merges, and perform other data related functions.
Ad Hoc Queries - Data is synchronized to Sage SalesLogix as XML data and mapped to a single database field as an XML blob. Data is only displayed on the Account's ERP Data tab.

You can right click and choose add, edit or delete to manipulate the queries used to populate SalesLogix.

In the Query Name box, type the name that identifies the query and displays on the ERP Data tab in the Sage SalesLogix Client.
In the Query Type box, select they data query type.
In the Data Table box, select the table in the Sage SalesLogix database that the data will be mapped to. This option is only available for mapped queries.
Click Map to open the Data Query Field Mapping dialog box to view or create the field map. This option is only available for mapped queries.
Select the Enable Record Parsing check box to remove records created in the mapped table when they are removed from Sage MAS. For example, if a sales order is invoiced as complete in Sage MAS and is removed, then the order is also removed from Sage SalesLogix. This option is only available for mapped queries.
In the Enter Query Text section, define the data query to be used to read data from the Sage MAS system. This must be a valid query for the selected Sage MAS system.
In the Parent Table box, select the parent table for the selected data query. The parent table is used to define the base relationship of the selected data.
A mapped query does not require a parent table, but if one is not selected, the imported data is not related to a specific account. A mapped query that has a parent table must use the AccountReference table as the parent. This allows the data to be associated with the account defined in the AccountReference link record.
In the Relationships grid, define the parent and child fields that create the relationship between the Parent table and the current query. Mapped queries must have a relationship defined. The relationship is automatically maintained in the Relationships table.
The query must be a child query of another query to define a relationship. Queries with no parent table or with the AccountReference parent table cannot have user defined relationships.
Click Add to add a new row to the grid.
Click Delete to delete the selected row from the grid.
Click Test to test the query.
Click Views to add Sage MAS system views to the selected query data using the Add/Edit User Views dialog box.
The views are not automatically added to the Visual Basic Forms created to display data. However, information is stored which allows the views to be opened if the required code is added to the Visual Basic Form.

Load Refresh Products

This utility is used to configure which products from MAS are mapped into SalesLogix products. This screen allows you to specify which MAS Company you wish to pull products from. It then allows you to see which MAS items are linked (highlighted in green with a Linked checkbox) and allows you to Select new MAS items to link to SalesLogix products (by clicking the Selected checkbox).

Select the Refresh Stock Status check box to reload the stock counts based on the configured sites (warehouses). When this option is cleared and you load products from Sage MAS none of the Warehouse stock numbers will be associated to the product. To associate stock numbers, you must select this option and refresh the product again.

This dialog also allows you to define the field mappings used when a MAS item is created as a SalesLogix product. This is done by clicking the Mapping button.

Clicking the Proceed button will link any newly selected MAS items. This creates a record for them in the SalesLogix product table.

Load Refresh Accounts

This utility is used to bring across customers in MAS as Accounts in SalesLogix. It can also be used to refresh the financial data of these customers.

Functionality on this screen is similar to the product screen, allowing you to see which MAS customers are already linked and allowing you to select new ones to bring across.

When the Sync All Linked Entities check box is cleared, data synchronization is disabled and only the link is created for the account . When the option is selected, data synchronization is performed. If the Sage MAS customer is added to Sage SalesLogix as a new account, this option is ignored and the full data synchronization will be performed.

Select the Refresh Accounting Data check box if you want to populate all the ERP Accounting tabs in the Account detail view.

Use the Auto Link dialog box to build a query by which Sage SalesLogix attempts to find existing records in the Sage MAS system. Auto Link finds duplicates when creating associations between Sage MAS and Sage SalesLogix. You can set selection criteria using the following fields. Use the drop-down boxes to set the query parameters.

An important notes on the Account Loading

According to the Sage ERP Link online help: "If more than one user is loading or refreshing accounts at the same time, duplicate accounts may be created in Sage SalesLogix. To ensure duplicates are not created, do not allow multiple users access to the load/refresh account function."

Conclusions

So that is ERP Link in a nutshell. The installation and configuration of ERP Link is pretty straight forward, provided you want it to do what it was designed to do. What exactly do I mean by that?

The downside (My Opinion)

Sage missed the mark in a couple of areas around ERP Link. Lets take a look at the two biggest problems

Templates

While I understand what the objectives were with the template concept (Allow an administrator to define what accounting level attributes a SalesLogix record should pass across as defaults in MAS when a new MAS record is created), the way in which it was implemented is critically flawed. Lets take a look at the Customer Template:

When defining an Account template, some of the options you can set for the account are:

Customer Class
Terms
Freight Method and Ship Via
Warehouse
Sales Person
Tax Schedule

If you remember, you can create multiple templates and then assign one template to each SalesLogix user. When creating an account, some of these options seem to be unrealistic to be stored in an account template. While I understand that these are required attributes MAS 500 will require during the customer creation in MAS, having all of these in a template will be a problem.

Consider just one of these options, Tax Schedule. It is completely unrealistic that a given SalesLogix user will create accounts that fall within only one Tax Schedule. As to Sales Person, that would mean you would need at least one template per sale person, in order for commissioning to properly be set up when a
customer is created in MAS, would it not?

Since the account attributes directly impact the pricing that a given account will receive, all of these settings will also determine what a given user will be able to price products at in SalesLogix. Again, it is unlikely that every new account a user creates in SalesLogix should have the same pricing attributes, yet since all accounts created are derived based on their Account Template this would be the case.

Also, these attributes would seem to require setting up a multitude of templates (One template per tax schedule, one template per sales person, etc.). When you then consider all of the needed combinations.

For instance: Our sales guy Jimmy sells to 15 different customer classes. Of those 15 customer classes, which actually comprise of hundreds of customers, there are 12 different sales terms involved. Jimmy also sells in 594 tax schedules. That means it would require at least 15x12x594 = 106,920 profiles just for jimmy to have a customer have a chance of getting correctly set up. Now imagine a sales force of 200 users, each with at least as many attribute combinations. The number of templates required would be daunting.

This would be assuming that one user could even use multiple templates. The rub there is that they can't. They are assigned only one template. Not good.

Another issue with the template usage is some attributes from the customer are used when posting a sales order. Specifically Terms, Freight Method, and Ship Via are all defined at the Customer Template level. This means that every Sales order posted from SalesLogix uses these same customer template attributes assigned to the SalesLogix user. Have an order that needs to ship over night? Too bad your template has UPS Ground defined. Selling a big order with special terms? Not according to your template. Along those same lines, whichever user actually pushes the order across is the user whose template is used in creating the customer/ order. What if you don't have the right sales person defined in your template?

As you can see the template implementation is seriously flawed. So much so that it renders the standard product virtually unusable.

Template persistence

OK, so we see the problem with templates. At least for existing customers in MAS the Sales Orders that post from SalesLogix will be correct, right? Nope. The other main issue with ERP Link is that the template attributes continue to be used even for existing customers. For instance, "ACME Corporation" is your largest customer and is set up in MAS with some very sweet terms, and special shipping rates. Well with ERP Link, an order processed through SalesLogix is subject to the terms and shipping details defined in whatever user's template that happens to process the order. Chance of that being correct?

So is all lost? Is ERP Link not a viable product? I wouldn't go that far. There are some ways of fixing these inherent flaws. They don't exist out of the box but they are possible.

Unfortunately, this post has gone on too long. You will have to wait for the next post when I discuss how to work around these limitations.