Nav view search

Navigation

Search

The Exchange

The place to download pre-configured tickets, code snippets, and solutions

Back

Job Search Tool

This toolbox snippet provides a complete Job Search tool that is built on a Home Page form.

Note: This solution is optimized for Virtual Ticket Developer.

If you are using Workgroups DaVinci and are planning to use a Toolbox snippet not marked as DaVinci-compatible, please check with Support for possible compatibility issues before using.

Concepts and elements used in this tool can be easily transfered to other areas of Virtual Ticket. Included in this snippet are examples of Server Side scripting, grid column sorting, setting control colors and visibility, and sql query.
The query included in the attahed snippet is a good starting point for create a query more specific to other VT environments. The form developer can add or remove reference to additional database fields.
For better perfomance, the SQL query is run on the Server Side scripting and results are then return to set the editable grid data. Here is the complete Server SIde script:

//@include "database:grid_and_model.gsd"  
//@include "database:PS.js"  
 
function GetJobsData(filter){  
 
// split the filter string into an array  
    var pcs = filter.split(" ");  
 
// create a string variable of the query  
    var sql = "SELECT j.[Job Number] AS Job_Number, j.[Job Name] AS Job_Name, j.[Customer Name] AS Customer, j.Job_Type, u.last_name AS Sales, j.[Entry Date] AS Entry_Date, j.[Due Date] AS Due_Date \  
               FROM [view Jobs] AS j (nolock) \  
               LEFT JOIN users AS u (nolock) ON j.Sales_Rep_Emp_Number = u.employee_number WHERE 1=1 ";  
 
// if an array item exists use it to query the specified data fields (Customer Name, Job number, Job Name, Job Type, Sales person's last name)  
    sql += !PS.Empty(pcs[0]) && pcs[0].length > 2 ? "AND (j.[Customer Name] LIKE '%"+PS.ReplaceChar(pcs[0])+"%' OR CAST(j.[Job Number] AS VARCHAR(15)) LIKE '%"+PS.ReplaceChar(pcs[0])+"%' OR j.[Job Name] LIKE '%"+PS.ReplaceChar(pcs[0])+"%' OR j.[Job_Type] LIKE '%"+PS.ReplaceChar(pcs[0])+"%' OR u.last_name LIKE '%"+PS.ReplaceChar(pcs[0])+"%') " : "";  
    sql += !PS.Empty(pcs[1]) && pcs[1].length > 2 ? "AND (j.[Customer Name] LIKE '%"+PS.ReplaceChar(pcs[1])+"%' OR CAST(j.[Job Number] AS VARCHAR(15)) LIKE '%"+PS.ReplaceChar(pcs[1])+"%' OR j.[Job Name] LIKE '%"+PS.ReplaceChar(pcs[1])+"%' OR j.[Job_Type] LIKE '%"+PS.ReplaceChar(pcs[1])+"%' OR u.last_name LIKE '%"+PS.ReplaceChar(pcs[1])+"%') " : "";  
    sql += !PS.Empty(pcs[2]) && pcs[2].length > 2 ? "AND (j.[Customer Name] LIKE '%"+PS.ReplaceChar(pcs[2])+"%' OR CAST(j.[Job Number] AS VARCHAR(15)) LIKE '%"+PS.ReplaceChar(pcs[2])+"%' OR j.[Job Name] LIKE '%"+PS.ReplaceChar(pcs[2])+"%' OR j.[Job_Type] LIKE '%"+PS.ReplaceChar(pcs[2])+"%' OR u.last_name LIKE '%"+PS.ReplaceChar(pcs[2])+"%') " : "";  
    sql += !PS.Empty(pcs[3]) && pcs[3].length > 2 ? "AND (j.[Customer Name] LIKE '%"+PS.ReplaceChar(pcs[3])+"%' OR CAST(j.[Job Number] AS VARCHAR(15)) LIKE '%"+PS.ReplaceChar(pcs[3])+"%' OR j.[Job Name] LIKE '%"+PS.ReplaceChar(pcs[3])+"%' OR j.[Job_Type] LIKE '%"+PS.ReplaceChar(pcs[3])+"%' OR u.last_name LIKE '%"+PS.ReplaceChar(pcs[3])+"%') " : "";  
    sql += !PS.Empty(pcs[4]) && pcs[4].length > 2 ? "AND (j.[Customer Name] LIKE '%"+PS.ReplaceChar(pcs[4])+"%' OR CAST(j.[Job Number] AS VARCHAR(15)) LIKE '%"+PS.ReplaceChar(pcs[4])+"%' OR j.[Job Name] LIKE '%"+PS.ReplaceChar(pcs[4])+"%' OR j.[Job_Type] LIKE '%"+PS.ReplaceChar(pcs[4])+"%' OR u.last_name LIKE '%"+PS.ReplaceChar(pcs[4])+"%') " : "";  
// five filters are provided, more can be added.  
 
// sort the results by the Job Number  
    sql +=    "ORDER BY Job_Number";  
 
// run the query with the string created  
    var q = RunSQLQuery(sql);  
 
// return the query results  
    return q;  
} 

The attached library file includes the following:

  • Job Search form
  • PS.JS
  • grid_and_model.gsd
  • Widget_Grid_Sort.js

This Snippet requires the following to install and use:


Last Modified:

2011-10-05

by

Cliff Lewis