Setting Up Filters

A filter selects a group of contact records that share one or more identical characteristics. The term filter is used because it sifts for items that are similar and separates them from items that are different.

Filters let you separate a group of contact records, based on common criteria of your choice, from of all contact records in the database. An active filter also changes the sequence and view so that you can move forward (F) and backward (B) through the filtered group of records only.

When you use a filter, you can select data using any field criteria from a contact record or an activity. For example, you might want to select only those records for people or businesses located in California. In this case, you would run a filter that selects records with CA in the state field.

Perhaps you have a database containing the names and addresses of doctors and lawyers, and you have differentiated these records using DOC and LAW in a field called Profession. If you want to select all doctors and not work with the lawyers, you would use a filter that selects records with DOC in the Profession field. You could narrow the range of records further if you want to work with records containing information on doctors in California by creating a filter that selects records with CA in the State field and DOC in the Profession field. You can use only one filter at a time. You can design as many filters as you want and save them to use again in the future.

It is very easy to create filters. The key to an effective filter is knowing your data. The filter is merely looking at the data and displaying contact records based on the criteria you have chosen. If you type lower case data as a value and the data is always in upper case you may not get the results the you expect when you run your filter. There are two ways of creating new filters:

Key:	Options:	What happens:
O	Other options	Other Options pop-up menu appears
F	run a Filter	Run-a Filter pop-up prompt window appear
F4	for List	Select/Maintain Filters screen will appear

Key:	Options:	What happens:
F8	Easy Build	This function key is a toggle between Easy Build and Expert Build

The words Easy Build appear at the top righthand of the Select/Maintain Filters screen. When you use F8 the word next to Easy Build changes from Yes to No and the F8 description changes from Expert Build to Easy Build. A Yes for Easy Build will mean that the F8 description is Expert Build and a No for Easy Build will mean that the F8 description is Easy Build. Starting with the Easy Build at Yes the next step is the same for either method. They are::

Key:	Options:	What happens:
F6	Add New Filter	The Filter Header screen will appear, once the Filter is finished it will be saved on the Select/Maintain Filters list so that you can run it whenever you like.
OR
F7	Add then Run	The Filter Header screen will appear, once the Filter is finished it will be saved on the Select/Maintain Fitters list and it will also be run immediately.

Filter Header Screen: This screen is used to Add a New Filter, if the filter is Easy Build you will be restricted to only two prompts, Filter ID and Description. You will have NO choice for any of the other values.:

Filter Header
Filter ID:	_____________
Description:	_____________________________________
Files Used:	___	1=Contact, 2=Activity, 3=Contact/Activity 4=Contact/Contact, 5=Activity/Activity
Focus File:	___	1=Contact, 2=Activity
Filter Type:	___	D=Dynamic, S=Static, U=Unique
Filter Owner:	________
Security Class:	___	U=Unsecured, S=Secured	Last Used
			Date
			Time
			User
			Count

Filter Header Labels:	What data is used in this Label:
Filter ID:	10 character field where you name the filter giving it a Filter ID for the F4 list. Many customers choose to use the dept name or the initials of the person who will be using the Filter as the first part of the ID, example SLS.Ter for Sales Territory or SJK.Ter to identify a particular persons territory.
Description:	32 character field where you describe the purpose of the Filter, often a good idea to use your dept name at the end of the description if not used in the Filter ID.
Files Used:	5 choices, Contact, Activity, Contact/Activity, Contact/Contact, and Activity/Activity. These choices are explained in greater detail below.
Focus File:	2 choices, Contact and Activity. These choices are explained in greater detail below.
Filter Type:	3 choices, Dynamic, Static, and Unique. These choices are explained in greater detail below.
Filter Owner:	Default is the userID of the person adding the Filter but can be changed to any userID. Many customers will change the Filter Owner to the userID of the person that will use the Filter the most. This field is used by security if it is active to restrict use of filters by owner.
Security Class:	2 choices, Unsecured and Secured. These choices are explained in greater detail below.

Last Used Date: Time: User: Count:	System fields that are updated automatically each time the filter is used. Last date filter was used Last time filter was used UserID of the person using the filter last How many times the filter has been used since creation

File Used: Indicates the File or combination of Files joined in the process of creating a Filter. See related topic for explanation of a data layout

Details on adding Easy Build and Expert Build Filters are found in these related topics:

The filter values and compare types are the same between the two methods of adding filters. The restrictions and limitations of Easy Build Filters means that you cannot use some of the values and compare types. Should you have a need for them you would have to use Expert Build Filter to add or maintain your filter.

Value:	Filter Type:	What happens:
and/or	Expert Build	Using the And and Or values are logical conditions. You use AND if the record must meet all conditions you specify. This means the filter searches for records that meet the condition specified on the first line AND any subsequent lines with AND's. Use OR if the filter must meet one of several conditions. This means the filter searches for contact records that meet the condition specified until another OR line occurs.
include/if	Easy Build & Expert Build	Defines which filed is the subject of the test described on the line. Multiple files are available in Expert Build filters only. Easy Build filters are limited to searches on the Contact file (CUST file).
position/length	Expert Build	Used for substring searches of data. Enter the starting position number of the character for the exact length of characters in the field to be searches. Example, a zip code field typically is 10 characters long. If you wanted all the zip codes containing 943 in the 1st 3 positions you would key 1 in position value and 3 in the length value. The search would ignore all data starting in position 4 for the length of the field and concentrate only on the 1st 3 characters in the data field, or 943.
upper case	Expert Build	Used to denote case sensitivity of data being searched. The best way to create an effective filter is to know your data. If the data is always upper case in that field you would key the data in the compare character prompt as UPPER case as well. If that is done properly you will not need to use this prompt at all.
compare type	Easy Build & Expert Build	Explained below.
compare number, or "characters" or &fieldName	Easy Build & Expert Build	Used for data searches. Key data in this prompt. Compare number only means that the search is going to be on data in a numerical field. Compare characters refers to alpha-numeric field data. &fieldName compares one field to another. See More Filter Examples
or %V (dept) or %T (date) or %U (user id)	Expert Build	%V uses the data found in the dept prompt in the userID setup for the search. %T used the system date as data for the search. %U uses the userID of the person currently signed on.

Compare Type:	Value:	What happens:
EQ	equal	searches for all exact matches to selected data typed
GT	greater than	searches for selected data that is greater than the data typed
GE	greater or equal	searches for selected data that is greater to or equal than the data typed
%W	wildcard	searches for character strings, using *'s for beginning & end of string
%L	list	searches on a list of data typed
NE	not equal	searches for selected data that is not equal to the data typed
LT	less than	searches for selected data that is less than the data typed
LE	less or equal	searches for selected data this is less than or equal to the data typed
CT	contains	searches for selected data by sorting through a sub-string of data typed
%R	range	searches for selected data by range for the data typed

Filter Wildcard Compare Types

The Wildcard compare type (%W) in an ActionWare filter is used to specify a pattern that performs a wildcard scan of a character field.

It can be used to find multiple characters, or strings of characters, in a field. It is similar to the 'contains' function, except that with 'contains' you can only search for a single string of characters within a field. Wildcard is much more versatile.

Compare type:	Value:	What happens:
%W	Wild Card	This compare type is used when the data is to be searched according to wild card rules
%L	List	This compare type is used on a new line following a normal line containing any of the standard compare types and the line has no entries other than the compare data
%R	Range	This compare type can appear on a new line following a normal line containing any of the standard compare types and the line has no entries other than the compare data. It indicates this line represents the high end of the range. The low end of the range is the compare data specified on the previous line containing the standard compare type. One line containing this compare type is allowed after a normal line.

In this filter we want all contact records where the CIT field contains 'N', followed by any character and an 'w', appearing anywhere in the field. The City field ID is CIT. The results are any City with New in it's name, New York, New Hudson, Newport Beach, etc. Note that an underscore must be used to indicate the character between N and w (N_w).

Filter Maintenance
and /or	include if field	position /length		upper case	compare type	compare number, or "characters" or &fieldName	or %V (dept) or %T (date) or %U (user id)
	CIT	___	____	__	%W	*"N_w"______________________*
____	______	___	____	__	____	______________________________
____	______	____	____	__	____	______________________________
____	______	____	____	__	____	______________________________

In this filter we want all contact records for the States of California, Colorado and Connecticut. The State field ID is STA.

Filter Maintenance
and /or	include if field	position /length		upper case	compare type	compare number, or "characters" or &fieldName	or %V (dept) or %T (date) or %U (user id)
	STA	___	____	__	EQ	"CA"_________________________
____	______	___	____	__	%L	"CO"_________________________
____	______	____	____	__	%L	"CT"_________________________
____	______	____	____	__	____	______________________________

In this filter we want all contact records with the zip codes with a range from 94111 to 94199. The Zip code field ID is ZIP.

Filter Maintenance
and /or	include if field	position /length		upper case	compare type	compare number, or "characters" or &fieldName	or %V (dept) or %T (date) or %U (user id)
	ZIP	___	____	__	EQ	"94111"_____________________
____	______	___	____	__	%R	"94199"_____________________
____	______	____	____	__	____	______________________________
____	______	____	____	__	____	______________________________

Advanced Filters

There are different combinations of files that maybe used for filters. Both Easy Build and Expert Build can create filters using the file called Contact. Only Expert Build can be used to search on these files:

All filters search on data in the file and display corresponding contact records based on the criteria chosen. Contact file filters use data fields and Activity file filters use activity data fields. Let's look at how and why you might use the other files for filters

Activity filters let you search for data in activity data fields found on Panel 7, the activity panel. The fields used on the activity panels are ActionWare programmed fields which means that they have the same field ID's, always. Contact (CUST file) fields ID's can vary greatly from application to application because of the program ability to create your own contact data fields. Contact data field ID's are always 3 characters long. Activity field ID's vary in length. Some of the most used fields in filter searches are:

Activity Field ID:

Value:

TRPID

Activity field for PID data

TRSID

Activity field for SID data

TRDAT

Activity field for activity date data

TRTU

Activity field for target user data

TRMET

Activity field for activity method data

TRDIR

Activity field for activity direction data

TRTYP

Activity field for activity type data

TRSTAT

Activity field for activity status data

There are many more field ID's in the activity file. These are just the most chosen. When using the Activity file in your filter you may use F5 to display all activity field ID's.

The Filter Header for Activity filters looks like this:

Filter Header

Filter ID:

_____________

Description:

_____________________________________

Files Used:

2

1=Contact, 2=Activity, 3=Contact/Activity 4=Contact/Contact, 5=Activity/Activity

Focus File:

2

1=Contact, 2=Activity

Filter Type:

U

D=Dynamic, S=Static, U=Unique

Filter Owner:

________

Security Class:

___

U=Unsecured, S=Secured

Last Used

Date

Time

User

Count

Other than the Filter Header using a different value for file, focus and type - the Filter Maintenance screen looks the same. An example of a filter using the Activity file would be the need to display all contact records with pending activities for a specific PID. We are using a PID of ACTION in this example. The filter would look like this:

Filter Maintenance
and /or	include if field	position /length		upper case	compare type	compare number, or "characters" or &fieldName	or %V (dept) or %T (date) or %U (user id)
	TRPID	____	____	__	EQ	"ACTION"_______________________
AND	TRSTAT	____	____	__	EQ	"P"_____________________________
____	______	____	____	__	____	________________________________
____	______	____	____	__	____	________________________________

The result of this search would be an active filter display of each contact record with a PID of ACTION that has an pending activity. The resulting contact records would be displayed with the focus on the activity panel and the pending activity would be highlighted on each contact record.

The Contact/Activity filter combines a search of data fields and activity data fields.

The Filter Header for Contact/Activity filters looks like this:

Filter Header

Filter ID:

_____________

Description:

_____________________________________

Files Used:

3

1=Contact, 2=Activity, 3=Contact/Activity 4=Contact/Contact, 5=Activity/Activity

Focus File:

2

1=Contact, 2=Activity

Filter Type:

U

D=Dynamic, S=Static, U=Unique

Filter Owner:

________

Security Class:

___

U=Unsecured, S=Secured

Last Used

Date

Time

User

Count

Other than the Filter Header using a different value for file, focus and type - the Filter Maintenance screen looks the same. An example of a filter using the Contact/Activity file would be the need to display all contact records in the City of San Francisco with pending activities. The City field ID is CIT and the activity status field ID is TRSTAT. The filter would look like this:

Filter Maintenance
and /or	include if field	position /length		upper case	compare type	compare number, or "characters" or &fieldName	or %V (dept) or %T (date) or %U (user id)
	CIT	____	____	__	EQ	"San Francisco"_________________
AND	TRSTAT	____	____	__	EQ	"P"_____________________________
____	______	____	____	__	____	________________________________
____	______	____	____	__	____	________________________________

The result of this search would be an active filter display of each contact record in the city of San Francisco with any activities pending. These filtered contact records would be displayed with the focus on the activity panel and the pending activity would be highlighted on each contact record.

Contact/Contact filters are useful because they let you filter for criteria stored in two different data layouts in the database. The result is a group of records that meet the criteria of both data layout selections. When creating a Contact/Contact filter you need to decide WHICH group of records you want to end up with. C1 and C2 are the database ID's (DB/id) that are used in Contact/Contact filters. C1 is the group that is searched first and C2 is used for the contact records you want to end up with. This type of filter can also be very useful to compare data with a resulting display of contact records meeting your criteria.

The Filter Header for Contact/Contact filters looks like this:

Filter Header

Filter ID:

_____________

Description:

_____________________________________

Files Used:

4

1=Contact, 2=Activity, 3=Contact/Activity 4=Contact/Contact, 5=Activity/Activity

Focus File:

1

1=Contact, 2=Activity

Filter Type:

___

D=Dynamic, S=Static, U=Unique

Filter Owner:

________

Security Class:

___

U=Unsecured, S=Secured

Last Used

Date

Time

User

Count

When you finish filling out the Filter Header Screen and press enter. The DB/id column appears to the right of the and/or column. It is in the DB/id column that you designate C1 or C2 for the filtering.

An example of a filter using the Contact/Contact file would be the need to display all contact records with a particular product interest who are are already customers. The field that designates product interest is on data layout 3 in a one character field ID of PRD. The field that designates a customer is a one character field ID of CUS on data layout 1. The data in these fields is the same, it is either Y (Yes) or N (No). These fields are made up field IDs for this example only. You would need to determine what if any field ID is used to identify product interest or customer data. We want to end up with all contact records who have product interest Y in field PRD on data layout 3 and have customer Y on data layout 1. The filter would look like this:

Filter Maintenance
and /or	DB Id	include if field	position /length		upper case	compare type	compare number, or "characters" or &fieldName	or %V (dept) or %T (date) or %U (user id)
	C1	PRD	____	____	__	EQ	"Y"_________________________
AND	C2	CUS	____	____	__	EQ	"Y"_________________________
_____	__	______	____	____	__	____	___________________________
_____	__	______	____	____	__	____	____________________________

The result of this search would be a display in the active filter of each contact record with Y (Yes) in PRD (product interest field who has a Y (Yes) in the CUS (customer) field. The filter displays contact records in data layout 1 because we used C2. Effectively the program build a filter of all contact records with Y (Yes) for product interest in data layout 3. It than used that group of contact records for a second filter to display all customers represented by a Y (Yes) in the CUS field on data layout 1.

Activity/Activity filters let you compare data in activity fields to produce a single result. The result is a group of records that meet the criteria of both selections. When creating a Activity/Activity filter you need to decide WHICH group of records you want to end up with. A1 and A2 are the database ID's (DB/id) that are used in Activity/Activity filters. A1 is the group that is searched first and A2 is used for the contact records you want to end up with. This type of filter can also be very useful to compare data with a resulting display of contact records meeting your criteria.

The Filter Header for Activity/Activity filters looks like this:

Filter Header

Filter ID:

_____________

Description:

_____________________________________

Files Used:

5

1=Contact, 2=Activity, 3=Contact/Activity 4=Contact/Contact, 5=Activity/Activity

Focus File:

2

1=Contact, 2=Activity

Filter Type:

U

D=Dynamic, S=Static, U=Unique

Filter Owner:

________

Security Class:

___

U=Unsecured, S=Secured

Last Used

Date

Time

User

Count

When you finish filling out the Filter Header Screen and press enter. The DB/id column appears to the right of the and/or column. It is in the DB/id column that you designate A1 and A2 for filtering. An example of a filter using the Activity/Activity file would be the need to display all contact records that have completed HDOC (hard document or letter) activities with pending TELE (telephone call) activities.

Filter Maintenance
and /or	DB Id	include if field	position /length		upper case	compare type	compare number, or "characters" or &fieldName	or %V (dept) or %T (date) or %U (user id)
	A1	TRMET	____	____	__	EQ	"H"________________________
AND	A1	TRSTAT	____	____	__	EQ	"C"________________________
AND	A2	TRMET	____	____	__	EQ	"T"________________________
AND	A2	TRSTAT	____	____	__	EQ	"P"________________________

The result of this search would be an active filter display of all contact records that had pending telephone activities using first a search of every contact record with completed hard document activities.

Filter Options

The Other Options Menu is available when a filter is active. To access press O (Other) O (filter Options)

Filter Options Selection
Filter Id: ________________________	Filter
Description: _____________________	Status
Select one of the following:	Active

1. Display List of Filtered Records	D
2. Reports Menu	R
3. Mass Mailing Options	M
4. Overlay Control (filter will become active)	O
5. Return with Filter Active	F

Selection: __

F3=Exit F11=Select New Filter F12=Cancel

The filter options use the active filter for it's source to perform the functions as follows:

Filter Options:	Values:	What happens:
Display List of Filtered Records	1 or D	Displays a list of the current filtered contact records in alphabetical order. Same function as F4
Reports Menu	2 or R	Displays Reports Menu. Same function as O (Other options) R (Reports menu)
Mass Mailing Options	3 or M	Displays Mass Mailing prompt
Overlay Control (filter will become active)	4 or O	Displays Overlay Control prompt
Return with Filter Active	5 or F	Displays current active filter starting at the first contact record in the filter

Reports Menu: These initial reports are shipped with the product and the first 3 require active filters to run:

Query refers to the use of the IBM Query for iSeries (formerly Query/400) product. Your System Administrator can add your custom query reports to this menu.

Reports 1 through 3 are based on an active filter and will print reports as follows:

Report:	What happens:
Abbreviated Contact List	Prints a report with Company name, Contact name and phone number information by filtered contact record
Name and Address Detail	Prints a report with full Company name, Contact name, address and phone number information by filtered contact record
Full Contact Detail	Prints a report with all data field information by filtered contact record

Mass Mailing Options: As System Administrator you will need to setup our Microsoft Word Interface (MSWI) in order for you to use any wordprocessing options such as mass mailing. This option also uses the active filter for it's source to perform create new documents from shells or templates to produce documents per the processing method chosen.

Mass Mailing Options:	Values:	What happens:
Shell Document name . . .	12 character prompt for existing shell document name. You can use F4 for a list of available shell documents	Using the filtered group of contact records and the shell (template) document typed in this prompt, the programs will create a letter based on that shell for each contact record.
Filter ID to use. . . . .	10 character prompt for filter name (filter ID). You can use F4 for a list of available filters.	A filter must be active to use mass mailing. The active filter ID defaults to this prompt.
Sort letters by ZIP Code?	1 character field with N (No) or Y (Yes) as the only options	Your choice, if N the resulting individual letters will not be sorted by zip code. If Y than they will be sorted in zip code file for printing.
Processing Method . . . .	1 character prompt with these options only: 1=only print letters, no activities 2=create activities based on mailing 3=prep docs, no print	Your choice, 1 will print letters without automatic activities, 2 will print letters and also update each contact record with an automatic activity HDOC O. The activities are updated with the document description name and also automatically completed., 3 documents are created for display and will not print.

Overlay Control: Your System Administrator must authorize you to use this function. Great care must be taken in using overlay because, depending on your choices, it changes selected data on every contact record in your filter. If you don't know what you are doing than you could change data incorrectly and not be able to retrieve it. There is NO undo with overlay.

Overlay Control
Key data and press Enter when ready
(Overlay may only be performed on Contact Fields)
Field to be changed: ___	Starting in character position: 1
	Case translation (A/U/L/M): A
SOURCE OF DATA
As keyed->: ______________________________________________________________
--OR-- (: forces a space)
From field: ___	Start position: 1
	Character count: ___
F4=prompt F12=cancel	Blank after (Y/N): N
(Once you Press Enter you will be prompted for a confirmation code:)
CONFIRMATION CODE ENTRY
CONFIRMATION CODE ENTRY _____	F12=cancel
(Once you Press Enter after keying the confirmation code following is displayed)
OVERLAY HAS COMPLETED SUCCESSFULLY
Counts: XX records changed
0 records NOT changed	F8=another overlay F12=cancel

ONE: Changing the data in the field to be changed to whatever is keyed in the as keyed prompt

Overlay prompts:	What happens:
Field to be changed:	The prompt for the 3 character field ID to be changed
Starting in character position: 1	Associated with the Field to be changed prompt. A 3 character prompt intended to be used as a numeric starting point for the data change. Example: If you are placing an X at the end of the field labeled Tertiary ID (RCL is the actual field ID) you would want the data to be inserted on a starting position of 15 because that is the last character in that field. Default is 1 for the 1st character in that field.
Case translation (A/U/L/M): A	Associated with the Field to be changed prompt. Values are Automatic, Upper case, Lower case or Mixed Case. This prompt is used if you want to force the data being change to a particular case. Default is A for automatic.
From field:
Start position: 1	Associated with the From field prompt. A 3 character prompt intended to be used as a numeric starting point for the data in the From field to be copied into the Field to be changed. Example: If the data in the From field is the word Pacific and you want to only move the 1st two letters of that word to the Field to be changed, you would keep the default of 1.
Character count: ___	Associated with the From field prompt. A 3 character prompt intended to be used as a numeric ending point for the data in the From field to be copied into the Field to be changed. In our previous example, you would use 2 (for a character count of 2) to get only the first two characters of PA. Default is blank.
Blank after (Y/N): N	Associated with the From field prompt. Values are Y (Yes) and N (No) only. Use Y (Yes) if you want a blanks after the data in the From field to be copied into the Field to be changed. The Default is No or N.

Confirmation Code: This code is a combination of the current day and hour entered in DDHH format. It is derived from the date and time displayed in the upper righthand corner of the Overlay screen.

If the date was 09/12/XX and the time was 09:33 than the confirmation code would be 1209 This example of the confirmation code is composed of 12, which is the day of the month (DD), and 09 which is the hour (HH) of the time.

Activity Field ID:	Value:
TRPID	Activity field for PID data
TRSID	Activity field for SID data
TRDAT	Activity field for activity date data
TRTU	Activity field for target user data
TRMET	Activity field for activity method data
TRDIR	Activity field for activity direction data
TRTYP	Activity field for activity type data
TRSTAT	Activity field for activity status data
There are many more field ID's in the activity file. These are just the most chosen. When using the Activity file in your filter you may use F5 to display all activity field ID's.