This document is intended to provide a basic guide to the database schema used in ServiceLedger 5.0 and above, specifically for use with crystal reports and other SQL reporting tools.
The guide assumes that you have a basic knowledge of database design and access. For instance: you should know what a table is, in the context of a database, and what a one-to-many relationship is. You should also have some method of accessing our database, via either a query tool such as Microsoft Query Analyzer or OSQL, or a reporting tool such as Crystal Reports. Information on accessing our database through Crystal is available a little later in the document. Finally, you should be familiar with filtering records, either through a where clause or the Select Expert in Crystal Reports.
An excellent reference for learning databases, if any of the ideas and concepts discussed here are unfamiliar, is currently available at: http://www.tomjewett.com/dbdesign/dbdesign.php
The "Basic UML & SQL" section provides a good introduction to key concepts in about a dozen pages, and is a good starting point for any foray into database tables and fields.
Most data in ServiceLedger is organized into tables. Some common tables that you might be interested in looking at are the 'client' table, which stores your customers, or the 'ticket' table, which holds all of your work orders.
Showing data from a single table is usually fairly straightforward: simply include that table in your SELECT statement, when using SQL, or choose the table from the list in your Crystal Reports Database Expert. A basic query or report will show all or a selected subset of fields from that table, and give you an idea of what fields in the table are associated with what parts of ServiceLedger.
Usually, you will want to show data from several related tables together in one report or query. For example, on the 'ticket' table, ServiceLedger stores basic information about jobs, such as the service request, date created, first and last scheduled date, and so on. However, to look at the invoice charges on that work order, you need to link in a second table: 'ticket_item'. This detail table contains the fields relevant to each of the line items on the work order, such as item description, quantity, price, and cost. For many reports, you will want fields from both the parent table (such as 'ticket') and the detail table ('ticket_item') on the same report.
In other cases, you will have related tables that aren't necessarily details, but still represent data stored elsewhere. For example, to show out information about the customer a job is for, you will need both the 'ticket' table AND the 'client' table, even though both are separate records. A report that shows a list of jobs and which customers they were for, for example, would take the work order number from the 'ticket' table, and the customer name from the 'client' table, linking between the two on the shared 'client_id' field. The next section discusses these kinds of relationships and how link these tables together to show this data.
Tables in ServiceLedger are generally linked on integer id references, rather than names or values. While this linking method can be confusing at first, it provides better retrieval speed and encourages accurate queries. Because of this design, most queries or reports need to link to a large number of tables.
By default, Crystal Reports and many other tools will try to link tables that you've added on their own. If it is done by field name, as is the default case in Crystal, this linking is almost always incorrect. Automatic linking by key is more accurate in our context, but should still be verified manually.
The general rule for table linking is that a kind of record that refers to another kind of record will have a linking field present for that relationship. The name of the linking field is almost always the table name of the linked table followed by '_id'. For instance: Work Orders (table name 'ticket') are linked to their Accounts (table name 'client') on a field called 'ticket.client_id', which links back to 'client.client_id'
Relationships between different parts of the software are generally exactly as above--two base table names will be involved, and the "owned" record will have a linking field: each account has many work orders, so the work order has a link to its account; invoices usually come from work orders, so each invoice has a link to its work order; and so on.
When multiple tables exist within the same component, however, the detail table will in addition to having a linking field, as described above, also be named with its master table as a prefix. Examples of such tables include account locations ('client_location', from 'client'), invoice charges on a work order ('ticket_item', from 'ticket'), and schedule entries on a work order ('ticket_resource', from 'ticket'). When you see tables with such names, you can generally assume that they are meant specifically to be linked to their parent tables. Using these detail tables without such a link is seldom useful.
There are two notable exceptions to this rule: First, names beginning in 'company_' cannot be linked to the 'company' table. The prefix, in this case, means that these tables are generic and used throughout the application. Second, tables with names ending in '_type', '_status', '_category', and similar are linked in the reverse manner from that described above. Work orders, for instance, are linked to their Type with 'ticket.type_id' to 'ticket_type.type_id'
A final kind of link, seen rarely, is when a linking table is used for a many-to-many relationship. An example of this kind of table is 'project_plan_phase_tasks', which keeps track of which project phases contain which project tasks. This table contains only two real fields, each of which is a link to another table ('project_plan_phase' and 'project_plan_task'). This table would be used to get a result set containing a list of tasks within all phases, and could be filtered to include only certain phases or tasks.
When creating a Crystal Report that works with a ServiceLedger database, you have two options to begin with. The first, much easier method, is to start with an existing standard report and build it towards your goal. If you want, you can also build a new report from scratch. In either case, the first place you'll be spending time is in the Database Expert.
This window contains two tabs. On the left tab, 'Data', you should either (1) if working with an existing report, preview the report before entering the Database Expert, and then choose ServiceLedger from 'Current Connections', or (2) only if this is a completely new report, select 'Make New Connection' from 'ODBC (RDO)' and choose the ServiceLedger ODBC connection. Once you've done this, you should see a list containing tables, views, and stored procedures under the 'Current Connections' folder. Before moving to the next tab, you should expand the 'Tables' list and add any tables you will want to your report.
On the right tab, 'Links', you should make sure that the table links are correct before continuing--the defaults are almost always wrong. Linking is described in detail above, but an example of some basic linking for a work order report is provided and explained below:
Note how the 'item' table is linked to 'ticket_item', which is then in turn linked to 'ticket'. While it is possible to link the 'item' table to 'client', for instance, your report will be examining the items listed on a work order. Thus, the only link for items that makes sense is to 'ticket'. This can't be done directly, since invoice items on a work order are example of a one-to-many relationship, so the 'ticket_item' detail table is used to correlate a number of items with a work order.
Also, note how the 'client' and 'ticket_...' tables are linked to the 'ticket' table--From a reporting context, these tables provide extra information about the work order, even though in fact the 'ticket' table is dependant on these tables. It is helpful to think about what basic information your report should include, add those tables, and then later add and link details as you need them.
The next place you will probably need to go is the Grouping Expert. This allows you to have a master-detail relationship within your report, just as such relationships exist in ServiceLedger. In a work order report, for example, you will almost certainly want to group your invoice items by work order. This is done by creating a grouping on the master table's id field: 'ticket.ticket_id' in this case.
This section lists some commonly used table names and links, as well as their representations in the ServiceLedger application.
A detailed image of database relationships is also available. Your browser may scale it to fit on the page, making it illegible. If so, you should adjust your browser's image settings.
