How to Retrieve Picklist Data in InforCRM: A Quick Guide

InforCRM (formerly SalesLogix) is a robust CRM platform that allows for extensive customization. One of its key features is picklists, which are dropdown menus that provide predefined options for users. Retrieving picklist data programmatically can be incredibly useful when building custom integrations, reports, or dynamic forms. In this guide, we’ll show you how to access picklist data from InforCRM, using Account Type as an example.

Why Retrieve Picklist Data?

Picklist data retrieval is essential for:

• Synchronizing options between InforCRM and external systems.
• Pre-populating fields in custom forms or external apps.
• Analyzing picklist usage across the system to improve data quality.

Understanding the Picklist Structure in InforCRM

InforCRM stores picklists in the PickList table. Both the row representing the picklist itself (header row) and the rows representing the individual items in the picklist are stored in this same table. Here’s how the structure works:

• Header Row (Picklist Definition): The picklist itself is represented by a single row in the table where PICKLISTID = 'PICKLISTLIST'. The ITEMID of this row serves as the identifier for the picklist and is used in the PICKLISTID field of all rows belonging to that picklist.
• Item Rows: Each individual picklist item is stored as a separate row. These rows share the same PICKLISTID, which corresponds to the ITEMID of the header row.

Key fields in the PickList table include:

• PICKLISTID: Links picklist items to their parent picklist. For the header row, this is always 'PICKLISTLIST'.
• ITEMID: A unique identifier for each row. The ITEMID of the header row is used as the PICKLISTID for its associated item rows.
• TEXT: The display text for each picklist item or the name of the picklist in the header row.
• SHORTTEXT: An optional short description or code associated with a picklist item.

Methods to Retrieve Picklist Data

1. Using SQL Queries

Example 1: Retrieve the Header Row for the “Account Type” Picklist

SELECT ITEMID, TEXT
FROM sysdba.PickList
WHERE PICKLISTID = 'PICKLISTLIST'
  AND TEXT = 'Account Type';

This query retrieves the header row for the Account Type picklist, including its ITEMID. The ITEMID serves as the key for finding all associated items.

Example 2: Retrieve Items for the “Account Type” Picklist

SELECT ITEMID, TEXT, SHORTTEXT
FROM sysdba.PickList
WHERE PICKLISTID = (
    SELECT ITEMID
    FROM sysdba.PickList
    WHERE PICKLISTID = 'PICKLISTLIST'
      AND TEXT = 'Account Type'
);

The subquery fetches the ITEMID of the header row for the Account Type picklist. The outer query retrieves all rows where the PICKLISTID matches this ITEMID, effectively returning all items in the picklist.

2. Using the InforCRM API

InforCRM’s REST API provides an easy way to retrieve picklist data. Below is an example of fetching picklist data using the header row’s ITEMID:

GET /sdata/slx/dynamic/-/picklists?where=PICKLISTID eq 'AccountTypeItemID'

Replace 'AccountTypeItemID' with the ITEMID of the Account Type picklist header row. This returns the picklist options in JSON format.

3. Custom Code in the InforCRM Web Client

InforCRM’s web client supports scripting to dynamically retrieve picklist data. Below is an example for fetching picklist data using the header row’s ITEMID:

var service = Sage.Data.SDataServiceRegistry.getSDataService('dynamic');
var request = new Sage.SData.Client.SDataSingleResourceRequest(service)
    .setResourceKind('picklists')
    .setQueryArg('where', "PICKLISTID eq 'AccountTypeItemID'");

request.read({
    success: function(data) {
        console.log('Picklist Data:', data);
    },
    failure: function(error) {
        console.error('Error fetching picklist data:', error);
    }
});

Replace 'AccountTypeItemID' with the ITEMID of the header row for the Account Type picklist to fetch its associated data.

Summary

Understanding how InforCRM stores picklists in the PickList table is key to retrieving picklist data efficiently. By recognizing the dual role of the table—storing both the header row and its associated items—you can leverage SQL, APIs, or custom scripts to dynamically access and manipulate picklist data.

Have additional tips for working with picklists in InforCRM? Share them in the comments below!