The Standard eLink search facilities allow the basic entity tables to be searched for user-specified values. This facility does not cover situations where a more complex search is required. For instance,
1. Where multiple tables need to be accessed
2. Where the search criteria are complex
The following worked example is based on a search for items previously purchased. The diagram below shows the desired type of search window:
In this example, there are four groups of search criteria to be satisfied:
1. Supplier (a company name)
2. Date Range
3. Search Text
4. Purchase Status
Where:
- The date range comprises a pair of dates defining when the item was purchased.
- The search text is not simply a piece of text, it is also a list of different possible fields where the search text could be located. So, the search includes a number of checkboxes defining which fields to search. The results should include all records where the specified text is present somewhere in the chosen fields.
- The purchase status is a field defining the overall status of a purchase. A series of checkboxes, allow the user to filter the results so they only include items whose purchase status matches one of the chosen status values.
Elink Implementation Issues
Implementing this facility in eLInk requires us to specify a search facility using a custom sql query; the standard search queries are too restrictive to allow this type of search to be constructed.
Having examined various forms of elink templates, the closest to the required solution is a dialog window. This template allows the following general form:
<#SQLQUERY …….>
<#DIALOGGRID…> or <#DATAGRID>
SQL Query
A complex SQL query is required as the basis of the search, for example:
<#SQLQUERY ID=”ItemSearchDescription”
SQL=”Select doc_prod.FLD269 as Name,
doc_prod.QTITY,
doc_prod.PRICE,
doc_prod.TOTAL,
proj.DATE_CREAT AS Date,
FLD294 as STATUS,
doc_prod.FLD272 as Notes,
left(doc_prod.FLD272,50) as Short_Notes,
doc.iddocument,
proj.idproject,
doc.memo as Purchase_Description,
left(cast(doc.memo as varchar(max)),50) as Short_Purchase_Description,
proj.name as Purchase_Name,
proj.memo as Journal,
left(cast(proj.memo as varchar(max)),50) as Short_Journal
from proj, proj_doc, doc_prod, doc, cy_doc
where doc_prod.iddocument = doc.iddocument
and doc.reference like ‘PO%’
and proj_doc.iddocument = doc.iddocument
and proj_doc.idproject = proj.idproject
and cy_doc.iddocument = doc.iddocument
and cy_doc.idcompany = :param1
and ( (doc_prod.FLD269 like ‘%’ + :param2 + ‘%’)
or (doc_prod.FLD272 like ‘%’ + :param3 + ‘%’)
or (proj.name like ‘%’ + :param4 + ‘%’)
or (proj.memo like ‘%’ + :param5 + ‘%’)
or (doc.memo like ‘%’ + :param6 + ‘%’)
or (doc_prod.qtity like ‘%’ + :param7 + ‘%’)
or (doc_prod.price like ‘%’ + :param8 + ‘%’)
or (doc_prod.total like ‘%’ + :param9 + ‘%’)
)
and datediff(day,proj.date_creat,convert(datetime,cast(:param10 as varchar(max)),103)) <= 0
and datediff(day,proj.date_creat,convert(datetime,cast(:param11 as varchar(max)),103)) >= 0
and Charindex(FLD294,:param12) > 0
“
PARAM1=”##PARAMETER PARAMETER=IDCOMPANY”
PARAM2=”##PARAMETER PARAMETER=PRODUCT_NAME”
PARAM3=”##PARAMETER PARAMETER=NOTES”
PARAM4=”##PARAMETER PARAMETER=PURCHASE_NAME”
PARAM5=”##PARAMETER PARAMETER=JOURNAL”
PARAM6=”##PARAMETER PARAMETER=PURCHASE_DESCRIPTION”
PARAM7=”##PARAMETER PARAMETER=QTITY”
PARAM8=”##PARAMETER PARAMETER=PRICE”
PARAM9=”##PARAMETER PARAMETER=TOTAL”
PARAM10=”##PARAMETER PARAMETER=START_DATE”
PARAM11=”##PARAMETER PARAMETER=END_DATE”
PARAM12=”##PARAMETER PARAMETER=STATUS”
>
Notes:
1. If a date range is not specified, the START_DATE parameter is left blank, and the END_DATE is set (by the caller) to today, causing the search to match all dates.
2. The textual searchs are mapped to a series of conditions, such as:
(proj.name like ‘%’ + :param4 + ‘%’)
Each condition, looks for the specified search text in one of the possible fields, each condition is given a separate template parameter. If that field is NOT to be searched, the parameter is filled by the caller with a dummy text string which is unlikely to ever match anything, causing that condition to fail. If the search text itself is blank, the corresponding parameters will be blank and the condition will PASS because
(proj.name like ‘%%’)
is always true.
3. The status field is a text field containing strings such as ‘In Progress’, ‘On Hold’, ‘Void’ etc. We want the query to return all records where the status field contains any combination of those. So, this has been inplmented by passing a STATUS parameter which contains a string which is the list of permitted conditions. eg. ‘In Progress Void’
The Charindex function looks to see if the actual status field matches some part of the status parameter. So, this approach will work as long as each possible condition contains different text.
4. The observant reader may notice that the query returns two copies of some of the fields; the complete field, and a truncated form. E.g. Notes and Short_Notes. This information is used for display purposes and will be explained later.
Query Selection
It is possible that a single SQL query may not cover all the combinations of options required for filtering. It is possible to put several SQL queries in a template and choose between them by a template parameter, passing this parameter to the DATAGRID as shown below. The value of the ITEMSEACH parameter should correspond to the name of an SQL query in the template:
<#DATAGRID TEMPLATE=”grids/GridPurchaseSearchList.htm”
SORTIMAGE=”img src=’images/but_sortascending_nor.gif’ width=16 height=16 border=0 align=top”
SORTDESCIMAGE=”img src=’images/but_sortdescending_nor.gif’ width=16 height=16 border=0 align=top”
QUERY=”##PARAMETER PARAMETER=ITEMSEARCH”
>
For instance, the above SQL query includes a condtion restricting the results to a single supplier. It would be possible to provide an alternative query which works for any suppliers, ans choose between them on invocation of the template.
Presentation of Results
The search conditions are complex, so it may be difficult for the user to grasp why a particular row meets the search criteria. In particular, the search text could appear in one or more fields. It is therefore useful to highlight the fields which contain the data which matches the search criteria. This is done by colouring the cell background White, to make it stand out.
Another difficulty is that some fields may contain a LOT of text. In particular the Description and Journal fields can get very long. If the full text is displayed, the table is very elongated. For this reason, the shortened form is displayed by default. However, a user may click on a field and cause it to be displayed in full.
Implementation
The following HTML extract shows how the notes field is implemented:
<td onclick=”show_field(‘Notes<#N>’,'Short_Notes<#n>’);”><div id=”Short_Notes<#n>”><#F=Short_Notes></div><div id=”Notes<#n>” style=”display:none”><#F=Notes></div></td>
A pair of div’s are used; one containing the short notes and the other, the complete notes. The short notes are visible, the full notes are not displayed. When the mouse button is pressed in the cell, it invokes the javascript function ‘show_field()’. This function switches the divs so that the full notes are visible, and the short notes are hidden:
function show_field(full_field,short_field)
{
// this function switches between the given full field and the
// short field so that the full field is displayed and the
// short field is hidden
// these fields are remembered
// on subsequent calls, the displayed fields will be reversed
// If the full field and short field names are null, only
// the prev fields will be changed.
var e;
if ( (prev_full_field) && (prev_short_field))
{
prev_full_field.style.display = “none”;
prev_short_field.style.display = “block”;
}
if (full_field)
{
prev_full_field = document.getElementById(full_field);
if (prev_full_field)
{
prev_short_field = document.getElementById(short_field);
if (prev_short_field)
{
prev_full_field.style.display = “block”;
prev_short_field.style.display = “none”;
}
}
}
}
The column highlighting is performed when the page loads by invoking the function highlight_field(). This invokes the function highlight_column() for each field that requires this capability:
function highlight_column(name, search_text)
{
//alert(‘highlight column ‘+name+’ ‘+search_text);
if (search_text.length < 1)
return;
var n = 1;
var e;
var found;
while (e = document.getElementById(name+n))
{
//alert(‘highlight ‘+name+n);
//found = e.innerHTML.indexOf(search_text)
//search_text = ‘/’ + search_text + ‘/i’;
var pattern = new RegExp(search_text,”i”);
found = pattern.exec(e.innerHTML);
if (found)
{
//alert(‘found’);
e.parentNode.style.background = “White”;
}
n++;
}
}
function highlight_fields()
{
// this function looks through each of the fields and
// will highlight those that match the search text
//alert(‘highlight_fields’);
highlight_column(‘NAME’,'<#PARAMETER PARAMETER=PRODUCT_NAME>’);
highlight_column(‘QTITY’,'<#PARAMETER PARAMETER=QTITY>’);
highlight_column(‘PRICE’,'<#PARAMETER PARAMETER=PRICE>’);
highlight_column(‘TOTAL’,'<#PARAMETER PARAMETER=TOTAL>’);
highlight_column(‘NOTES’,'<#PARAMETER PARAMETER=NOTES>’);
highlight_column(‘PURCHASE_NAME’,'<#PARAMETER PARAMETER=PURCHASE_NAME>’);
highlight_column(‘PURCHASE_DESCRIPTION’,'<#PARAMETER PARAMETER=PURCHASE_DESCRIPTION>’);
highlight_column(‘JOURNAL’,'<#PARAMETER PARAMETER=JOURNAL>’);
}
Technical Issues
The main difficulty with the above approach is that the search results do not appear to paginate. This implies that a large number of results may swamp the results. eLink supports a mechanism for paginating results, but I cannot see how this can be implemented in this system.
Source Code
The full source code for this example will be made available at a future date, when testing is complete and the outstanding technical issues have been resolved.
