Viewpoint
Datahandling for students

The Invoices Tutorial

This tutorial gives a good all-round demonstration of the main relational features of Viewpoint (except SQL and Scripting).

There's a copy of the final invoices file listed under 'Examples of Relational Databases'.

Step 1: Create a new database

Click on the Viewpoint icon and create a new, blank database.

This creates a blank database with a single table and a single layout in it. If you didn't want to create a relational database, you could simply click to create the fields here and now.

To create a relational database, you need to create some more tables.

Step 2: Create a new table

From the menu, choose Layout=>New table=> and enter the name 'Customers', then click on 'OK'.

This creates a new blank layout, which is attached to the new table called 'Customers'.

With the field type set to text, add the customer fields by clicking to create new fields and then typing the field name. You need to add the following fields:

Name
Address
Postcode
Telephone

Step 3: Import the customer data

Save your new database at this point. Choose 'File... Save' or click on the 'Save' button then open your new database in Sheet view.

To import the customer data, download the following CSV file (Customers.csv). Choose File=>Import=>Import info file to import the CSV file.

You will see the Merge dialogue, which lists the fields available in the text file in the left-hand column, and the fields in your customers table in the right-hand column. Click Merge and the customer data will be inserted in your new table.

Step 4: Create the Invoices table

From the menu, choose Layout=>New table=> and enter the name 'Invoices', and click on 'OK'.

You will see a new blank layout, this time attached to the Invoices table.

A letter head has already been created for you.

Download the file by clicking here.

Add the following question fields to the invoices layout:

Invoice number (Text)
Date (Date)
Customer name (Text)

Rather than creating more fields in the Invoices table to hold the customer's address and other details, you can link the details directly from the customer table, using a relational link.

Step 5: Creating a relational link

In order to show customer details on the Invoices layout, Viewpoint needs to know how the two tables are related. In this case, you need to tell the software that the 'Customer name' field on the Invoices table relates to the 'Name' field in the Customers table.

From the menu, choose Query/Table=>Relationships from the menu.

Click on the 'Add table' in the Tables toolbox, add 'Invoices' and 'Customers', then click Close.

Drag the 'Customer name' field from the Invoices table onto the 'Name' field in the 'Customers' table to create the link.

Step 6: Defining the primary keys

So far, the link you have created is lacking in one crucial way: there is no indication of which field acts as the unique key for the customer table. That means there is no way for Viewpoint to know that the 'Name' field in the customer table uniquely identifies a single customer, while the 'Customer name' field in the Invoices table indicates which customer this invoice relates to. In database parlance, the 'Name' field in the 'Customers' table is a primary key, while the 'Customer name' field in the Invoices table is a foreign key, which refers to records in another table.

To set the 'Name' field in the 'Customers' table to be its primary key, simply double-click on it in the relationships window. You'll see that the 'ID' field is no longer bold, and the 'Name' field has become bold.

Double-click on the 'Invoice number' field to set that as the primary key for the Invoices table.

Step 7: Defining integrity constraints

If you double-click on the join line between Name and Customer name, you can click on the 'Enforce one to many' button to insist that the 'Customer name' field in the Invoices table must always contain a value that matches an actual customer record. This is known as an integrity constraint, since it constrains the values that can go in the relevant field in order to make sure that the database is valid. If you click OK, you'll see that the join line now has a '1' at one end, and an infinity symbol at the other, to indicate that it's a one-to-many join (one customer in the Customers table can relate to many invoices in the Invoices table, but not vice-versa, because the linking field in customers holds a unique value for each customer, while the one in the invoices does not have to be unique).

Step 8: Putting the customer fields onto the invoice

At this point, it's a good idea to make sure that the Filed=>Show tab order option in the menu is turned off. This means that instead of showing 1 text next to the invoice number field, it shows the name of the field itself. This option is useful when you create joins between tables, as you will see.

With the field names displayed in the boxes, and a relationship between the Invoices and Customers tables set up, you can put the customer fields onto the invoices layout as follows:

In the Layout view toolbox, click on the small drop-down arrow to open the list of layouts, and then, holding the Ctrl key down, click on the Customers layout to open it in another window. Choose Window=>Tile from the menu to arrange the windows on screen so you can see both of them.

In the Customers layout window drag a box over the customer fields to select them, and then drag them into the invoices layout. Since the field boxes now display the field names, you can see that the existing invoice fields have changed to show that they come from the Invoices table, while the new fields come from the customers table.

What's actually happened here is that Viewpoint has automatically created a join query which joins the Invoices and Customers tables, and has attached the Invoices layout to this new query (called 'Join for Invoices'), rather than the Invoices table itself. This allows the layout to show fields from either table, since the resulting query contains all the fields from both tables.

Since you have already defined a relationship between the Invoices and Customers tables, Viewpoint will also have automatically copied this into the join query. This specifies that the 'Invoices.Customer name' field is used to find the Customers record that will be displayed in the customer fields of the layout.

Tip: You can find out more about the way in which Viewpoint uses queries to support relational operations in our documentation section.

The 'Invoices.Customer name' field and the 'Customers.Name' fields always have the same value (because of the join specified earlier). There is, therefore, no need to have both of them on the Invoices layout. Select the 'Customers.Name' field, press Delete and then rearrange the customer fields underneath the Invoices.Customer name field, to the right-hand side of the Invoices layout.

Note: Using Delete to remove the field from the layout doesn't actually delete the field itself, which remains in the customers table. All that happens is that the field frame is removed from the layout. If you want to delete a field completely, select its frame and press Ctrl-Delete. This will delete the field from the table as well, but be careful to make sure you don't need the field elsewhere (e.g. on another layout, or as a joining field).

Your layout should now look something like this:

In Viewpoint you can create default formulae for the 'Invoice number' and 'Date' fields, so that you don't have to enter values for them when you add a new invoice. To do this, do the following:

Click on the 'Invoice number' field to select it

Press Ctrl-Shift-G to edit its default formula

Type in the following formula: if 'ID' =NULL Then Null Else "IV" +RIGHT$ ("00000" +STR$ ('ID') ,5) and click on OK

Click on the 'Date' field to select it

Press Ctrl-Shift-G to edit its default formula

Type in TODAY and click on OK

Select all fields except the 'Invoice number' and 'Date' fields, then choose Fields=>Renumber=>, type in 1 and click on 'OK'

The last step moves the Invoice number and Date fields to the end of the tab order, so the caret will first move into the Customer name field when you add a new invoice. Note that the Invoice number field will not be filled in until you enter a value into another field.

Step 9: Entering a new invoice

Save your file then click on the Sheet view tool.

You can now type in the name of an existing customer (e.g. Adrian Critchlow), and when you tab out of the customer name field that customer's address, postcode etc. will automatically appear in the relevant fields.

If you type a new name (i.e. one that doesn't correspond to an existing customer), the fields will be left blank, and if you enter the new customers details and save the invoice, the new customer is automatically added to the list of customers.

Step 10: Using smart value lists

In Viewpoint you can specify a 'value list' for any field. This is simply a set of values that you can choose from a menu rather than having to type in a value each time.

You can take this further with 'Smart value lists', which make it easy to choose values from the database itself. They're called 'smart' because you don't have to work out which table the values are to be taken from, or even which field should be updated when you choose a value. You simply decide which field to put the value list on, and Viewpoint does the rest.

To put a value list on the Customer name field, go back to Layout view, select the customer name field and press Ctrl-Shift-V to open the value list dialogue. Click on 'Choose a value from the database' and then click 'OK'.

Back in Sheet view you will find that when you click on or tab into the customer name field, a menu containing the names of all customers in the database is displayed. You can now click on a name to enter it into the field.

There are more examples of using value lists later on. For now, though, let's return to the invoices.

Step 11: Invoice items

On each invoice you want to show a list of products that have been sold to the customer. Clearly you'll need a table containing a record for each product that is available for sale, but you also need an Invoice items table, with a record for each item that appears on an invoice. To see that that is the case, consider where you would put the 'Quantity' field, which indicates how many of a particular item youre sold.

All this will become clearer when you set up the relationships. For now, though, you need to create the tables:

Step 12: Create the products table

Choose Layout=>New table => from the menu, and type in 'Products' and click on 'OK'. Create the following fields:

Code (text)
Description (text)
Price (real number)
VAT (boolean)

Highlight the Price field and choose Field=>Type=>Real=> from the menu. Edit the Number format dialogue so it has 2 decimal places and a pound symbol before the number. Click 'OK'.

A Products CSV file has already been created. Click here to download it. In Sheet view choose File=>Import=>Import into file=> to import this CSV file. Check that the field names line up in the merge window, then click on Merge to complete the operation.

Step 13: Create the items table

To create a table that will contain the actual invoice items:

Choose Layout=>New table=> from the menu, type in 'Items' and click on 'OK'. Create the following fields:

Invoice number (text)
Product code (text)
Quantity (integer)

For each invoice item, the invoice number field indicates which invoice it belongs to, while the product code indicates which product it relates to.

Step 14: Create the invoice items subview

Return to the Invoices layout by choosing it from the drop-down list in the Layout view toolbox.

Click on the Create subview tool from the toolbox and drag out a box where you want the subview to go.

The subview dialogue will now open.

Click on the 'Show records from' drop-down menu. Notice that only the Customers and Invoices tables are shown on the menu. This is because you have not yet added the Items and Products tables to the relationships, which means that Viewpoint would not know how to link the existing Invoices table to the new table if you attached it to the subview. Click on the cancel button to close this dialogue box.

Step 15: Adding Items and Products to the relationships

Choose Query/Table=>Relationships from the menu to open the Relationships window. Click on 'Add table' and add Items and Products to the window. Click on 'Close' to close the Add Table dialogue.

Double-click on the 'Code' field of the Products table to set it as the primary key.

Drag a link from the 'Product code' field of the Items table to the 'Code' field of the Products table. Double-click on the line, select the 'Enforce one to many' option and click OK. The line should now have a '1' and 'infinity' symbol on it.

Drag a link from the 'Invoice number' field of the Invoices table onto the 'Invoice number' field of the Items table (or vice-versa). Double-click on the line and select the 'Enforce one to many option and click on 'OK'.

Step 16: Select Cascade Update and Delete for the Invoices/Items relationship

As each invoice item 'belongs' to its associated invoice, you should delete all the invoice items on an invoice when the invoice is deleted. Similarly, if the invoice number is changed, the invoice items should stay on the invoice. This is precisely what will happen if you enable the Cascade update and Cascade delete options in the relationship dialogue.

Click OK to finish with the relationship dialogue, and OK in the Tables toolbox to exit the relationships window and return to Layout view.

Step 17: Attach the subview to the Items table

Double click on the Subview on the Invoices Layout. The subview dialogue will open. Click on the drop-down arrow for 'Show records from' and choose the Items table, then click OK to finish with the dialogue.

The subview will now have 'Items' written at the bottom-left.

Step 18: Add the Items fields to the subview

With the subview selected, go to the Field menu and choose Add field=>Items.Product Code to add it to the subview. The field will automatically be added at the left of the subview with the field title outside (which is what you want, as the field title is to appear only once, at the top, while the field contents will be displayed in each row).

You could now add the 'Items.Quantity' field in the same way, but first lets add some of the Products fields.

You can add the Products fields to the join either by opening the Products layout in another window and Ctrl-dragging the fields across, or by editing the layout's join directly.

To do this, choose Query/Table=>Join from the menu, click on 'Add table', add the Products table by clicking on it and then click on Add. Close the dialogue.

You will notice that the relationship between Items.Product Code and Products.Code that youre added earlier has been automatically copied into the join. This is true of all joins - the relationships act as a kind of 'default join' for tables when you add them into a join, as well as indicating the primary key / foreign key relationships. You can change the join lines in the join view after you've added the table, but any changes will only apply to this particular join, and have no effect on the relationships.

If you click OK to return to Layout view, and open the Fields drop-down menu, you will notice that the Products fields are now available on the menu, as the Products table has been added to the join.

For each field that you want to add to the subview, you need to:

• Make sure the subview is selected
• Add the field from the Fields menu on the toolbox
• Resize the field by dragging the box at the right-hand side
• Resize the subview to make it wide enough to take the field, if necessary
• (Don't forget to reselect the subview ready to add the next field)

Using the above method, add the following fields to the subview:

Products.Description
Products.Price
Products.VAT
Items.Quantity

Your layout should now look similar to this:

You can also add a formula field to calculate the total price for an item as follows:

Make the subview wider to leave room for a new field to be added

Click on the Formula question tool

Click so the centre of the new field box is within the subview

Type the name of the new field and reposition it within the subview

Click on the pointer icon at the top-left of the toolbox

Select the new field and press Ctrl-Shift-G to edit the formula, then type: If 'VAT' Then 'Price' * 1.175 'Quantity' Else 'Price' * 'Quantity'

(VAT will be added to the price if the product is VAT-rated).

Step 19: Setting the field format

You now have two fields which are used to display prices: Products.Price and your new 'Total' field.

The field type is treated as a real number, but this doesn't display prices correctly as it stands: for example, £1.50 would be shown as 1.5

To set the correct formatting options, select the two fields: select the first item, then use Ctrl-click to add the second. Then open the Field=>Type=>Real dialogue box from the menu.

In the Number format dialogue type £ into the 'Text before number' box, choose 2 decimal places and tick the 'thousands separator' option, then click OK.

Normally this action would also set the type of the selected fields to Real, but in this case the 'Total' formula field remains as a formula, since it belongs to a query rather than a table, and therefore can only be a formula field, as queries don't contain any data of their own.

From the Effect menu, choose alignment to set the two price fields to be right-justified, so our columns of prices will line up neatly.

Step 20: Adding invoice items

You can now try adding some invoice items to your existing invoice - either tab into the subview or click just under the 'Code' title (within the subview) to enter a new record, then type 'ANG' and press tab. You should see 'Angle poise lamp' appear under 'Description' and if you keep tabbing forwards you will add more records.

Note 1: To add a new invoice, make sure the cursor isn't in the subview, then click on the Add new sheet button on the toolbox. If you click on Add while the cursor is in the subview, it will just add a new subview record, rather than a new main record.

Note 2: If you create a new invoice, you must enter the customer name before attempting to add any invoice items, as the main invoice record isn't created until you enter some data into it, and without the main invoice record, it's not possible to create related invoice items.

Note 3: If you want to enter a product that doesn't yet exist in the product table, you can do it directly in the invoice - but note that you must enter a new product code as well as a description, as the product code is the primary key of the product table, which uniquely identifies each product.

Step 21: Summary fields

One omission from the invoice as it stands is that it doesn't add up the total price of all items in the invoice.

To do this, go to the Layout view, and drag the 'Total' field from the subview down to the main record, just below the bottom of the subview itself.

You should find that the 'Total' field stays where it is, and you have created a new field that has the following formula:

Count('Total') For 'Invoices'

This will count the 'Total' field for all records in the subview, i.e. it will generate a different value for each record in the Invoices table (which is why it says '... For 'Invoices'')

Note: If you had created a main footer and then dragged the field into the footer, the formula would have read:

Sum('Total')

which would have summed the 'Total' field over all the records. This is known as a Grand Summary.

Look at your invoices now in the Sheets view. Invoices have been totalled up, and if you make any alterations to an invoice, the total updates automatically to show your changes.

Note that changes to a subview record won't update the main total until you move off that record, as the subview record won't be saved until then.

Step 22: More value lists

Unless you have an exceptionally good memory, you may not know all the product codes needed when filling in an invoice. It is best to have a value list on the code field. In Layout view:

Click on the 'Items.Product Code' field to select it

Press Ctrl-Shift-V to open the value list dialogue

Select 'Choose a value from the database'

Click OK

So far this is much like the earlier situation with the customer name field, except that you can add items within the subview, as well as altering existing items.

As Viewpoint's value lists are 'smart', you can put a value list on almost any field and Viewpoint will make sense of it.

Try putting a value list on 'Products.Description' and 'Invoices.Invoice number', and you'll see.

Return to Sheets view and click in the Description field.

If you choose a new description, Viewpoint works out that in order to display the given description, it needs to alter the value of the Items.Product Code field, since that is the foreign key that is linked to the primary key of the table to which the description field belongs.

In fact, choosing a value from a value list doesn't always result in entering a value into a field at all. If you click on the Invoice number field and choose a number, you'll see that Viewpoint actually moves to the record containing the given invoice, rather than physically altering any of the field values. What's happened is that since there is no foreign key in the join that links to the primary key of 'Invoices', the only sensible thing to do is simply to move to the indicated record. This makes sense in this example as you would not want two invoices with the same invoice number.

Viewpoint takes the view that if you choose a value from a smart value list, you simply want that value to appear in that field. You don't need to worry about which field (if any) needs to be updated - Viewpoint works it out for you by looking at the foreign key/primary key relationships in the join.

In fact, it goes further - you can put value lists on items in a table view, and it will check the relationships to see if that field acts as a foreign key for any other table. Try going to the Items table and putting value lists on the Invoice number and Product code.

Step 23: More relational views

Creating a file with multiple tables is more helpful than simply being able to see the latest version of the fields in the other tables. You can actually view the data from many different directions because it is properly structured.

Suppose you wanted to see which products a particular customer has bought. You can do that as follows:

Go to the Customers layout

Choose Layout=>New layout from the menu, type 'Customer orders' and click 'OK'

Click on the subview tool and drag out a subview rectangle

Open the 'Show records from' menu at the top of the dialogue, click on 'Items' and click OK

Choose Query/Table=>Join from the menu and add the Invoices and Products tables.

Double-click on the line between Invoices and Items and choose 'Include ALL rows from Invoices, and only those rows from Items where the joined fields are equal'. When you click OK the arrow should point from Invoices to Items.

Click OK in the toolbox to return to the layout.

With the subview selected, add Products.Code, Products.Description, Products.Price and Items.Quantity fields from the Fields menu on the toolbox.

Return to Sheets view

If you put a value list on the customer name field, you can even use the value list to find customers in the table without doing a search.

You should see that if a customer has bought anything, the products they've bought will be displayed in the subview. The view is also fully active, so if you open the invoices layout in another window, when you add items to invoices and save the record you'll see the Customer orders layout change to reflect what's happened.

You might wish to create another view to show the order for each product, and so on.

Step 24: Switching between layouts

Finally, a very useful feature of Viewpoint is the way in which it tries to stay on the same record when you switch from layout to layout, even if the two layouts are not looking at the same query or table.

This feature extends to most layouts - Viewpoint will attempt to find tables in common between the two layouts, and will stay on the same records if it does find any tables in common.

Step 25: Switch off your computer and relax

If you got this far, well done!

I hope you enjoyed going through the tutorial, and that it gave you an insight into the sort of things you can do with Viewpoint.