SQL’s can be run from the SAM Pro range filter screens to effectively filter on columns that do not have separate range fields. If you are not comfortable using SQL statements, contact your support representative for help.
Technician Viewer Example:
In the Technician record, the Technician Email address does not have a Range filter, nor is it displayed on any of the list screens. To provide an easy look-up by the Tech's email:
1. Add the following statement to one of the range options (you can use any range field in the viewer – doesn’t matter which one!):
sql= tchncn_eml in (select tchncn_eml from tchncn where tchncn_eml like '%')
2. Create a "SAVED RANGE" called *Find Tech by Email Address (* allows all users to run)
3. To use, the % could be replaced by the entire email address (tech@mycompany.com), the first few characters followed by the percent sign (tec%) or the percent sign followed by the domain (%mycompany.com). The last example would be useful for subcontractor technicians.
Job Viewer Example:
For example, on the Job Viewer, we have no filters on Tax Group and/or Tax status but support personnel and advanced users can:
1. Add these statements to the range options (you can use any range field in the viewer – doesn’t matter which one!)
sql=jb.slstxgrp_rn in (select slstxgrp_rn from slstxgrp where slstxgrp_id like 'CLEVELAND%')
sql=jb_tx_stts='use'
2. Create a "SAVED RANGE" called *Find tax group by tax status (* allows all users to run)
3. Then.... run the Saved Range and SAVE AS: .CSV (simply click on filename to see data in Excel, if registry setup is implemented). Using the list views, these are great/simple ways to export data (Inventory, Clients, Invoices, etc) off into Excel (without having to use ODBC).
Employee Viewer Example:
The Employee viewer does not include a Type range. To ‘create’ one:
1. Create a "SAVED RANGE" called *Find employee by employee type (* allows all users to run)
2. Add one of the following statements to the range options (you can use any range field in the viewer – doesn’t matter which one!)
sql=emplye.emplyetype_rn in (select emplyetype_rn from emplyetype where emplyetype_id in ('id1','id2','id3','etc'))
sql=emplye.emplyetype_rn in (select emplyetype_rn from emplyetype where emplyetype_id = 'theId')
sql=emplye.emplyetype_rn in (select emplyetype_rn from emplyetype where emplyetype_id like 'whatever%')
Client Site Equipment / Component Serial Numbers Example
The following allows the user to search for a serial number in the Client Site Equipment’s Component tab.
sql=clntsteeqpmnt.clntsteeqpmnt_rn in (select distinct clntsteeqpmntcmpnnt.clntsteeqpmnt_rn from clntsteeqpmntcmpnnt where clntsteeqpmntcmpnnt_srl_nmbr like 'xyz%')
Work Order Service Zone Filter
The following allows the user to search for work orders by Service Zone.
1. Create a "SAVED RANGE" called *Filter Work Orders by Service Zone (* allows all users to run)
2. Add the following statement to one of the range options (you can use any range field in the viewer – doesn’t matter which one!)
sql=wrkordr.srvcezne_rn in (select srvcezne_rn from srvcezne where srvcezne_id like 'EnterZoneId')
3. When the user selects the saved range, replace EnterZoneId with the actual Service Zone Id they wish to filter on, and then click the list button.