The Refresh procedure controls the process of displaying or updating the data displayed in the list. The type of action to be performed is passed into this procedure. If indicated, the procedure must populate the temporary table used for the list. The procedure must set the range options appropriately, and call the other procedures that are used during the display process.
For optimal performance, the process that loads the temporary table with data should be performed by code that is run directly on the SQL Server. One approach is to use the range copy statement, which will generate a SQL statement that will copy data from one table to another directly on the server. This is technique used for the Leads list. Pass-through SQL could also be used for this process.
Avoid using sanScript code, such as copy from table to table when loading the temporary table with data. Each record copied from the source to the temporary table will require two calls to the SQL Server, increasing network traffic and the SQL Server load.
If the list is displaying the Information Icon for each row, the Refresh procedure must set the initial value of this column by updating the InfoValue field for each row in the temporary table used for the list. For optimal performance, this update operation should be performed using pass-through SQL that will be executed by the SQL Server.
The InfoValue field in the temporary table specifies what the Information Icon will display for a specific row. The InfoValue field is a bitmask field. It has multiple states that can be set independently. The InfoValue_SetState() function of the syListObj form in the Dynamics.dic dictionary should be used to specify the values for the InfoValue column. The icon of the highest-priorty state will be displayed for the list. The tooltip for the icon will display all of the states that have been set.
As an example, the Leads list examines two conditions that are indicated by the Information Icon column. The first is whether the lead has been qualified. If the lead has not been qualified, the InfoValue column (which controls the Information Icon) is set to the “inactive” state. The following is the basic SQL statement used to find the qualified state and set the InfoValue field:
set InfoValue = (INFO_VALUE_INACTIVE * ((LISTTEMP.QualifiedLead) % 2))
The SQL statement examines the value of the QualifiedLead field, which has a drop-down list datatype. This field can have the value 1 (not qualified) or 2 (qualified). The modulus operation (%) will return 1 if the lead is not qualified (1 % 2 = 1). It will return 0 is the lead is qualified (2 % 2 = 0).
The LISTTEMP identifier refers to the physical name of the temporary table used for the list. The INFO_VALUE_INACTIVE identifier represents the value that the InfoValue field should be set to when the lead has not been qualified.
The second condition examined will find whether the lead has a record-level note attached. If a note is attached, the InfoValue column is set to the “note attached” state. The following is the basic SQL statement used to find whether a note exists for the current lead. If a record-level note exists, the InfoValue field will be set to indicate that state:
set InfoValue = (INFO_VALUE_NOTE * (select count(*) from NOTETABLE where NOTETABLE.NOTEINDX = LISTTEMP.NOTEINDX))
The select statement counts the number of rows in the note master table that have the same note index as the current row in the list’s temporary table. If a note is attached for the current row, the value 1 will be returned. Otherwise, the value 0 will be returned.
The LISTTEMP identifier refers to the physical name of the temporary table used for the list. The NOTETABLE identifier refers to the physical name of the SY_Record_Notes_MSTR table. The INFO_VALUE_NOTE identifier represents the value that the InfoValue field should be set to when the lead has a note attached.
The following is the complete Refresh procedure for the Leads list. Notice how it loads the temporary table for the Leads list, and then uses pass-through SQL to set the InfoValue field for each row of the list. The InfoValue_SetState() function is used to retrieve the values used for the InfoValue field.
inout ListObjState list_object;
in integer nRefreshType;
in string sSortColFieldName;
in integer nSortOrder;
optional in string sBackToID;
local boolean fReloadData, fResetRange, fGetNewIndex;
local text SQLCommand;
local long SQLConnection;
local long SQLStatus;
local string ListTempTable;
local string InfoValueField;
local string QualifiedField;
local string NotesTable;
local integer InfoValueInactive;
local integer InfoValueNote;
default window to State;
pragma(disable warning LiteralStringUsed);
case nRefreshType
in [REFRESHTYPE_CHANGESORT]
fReloadData = true;
fResetRange = true;
fGetNewIndex = true;
in [REFRESHTYPE_RELOAD]
fReloadData = true;
fResetRange = true;
fGetNewIndex = true;
in [REFRESHTYPE_FIND]
fReloadData = true;
fResetRange = true;
fGetNewIndex = false;
in [REFRESHTYPE_RESTRICT]
fReloadData = true;
fResetRange = true;
fGetNewIndex = false;
else {Refresh type is invalid, so do nothing}
fReloadData = false;
fResetRange = false;
fGetNewIndex = false;
end case;
{Change the key if necessary}
if fGetNewIndex then
call GetSortIndex of form ListObj_Leads, sSortColFieldName, nSortOrder, list_object;
'Field Name' = sSortColFieldName;
ColSortOrder = nSortOrder;
end if;
field(list_object:RecCountStringRef) = getmsg(5965); {Processing...}
if fReloadData then
{Load the temporary table with data}
Table_Truncate(table(list_object:'Table Reference'));
{Set the range on the source table}
range clear table IG_Leads_MSTR;
clear field 'Lead ID' of table IG_Leads_MSTR;
range start table IG_Leads_MSTR by number 1;
fill field 'Lead ID' of table IG_Leads_MSTR;
range end table IG_Leads_MSTR by number 1;
{Copy the records to the temporary table}
range copy table IG_Leads_MSTR to table(list_object:'Table Reference') by number 1;
{Be sure the buffer for the referenced table is cleared}
clear table(list_object:'Table Reference');
{Pass-through SQL used to set the InfoValue column}
{Define the values to which the InfoValue column must be set for rows that
meet the criteria}
InfoValue_SetState(InfoValueInactive, INFOVALUE_CUSTOM1) of form syListObj;
InfoValue_SetState(InfoValueNote, INFOVALUE_NOTEATTACHED) of form syListObj;
{Get the SQL connection to use}
SQLStatus = SQL_GetConnection(FOREGROUND, SQLConnection) of form XSQLExec;
{Get the names of the resources to use in the SQL statements}
ListTempTable = Table_GetOSName(table(list_object:'Table Reference'));
InfoValueField = physicalname(InfoValue of table IG_Leads_List_TEMP);
QualifiedField = physicalname('Qualified Lead' of table IG_Leads_List_TEMP);
NotesTable = Table_GetOSName(table SY_Record_Notes_MSTR);
{Build the SQL statement to execute}
SQLCommand = "update " + ListTempTable
+ " set " + InfoValueField
+ " = (" + str(InfoValueInactive) + " * ((" + ListTempTable + "."
+ QualifiedField + ") % 2)) "
+ " + (" + str(InfoValueNote) + " * (select count(*) from "
+ NotesTable + " where "
+ NotesTable + ".NOTEINDX = " + ListTempTable + ".NOTEINDX)" +
")";
{ Clear the Connection }
SQLStatus = SQL_Clear(SQLConnection);
{ Submit the command to SQL Server.}
SQLStatus = SQL_Execute(SQLConnection, SQLCommand);
if SQLStatus <> 0 then
warning "An unknown SQL error occurred updating data for the list";
SQLStatus = SQL_Clear(SQLConnection);
abort script;
end if;
end if;
if fResetRange then
{Reset the range for the table}
call SetRange of form ListObj_Leads, list_object;
end if;
if not empty(sBackToID) then
set 'Lead ID' of table(list_object:'Table Reference') to sBackToID;
get table(list_object:'Table Reference');
end if;
pragma(enable warning LiteralStringUsed);