An Overview of Selections.

What is a selection?

A selection is an extraction of a group of supporters from the InFoE database. This extraction can be used as the basis for a mass contact with FoE supporters, or may just be used for analysis and reporting. Selections are mainly made on people fields (eg member type code); supporter flags; and financial data. A selection might be:-
• a fundraising appeal mailing on Water Pollution
• a set of High Donor invitations for an event
• people who responded to the last transport mailing
• an analysis of response rates to certain appeals
• an analysis of a segment of the database (eg count of lapsed members)
• a geographical analysis (eg count of supporters in NW3)
(Each selection is uniquely identified by a selection number).

In order to understand Selections, you must understand the structure of InFoE.

• all data is contained in tables
• extracts from the database are made using queries written in SQL (structured query language)
• a selection consists of a series of cells, each of which contains one SQL query
• some cells are used only to exclude supporters
• tables must be joined to each other in order to make Selections
• views automatically join tables and exclude data - a useful shortcut.

These areas are explored in more detail in sections i to v below.

---

Top of page
An Overview | Tables | Cells, queries & SQL | Exclusion cells | Joins | Views
Some general points | Copying whole selections | Copying a single cell
Major steps - creating and running | Some fiddly little rules
Fields-types

---

Tables.

All InFoE data is held in tables. In a table, data is contained in records (also called rows). Each table has a number of fields (also called columns) so that the data in each record can be conveniently accessed.

When making selections from the database, you will have to access the data from these tables.

Tip! You can find a list of tables and their fields when writing your SQL. From the menu, select FindTable(F5), and FindFields(F4)

Frequently used tables in selections:-

USED FOR:	TABLE NAME:	CONTAINS:
demographic selections	people	most basic information on supporters on the system, including "flags".
	mailable_entity	it contains mostly mailing information, ie address details & EM details.
type of support	pledges	all details of S/Os, DDs, and cash membership.
past giving	financial_summary	contains a summary of financial performance of people.
	gifts	every gift we receive is recorded here.
contact history	communications	every communication we make with each supporter is recorded here.

A simple selection might be:-

select urn
from people
where trading_catalogue = "Y"

---

Top of page
An Overview | Tables | Cells, queries & SQL | Exclusion cells | Joins | Views
Some general points | Copying whole selections | Copying a single cell
Major steps - creating and running | Some fiddly little rules
Fields-types

---

Cells, queries and SQL.

Selections comprise one or more cells, each of which selects a group of supporters. Each cell has an accompanying query, written in Structured Query Language (SQL), and this extracts a precise set of supporters. Queries have the form:-

select urn
from table
where condition

A query for an appeal cell might be:

select urn
from people
where postcode = 'N10 7JQ'

A supporter can only be extracted once in a selection. If more than one cell tries to select a supporter, the first cell in the selection's running order "keeps" the supporter. (Cells are identified by increasing numbers, starting at one).

---

Top of page
An Overview | Tables | Cells, queries & SQL | Exclusion cells | Joins | Views
Some general points | Copying whole selections | Copying a single cell
Major steps - creating and running | Some fiddly little rules
Fields-types

---

Exclusion cells.

The first few cells of a selection can be exclusion cells. These comprise ordinary SQL, but are used only to remove people from the pool of all potential supporters. Once excluded, these supporters cannot be picked up by the cells which follow it in a selection. An example of an exclusion cell would be:-

select urn
from mailable_entity
where goneaway = 'Y'
or dead = 'Y'

Exclusion cells are essential for removing supporters, unless you are only using Views in your selection (see 4.1.5, page 7 below) which automatically exclude certain categories of supporters.

---

Top of page
An Overview | Tables | Cells, queries & SQL | Exclusion cells | Joins | Views
Some general points | Copying whole selections | Copying a single cell
Major steps - creating and running | Some fiddly little rules
Fields-types

---

Joining tables.

You will often need to refer to more than one table when making a selection. For example, suppose that you want all the people who receive a trading catalogue and Earth Matters. The flag for the trading catalogue is in the people table, and the flag for Earth Matters is in mailable_entity. This is how you achieve a join:-

select urn
from people p, mailable_members m
where p.urn = m.urn
and p.trading_catalogue = "Y"
and m.no_earth_matters > 0

---

Top of page
An Overview | Tables | Cells, queries & SQL | Exclusion cells | Joins | Views
Some general points | Copying whole selections | Copying a single cell
Major steps - creating and running | Some fiddly little rules
Fields-types

---

Views.

A view takes the sweat out of joining tables, and avoid the need for exclusion cells. Views look exactly like tables, they have records and fields, but they only appear to contain data, which is actually held in tables. The joins between tables have already been done for you, and exclusions made, so you only need to state:-

select urn
from mailable_members
where trading_catalogue = "Y"
and no_earth_matters > 0

Trap!! Be aware of which exclusions the view doesn't cover - add them yourself!

Frequently used Views in selections

These views all exclude supporters with dead, deleted, goneaway or suppress_all flags set to "Y". They all exclude high donors, apart for the mailable_high_donor view , obviously, which picks high donors only. (nb none of these views exclude supporters where appeals = N, you must add this exclusion if you want it).

mailable_members: supporters with paid up membership
mailable_donors: donors who have never taken out membership.
mailable_covenantors: current or lapsed covenantors.
mailable_lapsed: supporters with lapsed membership, no donation in last 15 months
mailable_high_donors: all mailable high donors (not to be used by Dev & Appeals!)

Please don't ever join a view to another view - this has a very bad affect on performance of the system. Also, if you find you need to join a table to a View, please ask someone for help - it may be possible to do things differently...

Trap!! Don't assume that all objects named with a prefix of mailable are Views. The exception is mailable_entity, which is a table.

---

Top of page
An Overview | Tables | Cells, queries & SQL | Exclusion cells | Joins | Views
Some general points | Copying whole selections | Copying a single cell
Major steps - creating and running | Some fiddly little rules
Fields-types

---

Some general points to remember

Menu
• Use the menu! When creating or editing a selection, press F1 for the menu
  (if you decide to return to the screen, just press the Return key).

Selections
• Give your selection a meaningful description (the really big box when you first AddSelection). This can include the project description, the date and method (ie mailing, count, etc).

• Mailsorting - if a selection needs mailsorting, it must be queued for over-night mailsorting. This must be performed the day after the selection has been run.

• Put all your exclusion cells at the top of the selection, and use VOID as the source code, and 0 as the output number.

Editing
• Save your work regularly when you are creating a selection. Yes, this does mean that you have to exit and re-enter your selection, but... save regularly and save tears.

• Make room first by inserting a line, when editing SQL text (press F8).

• When writing the SQL text, put each instruction on a different line. This makes it easier to alter or insert text later. For example

  select urn
  from people
  where trading_catalogue = "Y"
  and dead != "Y"
  and etc...

• You can copy individual cells or whole selections (see sections c and d below).

Source codes
• Source codes are there for you to track results based on your own segmentation and targeting, so you will tend to use a different source code for each targeted group.

• Use a short description at the beginning of a source code. This makes it easier to read in FindSource, as only the first three or four words are visible at a glance. For example, say 'Nov solus OAPs' for a legacy code - you don't need to say Legacy because the code prefix starts 'LE'.

Cells
• Set a figure for 1 in N testing, if you want to. For example, if you state 10, only 1 in 10 of the urns that your cell has selected will be extracted.

• One selection can refer to the result of another by referring to the temporary file which contains the selection results (eg tmp_s0001234 would be the temporary table for selection 1234). Temporary files are eventually over-written or deleted, but you can ask the FDM to keep a copy of the selection result file, so that you can refer to it later.

• One selection can refer to just one cell of a selection result. For example:

  select urn
  from tmp_s0001234
  where cell_no in (5,6,8)

---

Top of page
An Overview | Tables | Cells, queries & SQL | Exclusion cells | Joins | Views
Some general points | Copying whole selections | Copying a single cell
Major steps - creating and running | Some fiddly little rules
Fields-types

---

Copying whole selections

• From the menus choose InFoE, SelectionMenu, AddSelection
• Pick CopySelection
• Type in Selection No. (or anything else you know about it) and select Go
• Select (F4)

You now have a new selection, with all the details copied from your chosen selection (except source codes, which usually relate to only one selection). You can edit this selection in any way before you save it.

---

Top of page
An Overview | Tables | Cells, queries & SQL | Exclusion cells | Joins | Views
Some general points | Copying whole selections | Copying a single cell
Major steps - creating and running | Some fiddly little rules
Fields-types

---

Copying a single cell from another selection

• Choose a blank cell (you'll need RowInsert unless you want it on the end)
• EditCell
• CopyCell
• Type in Selection No. (or anything else you know about it)
• Go
• Move to desired cell
• ChooseSelection
• Make any changes
• Save

---

Top of page
An Overview | Tables | Cells, queries & SQL | Exclusion cells | Joins | Views
Some general points | Copying whole selections | Copying a single cell
Major steps - creating and running | Some fiddly little rules
Fields-types

---

Major steps - creating and running a selection.

• AddSelection, with a useful, meaningful description
• Edit cells, for each cell:-
  • Create/find a source code (or state VOID)
  • Attach a fulfilment document to the source code
  • Test the fulfilment document
  • Define an output for the cell
  • Test the selection
  • Tell FDM when output required.

---

Top of page
An Overview | Tables | Cells, queries & SQL | Exclusion cells | Joins | Views
Some general points | Copying whole selections | Copying a single cell
Major steps - creating and running | Some fiddly little rules
Fields-types

---

Some fiddly little rules for selections.

When setting up exclusion cells use VOID as the source code.

In selections you must use % as a wildcard, not a *, eg where postcode like 'N19%' (very different from how you find people - see 3.1 above)

The contents of most character fields you need for selections are in CAPITALS (character fields are those which are not dates, numbers or money). This means that flags, source codes, postcodes and county_town must be in CAPITALS (Uppercase). For example:-

select urn
from people
where high_donor = "Y"

The best format for dates is '01-jan-99': it's always best to use words for months to avoid confusion. The system will accept dates like 1/3/97, but is that 1st March or 3rd January? Dates don't mind about UPPER/lower case.

---

Top of page
An Overview | Tables | Cells, queries & SQL | Exclusion cells | Joins | Views
Some general points | Copying whole selections | Copying a single cell
Major steps - creating and running | Some fiddly little rules
Fields-types

---

Fields-types and how to use them - a quick guide

Dates are odd: they need quotes, but you can't use % to match them. (They're numbers in disguise...)

Table of Contents
Keys Used by Infoe | What is Infoe? | People | Creating Selections | Real Examples of Selections
Source Codes | Selection Output | Running Selections | Selection Results | Fulfilments | Answers
Title Page