You can use PipeDream to store a database of records. PipeDream provides facilities to sort your records on one or more fields. You can save out only those records which satisfy one or more conditions which you specify or those records which satisfy one of several specified conditions. PipeDream also provides several database functions which enable you to manipulate your records in various ways.
You can enter records in two different ways:
Each record takes up just one row in the document. On sorting, each row is considered as a separate record. Most databases have single row records.
If you have entered some names and addresses like this:
Mr. R.T. Smith. |
3, The Drive, |
Tonbridge, |
Kent. |
Mr & Mrs. G. Fox |
192b High Street |
Cambridge, |
Cambs. |
Mr. J. FitzHenry |
"RiverView", |
Sevenoaks, |
Kent. |
and then realise that you actually wanted to enter them as single-row records like this:
Mr. R.T. Smith. | 3, The Drive, | Tonbridge, | Kent. |
Mr & Mrs. G. Fox | 192b High Street | Cambridge, | Cambs. |
Mr. J. FitzHenry | "RiverView", | Sevenoaks, | Kent. |
all you have to do is mark each name and address like this:
Mr. R.T. Smith. | 3, The Drive, | Tonbridge, | Kent. |
Mr & Mrs. G. Fox | |||
192b High Street | |||
Cambridge, | |||
Cambs. | |||
Mr. J. FitzHenry | |||
"RiverView", | |||
Sevenoaks, | |||
Kent. |
and give the Transpose Blocks command:
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Blocks | Transpose blocks | Adjust ![]() |
BT |
You can sort the records in your database on any column, in either ascending order or descending order. You can also sort on several columns, one after the other in the order you specify. If sorting on several columns, you can specify ascending or descending order separately for each column.
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Blocks | Sort | ![]() |
BSO |
By default, the sort will be in ascending order. This means that the smallest item will be put first and the largest at the bottom. To reverse this, click the Ascending order option to turn it off.
You can set this option for each column you type in. If you do not type in a column reference, the sort will take place on the column containing the caret. Note that part of or all of the column must be within the marked block.
Databases can be built which have records containing paragraphs of text. A record in the database can be one row or it may be more than one row.
Note that you can sort multi-row records but you cannot do row selections or mailshots with them as these two operations assume that each row is a separate record. For this reason, it is usually best to have your databases comprising just single row records.
If you want to be able to sort records consisting of more than one line, you need to enter your records in the following way:
A | B | |
---|---|---|
1 | British Blue | The British Blue has a sturdy body and shortish |
2 | legs. Its fur is thick and short and an even | |
3 | grey colour. Its eyes are deep red copper. It is | |
4 | usually friendly with everybody. | |
5 | Seal Point Siamese | The Seal Point Siamese has dark brown 'points' |
6 | - ears, mask, legs and tail, contrasting with a | |
7 | cream body. Its fur is smooth and silky. Its | |
8 | eyes should be china blue. It is very friendly | |
9 | and rather noisy. | |
10 | Brown Tabby Persian | The Brown Tabby Persian has the typical flat |
11 | Persian Face, with golden eyes. Its long, thick | |
12 | fur has even tabby markings with a cream chin | |
13 | and black 'slippers'. It can be rather timid | |
14 | but is very friendly with those it trusts. |
When you sort such records, you must have the Multi-row records option in the Sort dialogue box turned on. The blank cells between the multi-row records ensure that when you sort the records on column A, all the rows in the record will be kept together. So, you can specify a sort on column A to get the cats in alphabetical order. The result would be as follows:
A | B | |
---|---|---|
1 | British Blue | The British Blue has a sturdy body and shortish |
2 | legs. Its fur is thick and short and an even | |
3 | grey colour. Its eyes are deep red copper. It is | |
4 | usually friendly with everybody. | |
5 | Brown Tabby Persian | The Brown Tabby Persian has the typical flat |
6 | Persian Face, with golden eyes. Its long, thick | |
7 | fur has even tabby markings with a cream chin | |
8 | and black 'slippers'. It can be rather timid | |
9 | but is very friendly with those it trusts. | |
10 | Seal Point Siamese | The Seal Point Siamese has dark brown 'points' |
11 | - ears, mask, legs and tail, contrasting with a | |
12 | cream body. Its fur is smooth and silky. Its | |
13 | eyes should be china blue. It is very friendly | |
14 | and rather noisy. |
You can save to a file those records which satisfy one or more conditions you specify, such as all the cars in your database which are both red and have five doors. You can save all those records which satisfy one of several conditions you specify, such as all the cars in your database which are manufactured either in Germany or in France.
The formula you type in will be evaluated before each row (record) is saved. Only if the result is true will the row be saved. The formula will normally contain cell references. These are updated by one row for each new line.
For example, if you enter B1B100>100
as the condition,
all rows where the second column evaluates to a number greater than 100 will be saved.
On row 1, the condition will be B1>100
.
On row 2, the condition will change to B2>100
,
on row 3 to B3>100
,
on row 4 to B4>100
, etc.
You can include text strings in the condition, but they must be placed within double quotation marks.
The condition A1A100>"Fred"
will save all lines lying alphabetically after Fred,
such as "Tom"
and "Harry"
but not "Dick"
.
A string can contain the wildcards
^?
(any single character) and
^#
(any multiple character).
All string comparisons treat lower-case and upper-case letters as the same.
You can save rows which satisfy more than one condition by using the &
operator.
Example: (here we are just showing the first ten rows of a 100 row database):
A | B | C | |
---|---|---|---|
1 | A. Jones | Weymouth | Ford Fiesta |
2 | B. Smith | Cambridge | VW Polo |
3 | C. Thompson | Lancaster | Vauxhall Cavalier |
4 | D. Reed | Weymouth | Citroen BX |
5 | E. Baker | Halifax | VW Golf |
6 | F. Baxter | Cambridge | Citroen BX |
7 | G. Parker | Cambridge | VW Golf |
8 | H. Walker | Norwich | Ford Fiesta |
9 | I. Potter | Cambridge | VW Golf |
10 | J. Brown | Cambridge | VW Passat |
To save all those who live in Cambridge and drive a VW Golf, you would use the condition:
B1B100="Cambridge" & C1C100="VW Golf"
To save all those who live in Cambridge and drive any type of VW, you would use the condition:
B1B100="Cambridge" & C1C100="VW^#"
You can also use the |
(vertical bar) operator to save those rows which satisfy one of a number of conditions.
Still using the above example database, to save those who either live in Weymouth or drive a Citroen BX, you would use the condition:
B1B100="Weymouth" | C1C100="Citroen BX"