While there are many ways of querying data in the SalesLogix entity model, including using the DataServices, or IQuery to run a direct query to the database, SalesLogix also has provided a Picklist assembly for working specifically with picklists. If you take a look at the entity model you will notice that there is no IPicklist assembly. Working with Picklists are a common enough thing for any SalesLogix developer. Wether it be populating a control with picklist values or doing a lookup to get picklist’s text, short text, picklistid, or itemid values, all of these things can be accomplished by using the Sage.SalesLogix.Picklists assembly.
The picklist assembly defines a Picklist class (Sage.SalesLogix.PickLists.PickList). This class exposes the typical properties about a picklist entity including:
- Code
- CreateDate
- CreateUser
- DefaultIndex
- Id (same as ItemId)
- ItemId
- ModifyDate
- ModifyUser
- PicklistId
- Shorttext
- Text
- UserId
It also contains several methods that can be called to return a specific Picklist object or a collection of them.
Lets take a look at one I recently had to use.
In the Support area of SalesLogix (Tickets in particular) there are several fields that store the picklist code rather than the text value. One of these is Ticket Status.
Now if you tried to run a server side script to get the contents of the Ticket Status’ picklist such as
if(pklStatusCode.PickListValue == “Open”)
It would never return true. This is because since the picklist stores the picklist code instead of the text, what it actually would need to say is
if(pklStatusCode.PickListValue == “k6UJ9A000039”)
In this example the ID represents the picklist’s ItemId value, which is the unique value for the item in the picklist table. Since picklist ItemID’s can be different between databases (They are sequentially generated at the time the picklist entry is created based on the key index of the database at the time) it is not a good idea to hard-code a specific value.
So how can we do a quick lookup to get what the correct ItemID value is? Use the Sage.SalesLogix.PickLists assembly, of course!
Lets take a look at one method in particular, called “GetPicklistItemByName”. This method accepts two inputs, the name of the picklist containing the value you want to query and the text value of the item you want to query. It ouputs a Sage.SalesLogix.Picklists.Picklist object. Lets take a looks:
Sage.SalesLogix.PickLists.PickList er = Sage.SalesLogix.PickLists.PickList.GetPickListItemByName(“Ticket Status”,”Closed”);
So in this sample I am going to get a Picklist object returned to me that represents the picklist item in the “Ticket Status” picklist where the item’s text = “Closed”. Now with my object I can then query any of its attributes. Here is full sample:
Sage.SalesLogix.PickLists.PickList er = Sage.SalesLogix.PickLists.PickList.GetPickListItemByName(“Ticket Status”,”Closed”);
if( pklStatusCode.PickListValue == er.ItemId)
There you have it. A very easy way of getting the picklist details about a particular item. Spend some time looking at this assembly. There is a lot it can offer when you need to work with picklists.
Excellent post Kris.