More thoughts about look up field types

Some time ago I wrote an article called "Sage CRM EntryTypes used in the COM ASP API" in which I listed the different EntryType codes that can be used to define fields (Entries).

I explained that within an existing system you can check which EntryTypes are being used by examining the Meta Data.

For example the SQL statement below can be used to report on the system EntryTypes

[code language="sql"]
SELECT DISTINCT dbo.Custom_Edits.ColP_EntryType, CONVERT(nVARchar(32), dbo.Custom_Captions.Capt_US) as "Caption"
FROM dbo.Custom_Captions RIGHT OUTER JOIN
dbo.Custom_Edits ON dbo.Custom_Captions.Capt_Code = CONVERT(nVARchar(32), dbo.Custom_Edits.ColP_EntryType)
WHERE (dbo.Custom_Captions.Capt_Family = N'EntryType')
[code]

Within all the results turning there are different look up fields defined. These are look ups that draw their data from other tables within the system.

  • User Select is EntryType 22
  • Team Select is EntryTpe 23
  • Search Select is EntryTpe 26
  • Search Select Advanced is EntryType 56

I was asked a question about parsing the fields. For example if you have a field called xxxx_mylookupid and it is of EntryType 56 how do you know that this field draws its information from one column or another?

How can we tell if a field 'comp_salesrep' links to Users, Teams or to another table?

The first thing to focus on is the EntryType and to realise what that means.

User Select field (EntryType 22) and Team Selects (EntryType 23) are examples of type of Intelligent Select fields. These are special because their lookup does not go directly to the database but rather to a set of the data within the web page itself. The list of possible users or possible teams are held as arrays within the web page. I have actually written several articles these types of list that are brought together in: An essential guide to Selection Lists and Intelligent Selects: A round up of articles that discuss selection entry types.

This means that for the EntryType 22 you can be sure that it is always only dealing with User data and for the EntryType 23 you are only dealing with Team data.

In an ASP page there different DefaultType relevant for each EntryType.

For an User Select

[code language="Javascript"]
var customTextEntryBlock = CRM.GetBlock("Entry");
with (customTextEntryBlock)
{
EntryType = 22; //User Select
DefaultType = 0 //No default
DefaultType = 1 //Specific User
DefaultType = 2 //Current User
}
[/code]

For a Team Select

[code language="Javascript"]
var customTextEntryBlock = CRM.GetBlock("Entry");
with (customTextEntryBlock)
{
EntryType = 23; //Team Select
DefaultType = 0 //No default
DefaultType = 1 //Specific Team
DefaultType = 3 //Current Users Default Team
}
[/code]

But that still leaves the question of how to tell if a field like xxxx_salesid is looking up to the Opportunity table or to another table?

If the field has the EntryType of 26 or 56 then we know that this information will be drawn directly from the database. But I am going to make life easier for myself and only consider EntryType 56 as this represents fields of type 'Search Select Advanced' whereas EntryTpe 26 is for the older Search Select. My point being that new fields defined by a system administration can only use EntryType 56, they can not use EntryType 26 as this has been removed as an option within the field definition screens. Nobody is going to be defining new fields of EntryType 26 therefore I don't need to worry about it.

A field like xxxx_salesid is going to be defined in the Meta Data table custom_edits.

This table provides the key information that tells a Search Select Advanced field where it 'looks up' to.

In ASP code when a Search Select Advanced is defined we would write something like

[code language="JavaScript"]
var myBlock = CRM.GetBlock("opportunitydetailbox");
var personidBlock = CRM.GetBlock("entry");
with(personidBlock)
{
EntryType = 56;
LookUpFamily = 'Person'; //The search entity to be used
//DefaultType = 0; //no default search
//DefaultType = 16; //with current person
//DefaultType = 17; //with current company
//DefaultType = 19; //with custom entity
DefaultType = 20; //with current entity
SearchSQL = "pers_type='customer'";
Restrictor = "oppo_primarycompanyid";
FieldName = "oppo_primarypersonid";
Caption = "Person:";
CaptionPos = CapTop;
NewLine = false;
}
[/code]

Note: You can see the use of the DefaultType.


Here the import thing I want you to note is the definition of the LookupFamily which explains the search entity to be used. This relies on a set of Meta Data defined in the custom_captions table.

If I was manually adding a new entity called Project into the system then I would have to define a set of 4 translations that would make my new entity available for the lookup in Search Select Advanced fields.

  • capt_Family =SS_Entities
  • capt_Code ='Project'
  • capt_us=Project'

  • capt_family=SS_SearchTables
  • capt_code='Project'
  • capt_us='Project'

  • capt_family=SS_ViewFields
  • capt_code='Project'
  • capt_us='proj_description'

  • capt_family=SS_IdFields
  • capt_code='Project'
  • capt_us='proj_projectid'

The link between the custom_edit record, that defines the field, and the custom_caption that defines where the field derives its data is proved between the colp_lookupfamily and the capt_code. The exact field that is the source of data for a Search Select Advanced field of EntryType 56 is the definition in the capt_family 'ss_idfields'.

We can explore this by running a query that will return all the Search Select Advanced fields and the source fields that provide the data.

[code language="sql"]
SELECT dbo.Custom_Edits.ColP_Entity AS 'Entity', dbo.Custom_Edits.ColP_ColName AS 'Lookup Column', dbo.Custom_Captions.Capt_US AS 'Source Column'
FROM dbo.Custom_Edits INNER JOIN
dbo.Custom_Captions ON dbo.Custom_Edits.ColP_LookupFamily = dbo.Custom_Captions.Capt_Code
WHERE (dbo.Custom_Edits.ColP_EntryType = 56) AND (dbo.Custom_Captions.Capt_Family = N'ss_idfields')
[/code]

Anonymous