Jason Buss' Blog

Updating text within Textblob fields in SalesLogix

2012-04-19T15:16:00Z

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

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

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

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

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

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

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

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

Thanks for reading!

Summary and GroupBy of data using IRepository and projections

2012-04-05T11:13:00Z

In the SalesLogix web client, you don't really have the same old options regarding Queries and Datasets. However, by using IRepository you can create similar functionality by using projections to define groups and other calculations.

In this case, I needed to present a dialog displaying a breakdown of completed ticket activity rates for a ticket. This was previously set up in the LAN client using this query:

select 
    b.typedesc, 
    sum(a.elapsedunits) as hourtime 
from 
    ticketactivity a 
left join 
    ticketactivityrate b 
on 
    a.ratetypedesc=b.ticketactivityrateid 
where 
    a.ticketid='123456789012' 
group by b.typedesc

Basically, this query was used to create a recordset which was looped through to build a string that was then displayed in a Messagebox.

In order to implement this in the web, I added a button to the Ticket Detail quickform, and added a C# Code Snippet to the OnClick event of that button.

First of all, I created a reference to the ITicket interface for the current record. I also used the repository helper to create a repository for Ticket Activity

 Sage.Entity.Interfaces.ITicket ticket = this.BindingSource.Current as Sage.Entity.Interfaces.ITicket;
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.ITicketActivity> repository = Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.ITicketActivity>();

Next, after setting a couple variables holding the TicketNumber and TicketID, I started building my Criteria for IRepository. Using a Projectionlist, I added a sum of the ElapsedUnits property, and grouped by the RateTypeDesc property from the TicketActivity entity. (These properties need to be visible in the entity model, otherwise this will not work. Out of the box, RateTypeDesc is not visible, so I had to modify the entity)

string sTicketNumber;
string sTicketID;
string sMessage;
sTicketNumber = ticket.TicketNumber.ToString();
sTicketID = ticket.Id.ToString();
Sage.Platform.Repository.ICriteria criteria = repository.CreateCriteria();
criteria.Add(repository.EF.Eq("Ticket.Id", sTicketID));

criteria.SetProjection(repository.PF.ProjectionList().Add(repository.PF.Sum("ElapsedUnits")).Add(repository.PF.GroupProperty("RateTypeDesc")));

System.Collections.IList result = criteria.List();

if (result.Count == 0) 
{
    sMessage = "No Time logged against ticket " + sTicketNumber;
}
else
{
    sMessage = "Time summary for ticket " + sTicketNumber + "<br><br>";
}

As you can see, I get my result and check the count to see what message I want to display. Finally, I loop through the IList to build my string. Since RateTypeDesc holds an ID for the rate found in the TicketActivityRate entity, I use the GetByID function in the EntityFactory to return the actual Rate description.

foreach (IList i in result)
{
    if (Convert.ToDouble(i[0])!=0) 
    {
    Sage.Entity.Interfaces.ITicketActivityRate rate = Sage.Platform.EntityFactory.GetById<Sage.Entity.Interfaces.ITicketActivityRate>(i[1]);
    sMessage = sMessage + (rate == null ? "None Defined": rate.TypeDescription.ToString()) + " - " + Math.Round(Convert.ToDouble(i[0]), 2).ToString() + " hours<br>";
    }
}

Once I have my string, I simply raise a ValidationException and pass the sMessage string variable. I ended up using the ValidationException because it allows you to include HTML code, which I used to include breaks after each rate.

 throw new Sage.Platform.Application.ValidationException(sMessage);

That's all there was to it. Ultimately, it ended up not being that difficult to accomplish once I started to understand Projections in IRepository.

Thanks for reading!

(not quite as) Easily Increasing the Height of the Picklist Dropdown in SalesLogix Web

2012-03-22T19:28:00Z

My colleague Ryan just posted about a simple method to increase the height of picklist dropdowns in the SalesLogix web client.

I have a slightly different way to accomplish the same thing for individual picklists, so while not really more difficult to do, would require you to do it for as many picklists you want to modify. Ryan's solution modifies a stylesheet to affect all picklists in the system at once.

In order to alter a specific picklist on a quickform, add a new C# Snippet action item to the Load event of the form, and add the following code to the action item:

string script = "var btn = document.getElementById('" + pklType.ClientID +"_Items'); if(btn) btn.size='8';"; 
ScriptManager.RegisterStartupScript(Page, GetType(), "FXSetPickListSize", script, true);

This particular code sample will modify the Account Type picklist on the AccountDetails quickform to a height of eight lines. Make sure you set the On Repaint Event and Refresh Data properties to True for the C# Code snippet, otherwise the change will not persist.

If you want to see how to do this for all picklists at once, see Ryan's post here.

Exporting Table data via the SalesLogix web client

2012-02-22T13:00:00Z

Recently, I had a request to create export functionality for a datagrid in the SalesLogix web client. I had a little difficulty exporting data from the actual grid, but as an alternative, we put together functionality to export data based on a provided SQL query. (Thanks to Ryan for working out the SmartPart code)

By creating a new custom smartpart, we can simply export the results of a SQL query to a CSV file, save it on the webserver and download it to an individual client.

First, you'll want to create a folder on the web server to hold the exported file. In this case, I'm exporting contact data based on the current account, so I created an export folder under the SupportFiles/SmartParts/Account folder in the portal manager.

Once we have a location for our file, we need to add the SmartPart files. I've attached the files I created to this post. They can be added by right-clicking on the entity folder and selecting "Add Existing Files..."

The page I created simply pulls in a few different contact table fields. This can be easily modified. On the code page for the Smart Part, there are a couple functions you'll need to understand in order to customize this to pull in the data you want. First of all, the CreateFile function is where you define the SQL script and fields you would like to return.

 private void CreateFile(string file)
{
    //First, we're checking to see if the file already exists, deleting it if found.
    if (File.Exists(file))
        File.Delete(file);
    using (var writer = new StreamWriter(file, false))
    {
        //This line defines the column headers in the CSV file.
        writer.WriteLine("\"ContactID\",\"FirstName\",\"LastName\",\"Workphone\"");
        using (var conn = new OleDbConnection(ConnectionString))
        {
            conn.Open();
            //Using an OleDBCommand, we pass the query defining the data we want to return.  For this example, 
            //we're just returning data from the contact table where the accountid is what is passed when calling the page.
            using (var cmd = new OleDbCommand(string.Format("SELECT * FROM CONTACT WHERE ACCOUNTID = '{0}'", AccountId), conn))
            {
                //Now we create a reader object to loop through the returned records.
                var reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    //Finally, we're defining the fields from the Database to return.  Obviously, these should match the column headers previously defined.
                    writer.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\"", reader["CONTACTID"], reader["FIRSTNAME"], reader["LASTNAME"], reader["WORKPHONE"]));
                }
            }
        }
    }
}

The Page load event calls the CreateFile function and handles pushing the CSV file to the requesting user. Also, you could modify this to create other types of files, such as an excel spreadsheet. We chose a CSV in this case to reduce potential overhead on the webserver.

 protected void Page_Load(object sender, EventArgs e)
{
    //The page_load event calls the CreateFile function, saving it to the Export folder 
    //and pushes it to the requesting user.  The file is saved on the server with the
    //name of (userid).csv.  This way there will only be as many files saved on the 
    //server as there are users in SalesLogix.
            
    var file = string.Format("{0}\\SmartParts\\Account\\Export\\{1}.csv", GetSlxClientBaseUrl(), UserId);
    CreateFile(file);
    Response.Buffer = false;
    Response.Clear();
    Response.ClearContent();
    Response.ClearHeaders();
    Response.ContentType = "application/csv";
    //This line defines the file name in the Save dialog for the user.  In this case, (accountid)-contacts.csv.
    Response.AppendHeader("Content-Disposition", string.Format("attachment;filename={0}-Contacts.csv", AccountId));
    Response.AppendHeader("Content-Length", (new FileInfo(file).Length.ToString()));
    //Finally, the file is transmitted to the end-user.
    Response.TransmitFile(file);
    Response.End();
}

Now that the SmartPart is set up, we just need some code to navigate to the page and pass the current accountID. You can launch this from pretty much anywhere. Since I'm calling this from within the account entity, I added a button to call a C# code Snippet. The code is pretty simple:

 var account = this.BindingSource.Current as Sage.Entity.Interfaces.IAccount;
Response.Redirect(string.Format("/SlxClient/SmartParts/Account/TestExport.aspx?accountid={0}", account.Id));

As you can see, I'm getting the current accountid from the current binding source, and performing a redirect to the SmartPart while appending the accountID.

That's all there is to it. With this in place, your end users can simply push a button and download a file containing any data that you can define in a SQL query.

I hope you find this helpful. Thanks for reading!

Missing Properties/Toolbox window in SalesLogix LAN

2012-01-25T20:56:00Z

If you work on SalesLogix across a number of remote desktop connections and a number of different workstations as I do, you may occasionally run into an issue where the Properties or Toolbox windows cannot be made visible. This happens when you first start a Remote session from a machine with a greater screen resolution, move one of those windows to the far right of the screen, then start a Remote session on a machine with a smaller resolution. You can then end up in a situation where the propertes and/or toolbars views are technically "visible" but are in a position outside of the current screen resolution.

This can be remedied pretty easily by closing Architect, deleting the registry key: HKEY_CURRENT_USER\Software\SalesLogix\Architect and then reopening Architect.

The registry key will be recreated upon reopening architect, and the Properties and Toolbar windows should be reset to their default positions.

Populating fields from a lookup result set in the SalesLogix web client

2012-01-11T11:30:00Z

Recently, I needed to create an account tab in the web client which would show a number of values from a stand-alone entity in SalesLogix. Using child relationships, SalesLogix web makes this easy to do.

In this example, I'm creating a simple Product tab at the account level. This tab contains a Product Lookup to select a product for the account, and then also shows a number of product fields on the view. These product fields are read only, and I wanted them to update based on what was selected in the Lookup.

Using relationships in the web client makes this easy. First of all, I created added a ProductID field in the account table, and added that as a property to the account entity. Once I had that property, I created a new child relationship to the Product entity under account.

Now that we have this relationship, I created a new QuickForm to hold the product information. On the quick form, I added a Lookup as well as a couple other fields.

On the lookup control, I set the bindings property to the Products relationship I created. In addition, I needed to set the Lookup Binding Mode property to "Object" and the Lookup Entity Name property to "Product".

For the other fields, the bind property just needs to be set to MainEntity.Products.FieldName, as well as setting the ReadOnly properties to "True".

That's it! On this view, when you use the product Lookup and select a product, the appropriate values will be displayed in the other fields on the form.

Modifying the QuickFind Lookup in the SalesLogix LAN client

2011-11-02T07:30:00Z

The Quick Find functionality in the SalesLogix web client is a fast and easy way to look up information from many different areas in SalesLogix, but did you know you could modify the layout of the various lookups displayed in that interface? The functionality is a little hard to find if you don't know where to look, but this post will explain how you can modify the Quick Find lookups.

For this example, I'll look at the Account Details Quick Find. All the lookups for this functionality are found with all the others under the Manage|Lookups menu in SalesLogix Architect. For the Quick Find for Account Details, it is the Account.Account lookup.

Once you have the lookup, it's a simple matter of changing the layout to include whatever fields you want to include. The only problem is this... there are many lookups defined under account, as there are for contacts, opportunities, leads, etc. How are you supposed to know which of those lookups are utilized in the Quick Find view for each entity?

The answer to that lays in the properties for the Main View for each of the areas. If you look at the Account Details Main View, you will see a property called "QuickFindLookup". This property defines which lookup is used for that entity.

Once you have that information, you know which of the lookups you want to modify. You also have the option to define a different lookup to be used for the Quick Find for that entity.

That's all there is to it. Once you know where to look, it's easy to change the Quick Find functionality for each entity.

Thanks for reading!

Jason

Launching Schedule Activity functionality in the SLX Web client

2011-10-27T12:31:00Z

On a recent project, I worked on emulating functionality similar to what is found in the LAN client where a user is presented a dialog asking them to choose between different activity types then launching into the Schedule Activity functionality. I found this was fairly easy to duplicate in the web client, and in this post, I'll quickly go through the steps for creating that type of functionality.

First of all, we need to create a quick from which will give users a choice of what type of activity to create. Since this form contains no bound controls, it doesn't matter where we save it. For the sake of consistency, I'd recommend saving it under SalesLogix Activity Support.

Under SalesLogix Activity Support in the Project Explorer, expand the Activity node, right-click the Forms folder and select "Add Form".

The important elements of this view are the radio group control and the button. The radio group will provide users the choice of what type of activity to create, and the button will handle launching the appropriate schedule activity dialog based on the option selected.

Once you have the blank form, add a radio group and control In the radio group control, I have added choices for Phone Calls, Meetings and ToDos.

On the button I added to the form, I added a C# Snippet Action item to the click event of the button and added the following code:

 LinkHandler Link = new LinkHandler(Page);
if (rgActivityType.SelectedIndex == 0)
{
    Link.SchedulePhoneCall();
}
else if (rgActivityType.SelectedIndex == 1)
{
    Link.ScheduleMeeting();
}
else if (rgActivityType.SelectedIndex == 2)
{
    Link.ScheduleToDo();
}
else
{
    throw new Sage.Platform.Application.ValidationException("You must select a activity type to continue");
}

Creating a LinkHandler object gives you access to the various Schedule Activity forms. An IF...Else statement calls the right schedule form based on what was selected. I also added a Validation Exception in case no activity type was selected before clicking the Next button.

Once you have saved the new quick form, it is ready to go. To use this form, you will need to add it to the available smartparts for whatever portal page you plan on calling the interface from. To add the form, expand the Portal Manager-Sage SalesLogix-Pages nodes, and double click on whichever page you want to use.

When the page has opened, select the SmartParts tab and add the new form. You will need to set the Target Workspace value to "DialogWorkspace".

Now that the QuickForm has been added, you will need to add code to launch the form. This could be from a button on a detail view, or wherever you can add a C# Snippit action.

I'm simply using the Dialog service to call the new QuickForm. The SetSpecs function I'm using allows you to set the dimensions of the form you launch. You may have to experiment with the settings to make things the appropriate size.

 {
    //create dialog for activity type to launch...
    if (DialogService != null)
    {
        DialogService.SetSpecs(110, 318, "ScheduleActivity", "Schedule Followup Activity");
        DialogService.ShowDialog();
    }
}

That's about all there is to it! I hope you find this example useful.

Thanks for reading!

Jason

Hiding tabs in the web client using Roles

2011-10-17T14:32:00Z

If you had a tab you wanted to hide in the LAN client, all you have to do is remove the current release. Once that was done, the Admin user will continue to see the tab, but no other users will have access to it. This is something that you can do in the Web client fairly easily as well, although you have to go about it slightly differently.

In this post, I'll do a quick walk-through of hiding the Products tab in the Opportunities area of the Web Client.

The first step would be to create a new Role in the Web client. This Role will be used on any tab you want to hide from users. First, login to the web client as Admin, right-click the Roles button in the Administration section of the Navbar and select "New Role".

On the Insert Role screen, give the new role a name (and description if desired) and click the "Save" button.

When the Role is created, you will have access to a "Users" and "Actions" tabs for this Role. Since we are only using it to hide tabs to all users, there's no need to define anything in either of these tabs. If you did want particular users to still see the tab you were trying to hide, simply add them under the Users tab. The Admin user is exempted from the Roles functionality and will continue to see the tab.

With the Role created, launch the Application Architect. In the Project Explorer, expand Portal Manager->Sage SalesLogix->Pages and double-click "Opportunity Detail" to open the page. With the page open, Select the OpportunityProductEX(Custom) smartpart from under the Smart Parts tab. In the "Allow Roles" dropdown for the smartpart, simply select the Role you just created.

After a rebuild, and redeploy, that's it! The Opportunity Products tab should now be hidden for all but the Admin user.

*Note, this will only work on smart parts in the TabControl target workspace. In the case of the Opportunity Products tab, this is also shown on the Insert Opportunity view, but in the MainContent workspace. To remove products from the Insert Opportunity view, you would need to actually delete the Smart Part from the Page itself.

I hope you found this post helpful. Thanks for reading!

Modifying the Insert Contact/Account view in the SalesLogix Web client

2011-08-22T14:08:00Z

Most of the time, it is a simple process to add or subtract fields from views in SalesLogix, but on occasion, it is necessary to perform those changes by modifying custom smartparts rather than making changes directly to a view. A good example of this in the Insert Contact/Account screen in the web client.

If you're not familiar with ASP or C#, this can be a little intimidating, but hopefully this post will show the areas you need to modify when making changes to the custom smart part.

First of all, you need to find the custom smartpart. The easiest way to do this is to look at the Project Explorer in Application Architect. Expand the Portal manager, Sage SalesLogix, and Pages. Scroll down to the Insert Contact/Account page and double-click on it. This will open the page, and show all the smart parts used.

We're interested in the InsertContact custom smart part, so highlight that and click the Edit button.

As you can see, this is all code. If you've worked with ASP before, this should be pretty easy to navigate. If not, you'll have to do some looking around to find what it is you want to change. Say for example, you wanted to remove the standard "Business Description" field from this form. You should find ASP code referring to the the control on the form. You can then comment out or remove that code entirely.

Here you can see the code defining the Business Description field along with the code defining the Account Fax field. The placement of these fields is accomplished by using the standard <td> (cell) and <tr> (row) tags that are used in HTML. You'll want to keep these tags in mind when removing fields, otherwise you might mess up your layout of the screen. For removing business Description, you'd want to remove everything including the <td> tags, but leave the closing </tr> tag in place.

After removing the control, you'll want to run a search of the code to remove any reference to the "txtAccountBusinessDescription" control. Otherwise errors could be thrown when trying to reference a control that no longer exists.

For adding a new control, there are a couple other code items you will need to have in place to control things like control behavior, data bindings, etc... For this example, we're going to replace the Business Description field we just removed with the Division field in the Account Table.

The first step you'll want to take is to add the binding code for the new field. If you scroll down a ways, you'll find the code change from ASP to C#. (Look for the line that reads "<script runat="server" type="text/C#">" Everything below this line is the C# code.

Find the function named IsAccountBinding. This function indicates that the field we are binding is an account level field. Within the case statement in this function, we'll want to add a reference to the Division field like this:

case "Account.Division":
return true;

This can be added anywhere in the case statement BEFORE the case else at the end.

The next function in the script is named OnAddEntityBindings(). This is where the actual binding takes place. Within this code, you'll need to add a line binding the Division field to a new text control we'll call "txtDivision".

BindingSource.Bindings.Add(new WebEntityBinding("Account.Division", txtDivision, "Text"));

Next, add a reference to the new control in the function Named "lueUseExistingAccount_ChangeAction". This function just disables the account controls on the form and uses the values from the selected account to set certain contact fields. We'll just need to add a line to disable our new control.

ownAccountOwner.Enabled = false;

Next, on the LoadView() method, you'll need to add a reference to the control in the area where the other account level controls are referenced. It's within the If statement looking to see if account != null.

txtDivision.Enabled = changeEnable;

That is all the changes we need to make in terms of the C# code, however we haven't yet placed the control itself of the form.

We can replace the code defining Business Description with the code for our new control (everything including the <td> tags:

And that is all there is to it! We now have a brand new Division field on the Insert Contact/Account view in the web client.

Basics - Ticket Activity Support - SalesLogix LAN

2011-06-03T12:08:00Z

In this post I'll outline some of the functionality available to you in the Standard Ticket Activity support script for SalesLogix LAN. As with the other built in SalesLogix scripts, access to this functionality allows you the ability to control the various processes regarding ticket activities.

Most of these functions and methods deal with populating the activity and history tables.

CompleteCurrentUserTicketActivities(TicketID)

This function completes all tickets for a given ticket for the current logged in user.

CloseTicketActivity(strTicketActivityID, strTicketID, blnSilent)

The CloseTicketActivity function completes a named ticket activity (by ID). The blnSilent boolean is used to complete the activity without displaying the Complete Ticket Activity dialog if the boolean is True.

GetElapsedTime(dteFromDate, dteToDate)

This function returns an Integer derived from a difference between two dates.

GetElapsedHours(intElapsedTime)

Used in conjunction with the previous function, this one returns an integer of the elapsed time.

CreateTicketHistoryEntry(strActivityType, strTicketID)

This function populates the TicketHistory table. The strActivityType variable is used to fill the Notes field in that table.

CreateTicketActivity(objActivity, strAccessCode, strFollowUp, strShortDesc, blnSendSLX)

This Method generates a new Ticket Activity resulting from the completion of a Activity. This method is currently invoked from the Global Activity script, in which case the Activity type is not generated from an Email or from the Ticket Main View, in which case the Activity was generated from an Email drop.

SaveTimedTicketActivity(TicketActivityID, TicketID, strTime)

This method saves a record to the TicketActivity table. Use the strTime variable to pass a date which is used in the date fields in the table and also in the description field.

There are more functionality in this script, including Contract functionality and various other update scripts. Reviewing this and other SalesLogix system scripts can provide a lot of information about how things are done in SalesLogix, and what sort of processes are available for you to use in your customizations. I cover more functionality in future posts.

Thanks for reading!

Adding Billing and Shipping addresses to SageCRM merge forms

2011-05-10T15:28:00Z

I was recently doing some work on merge forms in SageCRM and discovered that for Cases, SageCRM didn't take into account different address types in the OOTB Case merge fields. Normally, Address information is derived by joining the case_PrimaryCompanyID to the Company table, and then joining from Comp_PrimaryAddressID to the address tables. With these joins, you could only include the primary address for the Case's account, however I wanted to display both Shipping and Billing addresses as well.

In this post, I'll go through the view changes I made to include Billing and Shipping Addresses on this merge form.

Sage CRM has a table named Address_Link which holds the address type as well as IDs for Company and Person records. We're going to create 2 new views, one for the billing address, and one for the shipping address. These are defined simply as:

 CREATE View [dbo].[vCompanyBillingAddress_Link] as
select * from dbo.Address_link
where AdLi_PersonID is null and AdLi_Type = 'Billing'

and

CREATE View [dbo].[vCompanyShippingAddress_Link] as
select * from dbo.Address_link
where AdLi_PersonID is null and AdLi_Type = 'Shipping'

By grabbing records where the PersonID is null, we should only be getting Company address records. Once these views are created, then we'll need to modify the Case merge view (named vMailMergeCase) to include the new views and fields. This needs to be defined in the case view definitions, but I find it easier to figure out what I am doing in SQL Management Studio and then copy the code over to SageCRM. We will make two changes to the VMailMergeCase view. first of all, we need to join to the new views we created:

 LEFT OUTER JOIN
dbo.vCompanyBillingAddress_Link ON dbo.Company.Comp_CompanyID = dbo.vCompanyBillingAddress_Link.AdLi_CompanyID LEFT OUTER JOIN
dbo.Address Adr2 on dbo.vCompanyBillingAddress_Link.AdLi_AddressID = Adr2.Addr_AddressID LEFT OUTER JOIN
dbo.vCompanyShippingAddress_Link ON dbo.Company.Comp_CompanyID = dbo.vCompanyShippingAddress_Link.AdLi_CompanyID LEFT OUTER JOIN
dbo.Address Adr3 on dbo.vCompanyShippingAddress_Link.AdLi_AddressID = Adr3.Addr_AddressID

Once the joins are included, we want to add the new address fields. I used a CASE statement so that if there was not a billing or shipping address defined, it would just present the companies primary address as it was before.

BillingAddress1 = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Address1 else Adr2.Addr_Address1 END,
BillingAddress2 = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Address2 else Adr2.Addr_Address2 END,
BillingAddress3 = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Address3 else Adr2.Addr_Address3 END,
BillingAddress4 = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Address4 else Adr2.Addr_Address4 END,
BillingAddress5 = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Address5 else Adr2.Addr_Address5 END,
BillingCity = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_City else Adr2.Addr_City END,
BillingState = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_State else Adr2.Addr_State END,
BillingCountry = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_Country else Adr2.Addr_Country END,
BillingPostCode = CASE WHEN Adr2.Addr_AddressID is null Then dbo.Address.Addr_PostCode else Adr2.Addr_PostCode END,

after the billing information fields, I do the same with the Shipping address fields from the Adr3 alias. Once these view changes are done in SageCRM, you should have the billing and shipping address available in the case merge fields.

I've attached the entire modified MergeView so you can see how I incorporated everything together. Again, any changes to the Merge view must be defined within SageCRM, under Administration->Customization->Cases->Views tab.

Thanks for reading!

Simple Insert Trigger Example

2011-03-01T19:01:00Z

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

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

Here's how I created the trigger:

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

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

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

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

from dbo.CRMManagers where STATE = @STATE

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

That's about it. Pretty simple!

Running Sql Server Integration services package from a C Sharp application

2011-01-19T16:04:00Z

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

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

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

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

 
using Microsoft.SqlServer.Dts.Runtime;

namespace CFX_ExportPortalEmailLeads
{
    public partial class frmExportData : Form
    {

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

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

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

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

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

        }
             
    }
}

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

Thanks for reading!

Advanced Pivoting in MSSQL

2011-01-06T15:13:00Z

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

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

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

PrimaryKey
ForeignKey
Description
Amount

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

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

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

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

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

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

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

 CREATE procedure [dbo].[sp_BuildRotatedTempData] as

DECLARE @RC int
DECLARE @DataPart varchar(30)

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

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

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

Thanks for reading!

Web Basics - Using the UserService in a Code Snippet Action Item.

2010-11-15T09:16:00Z

It's easy to access the UserService interface from a Code Snippet Action item in the SalesLogix web client. For this example, I'm just going to add a button on account detail which will launch a DialogService message with the current user.

After adding the button to the AccountDetails form, I added a new CodeSnippet action item to the onclick event of the button, creating it as a C# Code snippet.

Since we're using a code snippet action item, we'll need to actually get the IUserService with the following code:

Sage.Platform.Security.IUSerService user = form.Services.Get<Sage.Platform.Security.IUserService>;

That's really all you need to do to access the IUserService. Once we have that, we have access to the CurrentUserID, if we wanted to do something with that in code. Since I'm displaying this in a Dialog, we'll need to get the IWebDialogService to be able to call the dialog:

Sage.Platform.WebPortal.Services.IWebDialogService dlg = form.WorkITem.Services.Get<Sage.PLatform.WebPortal.Services.IWebDialogService>();

Once that is done, calling the dialog itself is a simple matter of calling the dialog's ShowMessage function:

dlg.ShowMessage("Current User: " + user.PrettyName);

That's all there is to it! Again, we also have access to the current user's ID if we wanted to use that for some purpose. In future articles, I'll give some more examples of what we can do with the UserService interface.

Basics - Setting filters in the OpenDialog common dialog

2010-10-27T14:40:00Z

I wanted to run through quickly how to set filters when using the OpenDialog in the SalesLogix LAN client. When using OpenDialog, it's nice to be able to define filters so that your users only see the type of file that you want them to open. This is really easy to set up.

The Filter properties of the OpenDialog control displays two columns; Filter Name and Filter. The Filter Name column is what is displayed int he "Files of type" dropdown in the the actual Open form. The filters column defines the filters themselves. You do have to define the file extentions in BOTH columns otherwise the filtering will not work.

In my sample, I am setting a filter for text files, as well as all files. If you want to define multiple file types such as word documents (doc and docx), all you have to do is separate them by semi-colons. In that case, the value in the FilterName column would be "Documents (*.doc;*.docx)" while the Filter column would contain "*.doc;*docx"

That's all there is to it! Also, there are a bunch of options listed on the options tab when looking at the filters property for the dialog, which control much of the behavior of the dialog itself. I'd recommend taking a look through those options also to see how you can control the behavior of the OpenDialog.

Thanks for reading!

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

2010-09-20T16:12:00Z

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Picklist Column types - Dispaying ticket status in the SalesLogix LAN datagrid

2010-08-24T10:30:00Z

I recently had to build a datagrid displaying ticket information, including the current Status value. Under tickets, the Statuscode field contains the itemid from the picklist table rather than the actual text value as is stored for the Account, Contact or Opportunity areas. If you've only dealt with picklists in those other areas, it could cause some confusion when trying to determine just how to display the Ticket status text value in a datagird, however using the picklist column type makes this very easy.

When initially building the grid, you'll want to add the Ticket.Statuscode column within the Layout tab of the query builder for the grid, just as you would any other field. Once that is done, open the Columns collection properties for the grid.

If you right-click the Status column, one of the options will be "Change Column Type...". Select this and chance the type from "Standard" to "Picklist". Once this is done, right-click the status column again, but this type select "Properties...".

As you can see, this interface contains a number of properties. In the case of displaying ticket status values, there are two that you have to worry about. First, set the "Picklist" property to contain the appropriate picklist (in this case, Ticket Status). In addition, you'll need to check the "Store Items IDs" checkbox.

That's all there is to it. Your grid will now translate the itemid value do display the actual text value stored in the picklist table.

In my next blog post, I'll go over what this means in the Web client.

Thanks for reading!

Jason

Useful Functions from the SLX_Common script (SalesLogix LAN)

2010-07-19T11:58:00Z

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

GetItemIndex(Control, Value)

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

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

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

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

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

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

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

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

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

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

etc...

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

Jason

SalesLogix basics - Easy Data access in SalesLogix LAN

2010-06-22T13:30:00Z

In this basics article, I wanted to outline the use of include scripts within the SalesLogix LAN client. Specifically, I'm going to look at the standard SLX Database Support script.

Adding an Include script to either a VB Script or a Form is easy. On toolbar at the top of the script (or script page for the view), there is a button titled "Include Script". Clicking this button will open the Included scripts panel to the left of the code window. Click the plus sign and select the Script to include. (There are a number of standard scripts available under the 'System' family, beginning with 'SLX'. These hold much of the Standard functionality provided by SalesLogix.)

There are a few simple functions you can use right away. For example, to find a single value from a table, you can use the GetField Function:

Dim sValue
sValue = GetField("ACCOUNT", "ACCOUNT", "ACCOUNTID = '{valid accountid}')

I've written some other articles about using ADO in SalesLogix and have handled creating connections myself, however these functions are already contained within the class defined in the Database Support script.

After adding the SLX Database Support to your script as an include script:

First, you have in instantiate the Class

Dim objSLXDB = new SLX_DB

Now that you have a DB object, you can reuse the object throughout your script.

For Example, if you need a new recordset:

Set objRS = objSLXDB.Recordset
objRS.Open "SQL", objSLXDB.Connection

As you can see, not only did I use my SLX Database object to create my new recordset, I also retrieved a connection through the object.

There's also an ExecuteSQL function which is handy to

objSLXDB.ExecuteSQL "Update {tablename}..."

As you can see, the Database Support script can be very handy for performing simple tasks. Other SLX scripts include Error Support and Debug support along with a lot of other useful functionality. You can also create your own scripts to add your own functionality. Creating your own include script is a great way to encapsulate your custom functionality. I'll cover class building in a future article, so please keep reading!

Thanks!

Jason

Modifying the 'Copy to E-mail' link in the SalesLogix Web client Opportunity snapshot

2010-06-08T12:50:00Z

If you've ever wanted to modify the email generated from the Opportunity Snapshot in the web client, you'll find that it is incredibly simple to add new fields to the body of the email. To do so, you will need to modify the OpportunitySnapShot.ascx.cs smart part, found under Portal Manager->Sage SalesLogix->SupportFiles->SmartParts->Opportunity.

In this file, you will find a function named 'FormatEmailBody'. This function builds a string (emailBody) from available properties in the Opportunity Entity. As long as any new fields you have added to the opportunity table has been added to the entity properties, it is a simple matter to include those fields in the email body.

Current fields defined in the email are done so with the following code (Example shows description field):

emailBody += String.Format("{0} {1} %0A", GetLocalResourceObject("lblEmailOppDesc.Caption"),
CheckForNullValue(opportunity.Description));

To add a new field (c_orderno), we're going to copy this line, replace the GetLocalResourceObject call with the field label we'd like to use, and reference the new field from the opportunity entity in the CheckForNullValue function in this line.

emailBody += String.Format("{0} {1} %0A", "Order No:",
CheckForNullValue(opportunity.CORDERNO));

The code is formatted in such a way that it should be pretty simple to figure out where in the email body you are putting the new field.

If you want to add a date field, the format is slightly different, if you want to use the UTCDateTimeToLocalTime function inline:

emailBody += String.Format("{0} {1} %0A", "Modified:",
                                    opportunity.CMODIFIED.HasValue
                                       ? timeZone.UTCDateTimeToLocalTime((DateTime) opportunity.CMODIFIED).ToString(
                                             datePattern)
                                       : String.Empty);

As you can see, it's a pretty simple process to add new fields to the Email body in the email produced by the Opportunity snapshot.

Hope you find this helpful. Thanks for reading!

SQL Function for parsing name values

2010-05-25T15:13:00Z

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Return @TempString
end

GO

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

Thanks for reading!

Setting Billing/Shipping addresses on Insert Salesorder in the SalesLogix Web Client

2010-04-14T07:09:00Z

In a recent SLX Web project, it was requested that I add a contact lookup to the insert SalesOrder view, and when that was set, use the contact's address for the Shipping and Billing address in the SalesOrder. I was able to add a new Contact property to the SalesOrder and add the lookup to the insert SalesOrder view, but I wasn't able to reference the new property after it was set.

It turned out that on the insert form, the Contact property wasn't actually being written until after the new SalesOrder record was saved. In order to get the selected contact's address information, I simply referenced the Contact entity returned in the lookup as shown in this sample.

This code was added to a C# Snippet action in the OnChange Action event of the new contact lookup.

Sage.Entity.Interfaces.ISalesOrder salesOrder = BindingSource.Current as Sage.Entity.Interfaces.ISalesOrder;
Sage.Entity.Interfaces.IContact con = lkeContact.LookupResultValue as Sage.Entity.Interfaces.IContact;

if (salesOrder != null && con != null && con.Address != null)
{
    salesOrder.BillingAddress = salesOrder.SetSalesOrderBillingAddress(con.Address);
    salesOrder.BillingContact = con;
    salesOrder.BillToName = con.NameLF;
    salesOrder.ShippingAddress = salesOrder.SetSalesOrderShippingAddress(con.Address);
    salesOrder.ShippingContact = con;
    salesOrder.ShipToName = con.NameLF;
    return;
}

In addition, I added similar code the OnChange Action of the Account lookup, only instead I set the SalesOrder properties to Null instead. I did this so that if a new Account was selected, a new contact would also need to be selected along with the new address information.

Hope you find this example helpful. Thanks for reading!

-Jason

Hiding controls and control labels via code in the SalesLogix Web Client.

2010-04-12T14:21:00Z

This is a minor item, but one I always seem to forget, so I thought I'd throw it up here quick as a reference...

On occasion in the web, I want to hide a particular control instead of removing it. If I set this via code snippet in the onload event of the form, it's a simple matter of just including the line <control>.Visible = False. If this is all you do, the caption of the control will still display even though the control itself is hidden. In the past, I've just cleared out the caption property of the control, but you can hide it by appending "_lbl" to the end of the control name.

So, along with the line <control>.Visible = False, you should also include <control>_lbl.False.

That's all there is to it. Pretty straight-forward.

Thanks for Reading!

-Jason