The Invoices tutorial
This tutorial gives a good all-round demonstration of the main
relational features of DataPower 2 (except SQL and Scripting).
There's a copy of the final invoices file listed under Example files
(click here to download it).
Right - if you're sitting comfortably in front of your computer with
DataPower 2 loaded and waiting, we'll begin...
Step 1: Create a new database
Click on the DataPower icon on the iconbar to create a new, blank database.
Like DataPower 1, this creates a blank database with a single table and a single layout onto it. If we didn't want to create a relational database, we could simply click to create the fields here and now.
To create a relational database, we 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 press Return.
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:
Step 3: Import the customer data
First save your new database by opening the directory viewer where you want to save it, then clicking on OK in the DataPower toolbox, giving the file a name and then dragging the file icon into the directory.
To import the customer data, download the following CSV file (Customers). You'll need to save the file to your local disc - if your web browser displays the file in the browser window, you can use the Save as HTML option to save the file to disc without converting the tabs to spaces. Then drag the disc file onto the toolbox of your new DataPower Invoices file to import it.
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. You'll need to drag the left-hand field 'Tel No' just above the '<blank>' field to the left of the 'Telephone' field, as DataPower can't match these fields by name.
Then 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', then press Return.
You'll see another blank layout, this time attached to the Invoices table.
To make it easy to put a nice background on your layout, I've provided a DataPower file that has a suitable background for an Invoice layout in it.
Download the file by clicking here and saving the file to your hard disc (as before, if your web browser displays the contents of the file rather than giving you a save box, you can save it as HTML onto your hard disc). Make sure the filetype is set to 'Database' (&C27), and double-click on it. Press F5 to edit the layouts, then Ctrl-A to select all items on the layout, and drag them into your new layout to copy them over. Then close the header database.
Now that the invoice layout looks a bit nicer, we can start adding the fields:
Invoice number (Text)
Customer name (Text)
Now, rather than creating more fields in the Invoices table to hold the customer's address and other details, we'd like to get 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, DataPower needs to know how the two tables are related. In this case, we need to tell it that the 'Customer name' field on the Invoices table relates to the 'Name' field in the Customers table.
To do this, choose Query/Table => Relationships from the menu.
Then click on the 'Add table' button at the top-left, double-click on '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 we've 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 DataPower 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. If you like, you can adjust-double-click on other fields to extend the primary key to involve more than one field. You might want to do this if, for example, you had 'First name' and 'Surname' fields, and the unique key involved both of them.
Double-click on the 'Invoice no' 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 you created earlier, 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).
Click OK to return to your Invoices layout.
Step 8: Putting the customer fields onto the invoice
At this point, it's a good idea to make sure that the Fields=>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'll see.
So, 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:
Click on 'Layout' at the top-left of the toolbox to open the list of layouts, and then, holding the Ctrl key down, click on the Customers layout to open it in another window (the use of the Ctrl key is a shortcut to avoid having to choose File=>New view and then changing to the customers layout). Arrange the windows on screen so you can see enough of each of them.
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 should now 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 DataPower 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, DataPower 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.
You can find out more about the way in which DataPower uses queries to support relational operations in our documentation section, but for now let's just go on with the tutorial...
Since the 'Invoices.Customer name' field and the 'Customers.Name' fields always have the same value (because of the join we specified earlier), there's no need to have both of them on the layout. Select the 'Customers.Name' field, press Delete and then rearrange the customer fields underneath the Invoices.Customer name field, on 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, so you should be careful to make sure you don't need the field elsewhere (eg. on another layout, or as a joining field).
Your layout should now look like this:
Finally, 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 no' 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 press Return
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
Click OK on the toolbox to return to browse mode, and you should find yourself editing a new invoice.
You can now type in the name of an existing customer (eg. 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 (ie. 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 DataPower 1 you could specify that a field should have a 'value list' associated with it, which are simply a set of values that you can choose from a menu rather than having to type in a value each time.
DataPower 2 takes 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 DataPower does the rest.
To put a value list on the Customer name field, press F5 to edit the layouts, 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. Click on OK on the toolbox to return to browse mode.
You'll now 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 we want to show a list of products that have been sold to the customer. Clearly we'll need a table containing a record for each product that is available for sale, but we 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 we would put the 'Quantity' field, which indicates how many of a particular item were sold.
All this will become clearer when we set up the relationships. For now, though, we need to create the tables:
Step 12: Create the products table
Choose Layout=>New table=> from the menu, and type in 'Products'. Create the following fields:
Set the price field's number format using the dialogue box on the menu at Fields=>Type=>Real=> so it has 2 decimal places and a pound symbol before the number. Click OK to finish with the dialogue, and then click OK in the toolbox to return to browse mode.
Download the Products CSV file onto your local disc, then drag it onto the DataPower toolbox to import it into the products table. 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
We now need to create the table that will contain the actual invoice items.
Choose Layout=>New table=> from the menu, and type in 'Items'. Create the following fields:
Invoice number (text)
Product code (text)
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 layout menu.
Click on the subview tool at the bottom-right of the top group of four icons in the toolbox:
and drag out a box where you want the subview to go.
The subview dialogue will now open, and if you click on the arrow to the right of '<unknown>' you'll see the following:
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 DataPower would not know how to link the existing Invoices table to the new table if you attached it to the subview.
Step 15: Adding Items and Products to the relationships
Choose 'Define relationships' from the menu to open the relationships window. Click on 'Add table' and add Items and Products to the window.
Double-click on the 'Code' field of the Products table to set it as the primary key.
Now drag the 'Product code' field of the Items table onto the 'Code' field of products, 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 the 'Invoice number' field of Invoices 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.
Step 16: Select Cascade Update and Delete for the Invoices/Items relationship
Because each invoice item 'belongs' to its associated invoice, we'd like to delete all the invoice items on an invoice when the invoice is deleted. Similarly, if the invoice number is changed, we'd like the invoice items to 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 dialog, and OK in the toolbox to exit the relationships window and return to edit layout mode.
Step 17: Attach the subview to the Items table
The subview dialogue should be open when we return from the relationships window, so you can now re-open the menu 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, if you open the Fields menu from the toolbox (click on the word 'Fields' in the toolbox), you can click on the field 'Items.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 we 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 as well, but first we'd like to put some of the products fields in there as well.
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 double-clicking on it and then close the dialogue.
You'll notice that the relationship between Items.Product Code and to Products.Code that we 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 the layout editor, and open the fields menu, you'll 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:
Your layout should now look like 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 field icon (just above 'cancel' in the toolbox)
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 (Acorn: use the left mouse button to select the first item, then the right button to add the second to the selection; PC/Mac: select the first item, then use Ctrl-click to add the second), then open the Field=>Type=>Real dialogue box from the menu.
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.
Now press Ctrl-F7 to set the two price fields to be right-justified, so our columns of prices will line up correctly.
Finally, click OK in the toolbox to return to browse mode!
Step 20: Adding invoice items
Phew! 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'll start to add another record when you get to the end of the current one.
Note 1: To add a new invoice, make sure the cursor isn't in the subview, then click on the Add 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
Now that you've entered some records into the subview, let's go one step further.
The most obvious omission from the invoice as it stands is that it doesn't add up the total of all items in the invoice.
To do this, all you have to do is to edit the layout, and then 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 called 'Summary' that has the following formula:
This will sum the 'Total' field for all records in the subview, ie. it will generate a different value for each record in the Invoices table (which is why it says "... For 'Invoices'")
[ Aside: Note that if you had created a main footer and then dragged the field into the footer, the formula would have read:
Click OK to return to browse mode, and you should see that your existing 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
I said earlier that we'd return to the topic of smart values lists - well, now seems a good time to do just that!
Unless you have an exceptionally good memory, you won't want to keep typing in product codes when filling in an invoice. So, to put a value list on the code field, press F5 to return to the layout editing mode, then:
Click on the 'Items.Code' field to select it
Press Ctrl-Shift-V to open the value list dialogue
Select 'Choose a value from the database'
Click OK to finish off the dialogue.
So far this is much like the earlier situation with the customer name field, except that we can add items within the subview, as well as altering existing items.
However, I did say that DataPower's value lists were "smart", which means that you can put a value list on almost any field and DataPower will make sense of it.
Try putting a value list on 'Products.Description' and 'Invoices.Invoice number', and you'll see what I mean.
Click OK to return to browse mode, and click in the Description field.
If you choose a new description, DataPower works out that in order to display the given description, it needs to alter the value of the Items.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 DataPower 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.
DataPower 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 - DataPower 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
So far we haven't achieved much more with DataPower 2 than we could have with a flat-file layout with DataPower 1, except that we didn't need to botch together the Items subview by copying a load of fields down the page and using lots of formula fields with LOOKUP in them which relate to a text file containing a tab-separated file version of the products table. Well, OK, maybe it was a bit easier!
Anyway, the point of doing all this with multiple tables is more than simply being able to see the latest version of the fields in the other tables. We can actually view the data from many different directions because it is properly structured.
Suppose we wanted to see which products a particular customer has bought. We can do that as follows:
Go to the Customers layout
Choose Layout=>New layout from the menu, type 'Customer orders' and press Return
Click on the subview tool and drag out a subview rectangle
Open the 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.
Click OK to return to browse mode.
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 could do another view to show the order for each product, and so on.
Step 24: Switching between layouts
Finally, a very useful feature of DataPower 2 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.
For example, if you switch to the 'Product orders' layout in the example Invoices file given on the web site, find a record with a customer on it, put the caret into one of the customer fields and then switch to the 'Customer orders' layout, you can see which other products the customer has ordered.
This feature extends to most layouts - DataPower 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 DataPower 2.