Designing a Tree Index
Creating a point & click structure.

The tree index routines point to a base directory.

The indexing program will access the base directory and will ascertain if it contains an INI file. If so, there must be a sub-folder of the same name. If an INI file exists, the SQL parameters in this INI file will be executed and the results displayed. The sub-folder will not be displayed, but child directories/files in this sub-folder will be used as the next level.

If the sub-directory does not contain an INI file, the program will access a directory and ascertain if it contains a TXT file. If so, this will be read and the contents displayed. The sub-folder will not be displayed, but child directories/files in this sub-folder will be used as the next level.

If the directory does not contain an INI file or a TXT file then the sub-folders will be used as the next level.

A GRP file may also exist in a directory. If one exists it will be accessed to obtain the index folder for documents in this folder. The GRP file must have the same name as the holding directory: e.g. if the directory is called Bank Accounts then the folder file must be called Bank Accounts.grp.

INI file content
DBACCESS Section

DBT specifies the database type and may be one of the following: ORACLE, MSACCESS, E4DM, ECS, or MSSQLSERVER

DBQ specifies the database to query.

DBN specifies the database name.

SRVR specifies the name of server on which the database resides.

UID specifies the user logon

PID specifies the password

SQL specifies the command to execute. E.g.
SQL=SELECT ID,CLREF,CLNAME,CLDOB FROM CLIENT

WHERE specifies the WHERE clause of the SQL command. E.g.
WHERE=WHERE CLIENT='%client%'
The value between the % characters specifies the parent folder.

ORDER specifies the ORDER of the SQL command. E.g.
ORDER=ORDER BY CLIENT

KEY specifies the key field for each record from the SQL command. E.g.
KEY=CLIENT

UNIQUEKEY specifies the field from the SQL query to enter into the unique key field of the e4DM database. E.g.
UNIQUEKEY=ID

ONLYIF reduces the scope of the query based on the WHERE field specified above. E.g.
ONLYIF=FRED
will specify that our example query will only be executed if %client%=FRED

DISPLAY specifies the field names from the SQL query to show on the screen. These are separated by a comma. A maximum of 5 may be set. E.g.
DISPLAY=CLNAME
DISPLAY1=CLDOB
Will display CLNAME, CLNAME

INDEX specifies the key to index by - if blank then the DISPLAY section is used. If INDEX=CLNAME then CLNAME is used as the key. If blank or missing CLNAME, CLNAME is used as the key in our example. A maximum of 5 INDEX levels may be set. Each new index level will take an index slot in the Point and Click Index Templates. E.g.
INDEX=CLNAME
INDEX1=CLDOB

RESTRICTIONS Section
Restrictions allow partial searches of the database. If restrictions are set a pre-index screen will prompt for partial keys and only matching items will be displayed. E.g.
[RESTRICTIONS]
1=CLIENT like 'CLIENT REFERENCE'
2=PROPERTYREF like 'PROPERTY REFERENCE'
3=UNITDESC like 'UNIT DESCRIPTION'
4=TENANTREF like 'TENANT REFERENCE'
The value between the % characters specifies the prompt text from the pre-index screen.

ASK=ONCE
The restrictions will only be prompted for the first time the folder is clicked when indexing.
ASK=ALWAYS
The restrictions will be prompted for every time the folder is clicked when indexing.

CONFIG Section
Config allows the tree expansion to continue, or cease, based on the values set. If config options are set the tree will develop as long as the conditions apply. E.g.
[CONFIG]
ONLYIFPARENT=Categories=Claims
ONLYIFPARENT1=Categories=Clients
The tree will continue to develop if the previous 'Categories' level was Claims or Clients.
PREVIOUSDISPLAYS=2
Two DISPLAY fields have been displayed in the tree before the ONLYIFPARENT checks begin. The search through the nodes is by folder or input fields, but if fields are displayed then they increase the node structure. This field accounts for their presence.

FLD File Contents
A Document Folder to assign to the indexed item may be specified for a specific tree value. Create a .FLD file at the correct place within the index tree. For example, management agreements.fld may contain. E.g.
[INDEX FOLDERS]
DOCUMENT FOLDER= AGREEMENTS

A Document Template to assign to the indexed item may be specified for a specific tree value. Create a .FLD file at the correct place within the index tree. For example, management agreements.fld may contain. E.g.
[INDEX TEMPLATE]
DOCUMENT TEMPLATE= ACCOUNTS

If a template is specified then the DOCUMENT FOLDER in the [INDEX TEMPLATE] section MUST BE SPECIFIED.

A sample INI for MS ACCESS:

[DBACCESS]
#dbt=database type (ORACLE,MSACCESS,MSSQLSERVER,E4DM,ECS)
DBT=MSACCESS

#DBQ=Database to query
DBQ=C:and SettingsDocuments.mdb

#SQL=sequel query - %client% must match a previous level folder name
#the %client% will be substituted as the item clicked at the previous stage
# note the use of the ' character to denote character field
SQL=SELECT * FROM PROPERTY
WHERE=WHERE CLIENTID=%client%

#display=field name from SQL query to show on screen
DISPLAY=Property
DISPLAY1=PropertyAddress1

#key=field name from SQL query to store for later sub queries
KEY=id

A sample INI for MS SQLSERVER:

[DBACCESS]
#dbt=database type (ORACLE,MSACCESS,MSSQLSERVER,E4DM,ECS)
DBT= MSSQLSERVER

#dbn=database name
DBN=SFTCL

#srvr=name of server on which the db server resides
SRVR=PURDY

#uid=user id
UID=User01

#pid=password
PID=abcde

#SQL=sequel query - %client% must match a previous level folder name
#the %client% will be substituted as the item clicked at the previous stage
# note the use of the ' character to denote character field
SQL=SELECT * FROM CLIENT
WHERE=WHERE CLIENT='%client%'

#display=field name from SQL query to show on screen
DISPLAY=CLName
DISPLAY1=CLDOB

#key=field name from SQL query to store for later sub queries
KEY=id

A sample INI for ORACLE:

[DBACCESS]
#dbt=database type (ORACLE,MSACCESS,MSSQLSERVER,E4DM,ECS)
DBT= ORACLE

#dbn=database name
DBN=SFTCL

#dbq= Database to query
DBQ=SFTCL

#srvr=name of server on which the db server resides
SRVR=PURDY

#uid=user id
UID=User01

#pid=password
PID=abcde

#SQL=sequel query - %client% must match a previous level folder name
#the %client% will be substituted as the item clicked at the previous stage
# note the use of the ' character to denote character field
SQL=SELECT * FROM CLIENT
WHERE=WHERE CLIENT='%client%'

#display=field name from SQL query to show on screen separated by a comma (max of 5)
DISPLAY=CLName
DISPLAY1=CLDOB

#key=field name from SQL query to store for later sub queries
KEY=id

#Driver specifies the name of the ODBC driver
DRIVER=Oracle ODBC Driver

#Only if specifies that this query will only be executed if %client%='UNITS'
ONLYIF=UNITS