Miner is a “query builder” for selecting particular data from the C9 database. A business like a motorcycle retail shop is a complicated business operation, and the information in your business interconnects in complex and subtle ways. Although c9 contains some 70 different reports and a number of other data access tools, such as graphical charting; sometimes more flexibility is required. Miner is a tool which allows you to extract information and the inter-relationship of information and it permits you to do things such as export it, print it or use it as a basis of a customer mailing list mail merge.

Queries built in miner can be saved, to be opened and reused at a later date.  The resultant retrieved data may be exported to a CSV export file which can be opened by programs such as Microsoft Excel or used within ”Miner” to create a report. Miner has a built in word processor which allows the operator to embed the retrieved data within as letter that may be posted.

Entities

Miner has a concept it calls "entities". An entity represents a type of information.  For example Customers is one entity; while Workshop jobs are another entity.

Consider the example of a customer entity.  You may have 800 customers tracked in c9.  Each customer tracks many things such as: name, postal address, customer code, account balance (for account customers).  Miner permits you to retrieve information on any given entity; this process is known as "querying" for information. The range of control covers:

• Filtering: Ability to determine what customers to fetch. i.e. you could fetch all 800, or only account customers, or only customers whose name starts with 'JOHN'.
• Field Selection: You might only be interested in customer's name and address. You might want every scrap of information you can get for a customer.
• Sorting: You may want the results organized by town then account balance.  Or as they come (unsorted)

Consider an example using Customer. In miner, click on Customers in the right hand pane and click on "<< Add" button.

This will pop up a window where you can specify your inputs for manipulating Filtering, Field Selection and Sorting.

To manipulate the result of your query, you key in special rules into the fields of interest. The rules control how information is returned. This is known as query by example.

Example: Filtering

If you want to return all names that start with JOHN you key in "JOHN*" .  This means match any name that starts with john. But do not worry about the end.  The '*' means match anything.  If you query for 'JOHN' it will only return records where it matches JOHN exactly.

Filtering also works on dates and numbers. For example '>' means everything greater than this. e.g. >10 means return everything greater than 10.  Ranges can be done with a '->'. e.g. '1.1.60->1.1.80' means everything between 1960 and 1980.

Example: Field selection

Every field that has a filter statement in it is returned as part of the query result.

You can use the '.' symbol to mean return this value but do not filter it.

For filter statements, if you put a '#' infront it means filter but do not return this value. e.g. on account balance you might say #>0  which means return all records which have not being paid but I do not care exactly how much that number is.

Example: Sorting

Sorting is specified in square brackets thus [ ]. The brackets must appear before the filter rule.  Example: [1]>0

[n] or [na] Sort the field in ascending order. can be a number from 1 to 9 and indicates the sort order. Fields with a sort of 1 are sorted before fields of a sort of 2, and so on.
[nd] Sort the field in descending order.
[ns] Sort the field ignoring space. i.e. 'CBX' is before 'CB Z' when you ignore space, but other way round if space is important
[nw] Sort the field field based on word/number boundaries.  i.e.  'CBR 750' is before CBR-1000'

Examples
[3a] Ascending sort. Sort after sort fields 1 and 2 have been sorted
[1d]Descending sort. Sort this field first.
[1dw]  Descending sort based on word/number boundaries.

Full list of Query By Example rules follows:

[n] or [na] Sort the field in ascending order. can be a number from 1 to 9 and indicates the sort order. Fields with a sort of 1 are sorted before fields of a sort of 2, and so on.
[nd] Sort the field in descending order.

[3a] Ascending sort. Sort after sort fields 1 and 2 have been sorted
[1d]Descending sort. Sort this field first.

List of filter/selection and sorting rules.
A query must be in format:  <sort rule> <filter or selection rule>

Match if the field is in between n1 and n2 inclusive

Rule	Description
. or *	Always select this field, no filtering
!aaa or /aaa	Match if the rule aaa doesn't match
..	Match if the field is not blank
!..	Match blank fields
aaa	Match if the field is aaa (case insensitive)
*aaa or ..aaa	Match if the field ends in aaa
aaa* or aaa..	Match if the field starts with aaa
*aaa* or ..aaa..	Match if the field contains aaa
a?a	? means any letter. so aaa will match so will aba.
Numeric/Date Rules
nnn or =nnn	Match if the field is nnn (numeric).
>nnn	Match if the field is greater than nnn
>=nnn	Match if the field is greater or equal to nnn
<nnn	Match if the field is less than nnn
<=nnn	Match if the field is less than or equal to nnn
n1->n2	Match values between n1 and n2, inclusive. e.g. 10->15 means any of the following: 10,11,12,13,14,15

Date Format

Dates are treated as numerics. Date format is in dd.mm.yyyy.
If the year is omitted, then it defaults to the current year. If the month is omitted, then it defaults to the current month.
2 digit years are treated as four digit years, within the period of 80 years in the past and 20 years into the future. Thus, if the current year is 2003, 50 is treated as 1950, and 10 is treated as 2010.
Some dates in C9 are represented in mm.yyyy format (such as a unit build date). These fields do not have the dd component in their date.

Relationships

Miner has a concept it calls "relationships". A customer can own multiple units.  An invoice has many line items. An invoice can have a customer. A Relationship defines how different types of entities can be connected to one another. Relationships are bi-directional. i.e. a customer has multiple invoices and an invoice has a single customer.

In miner the relationships available to a given entity are displayed when you select the entity in miner screen on the left. When selected the available relationships are displayed in the right hand list.

So on selecting Customer on the left. The following relationships are displayed on the right:

• Owned Units
• Spares Invoices
• Workshop Jobs

You can now click on add and add filtering information to expand the details of your information query. The infromation returned on owned units, will not be on every unit in the system. The query will be limited to units that match customer records that match the "parent" customer query.

You can control how the relationship between customer and unit is used to determine what records that come back. After adding the entity you see to bottom/middle right of Miner a radio button list which allows you to control how information is retrieved across the relationship.

The options are:

• Select All Records
  Return all records where there is a customer and a one or more units. For every unit a row of data will be returned. So customer information will potentially appear multiple times. If a customer has no units then NO information is displayed
• Select One Record only
  Will only return one customer + one unit.  If a customer has multiple units, only one unit record will be returned.
• Select None or All Records
  Is similar to select all records except that if the customer has no units, then the customer will be returned once. But unit information for that record will be empty
• Select Parent if no records
  This only returns customer information. The unit information is not return. But it will only return customer information if there are no units.  i.e. only show me customers who do not have a unit.
• Select Parent if one or more records
  This only returns customer information. The unit information is not return. But it will only return customer information if there are any units.  i.e. only show me customers who have one or more units
• Select Parent if more than one record
  This only returns customer information. The unit information is not return. But it will only return customer information if there are 2 or more units.  i.e. only show me customers who have two or more units