The best of the immediate solutions is to change the design of your database, so that what's stored isn't the school year they're currently in, but the calendar year in which they started school. You can then work out what year they're in using two formula fields, one to work out what academic year it is at the moment:

monthof(today) > 8 => yearof(today) : yearof(today)-1

This formula will give "2001" for dates between September 2001 and August 2002, which is probably just what you need to give the academic year. If this formula field is called 'year now' and the integer field giving the academic year the pupil started is called 'year started', then the formula for which year the pupil is in now would be:

1 + 'year now' - 'year started'

This will give "1" for pupils who started school this academic year, "2" for those who started it in the previous one, and so on. Pupils who are kept back a year or accelerated a year will require you to tell a little white lie in the 'year started' field! (As Brett Lanoish has pointed out to us, this only works for primary schools -- if you're a UK secondary school you will want to add six to this so that your intake year is 7 and so on up the school, as per UK National Curriculum).

The moral of the story is this: if you've got a value which changes from time to time, but you can calculate it from another value which doesn't change, then store the unchanging one in the database and calculate the changing one when you need to use it. That way you don't involve yourself in lots of work every September!

-- "But it's too late! I've already got a 2,000-record database with the current academic year in!"

Not a problem. You can set up a formula field that goes the other way, and calculates the 'year started' field from the 'year now' field, using the formula:

1 + yearof(today) - 'year now'                     

Once you've added this formula field in the layout editor, click OK and it will be added to your database. Then go back into the layout editor, change its type to Integer (by clicking on it, then clicking on the "12" icon) and click OK again. It'll magically become an integer field with the right value in. Then all you need to do is go into Layout view again, remove the field's formula (Shift-Ctrl-G, Ctrl-U, Return) and turn the 'year now' field into a formula field depending on the 'year started' field, as outlined above.

That, admittedly, all adds up to quite a complex procedure, but, once it's done, not only have you solved the Year problem for this school year, but it'll solve itself each year from now on!

If you're using passwords, it is also possible to get different classes of user to see a different layout initially, since it's the first accessible layout in the list that's used by default.

Changing a formula field into an ordinary field with the same formula, is as easy as selecting it (while in the layout editor) and clicking on the type you want it to be in the toolbox pane to the left of your page.

More rarely, the same effect can also be observed if you've been adding and deleting lots of records. Using the File=>Reorganise file option to make a new copy can occasionally recover lots of space.

If you wish to install Viewpoint on an NT Server for loading onto your client machines, you still need to install it on each client as there are DLL files that need transferring to the local Windows directory, and entries to be made to the Registry.

However it can be substantially quicker to install, and much simpler to upgrade if you ensure that you always install to the same path on the server. To do this, when you get to the 'Choose Destination Location' page, use the 'Browse' button to browse to a suitable shared directory on your server and always install to the same location from all clients.

In that manner, when it comes to upgrading, the Viewpoint.exe file only needs to be replaced once. There is little network overhead, as Viewpoint is a relatively small executable by Windows standards, and loads quickly.

The sort can be saved and selected when the file is reloaded but the file itself will not automatically be sorted. Can you help?

The problem here is that Viewpoint allows multiple sort orders, whereas other databases only allowed the underlying records to be sorted, which meant there was only a single sort order active at any one time.

When you perform a sort under Viewpoint, it behaves much more like a subset, in that you can toggle between the new sort order and the old one, and the sort is lost if you close the database.

To make the sort order permanent, you need to give your query a name by entering one in the toolbar at the top of the window when you set up the sort order, then attach that query to your layout so that it is used by default.

Note that each layout can have a separate sort order if you want, so if you want all your layouts to have the new sort order you need to attach each in turn to the named sort query.

If you want the sort order to be maintained permanently, so it is stored in the main database and is immediately available when you reload the database (rather than being recalculated when you open it), you should set the 'Keep index' flag in the toolbar.

When you perform a sort, Viewpoint creates a 'secondary index' in the order you specify, which is then used to fetch the underlying records in the table in the order desired. The order of the underlying records does not change. This allows you to set up a number of alternative sort orders and switch quickly between them, as you can just choose your desired sort order from the Query=>View/Run menu, or even attach scripts to buttons on the background of your layout to switch to a given sort order.

I searched for some records within my database, and now I can't get my other records back. The subset button hasn't appeared, so I can't seem to turn it off.

What's happened here is that you have probably performed a search while in Edit Layout mode.

If you do this, Viewpoint creates a permanent query called 'Join for layoutname', and attaches the layout to that query.

This means that the layout shows the records that are returned by the query, rather than those from the underlying table. The other records are still there, but you won't be able to see them using that layout.

This behaviour is in contrast to what happens if you perform a search in browse mode, where Viewpoint will normally just create a temporary query on top of the table or query that the layout is attached to, and you can toggle between this query and the original query by clicking on the 'Subset' button.

If you've performed the search while in Edit Layout mode, however, the subset button will not come on unless you perform another search while in browse mode, and this search will only return records from within the original search, as the original/initial query will already have your original search applied to it.

Why is this behaviour useful?

In a similar way, it also allows you to have different sort orders in different layouts, since the sort order is just another aspect of a query.

OK, I see what you're saying. Now how do I get my records back?

I am trying to update some records by importing a CSV file using a Clustering key, but I can't get it to work: Viewpoint reports the error:

'To use 'Match Sort Key' you need to set the Clustering key of the destination table'.

Basically you need to make sure you're importing into a layout that's attached to a table, rather than a query, and then you need to set the clustering key (i.e. the underlying sort order) of the table records.

To check that the layout is attached to a table, edit the layout and look at the Query/Table=>Attach to menu - one of the tables at the top should be ticked, rather than one of the queries at the bottom (probably called "Join for <layoutname>").

If your layout uses a query, make a copy of the layout using the Layout/New Layout menu command, and choose the relevant table from Query=>Table=>Attach to menu.

Once the layout is attached to a table, you can set the clustering key by using the Query=>Table=>Edit=>Clustering key option, and set the sort order to the field(s) that identify each record (hopefully the same as the primary key, if your database is properly structured!).

Return to browse mode and import the CSV file - you should see that the field(s) you specified in the clustering key are shown in a darker grey, and if you select the 'Match sort key' option and make sure the correct fields from the CSV file are mapped onto the clustering key fields, it should all work as expected.

Note also that it may be advisable to clear the clustering key after performing the merge, especially if you have a number of secondary indexes (i.e. sorts) referring to the table in question, as each one of those needs to reference the clustering key of the table, and it's usually best if this is just left as the ID field, i.e. the entry order of the records.

How can I get totals of a field (i.e. summed data over all the records in a table)?

To make a summary of a field, you can simply create a footer area using the Arrange=>Main footer menu option, then drag the field you want to summarise from the body section into the footer.

Depending on the type of the field, you'll get a default summary type, e.g. for a numeric field you'd get a summary field with the formula:

Sum('OriginalFieldName')

Can I also group the records according to a certain field, and then have separate totals for each group? For example, can I group my butterflies by colour, and then find the average wingspan for each colour?

You can get a 'grouped' summary using a SQL query as follows:

I want to print out from a Viewpoint database a list of names numbered in alphabetical order e.g. Adams, Bernard, Cook. Adding a name would automatically cause them to be renumbered. I suspect this must be possible since there is a counter in the program - can you advise?

It is not possible for the entries to be automatically renumbered when you add or alter a name, since the 'ID' value associated with each record is the entry order of the record, and does not change once you've entered the record. This is useful as a unique ID for each record, but no more.

However, you can write a script that gives each record a number as follows:

First add a numeric field, called (let's say) 'Number'.

Then add a script to a button on the background that says:

I want to combine my existing single line address fields (address 1, Address 2 e.t.c.) into a single multi-line field, without leaving blank lines in the address.

You can use a simple formula field to combine the existing address fields as follows:

'Address1'+"\n"+'Address2'+"\n"+'Address3'+"\n"+'Address4'

The '\n' strings represent new lines within the text, and you just need to make the formula field's box big enough to show the required number of lines.

The problem with the above formula is that if any of the address fields is blank, you will get a blank line in your new address.

To solve this, modify the formula as follows:

Updating linked file fields