George Jensen's Blog

SalesLogix Web Form Designer

2012-11-15T17:28:00Z

New to the Sage SalesLogix v8.0 Web Client is the Web Form Designer. This tool will allow a user, with a proper role assignment, the ability to modify main entity forms in the the web client using existing database fields. Here is a quick introduction to the tool from Customer FX.

For this demo I am using a Sage SalesLogix v8.0 RC3 Web Client with an evaluation database set up in the Cloud. I found the information on this tool using the SalesLogix 8.0 Web Client Help Files with the key word search of "Working with Forms". The number one listing in the results are will be "Working with Forms". I found these files extremely helpful and accurate while I learned to use this tool.

In order to use this tool you must be logged in as the Admin user or a User assigned to the role of "Administrator". I set our user Lee Hogan up in my system with the Administrator Role.

There are two ways to bring up the Web Form Designer tool. The first is to go through the Administration Navigation panel and find the Form Manager icon:

This icon opens the Web Form Manager with a list of all forms in the system that are editable:

Using this area, if you select the form you want to edit it will display in the Web Client ready for editing.

The better option is to use the icon that you will find on the tool bar of the form that you want to edit. This option opens the form in another browser window separate from the Web Client. For example navigate to an Account record in the Web Client.

If you look just to the left of the Help button in the upper right hand corner of the view you will see the Form Manager icon (Shown Earlier). Select this icon while holding down your "Ctrl" key and the Account Detail form opens in the Web Form Designer in a separate window.

Here you can modify the form as described in the SalesLogix help file.

Change captions and other control properties
Move and hide controls
Add controls (Add data fields to a form)
Change form, row, and column properties

A couple of words of caution before you start to modify the form:

Only existing fields are available. You cannot add custom fields through this tool. You need to have some experience working with the SalesLogix Database Manager and the Application Architect in order to add custom fields into the SalesLogix database and have them available in the Web.
You can make any change you like to this form and it will not effect your the production system until you save it. Feel free to rearrange and add fields, if you do not like what you see just select your F5 key and the screen will go back to the original configuration. Once you save the change the form is immediately released to production system and will be visible to all of your users.
If you save the form and there is a field that you do not want to have on the form, your only option though this tool is to make the field not visible. You can go into the Application Architect and delete the field but you should know what you are doing before attempting that course of action.

On the extreme right of my screen shot you will see the list of existing fields that are available. Lets add USERFIELD1 to the Account Detail form to collect the Accounts AKA (Also Know As) value.

We need to make room for the field on the form. Right click on the form content and a menu will come up with the selection of "Add Column" or "Add Row", there will also be placement sub menu options. I will select "Add Row" and "Below".

For my example I want to add USERFIELD1 just below my Division field. I will arrange my screen layout and move the address block to the bottom of the column.

From the "Add Field" list on the right I will select "USERFIELD1 and drag it onto the form in the space above the address block.

The field is now on the form and labeled User Field 1. To the change the label, click on the new field, below the form you will see a properties area with three tabs, select the "Basic" tab, look for the "Caption" property, now enter your new label -"AKA:"

To release this change select the Save button and refresh the Web Client - The field appears!

Very cool.

OK, I know all of the System Administrators are asking "What if I update the form through the Application Architect, will I over write this change?"

I went into the Application Architect with the same concern. "Never fear, the change is here"

Again - very coo!

A couple of additional notes:

This tool does not replace the Application Architect. You cannot create new forms or modify custom forms and there is no way of adding code.
You cannot modify a list view like the Account Contacts, the icon is there but the fields that display are Account layer fields without reference to the contact layer.

Have fun using this great addition to the SalesLogix Web Client!

George

Working with the Picture Object in Crystal

2012-08-17T17:16:00Z

We have had several inquires lately on how to dynamically change images in a report based on report data.

For example you have pictures of products that you want to display in a report. One way to accomplish this is to add an image with each product in a huge segmented report and have Crystal sort things out through suppression rules. This is a valid solution but the report is going to become very unmanageable as your inventory grows and than changes. The best case is to store the picture in a central folder and call the image into your report based on the data.

With SalesLogix you have the ability to store attachments to records which can be be documents, spreadsheets, pdf's, or pictures. This provides a perfect area for managing images. But - the standard Products area in SalesLogix does not have the attachment capability.

I came up with an easy work around that will allow you to create a Product Report that displays the correct picture that you maybe able to relate to a report that you are working on.

I am using the evaluation database for a SalesLogix version 7.5.4 system. This database is owned by a company called "Phoenix Computers, Inc." and comes with product records we can use for our example.

The first thing I need is some pictures of some of the products. I searched on Google Images for photos of a some common products that are already in the database. I found images for the "BlackBerry", the "Dell Latitude", the "Dell Optiplex", and the "MacBook Pro". The pictures are all stored as .jpg

I went to the Phoenix Computers, Inc account record and added the pictures to the Attachment Tab. You could also create a dummy account for this purpose if you want but whereever you decide to put the attachment we will need the record name.

Above are the images in the attachment tab, notice the file description (Attachment column) matches the Product Name.

Now we can build the report. Open up Crystal and make your connection to the database. In the base report I am just going to pull in the PRODUCT.NAME and PRODUCT.FAMILY fields. For the purpose of this report I am going to set the Record Selection formulas to {PRODUCT.NAME} in ["BlackBerry","Dell Latitude","Dell Optiplex", "MacBook Pro"] which are our target products.

I am going to add two sub reports. The first is for pricing which will use the PRODUCTPROGRAM table. I will link the sub report to the base report using the PRODUCT.PRODUCTID (base) and PRODUCTPROGRAM.PRODUCTID (sub report). For this example I brought in the PROGRAM and PRICE fields.

The second sub report will be the one that contains the picture. The ATTACHMENT table is the primary table for this sub report and stores all attachment data for the entire database so to help narrow down the data I will include the ACCOUNT table. Using the ACCOUNT.ACCOUNT field we can create a record selection formula that limits the Attachments to the Account record where I am storing the images. Here is that formula:

{ACCOUNT.ACCOUNT} = 'Phoenix Computers, Inc.'

The next step in this report is to make formula field that provides the path to the Images using the data in the Attachment table. The ATTACHMENT.DESCRIPTION field gives us the name of the image as we entered and is the link we will use to match to the image to the records in the Product table. The other field is the ATTACHMENT.FILENAME fields which provides the unique name SalesLogix gave the image when we entered it into the database. The images are really stored in a shared foler and the path for the folder is set in the SalesLogix Administrator under the Systems/Office tab. If you double click on your office name in the list you will get the following screen.

At the bottom you find the Attachment Folder path. You will also need the name of the server where the folder is located. For my example I have a server name of w2003-dts. With this information I am able to create a formula field called "Path".

The formula inside the field is (using my example) : '\\w2003-DTS\Sage_SalesLogix_Demonstration_Tools\Demo_Database\Documents\'+{ATTACHMENT.FILENAME}

Next go to the Crystal Insert menu and select "Picture" from the list that appears. Immediately a search box comes up which will allows you to search for a default image. This default will display when the report cannot find an image at the path you provided. I like to use a screen shot with a white background and the text of "Picture Not Available".

Place this picture in the details section of the sub report. Than, suppress all other section of the report. Here is what my "picture" sub report looks like so far.

Now, to get the correct picture to display based on the product name listed in the report, right click on the image and select "Format Graphic" from the list that appears. Navigate to the Picture Tab and find the field labeled "Graphic" toward the bottom of the tab and select the button to the right, the Formula Workshop Editor appears.

Double click on the formula field labeled "Path", Save and Close the editor.

Finally we need make the link to the base report. If you remember we described each attachment with name of the product it represents. This allows us to match the data in the sub report to the main report using PRODUCT.NAME (Base) to the ATTACHMENT.DESCRIPTION (Sub-Report).

When I run my report I see the images are displaying next to the product information.

So, by using just one picture object we are able to call different images and match them to correct product.

Very cool. Thanks for reading!

geo

CFX Reports Are Updated

2012-07-27T17:51:00Z

I am very happy to back after 19 months of Military leave. One of my first tasks upon returning to my desk was to clean up my Customer FX email inbox that was full with a about 4,000 new emails, mostly automatic notifications from various resources. What surprised me the most was the number of download notifications from Box.net which is where we store all of the SalesLogix reports that were refreshed through this blog. Seeing that these reports are still being downloaded and with Sage SalesLogix (SLX) currently at 7.5.4 with the release of 8.0 just around the corner, I thought I should take a look at all of the reports and ensure they are still working. I set up a SLX 7.5.4 test environment and reviewed each report one by one. Most of them worked just fine but I did find somethings to adjust on 9 of the reports.

Use this link as a guide to all of our updated reports. The ones that were updated are marked by the words Updated 07/20/2012.

I will share some of the things I learned while doing this review during the next few weeks.

Thanks Geo

Setting up a date range parameter and displaying it in the report

2011-07-06T20:20:00Z

In a recent report, I needed to return data based on a date range selected. That's easy enought to put in... you just create a new Parameter field, set the type to "Date" and make sure the "Allow range vales" option i set to true. With the parameter field created, you need to add a record select formula that reads like this...

(TABLENAME.DATEFIELD) = {?DateRange Parameter)

...and all the records returned will be in that date range.

I also needed to display that range value in the report, which took a little digging to figure out. All you need to do is to create a Formula field with the following code:

WhilePrintingRecords;

DateTimeVar date1;
DateTimeVar date2;

date1:=Minimum({?DateRange Parameter});
date2:=Maximum({?DateRange Parameter});

totext(Date(date1)) + " and " + totext(Date(date2))

Then all you need to do is slap that formula field somewhere on the report, and it will display the date range selected in the parameter.

Thanks for reading! I hope you found this helpful!

Jason

Supressing the blank page after "New Page After" Group Footer setting

2011-06-08T15:59:00Z

I've worked with Crystal for some time, but there's always been one little thing that sort of bothered me, and I had never took the time to figure out how to deal with it.

If you have a group in your report, and want to start a new page after each grouping, you end up with a blank page at the end of the report. To get rid of this blank page, all you need to do is to add a simple script to the New Page After property of the group footer:

As you can see, we are on the group footer. In the selection expert, check the New Page After property and then click it's formula button. In the formula, you just need to compare the resident PageNumber variable with the TotalPageCount variable. If they are equal, then you are on the last page and the page will suppress.

Specifically, add the following code:

PageNumber = TotalPageCount

And you should be good to go. No more blank page at the end of the report!

I hope you find this tip helpful. Thanks for reading!

Jason

Setting custom date ranges in Crystal

2011-05-27T12:07:00Z

With George on deployment with the Minnesota National Guard, I'll be occasionally posting on various reporting topics.

Recently, I had to develop a report which was to return data within a particular date range. Specifically, a user would need to select a date and the report would then return data from a range of Monday of the previous week to Friday of the current week. I was able to accomplish this fairly easily using Global and Local variables and a couple of different date functions.

Basically, I needed to get the day of the week for the date selected, and then add or subtract days as necessary to come up with an appropriate number to use with a DateAdd function. All this is done in the record selection formula for the report:

Global DateVar sStartDate;
Global DateVar sEndDate;
Local NumberVar nDayOfWeek := DayOfWeek({?Date}); //Determine the Day of the week selected.
Local NumberVar nAddStart;
Local NumberVar nAddEnd;
//Determining the number of days to add to the Start Date
If nDayOfWeek = 1 Then
    nAddStart := -6;
If nDayOfWeek = 2 Then
    nAddStart := -7;
If nDayOfWeek = 3 Then
    nAddStart := -8;
If nDayOfWeek = 4 Then
    nAddStart := -9;
If nDayOfWeek = 5 Then
    nAddStart := -10;
If nDayOfWeek = 6 Then
    nAddStart := -11;
If nDayOfWeek = 7 Then
    nAddStart := -12;
//Determining the number of days to add to the End Date 
//(Note, Start and End date have to be done in separate If..Then blocks, otherwise it will not work)
If nDayOfWeek = 1 Then
    nAddEnd := 5;
If nDayOfWeek = 2 Then
    nAddEnd := 4;
If nDayOfWeek = 3 Then
    nAddEnd := 3;
If nDayOfWeek = 4 Then
    nAddEnd := 2;
If nDayOfWeek = 5 Then
    nAddEnd := 1;
If nDayOfWeek = 6 Then
    nAddEnd := 0;
If nDayOfWeek = 7 Then
    nAddEnd := -1;
//With the number of days to add/subtract to both dates determined, I use the DateAdd function to return the correct
//Start and End Dates
sStartDate := Date(Year({?Date}), Month({?Date}), Day(DateAdd('d', nAddStart, {?Date})));
sEndDate := Date(Year({?Date}), Month({?Date}), Day(DateAdd('d', nAddEnd, {?Date})));

//Finally, I use the new dates in the selection criteria.
{History.completeddate} >= sStartDate and
{History.completeddate} <= sEndDate

Pretty straight-forward, I think. Depending on your situation, it would just be a matter of figuring out your range and what values to add or subtract to the current date value.

I hope you find this helpful... Thanks for reading!

Jason

Leveling out Users Data.

2010-12-01T14:00:00Z

I am four posts into building a drill down report using Sage SalesLogix Visual Analyzer and Dashboard as a my guide. This series of post starts with a post labeled "Updating the Managed Quota Area for Sage SalesLogix" , if you follow the links on each post you will eventually end up back on this post. In the last post we started building our report which gave us our performance numbers for comparison. Today, we are going to go back into SQL Server Management Studio and build a view that will allow us to level out all of the user related data. This will allow us to display in one group the Accounts, Contacts, Opportunity, Ticket, History, and Activity data related to the user.

What we will use to bring all of this data together from six different tables is a well designed SQL view that uses a Union join. This join allows you to combine data from several select statements into one view. The key to a union join is each Select statement has the same number of columns labeled in the exact same order. So what columns do we need in our view?

An obvious answer is a way to relate each of the records from the previously mentioned tables to the user.
There also should be a way to categorize the record to tell us what type of record we are creating.
Thinking ahead, I want to include a column which will allow us to filter our report by a data range.
We need one column that will list each table primary id that will indicate a record does exist for the user and will allow us to group the records under the category.
Finally, we will need a column for each of the table primary id, this will allow you to join to the table and show the detail from the record under the category group.

So lets layout our columns:

Category
FilterDate
UserId
EntityId
AccountId
ContactId
OpportunityId
TicketId
HistoryId
ActivityId

The first step is to create the Account select Statement. This is what I came up with:

Select 'Account' as Category, a.Filterdate, a.Userid, a.AccountId as EntityId, a.AccountId, '' as Contactid, '' as OpportunityId, '' as TicketId, '' as HistoryId, '' as ActivityId

from

(Select Accountid, AccountManagerId as UserId, CreateDate as Filterdate from sysdba.Account) as a

In the "Select" part of our statement you see the list of our columns. "Account" is the Category of the record, this will change as we go. If you look at the prefix to each of the other columns you see an "a". Go down and look at the "From" portion of the statement. There you will see an embedded SQL statement that organizes our data from the Account table in a manner similar to a view. We provide an alias to this set of data and call it "a". Back up in the "Select" portion of our statement, if we could not derive supporting data out of our embedded SQL we simply added a blank value ( like '' as Contactid). Notice one other thing, we are using the AccountId twice once as the EntityId and once as the AccountId, this pattern will be followed through the process of creating our view.

Here is what the Contact statement.

Select 'Contact' as Category, c.FilterDate, c.userid, c.Contactid as EntityId, '' as AccountId, c.Contactid, '' as OpportunityId, '' as TicketId, '' as HistoryId, '' as ActivityId

from

(Select ContactId, AccountManagerId as userid, CreateDate as filterdate from sysdba.Contact) as c

I bolded the changes that differ from the Account set of data. All of the column headings are the same but "Contact" is now the Category, the prefix for each field is now "c", the Contact is now our target table in the embedded SQL Statement where we are getting are data from, and ContactId now is the EntityId and is populating the ContactId column. Also, notice that Accountid is pulling in blank value.

In the SQL view we are building, the word "Union" separates the two select statements. See how this is done below and compare the two scripts.

Select 'Account' as Category, a.Filterdate, a.Userid, a.AccountId as EntityId, a.AccountId, '' as Contactid, '' as OpportunityId, '' as TicketId, '' as HistoryId, '' as ActivityId

from

(Select Accountid, AccountManagerId as UserId, CreateDate as Filterdate from sysdba.Account) as a

UNION

Select 'Contact' as Category, c.FilterDate, c.userid, c.Contactid as EntityId, '' as AccountId, c.Contactid, '' as OpportunityId, '' as TicketId, '' as HistoryId, '' as ActivityId

from

(Select ContactId, AccountManagerId as userid, CreateDate as filterdate from sysdba.Contact) as c

I am aware that the embedded SQL might be a little over kill for my Account and Contact examples. You could also have the "From" portion of the statement looking like this " From sysdba.Account a" The embedded SQL allows you to build and organize your data in support of the main select statement. Take a look at the example below using the Opportunity portion of the view:

Select 'Opportunity' as Category, o.Filterdate, o.userid, o.opportunityId as EntityId, '' as AccountId, '' as Contactid, o.opportunityId, '' as TicketId, '' as HistoryId, '' as ActivityId

from

(Select OpportunityId, AccountManagerId as UserID, Case when sysdba.Opportunity.Status = 'Closed - Won' or sysdba.Opportunity.Status = 'Closed - Lost' then ActualClose else DateOpened end as Filterdate

from sysdba.Opportunity) as o

The bold here shows you the basic difference to what we have been doing so far. Here I have used a Case statement to help me set the Filterdate based on Opportunity Status.

Below you find the statement for Ticket:

Select 'Ticket' as Category, t.Filterdate, u.userid, t.TicketId as EntityId,'' as AccountId, '' as Contactid, '' as OpportunityId, t.TicketId, '' as HistoryId, '' as ActivityId

from

(Select ui.userid, sc.seccodeid from sysdba.userinfo ui inner join sysdba.Seccode sc on (ui.lastname + ', '+ui.firstname) = sc.seccodedesc) as u Inner join

(Select TicketId, AssignedToId, ASSIGNEDDATE as filterdate from sysdba.Ticket) as t on u.seccodeid = t.AssignedToId

This one is a Little more difficult because I want to only bring in Tickets that are assigned to the user. Since Tickets can be assigned to either a User or a Team we have to figure away of relating back to the user. Instead of the UserInfo table we have to reference the SECCODE table. If you did a query against the table you would discover that all users have a SECCODEID assigned to them and this is what is being stored as the AssignedToId. The SECCODEDESC actually lists the user name in "LastName, FirstName" format. I played around here a little bit and discovered that I could build a join using the UserInfo.Lastname and UserInfo.Firstname in a formula to match against the SECCODE.SECCODEDEC field. The formula looks like this (ui.lastname + ', '+ui.firstname) = sc.seccodedesc. Doing this allowed me to then use the USERINFO.USERID to get the Assigned to user for a Ticket.

When I run the statement the data looks like this:

I have attached the example I am using for my drill down report to this post.

What we have accomplished might still be a little confusing to some of you but you will see the benefit in the next post where I will add this view to our Crystal Report and start to build our drill down. We will see this style of view building one more time as we build the bottom level of the drill down.

Thanks for Reading, Keep Smiling - Geo

Writing the User - Quota vs Actual Crystal Report.

2010-11-23T16:45:00Z

Over the last three posts we have updated the Quota's are of Sage SalesLogix, we have built SQL views to collect both the Quota data and the Actual Numbers for comparison. Now we will start writing our report. I am not going into to much detail on layout. My goal is to walk you through the common steps:

connecting to the database
selecting our tables and views
establishing the group
adding the fields to the report.

Since we know this report is going to grow we need to accomplish all of this in a manner that will support expansion.

The first step in any report is making the connection. When you select the "New Report" icon ( ) located on the upper left hand corner of the Crystal interface, the Standard Report Creation Wizard will appear. I have two established posts that will talk you through the creation of connection to a SalesLogix SQL database. One labeled Report Database Connection talks you through using the "Microsoft OLE DB Provider for SQL Server" and recently published post labeled Crystal Connection through the SalesLogix OLE DB Provider talks you through using the SalesLogix OLE DB Provider.

With your connection to your database made and selected we are ready to select the tables and views we will need to support our report. In the "Available Data Sources" tree control on the left you should find your connection. Clicking on the plus (+) sign to the right of your connection will allow you drill down and find a value of "sysdba". Drilling into "sysdba" are two more listings, one each for Tables and for Views. Under "Tables" find and select the UserInfo table. Under "Views" you should find your custom SQL views that we built for this report in the previous posts (Quota data and the Actual Numbers ). My views are labeled CFX_UserPeriodReview_Goal and CFX_UserPeriodReview_Actual.

With your tables and view lets move on to linking the tables so our data flows correctly. Select the "Next" Button.

When the Link portion of the Standard Report Wizard opens, I usually just go ahead and click the "Clear Links" button right away. This proved true with this report also, crystal through a SQL error when I tried using the default links. With the links deleated and since the USERINFO table is the base table for this report, I moved it all the way to the left side of the screen. After moving the views to the right side of work area, I linked the USERINFO table to the CFX_USERPERIODREVIEW_GOAL and CFX_USERPERIODREVIEW_ACTUAL views, using the UserId field as my link between the entities.

Usually I change the default join of inner to left to ensure all records display even if a related table does not have data,. Because we only want those users who have Quotas entered in the system we will leave our join types set to the default.

With my tables and views selected and joined let's finish the wizard by selecting USERINFO.USERNAME in each of the following two screens and select finish. What this did was establish our first group, we selected the same field twice just to get through the field selection section of the wizard and were able to create that first group. When I selected "Finished" this is what I was presented with.

I know it looks pretty ugly right now and like I said, I am not going to get into to much detail on how to make it look better. Here is basically what I did to bring this report around.

I suppressed the Detail and Group1Footer Sections of the report.
I added another section in GroupHeader1.
In GroupHeader1a, I moved the username all the way to the upper left hand corner and collapsed in the rest of the section leaving just enough room to display the field.
In GroupHeader1b, I placed all of the fields from the views.
I combined each of the fields with labels to help me make sense of what the field is displaying. Make sure to format the dollar values before combining the field with the label.

This is a screen shot of what I came up with:

The first part of our Drill Down Report is complete.

Next, we have to come up with away of displaying all of the primary entities (Account, Contact, Opportunities, History, Activities, and Tickets) in one group. I can hear you scratching your head. If you remember, the overall idea of the report we are building is to be able to drill down into our data. The idea is to display just enough information to let you know what is going on for a user. If something peaks your interest, you will be able click on a record and be provided with more detail. A user of your system has references in each of those primary entities. So how do we display that information so it will all roll up under each of our user individually, but all in the same group. We will dig into this next time. See how here!

I have zipped up and attached the report displayed above. The report should install into any system where the untouched views provided in previous posts (Quota data and the Actual Numbers ) have been installed. Remember, if you are running this report outside of SalesLogix, to re-set the database location through the Database\Set Datasource Locations menu item.

Thanks for reading, have a Happy Thanksgiving, of course please travel safely, and most of all keep smiling.

Crystal Connection through the SalesLogix OLE DB Provider

2010-11-16T16:00:00Z

The first step in creating a report in Business Object Crystal Reports is always the connection to the database. In a post labeled "Report Database Connections", I discuss one method of making a connection to a SQL database. That post will lead you through how to make the connection using the Microsoft OLE DB Provider for SQL Server using the sysdba login and password. There is also a SalesLogix OLE DB Provider and since this is the primary way of a making a connection to SalesLogix data, I thought I would take the time to talk you through making this connection for Crystal Reports.

What is required to make this connection is the SalesLogix Admin Password, the location of the SalesLogix Connection Manager, and the SalesLogix Connection name. If you know or can obtain the password the rest is easily accessible by logging into the SalesLogix Client.

Instead of logging in, click on the button located to the right of the "Log on to:" field. This opens the Data Link Manager.

Usually you will just see one or two database connection listed here. Select the database that you want to report against out of the list and then click on the “Edit” button. This action will open the Data Link Properties window.

Now you will see the SalesLogix Server Name where the SalesLogix Connection Manager is located for your system ( for me it is Test 82), and the connection name given to the database you want to connect to (CFX_Reporting Class). The password may be visible but it will be encrypted.

Armed with this information, let's open Crystal Reports. When Crystal opens, select the New Report icon located in the upper right hand corner of the tool bar, selecting this should start the Crystal Report Wizard. The first screen of the wizard should be labeled "Data". You should also see a list of "Available Data Sources" along the left side of the interface.

Click on the Create New Connection Listing. Once the tree opens, find the "OLE DB (ADO)" listing.

When the OLE DB (ADO) appears scroll all the way to the bottom of the list, in my example I see the SalesLogix OLE DB Provider, third one from the bottom. Select the next button and the OLE DB (ADO) connection screen will appear.

Fill in your database information.

Data Source = your SalesLogix Server Name

User Id = "Admin"

Password = your Admin Password.

Database = the SalesLogix Connection Name

With the correct data added, select the “Finish” button.

Now the connection will appear under the OLE DB (ADO) listing in the data source tree. Right click on the connection name and select "Add to Favorites" from the list that appears. Your connection is now saved for future use. Open the Favorites list in the Available Data Sources tree and you should see your connection.

Gathering the Actual Numbers for Reporting Comparison

2010-11-10T14:00:00Z

I have just started writing a series of posts that will lead the reader of this blog through the creation of a report with the same kind of data break down as you see in the standard Sage SalesLogix Visual Analyzer and the Sage SalesLogix Dash Board. In the first week I provided a method to effectively establishing quotas ( or goals) for your users by updating the Manage Quota area of the Sage SalesLogix LAN. I also took the time creating the functionality for the SalesLogix 7.5.3 Web. Last week I showed you a method of using SQL Views to organize that quota data for use in reporting. This week my goal is for us to gather the Actual Numbers from our system for comparison against the quotas.

Once again we are going to use a SQL View to organize the data. I am going to go a little slower today because this is going to be a little different for everyone.

The first thing we need to do is create a list of users. The UserInfo table holds that data in SalesLogix. The following SQL statement provides that list.

SELECT USERID FROM sysdba.USERINFO

To prepare to add the different totals from each of your categories we need to write this statement a different way:

SELECT t0.USERID

FROM (SELECT USERID FROM sysdba.USERINFO) as t0

Here we are a using an embedded SQL Statement labeled "t0" to provide the user data for our SQL View. Now to add on a count of anything in the database we need to go create another SQL statement. Let's say we want to get a count of open Activities, for the current year, for each of our users. I believe I will need three fields, the UserId (to Group By), ActivityId (gives something to count), StartDate (Qualifies record for the current year). My SQL Statement looks something like this.

SELECT COUNT(sysdba.ACTIVITY.ACTIVITYID) AS OpenActivites
FROM sysdba.ACTIVITY
where Year (sysdba.ACTIVITY.STARTDATE) = Year(GetDate())
GROUP BY sysdba.ACTIVITY.USERID

Now lets work the above statment into our SQL view to match the data to the users in the system. I will try to use colors to direct you to the portion of the statement.

Black is the original statment form above

Add the words left join to the end fo the user embeded SQL
Copy the entire SQL Statement for Activities in to the "From" portion of our SQL View.
Add Open and Closed parenthesis
Alias the SQL ( as t1 )
Match the data to the established user. (on t0.UserId = t1.Userid)
Add the data collected to the SQL View select portion

SELECT t0.USERID, t1.OpenActivities

FROM (SELECT USERID FROM sysdba.USERINFO) as t0 left join

(SELECT sysdba.ACTIVITY.USERID, COUNT(sysdba.ACTIVITY.ACTIVITYID) AS OpenActivites
FROM sysdba.ACTIVITY
where Year (sysdba.ACTIVITY.STARTDATE) = Year(GetDate())
GROUP BY sysdba.ACTIVITY.USERID) as t1 on t0.UserId = t1.Userid

I am going to provide one more example to show that you can collect more then one value from each one of these embedded SQL statements. Lets add data for Closed - Lost Opportunities providing a count and a total dollar value of the lost sales. You will see the changes to SQL view are in green text.

SELECT t0.USERID, t1.OpenActivites, t2.ClosedLostOpportunities, t2.LostSalesPotential

FROM
(SELECT USERID FROM sysdba.USERINFO) as t0 left join

(SELECT ACCOUNTMANAGERID AS UserID, COUNT(OPPORTUNITYID) AS ClosedLostOpportunities, SUM(SALESPOTENTIAL) AS LostSalesPotential
FROM sysdba.OPPORTUNITY
WHERE (STATUS LIKE '%Closed - Lost%') AND (YEAR(ACTUALCLOSE) = YEAR(GETDATE()))
GROUP BY ACCOUNTMANAGERID) AS t2 ON t0.USERID = t2.UserID

Here I collected a count of closed lost Opportunities, and provided a sum displaying what those Opportunities were worth. Notice in the select portion how I called in the data from the embedded SQL.

If I run this SQL View right now my data looks like this.

I do not like the null values I am seeing here. We can write a formula in Crystal to make the correction but it is much easier here using the IsNull function. IsNull is a function that is standard in SQL that provides the user the ability to handle null values like we are seeing here. This is added to the select portion of my SQL View. The syntax looks like this:

IsNull (TargetField, Value if Null) as FieldName

Here is my new Select portion of SQL view:

SELECT t0.USERID
, IsNull(t1.OpenActivites,0) as OpenActivites
, IsNull(t2.ClosedLostOpportunities,0) as ClosedLostOpportunities
, IsNull(t2.LostSalesPotential,0) as LostSalesPotential

So for us if the value returned is null then we want to see a zero and we still want the field called by it's original name. Here are results

I hope you are seeing the value of preparing the data in SQL and why I say if you writing reports you simply need to have access to your SQL database. It makes things a whole lot easier in Crystal if you can prep your data before writing the report. I have attached the SQL View we will be using for our new report to this post for download. The challange on your end is to come up with some quotas in your system and then try and build a SQL view to pull the comparisons.

Next step, we will be bringing all of this data together and display it against our quotas in a Crystal Report. Have a great week, thanks for reading and of course "Keep Smiling".

See how this rolled up into a Crystal Report by following this link.

Geo

Gathering User Quotas for Reporting

2010-11-05T17:15:00Z

If you read last weeks post, you will know I am setting up to build a user based report that will give the reader a lot of the same data you see in the standard Sage SalesLogix Visual Analyzer and the Sage SalesLogix Dash Board. Last week I provided you the ability to effectively set goals for your users by updating the Manage Quota area of the SalesLogix LAN and actually creating the functionality for the SalesLogix 7.5.3 Web. This is available to you via the bundles attached to the post. This week I am providing you a means of grabbing the quota's for each user out of the SalesLogix database by providing you an example of a SQL view.

But first, I have added some text to the "About This Blog" section that I will reference from time to time just to remind everyone of my goals in delivering information to you through this blog, please feel free to click on this link to read.

So with the bundles from last week in place I have added some Quotas for some of the users in my eval system. The below screen shot gives you the examples that I have established.

Lets look at the data in the Quota Table.

As you can see, for every user and for each type of Quota there is a row of data, this means multiple rows of data per user. Now, if all I was asking Crystal to do was organize this data it could handle it with no problem simply by building a group based on the UserID and pulling in the fields I want displayed. Our report is going to be much more complex and this just an element of the data we are going to pull to roll up under a user. We need this data to be laid out so each user has one row of data, with a column for each Type of Quota that displays the value enterd for that Type. Below you will find an example of a SQL view that can be added to your system to draw out and bring the data together the way I described above:

Create View sysdba.CFX_UserPeriodReview_Goal
as

SELECT DISTINCT t1.UserID,
IsNull(t2.NumbOpenOpportunites,0) as NumbOpenOpportunites,
IsNull(t3.NumbOpenActivities,0) as NumbOpenActivities,
IsNull(t4.NumbOpenTickets,0) as NumbOpenTickets,
IsNull(t5.TotalYearlySales,0) as TotalYearlySales,
IsNull(t6.TotalWeightedPipeline,0) as TotalWeightedPipeline

FROM (SELECT USERID AS UserID FROM sysdba.USERINFO) AS t1 INNER JOIN

(SELECT USERID, SUM(QUANTITY) AS NumbOpenOpportunites FROM sysdba.QUOTA
WHERE (QUOTATYPE = 'Numb Open Opportunites') GROUP BY USERID) AS t2 ON t1.UserID = t2.USERID INNER JOIN

(SELECT USERID, SUM(QUANTITY) AS NumbOpenActivities FROM sysdba.QUOTA
WHERE (QUOTATYPE = 'Numb Open Activities') GROUP BY USERID) AS t3 ON t1.UserID = t3.USERID INNER JOIN

(SELECT USERID, SUM(QUANTITY) AS NumbOpenTickets FROM sysdba.QUOTA
        WHERE (QUOTATYPE = 'Numb Open Tickets') GROUP BY USERID) AS t4 ON t1.UserID = t4.USERID INNER JOIN

(SELECT USERID, SUM(AMOUNT) AS TotalYearlySales FROM sysdba.QUOTA
        WHERE (QUOTATYPE = 'Total Yearly Sales') GROUP BY USERID) AS t5 ON t1.UserID = t5.USERID INNER JOIN

(SELECT USERID, SUM(AMOUNT) AS TotalWeightedPipeline FROM sysdba.QUOTA
        WHERE      (QUOTATYPE = 'Total Weighted Pipeline') GROUP BY USERID) AS t6 ON t1.UserID = t6.USERID

This is not plug and play SQL view, you have to customize this script to match the quota types that are set in your system. There is a lot going on here and I will try to explain this a bit so you understand.

We have two major components to this view:

A "Select" portion.......a

            SELECT DISTINCT t1.UserID,
             IsNull(t2.NumbOpenOpportunites,0) as NumbOpenOpportunites,
             IsNull(t3.NumbOpenActivities,0) as NumbOpenActivities,
             IsNull(t4.NumbOpenTickets,0) as NumbOpenTickets,
             IsNull(t5.TotalYearlySales,0) as TotalYearlySales,
             IsNull(t6.TotalWeightedPipeline,0) as TotalWeightedPipeline

and a "From" portion.

            FROM (SELECT USERID AS UserID FROM sysdba.USERINFO) AS t1 INNER JOIN

             (SELECT USERID, SUM(QUANTITY) AS NumbOpenOpportunites FROM sysdba.QUOTA
             WHERE (QUOTATYPE = 'Numb Open Opportunites') GROUP BY USERID) AS t2 ON t1.UserID = t2.USERID INNER JOIN

             (SELECT USERID, SUM(QUANTITY) AS NumbOpenActivities FROM sysdba.QUOTA
             WHERE (QUOTATYPE = 'Numb Open Activities') GROUP BY USERID) AS t3 ON t1.UserID = t3.USERID INNER JOIN

             (SELECT USERID, SUM(QUANTITY) AS NumbOpenTickets FROM sysdba.QUOTA
             WHERE (QUOTATYPE = 'Numb Open Tickets') GROUP BY USERID) AS t4 ON t1.UserID = t4.USERID INNER JOIN

            (SELECT USERID, SUM(AMOUNT) AS TotalYearlySales FROM sysdba.QUOTA
            WHERE (QUOTATYPE = 'Total Yearly Sales') GROUP BY USERID) AS t5 ON t1.UserID = t5.USERID INNER JOIN

           (SELECT USERID, SUM(AMOUNT) AS TotalWeightedPipeline FROM sysdba.QUOTA
          WHERE      (QUOTATYPE = 'Total Weighted Pipeline') GROUP BY USERID) AS t6 ON t1.UserID = t6.USERID

Look at the 1st Column of the "Select" portion:

t1.UserID,

Now the first part of the "From" portion of the view statement supports the first columns by providing SQL a list of all usersid in the system.

FROM (SELECT USERID AS UserID FROM sysdba.USERINFO) AS t1 INNER JOIN

Now, look at the 2nd column in the "Select" portion of the statement:

IsNull(t2.NumbOpenOpportunites,0) as NumbOpenOpportunites,

This is saying give me a row of data with a label of NumbOpenOpportunites. This part of the select statement is matched to an embedded SQL statement in the "From" portion of the view statement to draw just that value out of the quota table for the user. Looking at the embedded SQL for this column it reads:

(SELECT USERID, SUM(QUANTITY) AS NumbOpenOpportunites FROM sysdba.QUOTA
WHERE (QUOTATYPE = 'Numb Open Opportunites') GROUP BY USERID) AS t2 ON t1.UserID = t2.USERID INNER JOIN

This is drawing data out of the Quantity field in the Quota Table where we set the Type for that row as 'Numb Open Opportunites'. As you can see this statement organizes the data just the way we want it, a User with a named column displaying a value

To make it easy, think of this as a table of data for SQL to use while it is building the view, this particular table was named "t2".

Look at the very last part of the embedded SQL:

t1.UserID = t2.USERID INNER JOIN

This matches the data to the established UserId in the 1st column, The "Inner Join" cleans out our view a bit because only records from the user table that have records in the t2 table will display records in our SQL view.

The rest of the our SQL view is adding on the different columns of data based on the Quota Types. For each column in the "Select" portion there is a supporting embedded SQL in the "From" portion. Look at the 4th column from the "Select" portion of SQL View Statement :

IsNull(t5.TotalYearlySales,0) as TotalYearlySales,

This columns uses "Amount" field and provide us our currency values. The matching Embedded SQL looks likes this:

(SELECT USERID, SUM(AMOUNT) AS TotalYearlySales FROM sysdba.QUOTA
WHERE (QUOTATYPE = 'Total Yearly Sales') GROUP BY USERID) AS t5 ON t1.UserID = t5.USERID INNER JOIN

The only real difference is instead of "Quantity" field we are using "Amount" field. The rest of the embedded SQL is basically the same.

Ok, to wrap this up the SQL view statement provided today is an example of how to prepare the data in the Quota table for use in a SQL report. I will use the above element of the SQL View Statement to help me explain what you need to modify in the view to make it work for you and your custom quota Types. Colors should match up to the section you need to modify

You will need to customize the embedded SQL to your custom quota types
Select whether the value you want is currecny (Use the Amount Field) or a number (Use the Quantity field).
The label for the Column in the view must match in both the Select Portion and the embedded SQL in the From portion of the SQL view.

Take your time while doing this. Remember your are building a view, you are not effecting your data or hurting your system so do not feel like you are breaking anything. SQL is one great tool and I have a lot of fun playing around and seeing what I can do. One other thing, fight for the right to have access to your SQL data. You are the report writer, you cannot really write great reports without access to the wonderful world of SQL.

Thanks for reading. Next time we pull together the data to compare these quotas against. Click Here

Keep Smiling, Geo

Updating the Managed Quota Area for Sage SalesLogix

2010-10-27T20:30:00Z

Often, when discussing reporting issues with clients I will bring up the Quota's area of Sage SalesLogix. If you do not know where this functionality is located, go to your Tools/Manage/Quota menu and the following screen will appear.

On the upper pane you find a place to list the users you want to set a quota for. As each user is highlighted the list below changes to display the quota's that have been established for that user.

Usually I bring up this area of SalesLogix when a conversation has to do with a way of tracking goals for the client's user base. This area has been around in SalesLogix since pre version 6.X. I believe this area is over looked or unused because of two basic issues. First, there is no place to define what the Quota being set is for. This limits the use of a quota set for a user to only one because it could only have one meaning. Second, the Quota section is set up for currency only. If we look at a tool like Visual Analyzer you are seeing a trend of knowing the counts of items like Activities or History records. There is a third issue, I think the entire set of functionality should be labeled "User Goals". This would provide a better understanding what the area is for. Oh yeah, one other thing, for you web users, the Quota functionality has been left out completely.

Today, I decided to fix at least 2 of these issues and bring this functionality to the web. I am making this available to the readers of this blog as I start down the trail of leading you through the building of a User based report. I will use the standard Visual Analyzer sheet and the Current SalesLogix Dash board to provide ideas of what we should include in this report. I am doing this to better show you that Crystal can, in most cases, meet you reporting needs.

Attached to this post you will find an attached zip file that contains two files. The LAN bundle labeled Update_LAN_ManageQuota_753.sxb should be installed first and will take the current functionality from what you see above to this:

You can now add numeric value in addition to the currency and can label the quota allowing the ability for multiple quotas or goals per user. The bundle has been tested down to SalesLogix version 7.5.0 and installed great.

The web bundle labeled Add_Quota_ToUsers_753Web.zip creates a new tab under User which is available in version 7.5.3 only.

The Quota Insert/Edit Screen looks like this.

I will provide links back to this post in the near future. The next step in preparing to write this report is to set your database up using SQL views to get the numbers from your data to compare these quotas against.

See how to collect Quota information for reporting by reading the next post.

Thanks for reading - Geo

Looking at Stonefield Query for Sage SalesLogix (Final Thoughts)

2010-10-06T20:30:00Z

For the last several posts I have been writing about my first hand experience using the reporting tool called Stonefield Query for Sage SalesLogix. I thought it might be time to wrap it up with some overall final thoughts. For those just joining the experience the series of post starts here. My blog series was meant to provide, at a high level, the ease of use and how I was able to accomplish what I think are some primary tasks for a reporting tool.

Overall, I really do like the tool. I think it is fairly easy to use, the documentation is well written and easy to follow which for a new user is very important. The people at Stonefield Query are very responsive to questions and are easy to work with. Stonefield Query would be great for someone who wants quick on the fly reporting with very little training. The more the user works with the tool the more they will be able to write more complex report. After the install there has not been any need for additional support from my system administrator which adds to the simplicity.

As with all tools like this there have been some little quirky things within my experience like the interface locking up or messages stating the connection to the database has been lost. All were self correcting by simply closing and reopening the tool. These things did not happen that often and in a system as complicated as ours could have been caused by issues outside the tool.

The only suggestion for improving this tool would be to limit the number of dialog boxes used to accomplish tasks. For example filtering:

As you can see, you have the Filter Tab which shows you the filters that have been created for the report in an easy to read format. In order to add or edit the filter you select the "Filter" button which opens the "Filter" dialog that again list the filters but in a more segmented manner, to add or edit a filter and select the appropriate button and the "Filter Condition" dialog appears which helps you add or edit the filter. I only bring this up because at times I found myself lost as to which dialog I was working in, especially in a multiple monitor environment.

In the end, if this is what I have to complain about, then maybe I am just a whiner. I like this tool and think it will do the trick for most users. I hope these posts will help someone on their decision to purchase. I have tried my best to give an honest representation of my experience using Stonefield Query without getting to deep into the weeds. Please Contact - Scott Weber @ scott.weber@customerfx.com if you are interested in seeing a demo of this reporting tool. Thanks for reading and Keep Smiling -Geo

Looking at Stonefield Query for Sage SalesLogix (Part 6)

2010-09-23T16:45:00Z

Again, I have to apologize for the rather long break between posts. The Minnesota National Guard and I have been seeing quite a lot of each other over the past few months. Reminds of a post I wrote over a year ago called "Supportive Commitment", what was written in that post is still true today. This is a great company to work for and they are very supportive of the efforts of U.S. Military Forces.

Today I want to look at creating a Formula field in Stonefield Query for Sage SalesLogix. In the Report wizard you find the controls that allow you to create a formula field in Step 2 - Data Selection. I added the Account Name to the list of fields selected for this report and then changed the table to the Address.

The formula that I am going to write for me demonstration will have address 1 display on the first line and the have the City/State/Zip field display on the second line all with in same formula field. Under the list of available fields along the left, you will see the "Formula..." button . Selecting this opens the "Formula Editor" below.

I added the name for my formula as "Custom Address". When I left the "Name" field the "Heading" auto populated, the value was still editable so I put a space between the two words. I then selected the button to the left of the formula space which brought up the "Expression Builder" dialog.

To build my formula I selected the my two fields from the list of available fields for this table. I then added some VB scripting that inserted a carriage return so the City/State/Zip field would list on teh second line. I was able to test my syntax on this form which helped alot. When I was satisfied with my formula I selected the "OK" button which returned me to the "Formula Editor" dialog. Here I selected the "OK" button which then returned me to the "Report Wizard". The list of available fields now contained my CustomAddress formula field, I moved the field into the list of Selected fields for this report. I selected Next and moved to Step three which allows me to establish filters for this report.

As you can see I only want Accounts that start with the letter "A". I then previewed my report to see the results, here are the first few records.

The one thing I played with is the filtering. I decided I might want to write the report using other letters so I changed the filter to ask me what letter I wanted to filter by at run time.

When I previewed the report I was provided the following dialog so I could select the letter I wanted to use.

Pretty quick and easy, real good stuff.

I am going to try and look at graphs in this environment and then we will probably wrap up this series of post. Until then do good things and keep smiling.

Looking at Stonefield Query for Sage SalesLogix (Part 5)

2010-09-01T17:00:00Z

Sorry for the long absence, I went away on some training with the Minnesota National Guard. This is the fifth post in series that is covering the use of a reporting tool called Stonefield Query for Sage SalesLogix. If you would like to take a look at the previous posts here are the links Post 1, Post 2, Post 3, and Post 4. In todays post we will cover the use of SQL Views in this reporting tool.

First of all, I like using SQL Views to organize my data for reporting. I have written a set of SQL views that provides counts of Activities and History Records by user for the current day. This is a set of views so I cannot just paste the statement in this post but I should be able to show you that the use of SQL views is possible.

The first step of course is creating the SQL View. Here is a link to a post I wrote back on May 27th 2008 which talks about using SQL Views in Reporting and Grouping and bringing together data from two independent databases. As discussed in the post, once you have the SQL View ready for use you may need to enable it for use in other systems like Stonefield Query.

To enable a SQL view go into the SLX Architect and open the Database Manager. Find your new view in the list of database tables

Right Click and select Properties from the list.

When the “Properties” window opens select the “Enable” button.

The view is now available for use in Stonefield Query reporting. With this step done open Stonefield Query and follow Stonefield's wizard to build your report.

In Step 2, find your SQL View listed in Table drop down and move the required fields to the right side by double clicking or using the centrally located buttons.

In Step 3, I created a filter to just get my username to be th only record to display. This is accomplished by clicking on the Filter button, the f Filter screen (in the back ground) will appear. With a click on the "Add" button the" Filter Condition" screen (in the foreground) will appear.

Over all, this filtering is very self explanatory and easy to use.

On Step 5, I selected "Portrait" for my orientation and then selected Preview button to see my report.

Ok, I have not done much today in SalesLogix but the point should be taken, I was able to call in a SQL view and then report upon it. My demonstration here is the easiest of examples. The view I used included the username with the data so we did not need to incorporate the userinfo table in our report. I did attempt incorporating the data collected in a similar SQL view with the userinfo table and was unsuccessful. I am not saying that it is impossible at this point but I could not easily figure it out in the time alloted to write for this blog. I will continue to play and see if I can get it to work.

Well that is it for today. Until next time - be safe, do good things and keep smiling. Geo

Looking at Stonefield Query for Sage SalesLogix (Part 4)

2010-08-11T17:45:00Z

The last couple of weeks I have been working on a series of posts introducing Stonefield Query for Sage SalesLogix. The first two posts have been focused on set up and interface layout. Last week we created our first report. This week we are going to see if our report can handle custom tables. I have created an add on module we call Power2Project for the Sage SalesLogix Web. We will use this module to test out how Stonefield Query can include custom tables.

My report layout will be simple, I will list the Account, Project Name, and some of the project numbers. I think my previous post explained well enough how to start a new report so we will not go into those details.

Here is the first screen of my wizard, notice that I selected the Data group of SalesLogix. I learned from trial and error last week that this is one way of filtering down the list of available tables.

Here on step 2 we see the custom tables already available for us to select from. Again, find the tables that you need for your report and then select the fields for the report by moving them from the list on the left to the list on the right. Once the fields are on the left you can start setting their properties by highlighting and selecting the "Properties" button.

Here I am setting the properties of the EstimatedBudget field. This is a currency field.

I went to the format tab and unselected the "Use Default Format" check box which then enabled the rest of the the fields for selection. "Display $" was an obvious choice. I learned from trial and error that you also need to set the "Decimal places". If not your value will look something like this, $25500.0000000. I went through and set the properties on each field. For this report there was a lot of mouse clicking when setting the properties for these field. To be honest I found this to be a little irritating. I looked for and could not find a format painter so you could set the format of one field and then paint others similar fields. Once you get into the Advanced Layout section for this report, I think you could set up the field the way you want and then copy it and then point the new field at a different database field to minimize all of the clicking.

With no Filters or Expressions I skipped step 3 in the wizard. I set the sorting to Account in step 4. In step five I used the Advance Layout and drew the report out they way I wanted.

OK - not exactly like I wanted but close enough for this test. We confirmed that Stonefield Query for Sage SalesLogix does work with custom tables. I also confirmed my suspicion on copying the fields and resetting the source field to minimize clicking when setting properties. Always seems to be more then one way to skin a cat in this tool, I like that. Next week I want to try filtering, after that I want to see if using SQL View is possible with this tool.

Thanks for reading! Stay safe and keep smiling.

Looking at Stonefield Query for Sage SalesLogix (Part 3)

2010-08-03T13:33:00Z

I always try to stress in classes that I have taught the importance of sitting down and creating a report plan. It can be as simple a drawing the report out and answering questions like these:

What are the fields I want displayed.
How do you want to group the report.
Are there any records you do not want displayed, what are the rules.
Are there any formula fields.

You can read more about my thoughts on planning reports here.

Answering these questions gets your mind focused on your task. My plan is to build an Account level report listing the Account, Account Manager, Status, Main Phone Number, along with the City, State, and Postal code displayed in one field. Under the Account I will list the Contacts using a formula field to format the FullName of the Contact, their Status, work number, and email. From my experience with other reporting writing tools I think I may have to create formula fields for formating the phone numbers, the contacts full name, and a combo City, State and Zip field. I will not filter this report at this point.

I want you to know that prior to writing todays post I have not built one report in Stonefield Query. This will be my first of many I am sure. I started writing the report at 8:56am - I completed writing the report at 9:25 am with some decent results. I did not take screen shots while I went but I did take some notes and I will try my best to recreate the experience in the next few lines.

The first thing I did was create a folder to hold my report. I right clicked anywhere on the tree control side of the interface and selected "New Folder" from the list that appeared. I named the folder "Custom Reports".

With my new folder highlighted, I right clicked again and selected "New Report" from the list.

Immediately I received the "Report Types" screen.

I used the default and selected "OK", the" New Quick Report Wizard" opened.

In step 1 - I named my report, left the defaults, and selected the "Next" button. Later I discovered that SalesLogix was one of the choices in the "Data Group" drop down. Playing around now I see using this drop down to filter the tables will make it easier to find the tables you want to work with in step two.

Use the "Table" field to select the table the display field resides in. The "Available Fields" list along the left hand side will display the all of tables fields. You can double click on or use the buttons in the center to move the display field from the left to the right. The left pointing arrows are the only way to remove a field from the list on the right. A double click on a field on the right brings up the fields property window where you can set display options. If you want to move a field up or down in list on the right then highlight it and drag to where you want it.

The formula button appears to be the place where you will create any formula fields you may need. The fields that I thought were going to have to be formulated were all already available in the tables I selected. Anyone who has went through the hassle of formatting phone numbers to display like these in Crystal will understand my relief.

The "Values" button allows you to see the data of the highlighted field that you have called into your report in a distinct list view. this gives you the ability to make sure the right field was selected

The "Properties" button, again, brings up the property window for any field highlighted in the list on the right.

At this point I am about five minutes into my report. All of my fields are selected and this is what my report looks like by selecting the the "Preview" button.

I know it is not pretty but we are only on step 2. Closing the preview and selecting the "Next" button and I receive Step 3 of the wizard used for filtering.

With no filters thought up ahead of time I skipped this but I will guarantee we will come back to this section in a later post. Next brought me to the Step 4 which is where we set up our Sort Options.

Same idea as before, find your table and select your field. To select the Direction highlight the field in the list on the right and select the direction in the center. I love how straight forward that is. Select the "Next" button and you come to what really amounts to the meat of this wizard, the Formatting Options area.

I will also tell you that I went straight through this the first time but found my report pretty much as displayed above except now sorted by the Account Name. In fact I buzzed right through the wizard in about 7 minutes. My challenge was figuring out the how to group the Contact under the Account. So to get back to the Formatting Options section of the report wizard, I highlighted my report in the tree control and selected "Edit" icon located on the Tool bar.

Then in the drop down in the upper right hand corner I selected Step 5 Formatting Options

I played around with the Templates area a little with no luck and then I checked the "Advanced Layout" check box on the bottom center of the screen and I selected the the "Edit" button below it.

My world started coming together again. First you will be asked if you want the objects to be autofitted, I selected no but that option is up to you. Then I was presented with the Advanced Report Designer. I started looking around in here trying to get me feet grounded.

The File menu led me to the "Page Set Up" which then provided me with the "Report Properties" interface it was then I new I was on the right track.

I opened the Data Groupings Tab, Selected the Add button to see the Expression Builder. The screen seemed a little complicated at first, I mean I did not know what kind of expression I needed. Following some of the same logic we used in previous screens I selected the Account table.

I double clicked on the Account field and it appeared in the Expression above. Experimenting now. I selected the "OK" button which brought me back to the Report Properties where we see the field now in the Group Nesting Order.

When I selected OK, I was brought back to the Advanced Report Designer where I could clearly see the new group area.

I am at about 12 minutes into building this report so I spent the rest of the time drawing the report out. Moving the Account level fields into the Account group, arranging the Contact fields in their order in the details section and playing with the labels until everything made some sense. You can see the results below.

At this point I am very pleased with the tool. I know Stonefield Query can do much more and that I have only touched the surface. Next week we will look at building a report using Custom SLX Tables and maybe a little filtering to boot. Thanks for reading, stay safe, and keep smiling.

Looking at Stonefield Query for Sage SalesLogix (Part 2)

2010-07-27T13:28:00Z

Last week I wrote about my experience downloading and setting up Stonefield Query. This SalesLogix reporting tool has been around for years and I thought it was time to take a close look at the tool and its capabilities. I am still in the introductory mode, getting use to where things are. Today we will take a look at the user interface.

I entered into Stonefield Query through the desk top icon left from the install. I had to enter in the username and password for Stonefield Query only, the system remembered the database login information for the last database I worked with which for me is great. I can't tell you how many times I have lost my database connection for reports I have built in Crystal.

The user interface that came up is clean and straight forward.

The File menu allows you to:

Create a new report or folder.
Delete, rename, copy, or edit an existing report.
Open a new database connection.
Import and export report.
Print and preview your reports.

The Edit menu contains the usual Cut, Copy, and Paste options along with the Undo and Redo functionality.

The Tools menu is where you:

Manage your passwords, users and licenses.
Run the report scheduler.
Set up templates
Create formulas
Import email Addresses
You will also find your user options interface

The Help menu brings you to what I like so far the most about Stonefield query, the documentation. Every question I have had so far has been easily answered through the Help files with clear understandable information. Also in the help menu you will find the Stonefield Query Reference Manual which is a .pdf document with over 400 pages of information.

As with most user interfaces, the tool bar brings some of the more commonly used functionality from the menus to the fore front.

The main pane is broken up into two sections. The section to the left is a tree control listing containing folders and reports. You have the ability to add folders one layer deep to organize your reports. A right click on any of the items in the tree control will bring up the list with all of the items seen on the File menu. As you highlight items on the left the section on the right changes to display the properties of the selected item. Everything is up front and easy to use and understand.

The SalesLogix version of this reporting tool comes with 7 canned reports. These reports are very simple and I believe are more to get the user started, more for showing some of the possibilities you have with reporting in this tool then to provide any real purpose. But they do run and provide feed back so their value is for you to decide. To run a report, highlight and click Preview button on the tool bar. Again very simple and easy to use.

So there you go, a very high level introduction to the user interface. Through this process I noticed that there is a wizard that helps you build your report. We will dig into that next week when we try to create our first report.

As I go through this process of writing about Stonefield Query I started to develop a list of questions:

Can I edit the layout of my reports?
Can I add graphs into any of the reports I build?
Often I have to use formulas to provide data for my reports, is this possible?
I know I can use filters, but can I set the data for the filter at the time I run my report?
Can I build my report with a lot of data elements and then use values entered at runtime to set what of that data is displayed when the report prints?
I often like to use SQL Views to organize my data for reporting, is this possible?

I am sure I will come up with some more as we go forward reviewing this reporting tool and I look forward to discovering the answers. Thanks for reading?

Until next time, be well, do good things and keep smiling.

Please Contact - Scott Weber @ scott.weber@customerfx.com if you are interested in seeing a demo of this reporting tool.

Looking at Stonefield Query for Sage SalesLogix

2010-07-19T13:26:00Z

Since reporting is one of my interests I always tend to take notice when I hear of a reporting tool. A few weeks ago Scott Weber, Customer FX's own CRM Practice Manager and Senior Consultant, introduced a product to me called Stonefield Query. It was a ten minute demo but I was pretty impressed by what I saw. I thought I would take some time and dig into the Sage SalesLogix version of this this reporting tool and write about my experience as a new user of this reporting tool.

To their credit Stonefield Query has been around for a number of years, since 1991. When you type in the words "SalesLogix Report Writing" they come up easily in the top 10 on any of the search engine. Jason Okrepkie and Chris Hodgins at Stonefield Query provided a NFR license for the purpose of this series of blog posts. You can go to their web site to download a free trial of the software for many types of CRM, Accounting, ERP, and Help Desk Applications. Using the website I found it very easy to find and download the software version for Sage SalesLogix. The file size of my download was about 23 MB so it took only about 3 minutes. The software loaded in less then 1 minute and when finished I was presented with a cool little wizard to help complete the set up.

The first step in the wizard is selecting a location where your custom reports will be stored after they are created. This allows you to centrally store your reports on the networks for others to use. The system defaults to the Program Files folder for your local machine but the interface allows for you to custom set this to any location.

The second step is setting up your email information so you can email your custom reports. I selected the help button which immediately helped me figure out what setting I required.

As soon as I set my email preferences I was presented with Stonefield Query login interface that had a list of all my SalesLogix database connections. The default login info for Stonefield Query is user name = Admin and the password is also Admin. Stonefield Query gives you this information right on the login interface and you can change these settings once you are in.

I selected my target SalesLogix database then the Stonefield system provided an interface to select an ODBC connection. This connection allows access to all of the custom tables that are in the system. The help file was pretty good at explaining the process for the unexperienced. FYI, I used the Search Tab and the value "ODBC" and I was brought right to the information I needed. The challenge here is this step requires a login into SQL. From my experience working with the readers out there, most report writers do not have a direct login to the SQL client. You will have to work with your SQL Database administrator to acquire this information. Also, you need one ODBC Connection for each database you want to work with. Overall this step was a little confusing but with the help file and little persistence I was able to log into Stonefield Query with access to the database.

I backed all of the way out and closed out of Stonefield Query. Logging back in I received the Stonefield Query login with my targeted database already selected and that was it. So the interface must store the SalesLogix user name and password and maintained the ODBC Connection. I am up and running in Stonefield Query in less then and hour. I am set up to report against a SalesLogix database that has been customized into Customer FX's Power2Project for SalesLogix the 752 web client. Next time we will review the interface and work with the standard reports that come with the tool.

Thanks for reading. Geo

Please Contact - Scott Weber @ scott.weber@customerfx.com if you are interested in seeing a demo of this reporting tool.

Running a SQL Update Statement from Saleslogix Web

2010-06-23T20:49:00Z

I am working for a client who would like a button to set all of the records in a table to either "Valid" or "" (blank). This then allows them to build groups on what records needs to be updated. I started my adventure by reading Kris Halsrud's post Retrieving the Manager in SalesLogix 7.5 Web . The solution Kris outlines provided most of what I needed and direct conversation with Kris himself provided the rest. I wanted to document it here so I do not have to bug him the next time I need to do this.

This is what our C# Snippet Action item looks like launched from the button.

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();

System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("update sysdba.TABLE set FIELD = 'Value'",conn);

cmd.ExecuteNonQuery();

}

The green text is the slq statement, the rest should be able to be used as is. I hope this little post helps someone else down the line.

Thanks

Geo

Adding Activities and History to a Custom Main Entity in the Saleslogix Web Client (Final)

2010-06-09T13:00:00Z

I am at the end of a series of posts providing some easy to follow steps that will help a developer add the ability to associate Activity and History records for a custom main entity within the SalesLogix Web Client. Read the previous four posts to catch up on where we are at. Part 1 Part 2, Part 3, Part 4, and Part 5.

The last step contains no coding what so ever. We simply are adding the already existing smart parts to our main entity page. There are four parts that are added in order for the functionality to be complete. These are all custom smart parts that come standard with Sage SalesLogix Web. They are labeled EditRecurrence, CompleteRecurrence, ActivityList, and HistoryList.

The first step is to find your main entity page. These are generally found in the Project Explorer Tree Control under Sage SalesLogix/Pages. Right click on your custom main entity page and select "Edit Page".

The tab we want to work on is labeled "Smart Parts". Along the left side you see a list of the smart parts that are operating with your main entity page, on the right you see the settings for each of the smart parts. To get the custom smart part working with your custom page select the plus sign on the upper left hand corner of the tab.

When the dialog opens find the "Custom" tab, on this tab look for the "SmartParts" folder, then look for the Activities folder, no look for the CompleteRecurrence.ascx custom smart part and select it Once selected you will see the smart part appear in your list, along the right you will need to make some adjustments to the Target Workspace and Show In Mode fields.

Follow this same process for each of the smart parts listed. You will find the EditRecurrence.ascx and ActivityList.ascx files are in the same list that you found CompleteRecurrence. The HistoryList.ascx is located in the History folder. The ActivityList and HistoryList parts are actually the tabs you will view the records on under your main entity. The Target Workspace property field should be set to Tab Contro for both the ActivityList and HistoryList views .

This completes this long but hopefully helpful series. Adding this functionality makes your custom entity more of an integrated part of SalesLogix.

Adding Activities and History to a Custom Main Entity in the Saleslogix Web Client (Part 5)

2010-06-06T13:39:00Z

Today I am sitting in the hospital helping my wonderful wife welcome our fourth child into this world. Cailyn Elizabeth was born on the 4th of June after 16 hours of labor measuring out at 10lbs and 21 inches. She is very beautiful and mom and baby are doing great. This is the second to last post of my effort to provide some easy to follow steps that will help a developer add the ability to associate Activity and History records for a custom main entity within the SalesLogix Web Client. Read the previous four posts to catch up on where we are at. Part 1 Part 2, Part 3, and Part 4.

In this post we will add scripts to the two list views that are used in all main entities to display Activity and History records.

If you have been following along with the earlier posts you hopefully have an idea of the flow of how we are accomplishing this task. Like we did in the last post, today we are going to just focus on the code and where it goes. But first, lets create some new definitions for todays code.

We still need to use EntityName but we will format the characters a little differently today. The first letter will be uppercase and all remaining letters will be lowercase. This is mandated by some existing code with the script we are working on. "EntityName" now looks like "Entityname" or "FXProject" now looks like "Fxproject".

The other definition we need is the EntityId in proper case. This is the primary id field for the custom entity. For the FXProject entity the entityId is FXProjectId.

The two files we will modify today in the SalesLogix Application Architect(AA) are labeled ActivityList.ascx.cs and HistoryList.ascx.cs. These files actually provide the code to support the pages that list the data. You can find these files in the AA under the Project Explorer tab. In the tree control look for and open the Portal Manager, then Sage SalesLogix, then Support Files. We will work for the ActivityList.ascx.cs file first which is located in the Activity folder. When you have the the file open, go to line 103 and hit enter twice. Add the following:

            if (entityName.Equals("Entityname"))//Mine
            {
                Page.Session["EntityId"] = entityId;//Mine
            }
            else//Mine
            {
                Page.Session["EntityId"] = "";//Mine
            }

The first entityName is actually code that should not be changed. I bolded the Entityname and EntityId that need to be replaced with your definitions.

Still in the same file with the previous set of code in place go to line 140 and hit the enter key twice. Then add this code:

           if (entityName.Equals("Entityname"))//Mine
           {
               Page.Session["EntityId"] = entityId;//Mine
               if (((BoundField)(col)).DataField.Equals("Category"))//Mine
                   col.Visible = false;//Mine
               if (((BoundField)(col)).DataField.Equals("ContactName"))//Mine
                   col.Visible = false;//Mine
               if (((BoundField)(col)).DataField.Equals("Notes"))//Mine
                   col.Visible = true;//Mine
           }

This bit of code adjust the visibility of columns based on the Entity level your are on. You can customize this to fit the needs for your custom main entity.

For the last bit of code in this file navigate to line 302, hit enter once and add the following code.

            case "Entityname"://Mine
                keyId = "EntityId";//Mine
                break;//Mine

This provides the keyId for the Activity grid so it knows what records to display when it is on your entity. This is the exact same code the you will use starting on line 132 of the HistoryList.ascx.cs file. You are currently in the Activity folder, the HistoryList file is located under the history folder.

That is enough for today. We finish up with next post where we add these and other smart parts to the main entity page.

Adding Activities and History to a Custom Main Entity in the Saleslogix Web Client (Part 4)

2010-05-20T14:19:00Z

I am near the end of a series of posts providing some easy to follow steps that will help a developer add the ability to associate Activity and History records for a custom main entity within the SalesLogix Web Client. Read the previous three posts to catch up on where we are at. Part 1 Part 2, and Part 3

Since the previous two posts went into great detail, today we are going to mostly focus on the code and where it goes. You still need the definitions we used in your code in parts 2 and 3. There are three areas we will modify today in the SalesLogix Application Architect(AA). Please find and open the following:

CompleteActivity.ascx (This holds the properties for the Complete Activity Page)
CompleteActivity.ascx.resx (This holds the definition for the labels the page above)
CompleteActivity.ascx.cs (This holds the code supporting the page)

Once again you find these files in the AA under the Project Explorer Tab following same process as detailed in parts 2 and 3.

Let's get started with the CompleteActivity.ascx. To add our code navigate to the end of line 205, hit the enter button, and paste in the following lines:

<%--Start My Code --%>
<%--End My Code --%>

These should be lines 206 and 207. Go to the end of line 206 and hit the enter key again and then paste the following on empty line 207:

   </tr>
   <tr>
      <td colspan="2">
                    <span class="twocollbl"><asp:Label ID="FieldName_lz" AssociatedControlID="FieldName" runat="server" Text="<%$ resources: FieldName_lz.Text %>"></asp:Label></span>
                    <span class="twocoltextcontrol">
                            <SalesLogix:LookupControl Width="95%" runat="server" ID="FieldName" AutoPostBack="true" AllowClearingResult="true" LookupBindingMode="string" ReturnPrimaryKey="true" LookupEntityName="EntityName" LookupEntityTypeName="Sage.Entity.Interfaces.IEntityName, Sage.Entity.Interfaces, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" >
                        <LookupProperties>
                            <SalesLogix:LookupProperty PropertyHeader="<%$ resources: Lookup_Field1.PropertyHeader %>" PropertyName="Field1" PropertyFormat="None" UseAsResult="True"></SalesLogix:LookupProperty>
                            <SalesLogix:LookupProperty PropertyHeader="<%$ resources: Lookup_Field2.PropertyHeader %>" PropertyName="Field2" PropertyFormat="None" UseAsResult="True"></SalesLogix:LookupProperty>
                            <SalesLogix:LookupProperty PropertyHeader="<%$ resources: Lookup_Field3.PropertyHeader %>" PropertyName="Field3" PropertyFormat="None" UseAsResult="True"></SalesLogix:LookupProperty>
                        </LookupProperties>
                        <LookupPreFilters>
                        </LookupPreFilters>
                        </SalesLogix:LookupControl>
                    </span>
                </td>

Add your definitions using the lists we developed in Part 2.

The modifications to the ScheduleCompleteActivity.ascx.resx field are pretty much as we did yesterday. Here we want to navigate near to the very end of the file, on line 326, hit enter and paste in the following lines:

<data name="FieldName_lz.Text" xml:space="preserve">
<value>LookupLabel</value>
</data>
<data name="Lookup_Field1.PropertyHeader" xml:space="preserve">
<value>Field1Label</value>
</data>
<data name="Lookup_Field2.PropertyHeader" xml:space="preserve">
<value>Field2Label</value>
</data>
<data name="Lookup_Field3.PropertyHeader" xml:space="preserve">
<value>Field3Label</value>
</data>

For the CompleteActivity.ascx.cs file there are 6 events we will modify. Remember to take your time. The first event we will work on is labeled OnFormBound, just before the closing bracket for the event add the following code:

        Sage.Entity.Interfaces.IActivity act = this.BindingSource.Current as Sage.Entity.Interfaces.IActivity; //Mine
        if (act.FieldName != null) //Mine
        {
            FieldName.LookupResultValue = act.FieldName.ToString(); //Mine
        }

To make sure we are all on the same page here, "FieldName" is the name of the field in the Activity Entity that represents our custom main entity. In my example FieldName = FXProjectId

For the Page_PreRender event, look for a line that looks similar to this:

TicketId.InitializeLookup = (TicketId.SeedValue.Length == 12);

Right under this line add the following line of code and then add your definition for FieldName to the code.

FieldName.InitializeLookup = (FieldName.SeedValue.Length == 12); //Mine

For the LeadId_LookupResultValueChanged event, look for a line that looks similar to this:

Activity.AccountId = null;

Right under this line add the following line of code and then add your definition for FieldName to the code.

Activity.FieldName = null;//Mine

For the AccountId_ChangeAction event, look for a line that looks similar to this:

Activity.LeadId = null;

Right under this line add the following line of code and then add your definition for FieldName to the code.

Activity.FieldName = null;//Mine

For the SetTacoDefaults event, look for a group of code that looks similar to this:

if (GetParam("tid") != null)
{
Activity.TicketId = GetParam("tid")

}

Right under this code add the following. In Part 3 (UPDATE WITH LINK) of this series you established an "alias2", make sure to use that same definition for alias2 in this code.

if (GetParam("alias2") != null)//Mine

{

Activity.FieldName = GetParam("alias2");//Mine

}

Still in the SetTacoDefaults event, look for the case statements not to far below the code you just set in place. You should find a case statement for ITicket. Add the following group of code on the next line after the break of the ITicket case statement.

case "IEntityName"://Mine
      found = true;
      IEntityName alias = EntityFactory.GetById<IEntityName>(hist.EntityId.ToString());
      Activity.FieldName = alias.Id.ToString();
      Activity.AccountId = alias.Account.Id.ToString();
      Activity.AccountName = alias.Account.AccountName;
      break;//Mine

This area is setting some defaults based on where you are launching the CompleteActivity page from. Think about if you were launching from your custom main entity, what field can you pre-populate. In the example I am providing the assumption there is a relationship to the Account entity named "Account". You need another definition for alias and this can be anything, as I did in the last post I am going to use a shorten version of my definition for EntityName so for me alias = fxproj.

The final event today is one labeled OnAddEntityBindings(), at the end of this event add these two line of code:

bs.Bindings.Add(new WebEntityBinding("FieldName", FieldName, "LookupResultValue"));//Mine
bs.Bindings.Add(new WebEntityBinding("AccountId", FieldName, "SeedValue"));//Mine

Remember - here again, if there is not a relationship to the account from your custom main entity name Account, then either delete or modify the second line as needed.

To test, save all files, build your web platform, then redeploy your site. When the website comes up go to any open activity in one of the Activity List views, select "Complete" from the list. When the Complete Activity page opens you should see your field, below you will see mine.

That's enough for today, we will finish out our seires with process for seeing the Activites and hitory Tabs on your custom main entity.

Adding Activities and History to a Custom Main Entity in the Saleslogix Web Client (Part 3)

2010-05-11T13:00:00Z

My goal within this series of post is to provide some easy to follow steps that will help a developer add the ability to associate Activity and History records for a custom main entity within the SalesLogix Web Client. Read the previous two posts to catch up on where we are at. Part 1 Part 2.

As promised in the last post we are going to get right at it today. The process is going to be very similar to what we did in Part 2. As a reminder, you will need the definitions we used in our code in part 2. There are three areas we will modify today in the SalesLogix Application Architect(AA).

ScheduleCompleteActivity.ascx (This holds the properties for the Schedule Complete Activity Page)
ScheduleCompleteActivity.ascx.resx (This holds the definition for the labels the page above)
ScheduleCompleteActivity.ascx.cs (This holds the code supporting the page)

You can find these files in the AA under the Project Explorer Tab. In the tree control look for and open the Portal Manager, then Sage SalesLogix, then Support Files, then Smart Parts, and then Activity.

Right click and select "Open" on the following files:

ScheduleCompleteActivity.ascx
ScheduleCompleteActivity.ascx.cs

Then open another folder just above Activity in the tree labeled "App_LocalResources". Right click and select open a file named "ScheduleCompleteActivity.ascx.resx".

Let's get started with the ScheduleCompleteActivity.ascx. To add our code navigate to the end of line 169, hit the enter button, and add the following lines:

<%--Start My Code --%>
<%--End My Code --%>

These should be lines 170 and 171. Go to the end of line 170 and hit the enter key again and then paste the following on empty line 171:

<%--Start My Code --%>
</tr>
<tr>
  <td>
            <span class="lbl"><asp:Label ID="FieldName_lz" Text="<%$ resources: FieldName_lz.Text %>" AssociatedControlID="FXProjectId" runat="server"></asp:Label></span>
        </td>
  <td>
   <span class="textcontrol">
    <SalesLogix:LookupControl runat="server" ID= "FXProjectId" AutoPostBack="true" AllowClearingResult="true" LookupEntityName="EntityName" OverrideSeedOnSearch="true" SeedProperty="Account.Id"
     LookupEntityTypeName="Sage.Entity.Interfaces.IEntityName, Sage.Entity.Interfaces, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" >
    <LookupProperties>
     <SalesLogix:LookupProperty PropertyHeader="<%$ resources: Lookup_Field1.PropertyHeader %>" PropertyName="Field1" PropertyFormat="None" UseAsResult="True"></SalesLogix:LookupProperty>
     <SalesLogix:LookupProperty PropertyHeader="<%$ resources: Lookup_Field2.PropertyHeader %>" PropertyName="Field2" PropertyFormat="None" UseAsResult="True"></SalesLogix:LookupProperty>
     <SalesLogix:LookupProperty PropertyHeader="<%$ resources: Lookup_Field3.PropertyHeader %>" PropertyName="Field3" PropertyFormat="None" UseAsResult="True"></SalesLogix:LookupProperty>
    </LookupProperties>
    <LookupPreFilters>
    </LookupPreFilters>
    </SalesLogix:LookupControl>
   </span>
  </td>
<%--End My Code --%>

The code above is very similar to the code we added in part 2 but there are some differences. Once the code is in place use the definitions from part 2 fill in FieldName, EntityName, and the Fields (1, 2, and 3). I tried to bold all of the values that need to be changed. You see with EntityName some of the code has an "I" before the EntityName, ensure you leave this "I" in place. FYI, there is an assumption within this code that your Main Custom Entity has a relationship to the Account layer labeled "Account", if this is not true then you do not include the SeedProperty="Account.Id" in your code. When you have completed this area make sure you save your work.

Using your definitions fill in FieldName, LookupLabel, Fields (1, 2, and 3) and FieldLabels (for 1, 2, and 3).

When ready save your work and open the ScheduleCompleteActivity.ascx.cs file. If you remember yesterday here we are updating already established events to work with our custom entity lookup. You can search for these events with in the file using the drop down field in the upper right hand corner. Simply find and select the event and AA will take you to the correct spot in the code.

First lets search for the "ResetForm" event. Look for a line that looks like this:

Ticket.LookupResultValue = null;

Right under this line add the following line of code and then add your definition for FieldName to the code.

FieldName.LookupResultValue = null;//Mine

Next lets search for the "SetSeedValues" event. Look for a line that looks like this:

LeadId.InitializeLookup = false;

Right under this line add the following line of code and then add your definition for FieldName to the code. The second line is only used if you have a relationship from your Main Entity to the Account Entity and that relationship is name Account. If this does not exist then you may not want to use the second line.

FieldName.InitializeLookup = true;//mine
FieldName.SeedValue = seedValue;//mine

Now this next section gets a little hairy so take your time. Search for the btnContinue_Click event. Look for a line that looks like this:

string leadId = "";

This line is declaring a variable. Directly below this line add the following code:

string variable = ""; //mine

The definition for variable can be anything. I am going to use a shorten version of the definition for my FieldName and I will set the text to all lowercase, so for me variable = fxprojid.

Staying in the same event you will a set of code that looks like this:

if (LeadId.LookupResultValue != null)
  {
    Lead lead = LeadId.LookupResultValue as Lead;
    if (lead != null)
{
leadId = lead.Id;

We need to add similar code right underneath this set, should look this:

if (FieldName.LookupResultValue != null) //Mine
  {
   EntityName alias = FieldName.LookupResultValue as EntityName;//Mine
   if (alias != null)//cfx
     {
       variable = alias.Id;//cfx
     }
  }

Again, add your definitions for EntityName, FieldName, and then use the variable we defined above. Just like for variable you need another definition for alias, this can be anything so I am going to use a shorten version of my definition for EntityName so for me alias = fxproj.

Still staying in the same event look for a line that looks like this.

Dictionary<string, string> args = new Dictionary<string, string>();

In this group of code we need to add another line similar to this:

args.Add("alias2", variable);//Mine

You can see the variable we defined is used again in this bit of code along with another alias. For alias2 we will follow SalesLogix policy and use the first initial of our EntityName in lowercase - example alias2 = pid

That should do it for the Schedule Complete Activity set of files. To test, save all files, build your web platform, then redeploy your site. When the website comes up go to the Schedule Menu Item, select "Complete an Activity" from the list.

Your view should come up with your field right below the Ticket Look up. Here is mine:

Next up on our list is CompleteActivity.ascx.resx, CompleteActivity.ascx, and CompleteActivity.ascx.cs files.

Take care until then!

Geo

Adding Activities and History to a Custom Main Entity in the Saleslogix Web Client (Part 2)

2010-05-04T15:36:00Z

Yesterday I started a series of post that will talk the reader through giving the ability to schedule activities and review history records for a custom main entity. Today we will continue on with the first set of modifications. Before we get in too deep we need to define some elements to use in our code. These definitions will be used over and over again as we move through this process so keep a separate list handy for easy references. One thing to remember while determining these definitions is the use of proper case in C# code. If you put a capital letter where a lowercase should be, C# will throw an error.

In the last post I asked you to come up with the fields from your entity that will be used in your Activity lookup. I will have three fields in my lookup, from the FXProject Entity I will have "Name" and "Status", and through a relationship to the User Entity called ProjectManager I will also have the UserName field.

Field1 = Name
Field2 = Status
Field3 = ProjectManager.UserName

Now those are the names of the fields as they are created in the entities. These may or may not be the names of the columns for your lookup. So Lets define what the columns names are:

Field1Label = Project Name
Field2Label = Status
Field3Label = Project Manager

When I look at the lookup on the Activity Detail page I want the field to have a label. For me, my lookup is for a project so in my case LookupLabel = Project:

Two other things to think about ahead of time are EntityName and FieldName.

EntityName is easy, look at the tree control and find the exact spelling using proper case, for my example I will use EntityName = FXProject.

For the FieldName lets use the name of the field we added to the Activity and History tables (In Part 1) as the name for the lookup field. This is consistent with the Account and Contact lookup already built on the pages we are going to modify. The name I used was "FXProjectId", so for todays code FieldName = FXProjectId

Lets add some code.

There are three areas we will modify today in the SalesLogix Application Architect(AA).

ActivityDetails.ascx (This holds the properties for the Activity Details Page)
ActivityDetails.ascx.resx (This holds the definition for the labels)
ActivityDetails.ascx.cs (This holds the code so the form reacts as expected)

Right click and select "Open" on the following files:

ActivityDetails.ascx
ActivityDetails.ascx.cs

Then open another folder just above Activity in the tree labeled "App_LocalResources". Right click and select open a file named "ActivityDetails.ascx.resx".

The first file we will work with is ActivityDetails.ascx. This file holds the code for the controls that make up the page that displays when you edit an activity. To add our code navigate to the end of line 247, hit the enter button, and add the following lines:

<%--Start My Code --%>
<%--End My Code --%>

I do this to help my find my code quicker especially when I am combing the use of Visual Studio with AA. These should be lines 248 and 249. Now put a line between these two, this is where you can paste in the following code that builds your custom entity lookup:

    </tr>
    <tr class="contact-row">
         <td>
            <div class="lbl alignleft"><asp:Label ID="FieldName_lz" AssociatedControlID="FieldName" runat="server" Text="<%$ resources: FieldName_lz.Text %>"></asp:Label></div>
            <div class="textcontrol">
                <SalesLogix:LookupControl runat="server" ID= "FieldName" AutoPostBack="true" AllowClearingResult="true" OverrideSeedOnSearch="true" LookupBindingMode="string" ReturnPrimaryKey="true" LookupEntityName="EntityName" LookupEntityTypeName="Sage.Entity.Interfaces.IEntityName, Sage.Entity.Interfaces, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" SeedProperty="Account.Id">
                <LookupProperties>
                    <SalesLogix:LookupProperty PropertyHeader="<%$ resources: Lookup_Field1.PropertyHeader %>" PropertyName="Field1" PropertyFormat="None" UseAsResult="True"></SalesLogix:LookupProperty>
                    <SalesLogix:LookupProperty PropertyHeader="<%$ resources: Lookup_Field2.PropertyHeader %>" PropertyName="Field2" PropertyFormat="None" UseAsResult="True"></SalesLogix:LookupProperty>
                    <SalesLogix:LookupProperty PropertyHeader="<%$ resources: Lookup_Field3.PropertyHeader %>" PropertyName="Field3" PropertyFormat="None" UseAsResult="True"></SalesLogix:LookupProperty>
                </LookupProperties>
                <LookupPreFilters>
                </LookupPreFilters>
                </SalesLogix:LookupControl>
            </div>
        </td>

Read through the code and find and replace the FieldName, EntityName, and Fields (1, 2, and 3) as we defined above. I tried to bold all of the values that need to be changed. You see with EntityName some of the code has an "I" before the EntityName, ensure you leave this "I" in place. When you have completed this area make sure you save your work.

With that code in place move to the ActivityDetails.ascx.resx file. This is where the field labels are defined. Here we want to navigate to the very bottom the file on line 263, hit enter and add the following lines:

Reference your labeling defined above for the FieldName, LookupLabel, Fields (1, 2, and 3) and FieldLabels (for 1, 2, and 3) and insert in the code.

Next we will move to the ActivityDetails.ascx.cs file. This holds code that defines how the page and all of it's properties react when loading or when changes occur. This can be pretty complicated for the unexperienced developer so take your time.

The first thing I did was to close all of the tree control items so my view looked like this.

If you look up in the upper right hand corner of the screen shot you will see a field with a the value of "Page_PreRender". This field will help you find the code we are going to modify. You simply look for the values I provide you and add code where indicated.

The first item I am going to search for is the Page_PreRender event. You might have to click on the plus sign to open to the code, when the code is visible look for a lint that looks like this:

"TicketId.InitializeLookup = (TicketId.SeedValue.Length == 12);"

Right under this line add the following line of code:

FieldName.InitializeLookup = (FieldName.SeedValue.Length == 12);//MINE

Add your definition for FieldName to the code and close up the tree. Replace the word mine with your initials, again to help you find your code later if necessary.

Search for the "AccountId_LookupResultValueChanged" event. Look for a line that looks like this:

Activity.LeadId = null;

Right under this line add the following line of code and then add your definition for FieldName to the code.

Activity.FieldName = null; //Mine

Search for "LeadId_LookupResultValueChanged" event. Look for a line that looks like this:

Activity.AccountId = null;

Right under this line add the following line of code and then add your definition for FieldName to the code.

Activity.FieldName = null;//Mine

Search for the "SetTACODefaultsFromHistoryId" event. Look for a line that looks like this:

Activity.LeadId = hist.LeadId;

Right under this line add the following line of code and then add your definition for FieldName to the code.

Activity.FieldName = hist.FieldName; //Mine

Search for the "SetTACODefaultsFromEntityHistory" event. Here there are several Case statements. We will add our code to the end of these existing Case statements. FYI, this area assumes that your main entity has a relationship built to the Account Entity and the the relationship name is Account. IF this is not the case I would leave the AccountId line out.

case "IEntityName"://Mine
     found = true;//Mine
     IEntityName EntityName = EntityFactory.GetById<IEntityName>(hist.EntityId.ToString());//Mine
     Activity.FieldName = EntityName.Id.ToString();//Mine
     Activity.AccountId = EntityName.Account.Id.ToString();//Mine
     break;//Mine

Add your definition for EntityName and FieldName.

Search for the "OnAddEntityBindings" event. Copy these lines any where within the brackets put in your definition for FieldName. Here again is an assumption about a relationship to the Account. If you do not have a relationship for the Account I would not use the second line.
bs.Bindings.Add(new WebEntityBinding("FieldName", FieldName, "LookupResultValue"));//Mine
bs.Bindings.Add(new WebEntityBinding("AccountId", FieldName, "SeedValue"));//Mine

With this last bit of code in place make sure you save your work. Go ahead and build your platform and Deploy your Test environment to see if everything is working correctly. The best place to test at this point is by editing an exisitng Activity. Mine worked well, here is a screen shot.

I know this was a lot of information all at once, the next post should be a little less at once. The next post will cover modifying the following custom smart parts:

ScheduleCompleteActivity.ascx.

ScheduleCompleteActivity.ascx.resx

ScheduleCompleteActivity.ascx.cs

Until then peace.