Ryan Farley's Blog

A Look at Querying Data in SalesLogix using IRepository and the SQL Equivalents

2010-03-05T18:17:00Z

For some who have been working in the SalesLogix LAN client for years, the transition from using SQL queries to retrieving data using a repository with the entity model can be a difficult change. This post will look at some common data retrieval methods and the SQL equivalents to help you get your head around how to get work done using the entity model.

Retrieving All Records from a Table

The following is an example query to retrieve all records in a table.

SQL Equivalent

SELECT * FROM PRODUCT

Code

Sage.Platform.Repository.IRepository<Sage.Entity.Interfaces.IProduct> repository = 
    Sage.Platform.EntityFactory.GetRepository<Sage.Entity.Interfaces.IProduct>();
     
// returns an IList<IProduct>
result = repository.FindAll();

Sorting Query Results

The following will retrieve all records from a table and sort the results.

SQL Equivalent

SELECT * FROM PRODUCT ORDER BY FAMILY ASC

Code

Sage.Platform.Repository.IRepository<Sage.Entity.Interfaces.IProduct> repository = 
    Sage.Platform.EntityFactory.GetRepository<Sage.Entity.Interfaces.IProduct>();
 
// setup the query
IQueryable query = (IQueryable)repository;
 
// setup the expression factory
IExpressionFactory ef = query.GetExpressionFactory();
 
// setup your criteria 
ICriteria criteria = query.CreateCriteria();
 
// add sort order
criteria.AddOrder(ef.Asc("Family"));
 
// returns an IList<IProduct>
result = criteria.List<IProduct>();

Query Using Where - Finding All Opportunities for an Account

The following will add a condition to a query to retrieve all opportunities for an account and order results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE ACCOUNTID = 'SOMEACCOUNTID' ORDER BY DESCRIPTION ASC

Code

// Using repository helper
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();
 
// EF and PF are setup automatically
 
// setup your criteria 
ICriteria criteria = repository.CreateCriteria();
 
// add condition for account
criteria.Add(repository.EF.Eq("Account", account));
 
// add sort order
criteria.AddOrder(ef.Asc("Description"));
 
// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Query Conditions using AND

The following will AND several query conditions together and order the results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE STATUS = 'Closed - Won' AND DESCRIPTION = 'Some Description' ORDER BY DESCRIPTION ASC

Code

// Using repository helper
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();
 
// EF and PF are setup automatically
 
// setup your criteria 
ICriteria criteria = repository.CreateCriteria();
 
// AND conditions together
criteria.Add(repository.EF.Conjunction()
    .Add(repository.EF.Eq("Status", "Closed - Won"))
    .Add(repository.EF.Eq("Description", "Some Description"));
 
// add sort order
criteria.AddOrder(ef.Asc("Description"));
 
// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Query Conditions using OR

The following will OR several query conditions together and order the results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE STATUS = 'Closed - Won' OR STATUS = 'Open' ORDER BY DESCRIPTION ASC

Code

// Using repository helper (EF and PF are setup automatically)
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();
 
// setup your criteria 
ICriteria criteria = repository.CreateCriteria();
 
// OR conditions together
criteria.Add(repository.EF.Or(repository.EF.Eq("Status", "Closed - Won"), 
                              repository.EF.Eq("Status", "Open")));
 
// add sort order
criteria.AddOrder(ef.Asc("Description"));
 
// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Query Conditions using IN

The following will IN several query conditions together and order the results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE STATUS IN ('Closed - Won','Open','Closed - Lost') ORDER BY DESCRIPTION ASC

Code

// Using repository helper (EF and PF are setup automatically)
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();
 
// setup your criteria 
ICriteria criteria = repository.CreateCriteria();
 
// IN conditions together
criteria.Add(repository.EF.In("Status", new string[] {"Closed - Won", "Open", "Closed - Lost"})); 
 
// add sort order
criteria.AddOrder(ef.Asc("Description"));
 
// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Query Conditions using BETWEEN

The following will BETWEEN several query conditions together and order the results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE STATUS BETWEEN 50000 AND 150000 ORDER BY DESCRIPTION ASC

Code

// Using repository helper (EF and PF are setup automatically)
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();
 
// setup your criteria 
ICriteria criteria = repository.CreateCriteria();
 
// BETWEEN conditions together
criteria.Add(repository.EF.Between("SalesPotential", 50000, 150000)); 
 
// add sort order
criteria.AddOrder(ef.Asc("Description"));
 
// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Query Conditions using LIKE

The following will LIKE a query condition and order the results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE STATUS LIKE 'Closed%' ORDER BY DESCRIPTION ASC

Code

// Using repository helper (EF and PF are setup automatically)
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();
 
// setup your criteria 
ICriteria criteria = repository.CreateCriteria();
 
// LIKE conditions together
criteria.Add(repository.EF.Like("Status", "Closed%")); 
 
// add sort order
criteria.AddOrder(ef.Asc("Description"));
 
// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Hopefully that helps make the transition a bit easier. On this same topic, don't forget to check out Kris Halsrud's post on using NOT to exclude conditions.

Community Explorer - The Desktop Client for the Sage SalesLogix Community Forums

2010-02-09T23:25:00Z

Ever since Sage released their official SalesLogix community website & forums, I've wanted a better way to search and browse the forums. I've been working on a desktop client that does just this in a better, faster, and more efficient way. So, I'd like to announce the public release of Community Explorer.

(click to view larger image)

Community Explorer is a desktop application that will allow you to search the official Sage SalesLogix community forums and blogs in a much faster, precise, and effective way - letting you get to the information you need faster. It also allows you to simply browse the community forums in an easier way, similar to browsing a newsgroup or e-mail.

The current version is beta, so expect to see releases with bug fixes. There are also more features planned for Community Explorer, including viewing recent posts & threads and even toast-like desktop notifications alerting you of new activity in the forums.

Take a look at the Community Explorer page and install it to give it a try. I would love to hear your feedback!

Big Update for Git Extensions for SalesLogix - Now Automatically Updates and Installer Available

2009-12-22T17:50:00Z

I have released a big update for Git Extensions for SalesLogix. This new version includes a ClickOnce installer and will keep itself up to date automatically. The installation of Git Extensions for SalesLogix is now brain-dead easy. You just access a URL and click the Install button. Not only that, you'll automagically be updated to new releases as they become available. I've recorded a quick video to show how these new features work.

Watch the Git Extensions for SalesLogix Installer Preview Video
The video will open on screencast.com in a new window

This update will make things great moving forward since you'll automatically get new updates without any effort at all. One important part of this release is that the SalesLogix.xml file is no longer updated at all. The Git Extensions for SalesLogix assembly gets loaded into Application Architect without any configuration changes at all. This will avoid the problem with Sage updates not updating the SalesLogix.xml file because it was modified. Kris described this problem here. This problem will no longer exist since the XML file is never touched with the new version.

The installer is a ClickOnce application. You'll just go to the install URL and a wizard will start:

Install Git Extensions for SalesLogix

To install Git Extensions for SalesLogix you must remove any previous versions. This is only a requirement this time only. From now on things will self-update. However, if you've had an earlier version installed, this is very important. See the full details to install Git Extensions for SalesLogix, or for instructions to remove an earlier version, visit the project Wiki on Github

Install Git Extensions for SalesLogix

I'd love to hear feedback on how Git Extensions for SalesLogix is working out for you. Personally, I love it. It makes working with source control for a SalesLogix project painless and easy since it is all built right into Application Architect. Remember, all the installer stuff is brand new, so please let me know if you run into any bugs by leaving a comment, or even better yet, log it as an issue on the project site.

Showing a Message to the User from a Code Snippet Action in SalesLogix Web

2009-12-10T19:28:00Z

Showing a message to a user in SalesLogix Web is an easy task. It should be, right? Of course - and it is, however, you might need to know where to look to do it. This post will take a look at how to do this from both a Code Snippet Action and a C# Snippet Action.

Showing a message in the web client, like using MsgBox from a LAN client script, requires use of the DialogService. The DialogService has a ShowMessage method you can use to display a message to the user.

From a Code Snippet Action

To use the DialogService from a Code Snippet Action, you need to use the form's WorkItem reference that is passed in to the code action. This will allow you to get access to the DialogService.

// Get a reference to the DialogService
Sage.Platform.WebPortal.Services.IWebDialogService dialogService = 
        form.WorkItem.Services.Get<Sage.Platform.WebPortal.Services.IWebDialogService>();
// Now show a message to the user
dialogService.ShowMessage("You must first do something.");

Easy enough, now let's take a look at how to do this from a C# Snippet Action.

From a C# Snippet Action

From a C# Snippet Action things a a little different. As I mentioned before, code in a C# Snippet Action gets placed directly on the SmartPart (the ASCX UserControl). The code is inline on the deployed ASCX file itself. The SmartPart inherits from EntityBoundSmartPartProvider which eventually inherits from Sage.Platform.WebPortal.SmartParts.SmartPart which has a built-in DialogService reference. What this means is that you no longer have to get a reference to the DialogService yourself, you just use the one you already have.

// Show a message to the user
this.DialogService.ShowMessage("You must first do something.");

Either way, whether you're in a C# Snippet, or the Code Action things are all nice and simple, as it should be.

Account Hierarchy for SalesLogix Web Source Code Has a New Home

2009-12-07T22:32:00Z

The Account Hierarchy for SalesLogix Web module has a new home. I have moved the source to Github to make a public repository for the code and make it easier for others to get the latest source as I make changes to it (and hopefully get some contributions from others too who use it).

I released a public download of the Account Hierarchy module almost a year and a half ago for SalesLogix 7.2. Since that time it has had hundreds of downloads. I have make some tweaks over time and decided there needed to be a better way to share those tweaks. So, the Github repository will be the permanent location for this module going forward. Also, I opted to make the repository for the SmartPart ascx and cs files separate from a full model. This is intentional because I typically will use this in a full model that is already under source control. This way you can just clone the repository and copy the files into your working model as needed. Of course the full bundle will be available as well.

Visit the repository on Github
Download the bundle from Github

Also, I wanted to mention that the code is now licensed under the GPL version 3 license as well.

Update to Git Extensions for SalesLogix Available

2009-12-02T23:10:00Z

For anyone who uses Git Extensions for SalesLogix, I've released a small update that adds some core functionality and makes it possible to complete the entire scenario of setting up and using Git with a SalesLogix project - all from within the SalesLogix Application Architect!

The new functionality includes:

Manage files or folders to exclude from source control
Manage remote repositories so you can link your SalesLogix project to an online or networked repository
Ability to open the working project folder in explorer to manage files in your model
Ability to initialize the current exported project model as a Git repository to start working under source control

See a screenshot of the complete list of functionality on the Git Extensions for SalesLogix tools menu:

So, you can now do the entire scenario of setting up and working with source control for your SalesLogix model. Say you have a new SalesLogix system you're working with, the typical flow is as follows (again, wanted to point out you're not leaving Application Architect at all in this scenario):

Set up your new SalesLogix database
Export your project model
Click the Git Extensions "cow head" icon to initialize the exported model as a Git repository *and* a default .gitignore will be created including the typical entries for a SalesLogix model
Go to the Tools menu and then select "Manage Remote Repositories" to set up any remotes such as a Github repository
Click the Commit button on the toolbar to do your initial commit (all non-excluded model files will already be staged for the commit
If linked to a remote, click the Push button on the toolbar to push to the remote

Pretty cool if you ask me.

To install this update, simply close Application Architect, download the new DLL, and copy it over the old one located in C:\Program Files\SalesLogix\SalesLogix. If you're setting it up for the first time, visit my initial post about Git Extensions for SalesLogix for setup instructions.

Excluding SalesLogix Model Files from Source Control

2009-11-12T19:52:00Z

I love working with SalesLogix models in source control. Specifically with Git. However, whether you're using Git, or some other source control system, there are certain files in a SalesLogix model that you do not want to include in your source control. Many part of the files in an exported SalesLogix model are automatically generated by the Application Architect and by the build process when you build your web platform. Having these files under source control would only complicate things when working with multiple developers since you'd each need to merge these automatically generated files with each pull/checkout from source control since they change every time.

Model Files to Exclude

Let's take a look at which files to exclude from source control and why.

ModelIndex.xml - This file is a temporary cache created by Application Architect as you access objects. This file can be deleted and will be automatically created again as needed. This is a volatile file and if included in source control would require constant merging with no benefit.
Model\Deployment folder tree - The deployment folder in the model is where the Application Architect will create all of the actual SmartPart.ascx files and other deployable items from the QuickForm meta data and entities in your model. This is not to be confused with a "deployment" per se, but this is a staging area for things to be deployed that are created when you build your platform. As you create, modify, delete items in the model and then build your web platform all of the deployment files are generated based on what is in your model. Including these files in your source control system will complicate things since these files are created all over again as you build your web platform. To keep your folder structure in tact, I usually will exclude the following from source control:

Model\deployment\webroot\common\SmartParts\**\*
Model\deployment\webroot\common\SmartParts\**\**\*
Model\deployment\webroot\common\SummaryConfigData\*
Model\deployment\webroot\common\bin\*
Model\deployment\webroot\common\*
Model\deployment\common\bin\Sage.Entity.Interfaces.dll
Model\deployment\common\bin\Sage.Form.Interfaces.dll

In addition to what is excluded from the SalesLogix model, using file merge tools, such as KDiff, DiffMerge, etc, you will have additional copies of files created that you'll want to exclude. When working with Git Extensions you'll see these copies of files with .LOCAL, .REMOTE, .BASE extensions.

Ignoring Files with Git

As I mentioned before, I love Git. To exclude files from a Git repository you simply create a text file in the root of the repository named ".gitignore" and you can add files or directories to ignore (or to not ignore by prefixing the file or directory name with a "!"). This is what the contents of my .gitignore file looks like for a standard SalesLogix model:

# Files created from merge tools
*.BASE
*.LOCAL
*.REMOTE
*.orig

# Model files that are auto-generated
[Mm]odel[Ii]ndex.xml

# Standard deployment files
*/deployment/webroot/common/[Ss]mart[Pp]arts/**/*
*/deployment/webroot/common/[Ss]mart[Pp]arts/**/**/*
*/deployment/webroot/common/[Ss]ummary[Cc]onfig[Dd]ata/*
*/deployment/webroot/common/bin/*
*/deployment/webroot/common/*
*/deployment/common/bin/[Ss]age.[Ee]ntity.[Ii]nterfaces.dll
*/deployment/common/bin/[Ss]age.[Ff]orm.[Ii]nterfaces.dll

Ignoring Files with Git Extensions for SalesLogix

To make all of this easier with working with Git for source control with a SalesLogix model, I have updated Git Extensions for SalesLogix. The menu in Application Architect now includes a menu option to edit your .gitignore, and if it does not yet exist, it will give you the option to add all of the standard entries I have mentioned above.

In addition, when you initialize a new Git repository for a model from Application Architect the .gitignore file will be automatically created for you wit the entries mentioned above. Note, when you open a model in Application Architect and click the browse button on the Git Extensions toolbar it will detect if the current model has a Git repository or not and will prompt you to create the repository if one does not yet exist.

To download this update to Git Extensions for SalesLogix, just download the new DLL and copy it over the old one. Take a look a the project wiki for full installation steps.

The Official Sage SData Specification Released

2009-11-05T21:48:00Z

The official Sage SData Specification has now been released. SData (which stands for "Sage Data") is a Sage communication protocol. Using SData you have a common protocol that defines data access, consumption, and exchange between Sage application and 3rd party applications as well. The SData specification is now released and public.

The new Sage website, sdata.sage.com, provides all the details and the complete specification. Take a look:

View the SData Specification website

The SData 1.0 specification brings some exciting new possibilities for the future of Sage products. For companies that use multiple products from Sage such as SalesLogix, MAS, SageCRM, etc, there will be some great things that this will make possible. However, even more exciting for me are the possibilities of developing 3rd party applications that work with Sage products using SData for communication.

Some of the more exciting areas I'd suggest you take a look at are:

The SData Core Specification (PDF) (Get up to speed on how to construct SData feed URLs and services)
The SData Core Specification (Online version)
SData under the covers (great intro for those not familiar with SData yet)
SData Showcases & Downloads (some sample apps, like an Outlook contact import, and Excel addin for consuming SData, as well as an SData validation tool)
SData Custom Extensions (for a look at some extension contracts for global data exchange between different applications)

One thing for sure, the website does put to REST (unintentional pun) the unclarity of the casing of the name. It should be henceforth referred to as "SData", not "sData".

Preview of Git Extensions for SalesLogix

2009-10-15T20:58:00Z

Yesterday I announced my new project Git Extensions for SalesLogix. I received a lot of e-mails from people who read that post, so I thought it would be a good idea to record a quick video so you can see it in action and get a better idea how it works. This quick 4-5 minute video will show you how to commit changes in Application Architect to a Git repository and push the changes to an online repository as well, all from inside Application Architect using Git Extensions for SalesLogix. You'll also get a peek at a few other pieces of functionality as well. All in all, if you already use Git Extensions then this will be a no-brainer to use. If you're new to Git, or to source control at all, then this will lower the learning curve significantly and make things much easier to get into.

Watch the Preview Video

Watch the Git Extensions for SalesLogix Preview Video
The video will open on screencast.com in a new window

Well, that should give you an idea of how Git Extension for SalesLogix works and allow you to jump into things easier. Take a look at my previous post to see how to get to the Git Extensions for SalesLogix repository on Github if you're interested and more importantly, the installation instructions.

See Announcing Git Extensions for SalesLogix and the Customer FX Open Source Initiative

As always, feedback is welcome. Thanks and enjoy.

Announcing Git Extensions for SalesLogix and the Customer FX Open Source Initiative

2009-10-14T21:51:00Z

This blog post is the first of many that will cover the topics of using source control with SalesLogix development, Customer FX's open source initiative, and the new Git Extensions for SalesLogix module for Application Architect. These are three things I am completely excited about. Using source control with SalesLogix development in Application Architect is a necessity if you want to do it right. Git Extensions for SalesLogix is the first of the Customer FX open source initiative that builds on the idea of using source control with SalesLogix development to make it even easier to use Git source control right from inside Application Architect as a natural, built-in part of the application.

The Customer FX Open Source Initiative

Before we get to the details about Git Extensions for SalesLogix, I want to mention a few things about the Customer FX Open Source Initiative. For years, Customer FX has been a consistent source of information in the SalesLogix community. We make it a point to post anything interesting or useful to our blogs. It is great to work for a company that puts so much emphasis on sharing with the community. After all, when the community thrives, we all benefit. The management team at Customer FX actually factors in blogging time into everyone's schedule to make it easy for us to share what we know.

We are going to start taking that a step further. Not only will we be sharing knowledge, but we'll be sharing code now too. There will be much more to come on this topic, so stay tuned and you'll start to see more and more open source projects coming from Customer FX. We hope to pick up some collaborators from the community as well. The first of our open source projects is the Git Extensions for SalesLogix module. Read on.

Using Source Control with SalesLogix Development

I'm not going to spend too much time talking about this topic since it deserves much more individualized attention. But, if you work in Application Architect customizing SalesLogix and you're not using source control. I hear your pain. Using source control for your SalesLogix models is the only way to do it right. My personal favorite is Git, so much of my information will be covering Git source control, however, I do plan on touching on Subversion from time to time as well.

Git Extensions for SalesLogix

So, without further ado, let's take a quick look at my new Git Extensions for SalesLogix module. Git Extensions for SalesLogix is a module for Application Architect that extends AA to include the most common Git functions, right on the AA toolbar and menus. Imagine this, you open a form in AA, make some changes, then click a button on the toolbar in AA to commit the change. Later, you click another button on the AA toolbar to push back to your online repository. All without ever leaving Application Architect. This is what Git Extensions for SalesLogix gives you. Well that, and a break from some headaches. Git Extensions for SalesLogix integrates the completely awesome open source Git Extensions into Application Architect. So to use Git Extensions for SalesLogix, you'll also need to have Git Extensions installed.

Here's a screenshot of Application Architect with Git Extensions for SalesLogix loaded:

(Click for a larger view)

Git Extensions for SalesLogix is fully functional and will likely grow to include many more functions in the future. You can grab this now and use it. You can also grab the code and submit patches for any changes you'd like to make. If you would like to collaborate submit a patch and I'll add you as a collaborator (or just ask). Take a look below for details on the repository location, or to just install it.

Visit the Git Extensions for SalesLogix Repository

To install Git Extensions for SalesLogix visit the Installation page in the project Wiki on Github.

Keep in mind, the initial version of this module was cranked out all in one night, so you're bound to find some bugs. I've only tested it so far in 7.5.2 and 7.5.1 (versions prior to 7.5.2 will have some funky placement of separators on the toolbar and menus). Also, the next version will no longer need to modify the XML file listed above (steps 4-6) and you'll just be able to drop the DLL into the modules folder in the SalesLogix directory.

Enjoy, and I hope to get some feedback from others who wish to use the module. Also, for anyone who would like to use source control but just don't know how, I am considering offering a training class on using source control with SalesLogix and working in a multi-developer environment. Let me know if you'd be interested.

Update: Don't miss my next post where I show a video preview of Git Extensions for SalesLogix in action. View it now.

Adding Custom Controls to Application Architect in SalesLogix 7.5.2

2009-10-09T00:25:00Z

I just came across some new info that has been added to SalesLogix 7.5.2 (currently in beta). The ability to add custom controls to Application Architect for use on QuickForms. This is a completely welcome and awesome addition to the SalesLogix Application Architect. The following is taken from the 7.5.2 AA help files:

Note: The following applies to 7.5.2 and higher only

Creating Custom Controls for Quick Forms

You can create custom controls and use them in Application Architect Quick Forms.

To create

Create a new Visual Studio project with the class library template.
Add project references to Sage.Platform, Sage.Platform.Application, and Sage.Platform.QuickForms.
Create a class that derives from QuickFormsControlBase or any control you want to extend.

Location of classes mentioned is: Sage.Platform.QuickForms.Controls.QuickFormsControlBase and Sage.Platform.QuickForms.Controls.QFTextBox.

Following is a code shell for extending a TextBox control:

using Sage.Platform.QuickForms.Controls;
namespace CustomQuickFormControls
{
    [BindableProperty(RenderPlatform.Web, "Text", true)]
    public class CustomTextBox : QuickFormsControlBase, ICustomTextBox
    {
        public string MyProperty { get; set; }

        public bool IsRight { get; set; }
        public string Text { get; set; }
    }
}
Add the properties to collect the information needed to generate the markup for your custom ASP.NET control.
Enable code snippet actions to refer to the custom control by wrapping it with an adapter.
1. Create an interface that represents the control, for example, ICustomTextBox.
2. Implement the interface in the custom quick form control class.
3. Create a control adapter that implements the interface.
The adapter will have a reference to the underlying control and acts as a wrapper for it.
1. Copy the assembly containing the adapter into the website bin in the active model, for example, Model\Portal\SlxClient\SupportFiles\Bin.
See the Sage.SalesLogix.Web.controls.Adapters namespace (in Sage.SalesLogix.Web.Controls.dll) for examples.
Copy the assembly containing the control class into the same location as SageAppArchitect.exe, for example, C:\Program Files\SalesLogix.
Add the assembly name to the QuickFormsConfiguration.xml file in order to add it to the list of controls in Application Architect.
- Add a <string> node for the dll containing the new control class to the <ControlAssemblyRegistryserialized> node.
- Application Architect creates the XML file the first time it opens after installation. It places it in C:\ProgramData\Sage\Platform\Configuration\Application\SalesLogix (Vista) or C:\Documents and Settings\All Users\Application Data\Sage\Platform\Configuration\Application\SalesLogix (Windows 2008).
Create an NVelocity template for rendering your custom control and put it in your active model at Model\QuickForms\Web. See other templates in this folder for examples.
Add a <WebControlRenderingProvider> node to the Web.ControlConfiguration.xml file.
- Locate the xml file in the model, Model\QuickForms\Web.
- The node provides the information needed by the control rendering engine. Follow examples in the file.
- Reference the NVelocity template you created.
- Provide a PropertyMaps property for any quick form properties with names that differ from their corresponding properties in the rendered control. For example, the quick form control property, Style Scheme, is mapped to the web control property of CssClass.
The generator uses PropertyMaps when generating data binding code.

Cool indeed. No doubt that I will be covering more on this topic in the very near future.

Flattening Out SalesLogix Data To Combine Multiple Rows into a Single String using SQL

2009-10-01T17:43:00Z

A question came up from a person attending our current Report Writing class about how to flatten out data that exists as multiple rows in the database to a single string in a report. There is a really cool trick you can do in SQL to accomplish this which I previously outlined here:

See Flattening Out Data with One of the Coolest SQL Tricks Ever

In this post from 2005 I outline a technique that can be used to make a query append each value from the rows in the query to a variable. Without using cursors, you will end up with a variable that has the values appended to it as one single string. Go read my original post to understand this technique further (I'll wait).

Now, that you've read that, let's look at a practical example of using that technique with SalesLogix Data so that it could be included in a report. In this sample, we will be combining all rows of activity data (the date and description only) into a single string for each contact. So, instead of having multiple rows of activities for each contact, we'll end up with a string of activities like this: "3/2/2009 Lunch Meeting, 4/20/2009 Appoinement, 8/10/2009 Follow-up Call".

There are two database objects we'll need to create. First, we'll create a SQL function. This function will take in a parameter of a ContactID and will return a string of activities for that contact. This function will look like this:

create function sysdba.GetActivityListForContact
(
    @contactid varchar(12)
)
returns varchar(8000)
as
begin

    declare @list varchar(8000)
    set @list = ''

    select 
        @list = @list + convert(varchar, a.startdate, 101) + ' ' + a.description + ', ' 
    from 
        sysdba.activity a 
    where 
        a.contactid = @contactid 
    order by 
        a. startdate

    set @list = rtrim(@list)
    if @list <> '' set @list = left(@list, len(@list) - 1)

    return @list 

end

Now that we have that function, we can use it in a query, or wrap it in a SQL view as follows:

create view sysdba.vContactExtended 
as

    select 
          *
        , sysdba.GetActivityListForContact(contactid) as ActivityList 
    
    from 
        sysdba.contact

Now you can just add your vContactExtended view into the report and you'll have the new ActivityList column added where you'll see the activities listed all in a single field.

Groups Not Loading in SalesLogix 7.5 Web

2009-08-31T21:08:00Z

I recently saw an issue where groups would not load in SalesLogix 7.5.1 web. Not a single group would load. You'd see the spinning circle as if it were attempting to load the group. However, that would disappear and then...nothing. No group data would appear. Not just the data, but not even the group definition, so you wouldn't even see any column headers. With a little troubleshooting the problem was easy to track down, and the solution turned out to be a simple one.

The Problem

The problem was that no groups would load. None. Not for any user at all. It would look as if it were loading, but then you'd end up with nothing at all. No data, not even the column headers would show up as seen in the screenshot below:

Pretty frustrating since I had some difficulty thinking where I should start looking. Let's go through the process I followed to find the issue and solution before we just right to the fix.

Troubleshooting to Discover the Cause

It is sometimes difficult to know where to start with an issue like this. One thing I did know is that the groups are loaded asynchronously, from the client (not loaded on the server and returned to the page). So, it made sense that I would see a request being made from my browser to get the group data. There is a great tool to see that sort of thing called Fiddler. Fiddler is a tool that will let you monitor and watch all HTTP traffic from your computer to the server. Looking at the traffic we should see this request and hopefully it will tell us something useful as to why the data is not returning.

Looking at the traffic from my computer to the server hosting SalesLogix there is one item that stands out. A request is returning an HTTP 500 error (which indicates an error occurred on the server executing the request). The URL being accessed is the following:

http://saleslogix.customerfx.com/client/slxdata.ashx/slx/groups?family=TICKET&name=Ryans%20Tickets&format=json&meta=true&_dc=1251755197076&start=0&limit=150

Also, worth noting is that the request is returning the typical ASP.NET error. Ah. That will help. If the request is throwing an unhandled error it should be logged in the event logs on the server.

Looking in the event logs we can find the following error logged:

Event Type:    Error
Event Source:    Sage.SData.Service
Event Category:    None
Event ID:    0
Date:        8/31/2009
Time:        4:45:31 PM
User:        N/A
Computer:    CFXWEB
Description:
Exception caught during the processing of a message

Verb: GET
Uri: http://saleslogix.customerfx.com/client/slxdata.ashx/slx/groups?family=TICKET&name=Ryans Tickets&format=json&meta=true&_dc=1251755197076&start=0&limit=150

Original Message: Thread was being aborted.

Stack Trace:    at Sage.SalesLogix.Client.GroupBuilder.SLXSchemaInfo.RebuildXMLSchema(String constring, Boolean forceRebuild)
   at Sage.SalesLogix.Client.GroupBuilder.GroupInfo.GetXMLSchemaDocument()
   at Sage.SalesLogix.Client.GroupBuilder.GroupInfo.GetColumnsForTable(String TableName)
   at Sage.SalesLogix.Client.GroupBuilder.GroupInfo.UseUCField(String sortOn)
   at Sage.SalesLogix.Client.GroupBuilder.GroupsRequest.GetGroup(IRequest request)
   at Invoke58748ac5b47c438e91099062b3de65cf.Invoke(Object , IRequest )
   at Sage.Integration.Messaging.RequestTargetRegistration.RequestTargetInvoker.Invoke(IRequest request)
   at Sage.Integration.Messaging.Request.Process(RequestTargetInvoker invoker)
   at Sage.Integration.Messaging.MessagingService.Process(IRequest request)

Now we are getting somewhere. Looking at the stack trace we can see that the place this error is coming from is in the SLXSchemaInfo.RebuildXMLSchema method in the Sage.SalesLogix.Client.GroupBuilder.dll. Let's open up Reflector and take a look at what is going on there.

We know the error is happening when this method is called (last method shown in the stack trace). Going through it we see that the first item called is "isRebuildFlagged". The code in that method checks the results of the following query:

select datacode from plugin where pluginid='XMLSCHEMA'

If the value returned by that query equals "REBUILD_SCHEMA" then it flags to the code to proceed and then "loadDbschema" is called. In this method the XML Schema is recreated and saved to the database. Checking this query in the SalesLogix database I could see that the value returned was "REBUILD_SCHEMA". So, that is what is causing my problem, when the schema is being rebuilt.

Looking again at the event logs on the server that there was also another error logged immediately following the one I mentioned earlier. The error text logged was:

System.Web.HttpException: Request timed out.

The real issue here then, is that the call to rebuild the schema is timing out. It is taking longer to call the method to rebuild the schema than the default timeout setting on the server (the default ASP.NET execution timeout is 90 seconds).

The Solution(s)

Now that we know exactly what is causing the issue we can come up with a few reasonable solutions to fix it. Note, you do not have to do all of these, any single one of these solutions would work.

Increase the timeout on the server. To increase this you can open the web.config and look for the executionTimeout attribute in the httpRuntime node. This value will be set to 90 if it hasn't been changed before. Increase this number to a value large enough (in seconds) to allow the operation to complete. This will allow this to execute again in the future if needed, but use extreme caution when increasing the value to something too large as you'll be opening yourself up to potential attacks.
Open the LAN client and do something that will cause the XML schema to be rebuilt. One way to do this is to attempt to create a merge form. This will rebuild the schema if needed. This is a one time fix. If the schema changes and this flag is set again then this will need to be done again.
Simply clear the "REBUILD_SCHEMA" flag from the datacode field in the database and just set it to NULL. The schema won't be rebuilt, but it will stop attempting to rebuild it and the groups will load.

Conclusion

Well, there you have it. Of course, I could have just told you the problem and provide the fix. But that isn't really what this post is about (well it is, but there is more). This post was really more of an exercise in troubleshooting. That's the most difficult part of working with SalesLogix Web some times. Just knowing where to look and what to look for when an issue creeps in.

Determining if a User Has Read or Write Access to a Field in SalesLogix Web

2009-07-23T23:28:00Z

It is useful at times to be able to programatically determine at runtime if a user has read access, write access, or no access to a particular field at runtime. There are many different ways this information can be used to ensure you're giving the current user the best, and most understandable experience based on that particular user's security settings allow for. Luckily, with the introduction of the FieldLevelSecurityService in SalesLogix 7.5 this is an easy task. Let's take a look.

Let's say for this example, you want to check the current user's access level to the "AccountName" property on the Account entity. We'll do this in a Code Snippet Action, so we'll be passed a reference to the current form (let's assume we're doing this as an action on the AccountDetails form) which will contain a reference to CurrentEntity. The code would look like this:

// Assume they can read and write to the field
Sage.Platform.Security.FieldAccess access = Sage.Platform.Security.FieldAccess.ReadWrite;
 
// Locate the FieldLevelSecurityService 
Sage.Platform.Security.IFieldLevelSecurityService svc = ApplicationContext.Current.Services.Get<Sage.Platform.Security.IFieldLevelSecurityService>();   
if ((svc != null) 
{
    // Check if the current entity is secured
    if (svc.EntityIsSecured(form.CurrentEntity.GetType()))
    {
        // Use the entity as an IPersistentEntity 
        Sage.Platform.Orm.Interfaces.IPersistentEntity entity = form.CurrentEntity as Sage.Platform.Orm.Interfaces.IPersistentEntity;  
        // Get the access leve for the AccountName property on the Account entity (current user is built into the check)
        access = svc.GetAccessForProperty(entity, "AccountName"); 
    }
}
 
// Now the 'access' variable shows their access level
switch (access)
{
    case Sage.Platform.Security.FieldAccess.ReadWrite:
        // User has read/write access to Account.AccountName
        break;
    case Sage.Platform.Security.FieldAccess.ReadOnly:
        // User has read only access to Account.AccountName
        break;
    case Sage.Platform.Security.FieldAccess.NoAccess:
        // User has no access to Account.AccountName
        break;
}

Ideally you would wrap that up in your own assembly and then reference that as needed. However, there are other ways as well which makes all this even easier.

Typically, when you use an entity in SalesLogix you reference it via it's interface. This is a best practice for most cases, however, you can also cast the entity reference to it's actual implementation type, in this case Sage.SalesLogix.Entities.Account and make for a much easier time and a lot less code. All entities will inherit from Sage.SalesLogix.Orm.EntityBase. The cool part is that EntityBase has a built in method that does the code we did earlier. This method is called GetPropertyAccess. Since the Account (and all entities) inherit from EntityBase, this method comes built in. What all this means is that we can use this implementation instance type and just call that method, passing it the property name (as a string), like this:

Sage.SalesLogix.Entities.Account account = (Sage.SalesLogix.Entities.Account)form.CurrentEntity;  
switch (account.GetPropertyAccess("AccountName"))
{
    case Sage.Platform.Security.FieldAccess.ReadWrite:
        // User has read/write access to Account.AccountName
        break;
    case Sage.Platform.Security.FieldAccess.ReadOnly:
        // User has read only access to Account.AccountName
        break;
    case Sage.Platform.Security.FieldAccess.NoAccess:
        // User has no access to Account.AccountName
        break;
}

That is much easier and much cleaner, but knowing what that method does exactly makes things more flexible for you so you can do this with or without using the actual entity type and just stick to using the interface.

Creating Modules for SalesLogix Web and Hiding Toolbar Items at Runtime

2009-07-15T21:31:00Z

Creating modules for SalesLogix Web is something that is likely something that is often overlooked or not used because they are assumed to be too complex or are just not understood. A module is an assembly that you've added to a page or the portal that adds functionality to the page. The great thing about a module is that you can change things about the page without needing to resort to doing something like modifying the deployed pages or SmartParts. This article will look at how to create a module to conditionally hide a menu item at runtime.

Note: In this article I will be creating a module that removes the "Import Leads" menu for users that are not on the Midwest team.

To create a module, you simply just need to create a new Class Library project in Visual Studio and reference some of the Sage DLLs. The main ones you'll likely need referenced are:

Sage.Platform.dll
Sage.Platform.Application.dll
Microsoft.Practices.ObjectBuilder.dll

There will be other DLLs that you'll likely need to reference as well depending on what exactly you're doing in the module. The sample module in this post does include many others. The main thing is that your class in the DLL will need to implement the IModule interface from Sage.Platform.Application.dll.

When you implement the IModule interface, there are two things you must implement. The void Load method and a ModuleWorkItem get property returning a WorkItem. The shell of your module will look something like this:

namespace FX.Modules
{
    public class LeadMenuModule : Sage.Platform.Application.IModule
    {
        public void Load()
        {
            // implement load code here 
        }
        public WorkItem ModuleWorkItem
        {
            get { return null; }
        }
    }
}

Now we'll need to add the code into our Load to do the work of checking to see the teams the user is on, and if not on the Midwest team to remove the menu item. However, in order to do that, we'll need to access some of the available services for things like getting the parent WorkItem, using the UserService to get the current user, and so forth. We can easily get the references through dependency injection by adding the following to our class:

using Sage.Platform.Application;
using Sage.Platform.Security;
//...
 
private UIWorkItem _parentWorkItem;
[ServiceDependency(Type=typeof(WorkItem))]
public UIWorkItem ParentWorkItem
{
    get { return _parentWorkItem; }
    set { _parentWorkItem = value; }
}
 
private IUserService _userService;
[ServiceDependency]
public IUserService UserService
{
    set { _userService = value; }
    get { return _userService; }
}

Having those dependencies will make the work easy from here on out. One of the things we'll need is to determine if the user is on a certain team or not as well. We can reference one of my earlier posts to determine that (See Determining if a User is a Member of a Team in SalesLogix Web).

Removing the Menu Item at Runtime

Before we look at the code altogether, let's take a look at how to remove the menu item at runtime. It is easy enough to do now that we'll have a reference to the page Work Item.

using Sage.Platform.WebPortal.Workspaces;
using Sage.Platform.WebPortal.Services;
using Sage.Platform.WebPortal.SmartParts;
//...
 
// Get the ToolbarWorkspace
ToolbarWorkspace toolbar = _parentWorkItem.Workspaces["ToolBar"] as ToolbarWorkspace;
 
// Get the MenuService for the ToolbarWorkspace
IMenuService menusvc = toolbar.WorkItem.Services.Get<IMenuService>();
 
// Find the "Tools" menu by it's ID 
NavItemCollection navcol = menusvc.FindMenu("mnuTools") as NavItemCollection;
if (navcol != null)
{
    // Remove the "Import Leads" menu by using it's ID 
    navcol.RemoveItem("tools_ImportLeads");
}

One thing to note is that if you look at a deployed page, such as the Account.aspx.cs file, you'll see that in the Page_Load, the menu's are constructed *before* the modules are loaded. So, when our module is added to the page, the menus will be loaded and ready for us.

Now, let's see the complete code for the module:

using System;
using System.Collections.Generic;
using Sage.Platform.Application;
using Sage.Platform.Application.UI;
using Sage.Platform.WebPortal.Services;
using Sage.Platform.WebPortal.Workspaces;
using Sage.Platform.WebPortal.SmartParts;
using Sage.Platform.Security;
using Sage.SalesLogix.Security;
 
namespace FX.Modules
{
    public class LeadMenuModule : Sage.Platform.Application.IModule
    {
        public void Load()
        {
            User user = (_userService as SLXUserService).GetUser();
            if (user.Id.ToLower().Trim()=="admin") return;
            if (!IsOnTeam("Midwest", user))
            {
                ToolbarWorkspace toolbar = _parentWorkItem.Workspaces["ToolBar"] as ToolbarWorkspace;
                IMenuService menusvc = toolbar.WorkItem.Services.Get<IMenuService>();
                NavItemCollection navcol = menusvc.FindMenu("mnuTools") as NavItemCollection;
                if (navcol != null)
                {
                    navcol.RemoveItem("tools_ImportLeads");
                }
            }
        }
        public bool IsOnTeam(string teamname, User user)
        {
            bool res = false;
            IList<Owner> teams = Owner.GetTeamsByUser(user);
            foreach (Owner team in teams)
            {
                if (team.OwnerDescription.ToLower().Equals(teamname.ToLower()))
                {
                    res = true;
                    break;
                }
            }
            return res;
        }
        public WorkItem ModuleWorkItem
        {
            get { return null; }
        }
        private UIWorkItem _parentWorkItem;
        [ServiceDependency(Type=typeof(WorkItem))]
        public UIWorkItem ParentWorkItem
        {
            get { return _parentWorkItem; }
            set { _parentWorkItem = value; }
        }
        private IUserService _userService;
        [ServiceDependency]
        public IUserService UserService
        {
            set { _userService = value; }
            get { return _userService; }
        }
    }
}

Not too bad. It made fairly easy work to get the job done. Now we just add this assembly into the "bin" folder in the Support Files for the portal and then add the module in to the "Modules" tabs of the portal itself (not to a specific page) and the code will apply throughout the entire portal. On any page the user visits, the code will ensure the Import Leads menu item is removed.

Note, we could make this even more generic if we wanted to. Let's say we didn't want to hard-code the team name, or the menu item ID. We could simple add some properties to the class like this:

public TeamName { get; set; }
public MenuItemID { get; set; }

Now, when we add it to the portal we can click the "Configure" button next to it on the Modules tab and we'll see our properties for "TeamName and MenuItemID. We'd change the code to use those property values, instead of the hard coded ones, and then the administrator could simply use the Configure button to see the properties listed and enter the TeamName and MenuItemID to remove for anyone not on that team. Then a redeploy and the changes are live. Now you can change the values easily, without touching the code, or even reuse the module for something else.

If you want to take a look at the completed DLL (with the hard coded values), I've attached the Visual Studio project to this post (it will look for the references Sage DLLs in the usual Copy Assemblies location C:\Program Files\SalesLogix\ReferenceAssemblies).

Extracting Reports from the SalesLogix Plugin Table from External Applications

2009-05-29T20:09:00Z

There are many reasons why extracting a SalesLogix Report from the SalesLogix database might be useful. There are possibilities of integration, automated running of reports, as well as the need to generate a PDF from some code on a server using a SalesLogix Crystal report.

SalesLogix stores reports in the DATA field, which is a binary (BLOB) field in the PLUGIN table. Not just the report, but the SalesLogix report plugin, which contains not only the report itself but also the plugin info for the report. All together in a single BLOB field. This BLOB field is the serialized Delphi class named TCRWReportWrapper with the report file bytes appended to the end of the serialized class bytes.

Luckily, a serialized Delphi class will always end in two consecutive NULL bytes (0x00, 0x00) and will never contain two consecutive NULL bytes internally. This makes out job a bit easier in knowing where the serialized Delphi class ends and the bytes making up the report file begin. We can read out the bytes from PLUGIN.DATA for the report record and then read through them until we find the two consecutive NULL bytes, when write out everything following to a file and we'll have the report itself.

Here is some C# code to do just that:

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
 
namespace FX.SalesLogix.Utility
{
    public class ReportExtractor
    {
        public ReportExtractor(string ConnectionString)
        {
            this.ConnectionString = ConnectionString;
        }
 
        public string ConnectionString { get; set; }
 
        public void GetReport(string PluginID, string DestinationFile)
        {
            using (OleDbConnection conn = new OleDbConnection(this.ConnectionString))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand("SELECT DATA FROM PLUGIN WHERE PLUGINID = ?", conn))
                {
                    cmd.Parameters.Add(new OleDbParameter("PLUGINID", PluginID));
 
                    // Get the plugin byte array
                    byte[] data = (byte[])cmd.ExecuteScalar();

                    // Look for the two consecutive null bytes
                    int idx = 0;
                    for (int i = 0; i < data.Length; i++)
                    {
                        if ((int)data[ i ] == 0 && (int)data[i + 1] == 0)
                        {
                            idx = i + 2;
                            break;
                        }
                    }
 
                    // Get everything after the two consecutive bytes
                    byte[] rpt = new byte[data.Length];
                    Array.Copy(data, idx, rpt, 0, data.Length - idx);
 
                    // Write out to an RPT file 
                    using (FileStream f = new FileStream(DestinationFile, FileMode.CreateNew))
                    {
                        f.Write(rpt, 0, rpt.Length);
                        f.Close();
                    }
                }
            }
        }
    }
}

To use the code you'd do something like the following:

// Extract report to C:\MyReportFile.rpt
ReportExtractor rptextractor = new ReportExtractor(MyConnectionString);
rptextractor.GetReport("pDEMOA0000HQ", @"C:\MyReportFile.rpt");

Now you can do whatever you'd like with the report such as open it in an RDC object and generate a PDF from it.

Determining the Page Mode at Runtime in SalesLogix Web

2009-04-20T18:42:00Z

One of the keys to reusing a form for both inserting new records and displaying existing record detail is the ability to determine if the form is currently in insert mode or detail mode. There are several ways to determine this at runtime so you can take the necessary steps depeding on the current form mode.

Out of the box, there are three defined form types: Detail, Insert, & List

Some of the code we'll use will differ whether you're using a C# Snippet Action (where the code is added to the form itself) or a Code Snippet Action (where the code is added to a separate assembly and you're passed an adapter object to access limited form properties).

From a C# Snippet Action

Use the following code to access the exposed EntityForm property of the form and determine the form mode.

if (EntityPage.IsInsertMode)
    labelMode.Text = "Insert Mode";
else
    labelMode.Text = "Not insert mode";
 
 
// or
 
 
if (EntityPage.ViewMode == Sage.Platform.Orm.Entities.EntityViewMode.Insert)
    labelMode.Text = "Insert Mode";
else
    labelMode.Text = "Not insert mode";

From a Code Snippet Action

For a Code Snippet Action, you'll need to do things a little differently. In a Code Snippet Action you're only passed an adapter, or a wrapper, for the form, not the form itself. So, the problem is that you cannot access the page's EntityPage reference. You can still get to it however, you'll just have a few extra steps to dig through. Luckily, the form adapter exposes the SmartPart via the NativeForm property.

// First get a reference to the underlying SmartPart itself
Sage.Platform.WebPortal.SmartParts.SmartPart smartpart = form.NativeForm as Sage.Platform.WebPortal.SmartParts.SmartPart;
// Now get the SmartPart's parent page and use it as an EntityPage
Sage.Platform.WebPortal.EntityPage page = smartpart.Page as Sage.Platform.WebPortal.EntityPage;
// Now you can proceed as normal...

if (page.IsInsertMode)
    form.labelMode.Text = "Insert Mode";
else
    form.labelMode.Text = "Not insert mode";
 
 
// or
 
 
if (page.ViewMode == Sage.Platform.Orm.Entities.EntityViewMode.Insert)
    form.labelMode.Text = "Insert Mode";
else
    form.labelMode.Text = "Not insert mode";

Checking the Entity's ID Property

One last way you can use to check to see if the page is in Insert mode is to check the entity's Id property. If it is null then you have a new entity, not yet persisted to the database.

if (myentity.Id == null)
    // this is a new entity

The problem is that it is possible that this won't infer the correct mode. That is telling you that the object hasn't yet been persisted to the database, which might be enough, but you might want different behavior for new objects on the form vs the form being in insert mode itself. Just make sure that if you use this method to determine the page mode that you understand the differences so you know what you're really checking.

Accessing the Current Parent Entity from a Code Snippet Action

2009-04-08T15:00:00Z

The transition from using pre-version 7.5.1 "C# Snippet Actions", where the code is placed inline in the form, and the new "Code Snippet Actions", where the code is compiled into a separate DLL and a psudo-form reference is passed in, can be frustrating at times. I ran into yet another instance of that today. Before, we were spoiled using the C# Snippet Actions. We had access to everything on the form. Everything. That's no longer the case with Code Snippet Actions. While in a Code Snippet Action, I needed a reference to the current parent entity (not the current entity that is exposed via form.CurrentEntity, but it's parent). This Code Snippet Action was on a LoadAction of an Insert screen so the parent had not yet been set. Let's take a look at how to solve that.

Before you go on, read a previous post of mine where I discuss the differences between C# Snippet Actions and Code Snippet Actions if you've not done so already: Setting the Default Sort for a DataGrid in SalesLogix Web REVISITED

The Problem

The problem I needed to solve was get the current entity's parent from a LoadAction on an insert screen. At that time the parent had not yet been set so we couldn't just use myentity.ParentEntity to get it. If this were an older C# Snippet Action then I could have simply used the following:

IMyParentEntity myparent = this.GetParentEntity() as IMyParentEntity;

That would have been great. But this wasn't in a C# Snippet Action and "this" in a Code Snippet Action is an entirely different thing where GetParentEntity did not exist. My first thought was to use the form.NativeForm and cast it as an EntityBoundSmartPart. That seemed logical. The cast worked, however when I attempted to call GetParentEntity it failed due to it's protection level. Doh! It is protected so I can't call it from another assembly.

The Solution

Thankfully, one of the most useful things the form referenced that is passed in has is the page's WorkItem. Through the WorkItem you can get a reference to one of the many useful services in the portal. In this case, the EntityContextService. The EntityContextService has a method named GetEntity that will give you the currently accessed entity. Not the child entity that my dialog was for, but the entity the page itself is for, my parent entity. I wired up the following code:

// Get a reference to the EntityContextServce
// and call GetEntity() 
IMyParentEntity myparent = form.WorkItem.Services.Get<Sage.Platform.Application.IEntityContextService>().GetEntity() as IMyParentEntity;

That is it. It works and it isn't so bad. However, I do miss the C# Snippet Actions as well. I realize I can still use them even though they are marked as obsolete, however, I think there is also value in learning the new way to do things and living with the changes.

Adding a Default Sort to the Add Opportunity Products List in SalesLogix Web

2009-04-07T14:27:00Z

Usually by the third time I see a question asked, I realize it needs to be a blog post. I've been asked by a couple of customers how to add a default sort to the products list on the Add Opportunity Products form in the SalesLogix Web client. The question of how to add a default sort to the list of products in the Add Opportunity Products form in the web client surfaced again today so I am posting the answer here.

The approach to sorting the products list on the Add Opportunity products form is different than what I have outlined before for simply sorting a grid. The opportunity products list is not a DataGrid, but a Tree control.

To sort the products list, instead of telling the control to sort, we will just sort the list of objects that it is bound to.

First, locate the AddOpportunityProduct SmartMart. This is a custom SmartPart, not a QuickForm, so you won't find this under the Opportunity entity itself, but instead in the Sage SalesLogix Portal in following location (we'll just need the code-behind, or "cs" file for this, not the "ascx" file):

Support Files\SmartParts\Opportunity\AddOpportunityProduct.ascx.cs

Double-click that file to open it in the Application Architect. In the code, look for the method named GetProductList. You'll see the following two lines at the very end of that method:

List<Product> ProductList = criteriaProduct.List<Product>() as List<Product>;
return ProductList;

Right before those two lines, add the following line (in this case, we'll sort the list by family):

criteriaProduct.AddOrder(expProduct.Asc("Family"));

Obviously, you can replace "Family" with any other valid property name on the Opportunity entity to sort the results by.

Filtering a DataGrid in SalesLogix Web at Runtime

2009-04-02T17:22:00Z

Binding a DataGrid at runtime is something that many SalesLogix developers are used to doing while working in the LAN client. This task is every bit as easy in the web platform as well. Let's take a look at how to put this together.

Scenario

For this example, we will put together a tab on the account page. We'll call this tab "Related Accounts". It will have a Picklist, showing the Account Types picklist, and a DataGrid. As the user selects an account type value in the picklist we will display all accounts with that account type. Not a really useful customization, but one that will allow us to see how to bind the grid up at runtime using a user selected value.

Building the Solution

To put this together, let's start with building a busioness rule that we'll use to return the data for the grid. You can read more about using GetByMethod to bind a grid to a rule in my previous articles: Creating a Business Rule to Return a List of Objects and Binding a Grid to a List Returning Rule using GetByMethod.

Our business rule will return a list of accounts and will take in one parameter, which will be the user selected account type value. The rule will look like this:

Name: GetRelatedAccounts
Return Type: System.Collections.Generic.IList<Sage.Entity.Interfaces.IAccount>

Add a parameter as follows (click on the Add link in the Parameters section):

Parameter Name: TypeValue
Type: String

Now for the code:

public static void GetRelatedAccountsStep(IAccount account, string TypeValue, out System.Collections.Generic.IList<Sage.Entity.Interfaces.IAccount> result)  
{
    IRepository<IAccount> repository = EntityFactory.GetRepository<IAccount>();
    IQueryable qry = (IQueryable)repository;
    IExpressionFactory ef = qry.GetExpressionFactory();

    ICriteria criteria = qry.CreateCriteria();
    criteria.Add(ef.Eq("Type", TypeValue));

    result = criteria.List<IAccount>();
}

Now, we'll need one more business rule. The SalesLogix DataGrid control itself will throw an error if it does not have a DataSource set at build time. We're not going to really use the SalesLogix DataSource component at all since we'll be binding up the grid at runtime. However, it still requires this or it won't build. To get around this we will have to add a DataSource component to the form as well. Now, we'll also get errors if we have a DataSource component that is not set to return some data. We can't use GetByMethod with our new rule we created since our rule takes a param and there is no way to set that for the DataSource. What we will do is create another business rule that returns a blank, empty list of accounts and we'll use that for the DataSource, just so things compile.

This is what our empty rule will look like:

Name: GetEmptyAccountList
Return Type: System.Collections.Generic.IList<Sage.Entity.Interfaces.IAccount>

The code will be as follows:

public static void GetEmptyAccountListStep( IAccount account, out System.Collections.Generic.IList<Sage.Entity.Interfaces.IAccount> result)  
{
    result = new System.Collections.Generic.List<IAccount>();
}

Now you can use that rule that returns an empty list to set up as the GetByMethod for the DataSource component and then wire the DataSource to the grid. Also throw a Picklist onto the form using the Account Type picklist.

The picklist will have some code in the OnChange action. Add a "Code Snippet Action" to the OnChange of the Picklist control. The code for that will look as follows:

public static void picklistType_OnChangeStep(IRelatedAccounts form, EventArgs args)
{
    // Get a reference to the underlying ASP.NET GridView control
    System.Web.UI.WebControls.GridView grid = (System.Web.UI.WebControls.GridView)form.gridAccounts.NativeControl;  

    // Make sure a value was selected in the Picklist
    if (form.picklistType.PickListValue != null && form.picklistType.PickListValue != string.Empty)
    {
        // Get a reference to the account and call the rule, passing the picklist value
        IAccount account = form.CurrentEntity as IAccount;                
        grid.DataSource = account.GetRelatedAccounts(form.picklistType.PickListValue);
    }
    else
        grid.DataSource = null;

    // Call the DataBind method of the GridView to set the binding 
    grid.DataBind();
}

That is it. The key is to get a reference to the native underlying ASP.NET GridView control and then do the binding just as you would normally with that control. The end result will look like this:

I've also included a bundle of the code and form for this sample as an attachment to this article. Have fun.

Relationship Property Names and Case

2009-03-27T18:38:00Z

In today's SalesLogix Web Developer class, we discussed an issue that someone in the class was having at runtime with a relationship that had a property that started with lower-case letters. Here are the details of that issue and the fix:

Scenario

Create a entity named "testProject" and then relate it to the Account entity. In the relationship, the Account will have a collection named "testProjects" (notice the property name starts lower case). You'll then use that relationship when you wire up your lookup on a form.

Problem

Every things builds fine, however at runtime you get an error saying "Could not find field '_TestProjects' in class Sage.SalesLogix.Entities.Account". Notice that the error refers to the property with an uppercase "T".

Solution

To fix this you just need to simply delete (or change) the relationship property to NOT start lower-case. Always name your relationship properties Proper-case, starting with an upper case letter always.

I don't know why this problem occurs. Seem that somewhere in the system an assumption is being made that the property name will be proper-cased (starting with an upper-case letter). However, I can tell you that you don't see the problem if you use the first letter upper-cased for the properties created in the relationship. Weird.

Setting the Default Sort for a DataGrid in SalesLogix Web REVISITED

2009-03-26T21:28:00Z

A while back I wrote an article on setting the sort for a DataGrid in SalesLogix Web at runtime. That solution worked great in SalesLogix 7.2. However, things changed in SalesLogix 7.5 so I thought it was time for an update to that original post. Let's see what changed and how to make it work again.

Take a look at the original article

In the original article, I mentioned that all this was possible because the SalesLogix DataGrid (Real class name SlxGridView) inherited from the ASP.NET GridView control. That hasn't changed. However, here is what has changed:

Change #1: Where you LoadAction code is located

The original "C# Snippet Action Item" has been deprecated. This is what was used before. Now there is a "Code Snippet Action Item" is what is used for code in LoadActions. The reason why this is significant is that the deprecated "C# Snippet Action Item" was placed inline in the SmartPart/ASCX file. So, you could rely on the fact that you could easily just reference controls on the form since the code also existed on the form. Now, with the new "Code Snippet Action Item", the code is compiled into a separate assembly, much like the code in business rules. You are passed a reference to the form - which is not really a reference to the SmartPart class itself, but instead via an object that implements IForm and IEntityForm.

Change #2: In LoadAction code, you no longer get a direct reference to the controls

This form object mentioned above exposes the controls on the form, however, not as the real control type, but as an adapter that wraps the control - only exposing certain things. For example, instead of exposing a reference to your SlxGridView (the datagrid), you get a reference to a faux-grid the implements IDataGridControl. This interface only provides access to a few handful of properties of the grid and the columns. The IDataGridControl implements IControl, which provides a few additional things, most notably, it provides a reference to the actual underlying control via an object property named NativeControl.

How do we make this work?

Using the IControl interface, you can take the control adapter you are passed in the LoadAction code and get to the native control. That is a simple task and then you'll be able to do anything you need with the native control reference. Here's some sample code that get's a reference to the native control and then does the sorting, just like before:

public static partial class RelatedAccountsEventHandlers
{
    public static void Step(IAccountTickets form,  EventArgs args)
    {
        System.Web.UI.WebControls.GridView grid = (System.Web.UI.WebControls.GridView)form.grdAccountTickets.NativeControl;  
        grid.Sort("ReceivedDate", SortDirection.Descending);
    }
}

Not so bad. The trick is knowing how to get to the underlying control from the adapter reference you are passed. One thing to note is that the code in the original article will still work in code in the now deprecated "C# Snippet Action Item", so if you've used the code prior to 7.5, it will still work in the C# action. However, for new systems you need to use the new code above.

Presentation of the Infusionsoft Outlook Integration at InfusionCon

2009-03-12T00:19:00Z

I was out at InfusionCon today, the annual user conference for Infusionsoft, for the public unveiling of the Infusionsoft Outlook Integration with Patrick Sullivan. I've been really excited for the Outlook integration to get into the hands of the Infusionsoft masses because, frankly, it is so completely cool. Patrick and I met up early to make sure all was working and ran through the demo several times.

Flash forward to the actual demo. Things started well. There was even loud applause and cheering. That's when things went downhill. Doh! Patrick's Outlook started to try to connect to Exchange and sync up folders via the wireless at the resort (something that it wasn't doing via Patrick's Verizon wireless that we were rehearsing with). The connection to his Exchange server just wasn't happening via the wireless connection from the resort so the result was that Outlook started freezing up while trying to sync the folders. Needless to say this made it, er, difficult for Patrick to continue demoing the integration. To be honest, it was disappointing to not get to show more than what we did, not because we were struggling through getting Outlook to behave, but because I think the Outlook integration rocks and I wanted for the Infusionsoft users to get to see all that it can do.

However, it was surprising that despite the difficulties in demoing the integration that there was so much positive feedback about the integration. I guess people saw enough before the problems started to know that they liked what they saw. When it comes to knowing how software and UI should work, Patrick is king. I think we've captured a completely unique yet intuitive experience in the integration. Best of all, this is just the beginning. If you've seen the integration or you're using it already, be prepared to have your socks knocked off throughout this year. There is some mind-blowing stuff that will be coming in the integration. It's great already, but we're going to be taking it up to 11 :-)

For those of you who have not yet seen it, you can see a short demo (the one that Patrick was intending to do at InfusionCon) here (be sure to click the "Full Size" button for full effect). However, keep in mind that Patrick is using a new untested build (not the same publically available version) so there are a couple of very quick nasties that pop up in the video.

View Patrick's Quick Video Demo of the Infusionsoft Outlook Integration
(Be sure to click the "Full Size" button)

All in all, it was great to be at InfusionCon with the guys from Infusionsoft. What a completely exciting company.

Determining if a User is a Member of a Team in SalesLogix Web

2009-02-04T21:13:00Z

Five years ago I wrote a post about how to determine if a user is a member of a team in SalesLogix LAN scripts that is still referred to today. I've been asked on occasion how one would go about doing the same in the SalesLogix Web platform, so I thought it was time for a follow up post. In this post I will demonstrate how to use a HQL query to create a reusable function in the SalesLogix Web platform to determine whether a user is a member of a specified team.

We will be creating this as a reusable method that we'll pass a User object to as well as the team name we want to check. Let's look at the code first and then we'll look at how to use it and where you might create this reusable code:

private bool IsUserOnTeam(Sage.Entity.Interfaces.IUser user, string team)
{
    using (NHibernate.ISession session = new Sage.Platform.Orm.SessionScopeWrapper())
    {
        return 0 < Convert.ToInt32(session.CreateQuery("select count(*) from OwnerRights r where r.Owner.OwnerTypeChar = :type and r.Owner.OwnerDescription = :teamname and r.User.Id = :accessid")
                   .SetAnsiString("type", Sage.SalesLogix.Security.StringEnum.GetStringValue(Sage.SalesLogix.Security.OwnerType.Team))
                   .SetAnsiString("teamname", team)
                   .SetAnsiString("accessid", user.Id.ToString())
                   .UniqueResult());
    }
}

The code above queries the SalesLogix entity model, looking at the OwnerRight entity specifically (this maps to the SECRIGHTS table in the database), and then limits that with conditions that the OwnerTypeChar property has the corresponding value for a team, the OwnerDescription has the correct team name, and to limit that to the OwnerRight entity instance where the UserId matches the Id of the User we pass in.

The code to use it might look something like this:

Sage.SalesLogix.Security.SLXUserService usersvc = (Sage.SalesLogix.Security.SLXUserService)Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Security.IUserService>();
Sage.Entity.Interfaces.IUser user = usersvc.GetUser();
 
if (IsUserOnTeam(user, "Midwest"))
{
    // user is on the Midwest team
}
else
{
    // user is NOT on the Midwest team
}

Now, where might we implement something like this? Personally, I would create this as a separate assembly in Visual Studio and plug in where needed. However, you could also implement this in a business rule as well. If you implemented this code in a business rule, you'll need to make sure the rule has the correct references. Otherwise it will produce an error when you build the web platform saying something about "are you sure you are not missing an assembly reference?".

By default, the rule will have references to the following:

Sage.Entity.Interfaces.dll
Sage.Form.Interfaces.dll
Sage.Platform.dll

For the code I outlined above, you'll also need to have references for:

NHibernate.dll
Sage.SalesLogix.Security.dll

I won't go into a whole lot of details now about how to add these references, I'll have those steps outlined in a future post. However, while in the rule editor, open the Property pane (F4), click the drop-down at the top of the Properties pane and select "Sage.Platform.Orm.Entities.CodeSnippetHeader". Then you'll see properties for the CodeSnippetHeader for your code. You can bring up the AssemblyReferences and add the ones I mention above to that dialog. Things should complile fine now and you could shorten the code a bit too since you can add usings for these namespaces and drop the fully-qualified names of the objects. Just make sure you don't try to add this rule to the User or other Security entities (See Accessing User and Security Entity Properties in SalesLogix Web)

One that thing I wanted to point out as well. The Owner object in Sage.SalesLogix.Security also has a similar function that will return a List<Owner> of all teams a specified user belongs to. That can come in handy as well. Here's a sample that you could add to a LoadAction on a form to display a list of the teams the current user belongs to in a multi-line TextBox on the form called QFTextBox (Note: for this code, you do need a Sage.SalesLogix.Security.User object, not just a Sage.Entity.Interfaces.IUser reference since the method specifically takes a User, not IUser, parameter):

Sage.SalesLogix.Security.SLXUserService usersvc = (Sage.SalesLogix.Security.SLXUserService)Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Security.IUserService>();
Sage.SalesLogix.Security.User user = usersvc.GetUser();
 
System.Collections.Generic.IList<Sage.SalesLogix.Security.Owner> teamlist = Sage.SalesLogix.Security.Owner.GetTeamsByUser(user);
 
string list = string.Empty;
foreach (Sage.SalesLogix.Security.Owner team in teamlist)
{
    list += team.OwnerDescription + "\r\n";
}
QFTextBox.Text = list;

This code could also be used to check if a member is on a given team. However, the implementation of the method used in the Owner object will also return all the data in the List as well (and if I would have just used that I couldn't have shown you the example using the HQL query).

Accessing User and Security Entity Properties in SalesLogix Web

2009-01-30T23:34:00Z

There are likely many cases in SalesLogix Web where you need to access properties on the User and Security related entities that are not included in the entity by default, meaning that there is no public property exposing the value. For example, the IsManager property on the User entity. A co-worker of mine ran into just this problem this week (See Retrieving the Manager in SalesLogix 7.5 Web)

The Problem

The root of the problem is that there are some often necessary properties on the User & Security related entities that are not included in the entity. See the image below:

Notice in the image above that the "Include" checkbox is not checked for the IsManager property on the User entity. What the Include value means is whether to include a public accessor property for this when the entity is built. So, you might think that checking this checkbox and doing a build will make it available to you at runtime. You build & deploy and get no errors. However, when you attempt to access the site you get a runtime error that states the following:

System.TypeLoadException: Method 'get_ManagerId' in type 'Sage.SalesLogix.Security.User' from assembly 'Sage.SalesLogix.Security, Version=7.5.0.1484, Culture=neutral, PublicKeyToken=null' does not have an implementation

Here is why you are getting this error; the "Packages" in the entity model are more than just for the sake of organization. The "SalesLogix Security Support" package of entities does not just exist to separate these entities from the others. Each package is built to a separate assembly. They can have different templates for code generation. They can also have properties to indicate whether they are built into an assebmly at build time at all. This is the case with the User and Security entities in the SalesLogix Security Support package. If you open the package itself and "Build Settings" tab you will see something different about this package.

You can see in the screenshot above that the SalesLogix Security Support package isn't event built when you build the web platform. However, it's interface is. When you check the Include checkbox on the IsManager property it is added to the interface. This is why you don't get a compile error when you use it in a code snippet because you're likely using the User entity via it's interface. However, since the assembly is not built as well there is no implementation for the IsManager property that now exists in the IUser interface, hence the "does not have an implementation" error. Checking the box for "Generate Assembly" on the package won't fix the problem. If you do, you'll get compile errors. These User and Security entities are available in the Application Architect so you can create relationships to them from other entities, however their implementation is quite different and the standard code generation templates will not account for all the extra things they do. So, what is the solution?

The Solution

Knowing why you're getting the errors and what the problem is does not exactly help in getting to these not-included values that you need from these entities. However, there is hope. When you access any entity in SalesLogix code, you're likely accessing the entity via it's interface (as you should). However, I mentioned above that there is a lot more to the implementation of the User and Security entities. That is where you'll find what you need.

Normally, you would access a User, such as the current user, like this:

Sage.SalesLogix.Security.SLXUserService usersvc = (Sage.SalesLogix.Security.SLXUserService)Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Security.IUserService>();
Sage.Entity.Interfaces.IUser user = usersvc.GetUser();

Note that you are accessing the User via the IUser interface. Now, the IUser interface is just a standard entity interface. Since the interface is being built when you do a "Build Web Platform" (note, the checkbox to build it's interface is checked for the package) it is a true representation of what you see in the Application Architect. However, there is quite a bit more to it's implementation found in the User class. Fire up Reflector and take a look at some of the things available in Sage.SalesLogix.Security.User:

Notice all the extra goodies there to use? For example, the Manager property, that returns a Manager object. The Manager class simply inherits from User, meaning that it has all the properties and methods that a User object has as well as a few more. To use this property, as well as the others in the User class, you just need to use the reference via the User class instead of via the IUser interface. Something like this:

// get the current user as a User (not IUser)
Sage.SalesLogix.Security.SLXUserService usersvc = (Sage.SalesLogix.Security.SLXUserService)Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Security.IUserService>();
Sage.SalesLogix.Security.User user = usersvc.GetUser();

// now get the user's Manager
Sage.SalesLogix.Security.Manager manager = user.Manager;
string manageremail = "Manager e-mail: " + manager.UserInfo.Email;

Note that the code is basically the same as when you accessed the User via the IUser interface. You just use the actual implementation class instead and you can get to what you need.

A Solution for All Cases?

This is not a solution for every case. There will without question be some properties that you just can't get to. The "last resort" solution to access these inaccessible properties is to go to the database. You can easily get a connection to the underlying database and use that to query for the value you're after. Here's an example:

// get the manager's e-mail address using a connection to the database
//
// Note: this is just for example purposes, instead of doing this
// use the user.Manager property available on Sage.SalesLogix.Security.User

string manageremail = string.Empty;
// get the DataService to get a connection string to the database
Sage.Platform.Data.IDataService datasvc = Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Data.IDataService>();
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(datasvc.GetConnectionString()))
{
    conn.Open();
    using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select u1.email from userinfo u1 inner join usersecurity u2 on u1.userid = u2.managerid where u2.userid = '" + user.Id.ToString() + "'", conn))
    {
        object o = cmd.ExecuteScalar();
        if (o != null) manageremail = o.ToString();
    }
}

This is not an ideal solution, and isn't meant to be.In my opinion, accessing the database directly should absolutely be a last resort and avoided if possible. Why, the entity model is rich and complete in most cases. The majority of times I've seen someone go direct to the database it is more than likely because of a lack of knowledge of how to do something in the entity model, not because the entity model could not provide what was needed. Business rules that might be in place in the entity model will not apply to things you do in the database (unless they are implemented in Process Orchestration).

Ideally these properties in the implementation that are hidden by their absence in the interface would just be included in the interface. However, knowing that some of these things are there, and how to get to them, will definitely save you some headaches.