Show DateTime value as boolean in SalesLogix DateGrid

One time I received a request to add a new column to a dategrid. The client wanted to show a datetime field in the grid to indicate if a thing has been completed or not. However there are already too many dates in the current grid and the client only cares about if the field has a value or not, instead of the real value itself. I added the field but set the date type to be boolean, then in "Format" field put the desired wording when the value is not NULL. It will show blank when the datetime value is NULL. It perfectly solved the problem.

Another reason not to use Date type in SalesLogix

In my previous post I logged some of my finding about Date vs. DateTime type. Here is another reason that Date type may cause problem.

In TABLE1 we have a StartDate which is a Date type. Through SLOX UI everything works fine - 00:00:00 is used as the time value when saving and SLOX knows not to do any conversion.

However there is a database view retrieving same field. It seems that SLOX doesn't know the exact type of the field in the view so it does the UTC-to-local conversion. The result is that we get back a value which is 7/8 hours earlier.

I don't know why SLOX provides different Date and DateTime type in table design. In SQL 2005 they are all DateTime. Maybe it saves spaces in other databases?

SalesLogix Picklist management and tips

All the picklists and the picklist items are stored in sysdba.Picklist table. To get a list of all picklists:

select * from sysdba.picklist where picklistid='PICKLISTLIST'

The ShortText field seems to store settings code for each picklist. ItemID is the identifier for picklist. To get all items of a particular picklist:

select * from sysdba.picklist where picklistid='k6UJ9A00032L'

Worth noting is the 'USERID' field. If the picklist's setting is to allow user edit (Users cannot edit items is unchecked), the USERID field will have the user ID of the user who inserts the item, and this item will be visible for that particular user only. Only USERID='ADMIN' are shared by all users. Additionally, non-admin user can only edit/delete their own items.

There was a request that a business user wanted to be able to modify a picklist herself without asking ADMIN to do it and her change should be made available to all users. What I did is to insert a INSERT Trigger on Picklist table:

Declare @userID char(12), @itemID char(12)
SELECT @userID = USERID, @itemID=itemID FROM INSERTED

IF @userID = 'U6UJ9A00000Q'
BEGIN
UPDATE sysdba.PickList SET USERID='ADMIN' WHERE ITEMID=@itemID
END

The outcome is that any picklist item this user ('U6UJ9A00000Q') adds will be available to all users (after a refresh of course). However, since the USERID is changed to ADMIN, this user can't change/delete afterward.

The forms to manage the picklist are not listed in Plugin window. I assume that is the part that Sage doesn't allow you to customize. To give total control of picklists to a non-admin user, customized forms are needed. Basically you have to duplicate what SalesLogix already has, then add your function on top of that.

Inserting background picture to Crystal Report

I don't know if this is only my dev machine - the background picture disappears after I reopen the report.

There are a few jpg files I want to use as the background of my report. I used Insert->Picture, then select the file. Everything looks good (move to back). However after I close and re-open the report, the picture is blank. I can tell the object is still there (by the blue
border when move mouse over) however it's just blank. Double clicking won't activate the edit mode.

I found that, in order for this picture to retain, I have to insert the picture to a Word file first then copy it in memory from Word. Copying from MSPaint doesn't work. Has to be Word. After inserting I had to adjust the size a little bit and the report file size is also much smaller than the version which I directly inserted picture file to.

Strategies to improve ASP.NET web app performance

There are few more things you can do other than adding hardware and caching...

• combine small files. A lot of small files is slower to download than a few large files
  
  
• move static resource files such as images, CSS, and JS files off of the ASP.NET servers, and set cache settings on that server. A server tuned for ASP.NET is not especially well-suited to server these files. From the client side, the browser can open two additional concurrent connections to download the static files. Another benefit is that the browser doesn't need to send the cookies to the static files (ASP.NET cookies will be sent to web apps otherwise).
  
  
• think twice before breaking services into different server. All those out-of-process calls between the web servers and business object servers create a lot of overhead.
  
  
• IP affinity might be easier at the beginning (so that session can be managed in-process), but will create grief in the long run/bigger load. Scenarios: IIS process recyles, some megaproxy (ie AOL) will be bound to a single server etc
  
  
• use compression. Yes it costs processor cycles but you typically have plenty of extra CPU capacity on a dedicated web server. IIS7 is even optimized so that when the processor gets really busy, it will suspend compression. Or check http://www.port80software.com/ for 3rd party solutions
  
  
• optimizing the HTTP pipeline. There are some HTTP Modules that sit in the ASP.NET request pipeline by default that you may not need. For example, if you don't need session management, and only use FormAuthentication, you can remove Session, WindowsAuthentication, PassportAuthentication and FileAuthorization in httpmodules section.
  
  
• reduce or even completely disable the ViewState
  
  
• GET is preferred over POST for AJAX calls. GET carries much smaller footprint than POST and POST costs one extra HTTP call than GET.
  
  
• Put CSS Files on Top and JS Files at the Bottom. This way, the page would start displaying as soon as the CSS files are downloaded on the client side and won't wait for the JavaScript files which could be loaded in the end once the page is fully loaded and client could interact with it. ScriptManager also has a parameter LoadScriptsBeforeUI which can be used for this purpose
  
  
• programmatic caching add complexity but has great benefit too
  
  
• scaling database. Move to cluster, partition the tables, use dedicated reader and writer databases (and of course the sync process too)