Columns

Additional columns you add to a SmartList object will reference data related to the object. For example, the sample integrating application adds columns to the Customers SmartList object that track customer contact history information. The new columns appear identical to those that exist for the object.

Trigger to add columns

Additional columns should be added when the SmartList window is being opened. At that time, the resources in the SmartList dictionary will be available so the columns can be added. The following example is a portion of the Startup script for the sample integrating application. It registers a focus trigger for the ASI_Explorer form which is the main form for the SmartList. Notice that the trigger is registered by name, since the form is located in the SmartList dictionary, not the core dictionary.

l_result = Trigger_RegisterFocusByName(SMARTLIST, "form ASI_Explorer", TRIGGER_FOCUS_PRE, TRIGGER_AFTER_ORIGINAL, script SmartList_AddCustomerColumns);
if l_result <> SY_NOERR then
	warning "Focus trigger registration for SmartList failed.";
end if;

The trigger processing procedure that runs when the trigger is activated will add the new columns to the Customer SmartList object. Use the Explorer_Add_Column_To_Object procedure in the SmartList dictionary to add the new columns. When you add a column, you specify the following:

The product and SmartList object to which a column is being added
The unique ID of the column (typically the field’s resource ID)
The label to display for the column
The initial width for the column
The technical name of the global field used for the column
Whether the column will appear by default for the object
The type of data the column will display
The type of list (used if the field is a list-type field)

The following trigger processing procedure from the sample integrating application shows how two columns are added to the Customers SmartList object.

local integer l_error;
local string l_message;
local integer display_sequence, field_sequence;

{Add additional columns to the Customer object for SmartList}
call with name "Explorer_Add_Column_To_Object" in dictionary SMARTLIST,
	DYNAMICS,
	SMARTLIST_OBJECTTYPE_CUSTOMERS,
	IG_PROD_ID,
	resourceid(field 'First Contact Date'),
	"First Contact",
	technicalname(field 'First Contact Date'),
	false, {Do not include in default query}
	SMARTLIST_DATATYPE_DATE,
	1,
	false,
	false,
	display_sequence,
	field_sequence,
	l_error;

if l_error <> OKAY and l_error <> 17 then
	l_message = getmsg(22003);
	substitute l_message, "Explorer_Add_Column_To_Object", str(l_error);
	error l_message;
end if;

call with name "Explorer_Add_Column_To_Object" in dictionary SMARTLIST,
	DYNAMICS,
	SMARTLIST_OBJECTTYPE_CUSTOMERS,
	IG_PROD_ID,
	resourceid(field 'Contact Salesperson ID'),
	"First Contact Salesperson",
	technicalname(field 'Contact Salesperson ID'),
	false, {Do not include in default query}
	SMARTLIST_DATATYPE_STRING,
	1,
	false,
	false,
	display_sequence,
	field_sequence,
	l_error;

Retrieving the table name

When SmartList displays columns for the selected object, it must retrieve which table the field used for the column can be found in. The Explorer_Get_Table_Name procedure will be called by SmartList to perform this action. In this procedure, you will return the table technical name for the field used in the columns you added to a SmartList object.

The following is the Explorer_Get_Table_Name procedure that retrieves the table technical name for the columns added to the Customers SmartList object. The fields for the columns added all come from the IG_Contact_History_MSTR table.

Procedure name: Explorer_Get_Table_Name

inout string l_Table_Name;
in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in integer IN_Field_Dict_ID;
in integer IN_Field_ID;
in integer IN_Doc_Status;

if IN_Object_Dict_ID = DYNAMICS then
	if IN_Object_Type = SMARTLIST_OBJECTTYPE_CUSTOMERS then
		l_Table_Name = technicalname(table IG_Contact_History_MSTR);
	end if;
end if;

Retrieving column datatypes

As SmartList works with a SmartList object, it must be told what type of data is found in each column. The Explorer_Get_Datatype procedure will be called by SmartList to perform this action. In this procedure, you will set the datatype for each column you added to a SmartList object.

The following is the Explorer_Get_Datatype procedure that retrieves the column datatypes for the columns added to the Customers SmartList object. Note the else clause of the case statement. This is necessary so that the “Any Field” search feature of SmartList works properly.

Procedure name: Explorer_Get_Datatype

inout integer IO_Datatype;
in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in integer IN_Field_Dict_ID;
in integer IN_Field_ID;

{Handle cases for objects already defined in Microsoft Dynamics GP}
if IN_Object_Dict_ID = DYNAMICS then
	if IN_Object_Type = SMARTLIST_OBJECTTYPE_CUSTOMERS then
		if IN_Field_Dict_ID = IG_PROD_ID then
			case IN_Field_ID
				in[resourceid(field 'First Contact Date')]
					IO_Datatype = SMARTLIST_DATATYPE_DATE;
				in[resourceid(field 'Contact Salesperson ID')]
					IO_Datatype = SMARTLIST_DATATYPE_STRING;
				in[resourceid(field 'Last Contact Date')]
					IO_Datatype = SMARTLIST_DATATYPE_DATE;
				in[resourceid(field 'Contact Salesperson ID 2')]
					IO_Datatype = SMARTLIST_DATATYPE_STRING;
				else
					{The "Any" field case for searching}
					IO_Datatype = SMARTLIST_DATATYPE_ALL;
			end case;
		end if;
	end if;
end if;

Retrieving column values

As SmartList fills with data, each column value must be supplied as a string. The Explorer_Get_Field_As_String_From_Table procedure will be called by SmartList to retrieve the string value to use for a column as each row of the SmartList is filled. This procedure must have the following parameters:

inout string IO_String;
in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in integer IN_Field_Dict_ID;
in integer IN_Field_ID;
in boolean IN_Searching;
in string IN_Master_ID;
inout integer IO_Datatype;
inout long IO_Long;
inout date IO_Date;
inout currency IO_Currency;
inout time IO_Time;

The IN_Master_ID parameter will contain the values of the key fields for the primary key of the main table for the SmartList object. You will use these key fields to retrieve the appropriate record from your table for the row being displayed in the SmartList. Refer to SmartList objects for a list of the primary tables that correspond to the SmartList object types.

The field values passed in though the IN_Master_ID parameter will be padded with spaces to each field’s specified length. For example, a field with the STR30 datatype will be padded to a length of 30. Your code must parse the string into the individual field values.

After your code has retrieved the appropriate record from your table, it must set the inout parameters for the column you added. The value of the IO_String parameter must be set to the string value you want displayed in the SmartList. If necessary, you can apply formatting to this string. For instance, you could use the format() function to apply formatting to a currency value.

Your code must set the IO_Datatype parameter, which specifies the underlying datatype for the column. Then it must set the value of the corresponding parameter for the datatype. For example, if the column displays a currency value, you will set the IO_Datatype parameter to SMARTLIST_DATATYPE_CURRENCY and set the IO_Currency parameter to the actual currency value for the column.

The following is the Explorer_Get_Field_As_String_From_Table procedure from the sample integrating application. Based on the key value passed in through the IN_Master_ID parameter, it retrieves the appropriate record from the IG_Contact_History_MSTR table. It then sets the IO_String parameter and the appropriate datatype parameters.

Procedure name: Explorer_Get_Field_As_String_From_Table

inout string IO_String;
in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in integer IN_Field_Dict_ID;
in integer IN_Field_ID;
in boolean IN_Searching;
in string IN_Master_ID;

inout integer IO_Datatype;
inout long IO_Long;
inout date IO_Date;
inout currency IO_Currency;
inout time IO_Time;

if (IN_Object_Dict_ID = DYNAMICS) and (IN_Field_Dict_ID = IG_PROD_ID) then
	if IN_Object_Type = SMARTLIST_OBJECTTYPE_CUSTOMERS then
		'Customer Number' of table IG_Contact_History_MSTR = IN_Master_ID;
		get table IG_Contact_History_MSTR;
		if err() = OKAY then
			case IN_Field_ID
				in[resourceid(field 'First Contact Date')]
					IO_Datatype = SMARTLIST_DATATYPE_DATE;
					IO_Date = 'First Contact Date' of table IG_Contact_History_MSTR;
					IO_String = str('First Contact Date' of table IG_Contact_History_MSTR);
				in[resourceid(field 'Contact Salesperson ID')]
					IO_Datatype = SMARTLIST_DATATYPE_STRING;
					IO_String = 'Contact Salesperson ID' of table IG_Contact_History_MSTR;
				in[resourceid(field 'Last Contact Date')]
					IO_Datatype = SMARTLIST_DATATYPE_DATE;
					IO_Date = 'Last Contact Date' of table IG_Contact_History_MSTR;
					IO_String = str('Last Contact Date' of table IG_Contact_History_MSTR);
				in[resourceid(field 'Contact Salesperson ID 2')]
					IO_Datatype = SMARTLIST_DATATYPE_STRING;
					IO_String = 'Contact Salesperson ID 2' of table IG_Contact_History_MSTR;
			end case;
		end if;
	end if;
end if;

Retrieving list field type

Most list fields are 1-based and number the items in the list 1, 2, 3, and so on. The exception is radio groups, which are 0-based and number the items 0, 1, 2, and so on. If you are adding a list field as a column to a SmartList, you must use the Explorer_Get_DDL_Type procedure to tell SmartList how the items in the list field are numbered. The value 1 indicates a 1-based list field, while the value 0 indicates a 0-based list field.

The following is the Explorer_Get_DDL_Type procedure for the sample integrating application. All of the list fields from the sample that are used for SmartList columns are 1-based, so the value 1 is returned.

Procedure name: Explorer_Get_DDL_Type

out integer OUT_DDL_Type;
in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in integer IN_Field_Dict_ID;
in integer IN_Field_ID;

{All of the drop-down lists in the sample are 1-based}
if IN_Object_Dict_ID = IG_PROD_ID then
	OUT_DDL_Type = 1;
end if;

Retrieving list field values

To make it easier for the user to create SmartList search criteria, the items for list field columns are displayed in the Search window. For instance, the following illustration shows the choices displayed for the Qualified list column.

You will add the Explorer_Fill_Range_DDLs_From_Field_ID procedure to your application to retrieve the the values for the list columns you have added. This procedure must have the following parameters:

in integer IN_Object_DictID;
in integer IN_Object_Type;
in integer IN_FieldDictID;
in integer IN_FieldID;
inout anonymous field IO_From_DDL;
inout anonymous field IO_To_DDL;

The IO_From_DDL and IO_To_DDL parameters contain references to the two drop-down lists that appear in the Search window. Your code must fill these drop-down lists with the values for the list field of the column the user selected. List fields must be window fields to have their static values read by sanScript code. You will need to place an instance of the list field on a hidden window so its static values can be accessed. A hidden window on a command form is a good place to add the hidden list field.

The following is the Explorer_Fill_Range_DDLs_From_Field_ID procedure for the sample integrating application. When the Qualified column is searched, it sets the static values for the drop-down lists that are displayed in the Search window. Notice that it references an instance of the Qualified Lead list field on a hidden window so that the static items can be retrieved.

Procedure name: Explorer_Fill_Range_DDLs_From_Field_ID

in integer IN_Object_DictID;
in integer IN_Object_Type;
in integer IN_FieldDictID;
in integer IN_FieldID;
inout anonymous field IO_From_DDL;
inout anonymous field IO_To_DDL;

local integer i;

if IN_Object_DictID = IG_PROD_ID then
	if IN_Object_Type = SMARTLIST_OBJECTTYPE_LEADS then
		if IN_FieldID = resourceid(field 'Qualified Lead') then
			{Clear the drop-down lists before filling them}
			clear field IO_From_DDL;
			clear field IO_To_DDL;
			for i = 1 to countitems('Qualified Lead' of window Dummy of form Command_IG_Sample) do
				add item itemname('Qualified Lead' of window Dummy of form Command_IG_Sample, i) to field IO_From_DDL;
				add item itemname('Qualified Lead' of window Dummy of form Command_IG_Sample, i) to field IO_To_DDL;
			end for;
		end if;
	end if;
end if;

SQL optimization

SmartList can perform optimized searches when used with SQL Server. To take advantage of this optimization for the columns you have added, you must indicate that you have added the necessary procedures to support the optimization. These additional procedures will be called by SmartList as it searches data in the new columns you have added.

Since SQL Server is supported for all products, we recommend that you support SQL optimization for the columns you add to existing SmartList objects.

Explorer_Optimize_
For_SQLTo indicate that the new columns you have added support SQL optimization, you will add the Explorer_Optimize_For_SQL procedure to your integration. This procedure should return true if you support optimization for the object, and false if you do not.

The following is the Explorer_Optimize_For_SQL procedure from the sample integrating application. The sample adds columns to the Customers SmartList object. It provides the additional procedures needed to support SQL optimization, so it returns the value true when asked about whether the Customers SmartList object is optimized.

Procedure name: Explorer_Optimize_For_SQL

in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in integer IN_Field_Dict_ID;
in integer IN_Field;
inout boolean IO_Optimize;

{By default, assume not optimized}
IO_Optimize = false;
{Customers SmartList object additional columns are optimized}
if IN_Object_Dict_ID = DYNAMICS then
	if IN_Object_Type = SMARTLIST_OBJECTTYPE_CUSTOMERS then
		IO_Optimize = true;
		warning "Customers stuff is optimized";
	end if;
end if;

Retrieving SQL field information

Most SmartList objects are optimized for SQL Server. To allow searching based on the columns you add to a SQL-optimized object, you must add the Explorer_Get_SQL_Field_Info procedure to your application. This procedure returns the following for each column:

Table technical name of the table that stores the data for the column
ID of the dictionary containing the table that stores the data for the column
Type of list field used for the column (if applicable)

This information is used when generating the SQL query used to search for matching records.

The following is the Explorer_Get_SQL_Field_Info procedure from the sample integrating application. For the sample, all of the fields for the additional columns come from the IG_Contact_History_MSTR table.

Procedure name: Explorer_Get_SQL_Field_Info

in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in integer IN_Field_Dict_ID;
in integer IN_Field;
inout string IO_Table_Name;
inout integer IO_Table_Dict_ID;
inout integer IO_DDL_Type;

if IN_Object_Dict_ID = DYNAMICS then
	if IN_Field_Dict_ID = IG_PROD_ID then
		if IN_Object_Type = SMARTLIST_OBJECTTYPE_CUSTOMERS then
			IO_Table_Name = technicalname(table IG_Contact_History_MSTR);
			IO_Table_Dict_ID = IG_PROD_ID;
			IO_DDL_Type = 1;
		end if;
	end if;
end if;

Setting SQL join information

For SmartList objects that are optimized for SQL Server, it’s necessary to describe how the tables used for additional columns are related to the main table for the SmartList object. You will add the Explorer_Get_SQL_Join_Info procedure to your application to do this. In this procedure you will call the Explorer_Set_SQL_Join_Info procedure in the SmartList dictionary to define the join that describes how the main table for the SmartList object is related to the table that contains data for the columns you’ve added. You will call this procedure once for each field that is part of the join.

Several types of joins are supported, but virtually all joins needed for SmartList are left-outer joins.

The following is the Explorer_Get_SQL_Join_Info procedure from the sample integrating application. In the sample, columns are added to the Customers SmartList object. The main table for this object is the RM_Customer_MSTR table. The data for the new columns comes from the IG_Contact_History_MSTR table. This means that a join must be made from the RM_Customer_MSTR table to the IG_Contact_History_MSTR table. The key field these two tables share in common is the Customer Number. There are 0 or 1 records in the IG_Contact_History_MSTR table for each record on the RM_Customer_MSTR table, so a left-outer join is appropriate.

Procedure name: Explorer_Get_SQL_Join_Info

in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in Explorer_INT_List IN_Field_Dict_ID;
in Explorer_INT_List IN_Field;
inout integer l_Doc_Status;

local integer i, l_Exp_Error;
local string l_From_Table, l_From_Field, l_To_Table, l_To_Field;
local integer l_From_Table_Dict_ID, l_To_Table_Dict_ID, l_Join_Type;

if IN_Object_Dict_ID = DYNAMICS then
	if IN_Object_Type = SMARTLIST_OBJECTTYPE_CUSTOMERS then

		{Set the characteristics of the join}
		l_From_Table = technicalname(table RM_Customer_MSTR);
		l_From_Table_Dict_ID = DYNAMICS;
		l_From_Field = technicalname(field 'Customer Number');
		l_To_Table = technicalname(table IG_Contact_History_MSTR);
		l_To_Table_Dict_ID = IG_PROD_ID;
		l_To_Field = technicalname(field 'Customer Number');
		l_Join_Type = SMARTLIST_JOINTYPE_LEFTOUTER;

		{Add the join}
		call with name "Explorer_Set_SQL_Join_Info" in dictionary SMARTLIST,
			l_From_Table,
			l_From_Table_Dict_ID,
			l_From_Field,
			l_To_Table,
			l_To_Table_Dict_ID,
			l_To_Field,
			l_Join_Type,
			l_Exp_Error;
		
		{Check for a join error}
		if l_Exp_Error <> 0 then
			warning "SQL join error: " + str(l_Exp_Error);
		end if;
	end if;
end if;

Overriding field names

In special cases, the name used for a field will change depending upon the type of document the field is contained in. For example, this issue occurrs for inventory transactions. If the inventory document being displayed in SmartList comes from the “work” table, the document number field is named IV Document Number. However, if the document comes from the “history” table, the document number field is named Document Number. You will add the Explorer_Get_SQL_Override_Field_Name procedure to your application to handle these special cases. This procedure must return the appropriate field name to use, based on the document type.

If you don’t have field names that change based on the document type, this procedure should return the empty string. This causes the original field name to be used.

The following is the Explorer_Get_SQL_Override_Field_Name procedure for the sample integrating application. There are no field names that change based on the document type, so an empty string is returned for the field name.

out string OUT_Field_Name;
in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in integer IN_Field_Dict_ID;
in integer IN_Field_Number;
in integer IN_Doc_Type;

clear OUT_Field_Name;