The Joy of Sets

THE JOY OF SETS

Can you remember which records you want to publish in a particular publication? Is it all the records in one table? Just the companies in North America? Only the products on special offer? Only the ones that are in the "Over 50s" category? A selection that you chose manually? Use the Set Manager to create and maintain any number of sets of data!

There are two types of Set:

Dynamic Sets: You tell the Set Manager which table to draw data from and how to determine which records to include in the Set. The set is dynamically maintained by CatBase.

Ad-Hoc Sets: You choose some specific records and create an ad-hoc set. The selection of records included in the set does not change unless you manually add to or delete records from it.

Using the Set Manager (Dynamic Sets)

With the Set Manager, you tell CatBase which criteria to use to maintain the set.

Select the table that you want to create a set for.
Expand the Sets topic in the left-hand menu
Click on New Set

A new Set looks like this:

Set Name

Enter a name for your Set. Set names must be unique within a Database Project.

Table

Name of the table that the Set will draw its data from.

Create the set using

There are two choices here:

All records in the table: All records in the table will be included in the set.

Query the table: You tell CatBase how to decide which records to include in the set

Include records marked for Deletion

By default, records marked for deletion are NOT included in dynamic sets. Select this option if you want to include them.

Include records marked as inactive

By default, records marked as inactive are NOT included in dynamic sets. Select this option if you want to include them.

As an example, let's create a set containing all people listed in our database who live in New York. In the Telephone Directory project in our Demo Database, we have a table called White Pages which this data will be drawn from.

Select the White Pages table.
Expand the Sets topic and click on New Set.
Enter a name for the set, for example "Everybody in New York".
Select the Query the table option from the Create the set using: popup menu. The view changes:

Click the Add Criterion button. A Query Line window opens:

The options are:

Field to query
Query operators (eg "is equal to", "contains" etc.)
Criterion entry or selection

Choose the field that you want to base the query on. The display may change, depending on the type of field you selected:

STRING OR TEXT FIELD

You'll usually enter one or more characters into the Criterion field. The Criterion field can also be left empty. For example, if you want to include all records that have nothing in the City field, you would choose the field (City) Is equal to (nothing).

SCRIPT VARIABLE

A script variable is value that can change. You can specify a Script Variable for any Publishing Project or for a Project Step within a Publishing Project. For example, you might want to be able to specify any city or state to include in a publication. You could either create a separate Set for each possible option (that could be very tedious) or - simpler - you select the Script Variable here and then, in your Publishing Project, enter the search criterion into the Script Variable field - eg "new york" or "los angeles" etc. Think of it as a dynamic criterion.

The query operators are:

Is after: for when you want to find data that comes after the criterion you specify, alphabetically. For example,Last Names that are after N.
Is before: to find data that comes before the specified letter. For example, names that come before P.
Contains: data that contains the specified text. For example, all Product Names containing "chocolate".
Is equal to: find data that exactly matches your criterion. For example, City is equal to "New York". Note: the queries are NOT case-sensitive.
Is not equal to: find data that is not the same as the criterion you specify. For example, City Is not equal to New York.
Starts with: Data that starts with the text you specify. For example, all product names that start with "cardboard"

NUMERIC FIELD

The query operators are:

Is equal to: find data having a value equal to the value you enter (can be 0)
Is greater than: Find values larger than the value you enter (can be 0)
Is greater than or equal to: find values the same as, or greater than, the value you enter
Is less than: Find values lower than the value you enter (can be 0, in which case it will find any negative values)
Is less than or equal to: find values the same as or less than the value you enter (can be 0)
Is not equal to: find values that are not the same as the value you enter

DATE FIELD

When you select a Date field, you have two options for the Criteria:

You can either enter a specific date, or click the Choose option popup and select Current Date. If you select Current Date, CatBase will use the actual date each time the set is used.

The query operators are:

Is equal to: find data that has the same date as the selected criterion. For example, you could have a dynamic set containing all records that were modified "today" by choosing Is equal to with the Current date option selected.

Is after: find data having a date later than the specified date

Is equal to or after: find data having a date the same as or later than the date you specify

Is before: find data that has a date earlier than the date you specify

Is equal to or before: find data that has a date the same as or earlier than the specified date

Is not equal to: find data that has a date that is not the same as the date you specify

BOOLEAN FIELD

The query operators are:

True
False

The Criterion field is hidden. Boolean fields can only be either True or False.

CHOICE LIST FIELD

The Query Operators will be the same as for a String or Text field, but instead of a field to enter the criterion you'll see a popup menu listing the Choice List choices.

Example: Create a Set comprising all people in New York

To find all the people living in New York, we would create a Query Line like this:

Click Save to save the query line.

You can have any number of query lines. Suppose we want to further refine our New York set to include only males.

1. Add a second Criterion

2. Select the field Title

3. Select the query operator Is equal to

4. A choice list appears, because the Title field is designated as a Choice List field. Select Mr from the choice list.

Note that a new popup choice has appeared to the left of the Field popup: the Conjunction popup. And is selected by default. The other option is Or.

Our completed Set Query looks like this:

Click the View Set Now button to check that you have created the set correctly.

CatBase updates the set and opens a new window displaying the data in the set.

Creating an "ad-hoc" set

You've just done a rather complicated query and found a set of records that you might want to revisit. Or maybe you manually selected some companies that you want to target for a marketing campaign and you need to have a way of instantly finding that same selection again.

No problem!

1. Select the records you want to include in the set (so that they are highlighted in yellow)

2. Right-click or Ctrl-click on the list of records

3. From the popup menu, choose Sets->Create an Ad-hoc set:

CatBase will ask you for a name for your new set, and then the set will be created.

Adding records to an ad-hoc set

Select one or more records that are not in the ad-hoc set
Right-click on one of the selected records
From the popup menu, choose Sets->Add to ad-hoc set->(your set name)

Displaying a Set

To view the contents of a set:

Select the table that the set belongs to
Expand the Sets topic in the left-hand menu
Select the name of the set you want to view
The records belonging to the selected Set will be displayed

You can also right-click on the data list and choose Sets->Display the set-> from the popup menu.

Editing a Set

To edit a dynamic set's criteria:

Select the table that the set belongs to
Expand the Sets topic in the left-hand menu
Right-click / Ctrl-click on the name of the Set you want to edit
Choose Edit from the pop-up menu

Deleting a Set

To delete a Set:

Select the table that the set belongs to
Expand the Sets topic in the left-hand menu
Right-click / Ctrl-click on the name of the Set you want to edit
Choose Delete from the pop-up menu

Duplicating a Set

To duplicate a Set:

Select the table that the set belongs to
Expand the Sets topic in the left-hand menu
Right-click / Ctrl-click on the name of the Set you want to edit
Choose Duplicate from the pop-up menu
The Set will be duplicated,, with "Copy" appended to the name of the duplicate

Using a Set in a Publishing Stylesheet

You can select a Set to be used in a Publishing Stylesheet. This is useful when you want to only include certain data when you are publishing with a particular Publishing Stylesheet.

Go to the Preferences tab on the Publishing Stylesheet.
Select the checkbox labelled Only publish records that are in the set:
Choose the appropriate Set form the choice list:

Using a Set in a Related Table in a Publishing Stylesheet

As well as specifying a Set for the base table in a Publishing Stlyesheet, you can also specify one for any related table. Let's say for example that you have a Companies tab le and a related Personnel table. Maybe you only want to include the Personnel who are Directors when you publish the directory. This is how you would do it:

Create a Set in the Personnel table to find the records you want to publish from that table (in this example, you'd find the ones whose Job Title contains the word "director").
In the Relation to the Personnel table in your Publishing Stylesheet, go to the Preferences tab.
There you will see a checkbox labelled Publish records only if they are in the set: and a popup list of Sets from the related table.
Select the checkbox and choose the Set you want to use.

Using a Set in a Publishing Project

Sets are very useful when setting up Publishing Projects (in fact, they are a requirement!). Please see the Publishing Projects Tutorial for details.