Queries can be created in the Mill7 reports program.
This option is only available with the module relation management.
Queries can be started from tree view in the report module but also from the custom menu.
This last option is only possible for queries which are not saved in a sub folder.
Query reports are not supported by the scheduler and cannot be scheduled.
Queries will be saved in the reports\custom folder unless at setting "Default folder of query files" an other path is entered.
These setting can be maintained through menu Maintenance - Settings -> Relations.
How it works
In all folders displayed under the option Relation management Query, an item <New query> is shown.
This item must be used to create a new item in the concerning folder.
The query will be saved in this location on the harddisk as well.
Alle queries are based upon clients, therefore the fist two fields shown in a query are the client code and name.
The first five (red) selections are pre determined, as shown in the example, and apply to the client selection.
Every field that is added to the query will also appear in the selection list and is colored black.
Creating a selection on these fields only affects the outcome of these fields.
Example:
In the query shown in the example above, a selection is made on the person in the file trust employees.
This selection will affect all fields before and after Person that come from the same file trust employees: Function and address.
Manager comes from the file Management and therefore will show all results for this file and field.
After manager the fields Function, Person and Address are repeated again to show that these fields are not affected by the selection.
This is because the fields are interrupted by the field manager from another file.
The result:
Code |
Name |
Function |
Person |
Person, Address |
Manager |
Function |
Person |
Person, Address |
1 |
relatie 1 |
|
|
|
|
|
|
|
2 |
relatie 2 |
|
|
|
|
Account managers |
Waard, E. van |
Prolkade 7 |
3 |
relatie 3 |
|
|
|
|
|
|
|
4 |
relatie 4 |
|
|
|
|
|
|
|
5 |
relatie 5 |
|
|
|
|
Account managers |
Waard, E. van |
Prolkade 7 |
6 |
relatie 6 |
|
|
|
|
|
|
|
7 |
relatie 7 |
|
|
|
|
Account managers |
Waard, E. van |
Prolkade 7 |
8 |
relatie 8 |
|
|
|
|
|
|
|
9 |
relatie 9 |
|
|
|
|
|
|
|
10 |
relatie 10 |
|
|
|
|
|
|
|
11 |
relatie 11 |
Account managers |
Boot, S |
prolkade 7 |
|
Account managers |
Waard, E. van |
Prolkade 7 |
11 |
relatie 11 |
|
|
|
|
Account managers |
Boot, S |
prolkade 7 |
12 |
relatie 12 |
|
|
|
|
|
|
|
13 |
relatie 13 |
|
|
|
|
Account managers |
Waard, E. van |
Prolkade 7 |
14 |
relatie 14 |
Account managers |
Boot, S |
prolkade 7 |
|
Account managers |
Boot, S |
prolkade 7 |
14 |
relatie 14 |
|
|
|
|
Account managers |
Waard, E. van |
Prolkade 7 |
Trust employees
All fields come from the fields that are used in the relation management module except for the field Function, <function code>.
These are programmed fields which makes it possible to show the different trust employees in different columns.
If the following codes exist for functions: AM, AAM, RM, the list of fields for the file trust employees will contain the following fields:
- Function AAM, Function AM and Function RM.
After a Function, <function code> some related fields can be entered like Address, Country etc.
These fields will contain the value that accompany the chosen function.
For example:
Code |
Name |
Function, AAM |
Function, AM |
Person, Address |
Function, RM |
Person, Address |
Millogic |
Millogic Software BV |
SB |
EVW |
Prolkade 7 |
VEE03 |
Prolkade veen 7 |
If a selection is made on one of the fields, it will affect the outcome of all the fields that are related.
If different functions are entered in the query, the different functions will be treated like they were different files.
This means that if a selection is entered for by example Function AM (or one of the related fields like address), this will not affect the outcome for Function RM.
Repeating unique data for a client.
Different files can be combined in a query, with a different number of records for a client.
When file Bookyear status is combined with Share holders, the output for a client can have 5 records for Bookyear status and 3 for Shareholders.
In line 4 and 5 of the output for this client, the data in the columns regarding Share holders will be empty.
When data is printed that only occurs once at a client, this data will be repeated every line.
If in the previous example the data of file General is combined with Bookyear status, the data of file general will be repeated all 5 lines.
This only works when this data is printed after the data of Bookyear status.
So always place data of a file that will only generate 1 record at the end of the query!!!
This also works if the result of a selection will only generate one record.
Rights
The rights for the relation management files in Mill7 are integrated in the query tool.
To view datafields in a query, read rights are necessary to display the results.
The read rights for Relation management are set per tabpage which means that all field on the same tabpage have the same rights.
As an extra option, a selection can be made on a specific file, and whether to print empty fields or fields with data.
This report cannot be saved.
Relation management extended: report all fields
In the Relation management query, management extended can be selected with which all the fields per company can be shown.
Fixed selections are;
- Client;
- Status;
- Account manager;
- Files;
- Show empty fields;
- Show fields with data;
- Only show required fields.
It is not possible to save different versions from this report.
Be careful
If all clients are selected, the output can be become too large for Excel.