PipeDream enables you to store a database of records and provides facilities to sort your records on one or more fields. You can save and print out those records which satisfy one or more conditions which you specify. PipeDream also provides several database functions which enable you to produce statistics about your records.
The best way to become familiar with using PipeDream as a database is to load a database of records and try out the various facilities upon it. An example is a pupil records database called Markbook, which is provided in the PipeDream Examples, in the Tutorial directory.
To load the example database:
The database consists of a list of pupils together with their classes, subjects (one of history or physics, indicated by H or P), continuous assessment marks, examination marks and total marks. Each record is stored on one row and each field is in a separate column.
If you scroll down through the database, you will find that the column headings, which let you know which field is in each column, disappear from the top of the window.
It is possible to fix rows so that the column headings always remain at the top of the window. To do so:
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Layout | Fix row | LAF |
The borders of rows one and two change colour to show that the rows are fixed.
Try scrolling down through the database. You will see that rows one and two remain at the top of the window.
When you first load the database it is sorted on column C, i.e. on class. You will see that all the pupils in 1A are first, followed by those in 1B, then 2A, then 2B.
If you would rather have the pupils listed in alphabetical order of surname, you need to sort the database on column B, the column containing the surnames.
To sort on column B:
Note that dragging beyond the bottom of the window has the effect of scrolling downwards so that you can see and mark data which was not visible at first.
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Blocks | Sort | ![]() |
BSO |
PipeDream also enables you to sort on more than one column at once.
If you want to list pupils in classes 1A, 1B, 2A and 2B in order of the total marks they gained in history and physics, you need to sort first on column C, which contains the classes, then on column D, which contains the the subjects, and finally on column G, the one containing the total marks.
Mark rows 3 to 39 by dragging down the row borders.
Give the Sort command.
Type C on the first row of the dialogue box, D on the second row and G on the third row. To move through the dialogue box, press ↓ or or click in the field you want.
Click the Return↵).
button (or pressThe records are now arranged in order of class, then subject, then total mark, enabling you to see who was first in history in class 1A, who was bottom in physics in class 2A, and so on.
Clear the marked block by clicking the mark block box.
If you want to find a particular pupil’s record quickly, you can use Search to find him or her.
Give the Search command:
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Blocks | Search | ![]() |
F4 | BSE |
When the Search dialogue box appears, type in the name you want to find.
Try typing Quinn.
Click the Return↵).
button (or pressThe caret will be positioned next to the name Quinn.
If you want to find all pupils called John, give the Search command again.
When the Search dialogue box appears, delete Quinn and type John.
Click the Return↵).
button (or pressThe caret will be positioned next to the first occurrence of the word John.
To find the next occurrence of the word, give the Next Match command:
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Blocks | Next match | ![]() |
F6 | BNM |
Searching can be especially helpful if you have a very large database.
One of the most useful aspects of database use is the ability to save and print records which satisfy certain conditions.
If you need to predict your pupils’ GCSE results, for example, you might want to know which pupils in class 1A who study history got a total mark higher than 100.
Or you might want to know which pupils got either a continuous assessment mark lower than 50 or an examination mark lower than 45 so that you can give them extra attention next term.
To find out which pupils in class 1A who study history got a total mark higher than 100:
C1C39="1A" & D1D39="H" & G1G39>100
The condition is too long to be completely visible in the dialogue box input field, so the field will scroll as you type.
It tells PipeDream to save all those rows which have 1A in column C, H in column D and a number higher than 100 in column G.
The cell references you have typed, C1, D1 and G1, will be updated automatically for each new line in the database, changing to C2, D2 and G2, then C3, D3 and G3, and so on.
Notice that any text included in your condition must be enclosed within double quotation marks, for example "1A".
The & sign is used to link conditions when there are more than one.
The conditions on both sides of the & sign must be true for the record to be selected.
A new PipeDream document window will appear on the screen containing the records you selected, i.e. a list of all pupils in class 1A who study history and got a total mark higher than 100.
You can now save the new file or print it out, depending upon your requirements.
To find out which pupils got a continuous assessment mark lower than 50 or an examination mark lower than 45:
E1E39<50 | F1F39<45
To type |, hold down Shift and press the \ (backslash) key, which is usually either just above the Return↵ key or to the left of the Z key on UK PC-style keyboards.
This tells PipeDream to save all those rows which have either a number lower than 50 in column E or a number lower than 45 in column F.
| is used to represent ‘OR’. Only one of the conditions needs to be true for the record to be selected.
A new PipeDream document window will appear on the screen containing the records you selected, i.e. a list of all pupils who either got an assessment mark lower than 50 or an exam mark lower than 45.
Once again, you can save the new file or print it out, depending upon your requirements.
You can type new records into PipeDream in the same way as you type text. Type each record on a separate row and each field of the record into a separate column.
Try entering a few new records into the Markbook database. Position the caret in cell A15, for example.
Give the Insert Row command:
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Edit | Insert row | Ctrl-F7 | N |
Type in the first name of a pupil. Press Tab. You are now in column B, ready to type in the pupil’s surname. When you have done so, press Tab again to move to column C, where you can type in the pupil’s class. Continue in this way until the pupil’s record is complete.
Try adding more pupil records to the database.
If a pupil leaves the school, you will want to remove his or her record from the database.
Position the caret on the row you want to delete. Give the Delete Row command:
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Edit | Delete row | Ctrl-F8 | Y |
Use the Auto Width command to ensure that each column is the correct width for its contents:
Menu | Command | Tool | Key | Cmd-sequence |
---|---|---|---|---|
Layout | Auto width | LAW |
PipeDream provides a number of database functions which enable you to produce statistics about selected records in your database. The records are selected by the condition which forms the second part of the database function.
For example, you can use the function DCOUNTA to find out how many of your pupils study history:
DCOUNTA(D3D39, D3D39="H")
The number of pupils who study history appears in the cell containing the DCOUNTA formula.
D3D39 is the range of cells upon which you want to carry out the function i.e. all cells from D3 to D39 inclusive.
D3D39="H" is the condition. You only want to count the cells in the range D3D39 which contain H.
Note that the parameters of the function are enclosed within brackets and any text, in this case H, within double quotation marks.
If you want to know the average physics exam result:
DAVG(F3F39, D3D39="P")
The average physics result appears in the cell.
There are many other database functions which are explained fully in the Database Functions section of the Reference Guide.