Index, Pick Items and File Linking

The first file displayed is the main file for the application. In the example being used, this is the ACCOUNTS file.

Format

If you have defined a prime index for the file then you may here specify a restriction on the format of the data to be used in the prime index field.

At the bottom of the screen, some example formats are shown. The index format may be partitioned, to make it easy to maintain a standard within your file. Once you have decided on your preferred format, all prime index entries in this file must conform to it. Use "A” to represent each alphabetic character (A-Z), "9” to represent each numeric character (0-9), and "U” to represent any value at all.

For example, if you wish to use a code that consists of two alpha characters followed by four numeric digits, then two more alpha characters, and with "/” as the separator, then the format should be specified as AA/9999/AA

Common index formats include "AAA999”, "A999”, "A/9999”, and "9999”. You choose whether you want to have separators or not. When adding new records, the program checks that the index you enter is in the format specified here. An invalid format is not acceptable. If you choose a wholly alphabetic code, such as "AAAAAA”, note that all six characters must be used for every item. You can solve this problem by defining the format as "UUUUUU” instead, which allows up to six alphanumeric characters — a space is considered a character for this purpose.

Leading zeros need not be typed for the numeric parts of index formats — the program inserts them for you. So, if the index format is "A999” and "G6” is entered, the program converts this to "G006”

Prime index greater than 16 characters

You may choose up to ten items from all those in the file which will be displayed when using the application and the <F4-Select> facility to find the record you want.The items specified here are also the default items shown when using a screen ‘Pick’ action.

You do not include the prime index as this will be automatically included, so it is unnecessary to specify it again here.

Key in the numbers of the data items you require to be shown when you use the <F4-Select> facility.

L.I.F.O. Sequence

This option allows you to see records in a last-in-first-out sequence when you use the <F4-Select> key to list records.

Ignore Sel?

Short for ‘Ignore from Selection’ this option allows you to hide records from selection – for example, if using the Sales Ledger Accounts file in the application you may want to prevent selection of prospect accounts.

Item – select the data item you wish to test.

Eq/Neq – select whether you are to hide the records when the record equals or doesn’t equal the value entered here (see below for ‘S’ usage).

Value – enter the value to test. If the condition is met then the record is hidden from display/selection

S – Split Codes

Here the system must be told what type of linking is to be used between the file currently under consideration and the other (maximum seven) files to be used in this application.

While your cursor is in this area of the screen, the bottom of the screen shows the possible link types:

Link type: D - Detail, L – Lookup, R- Reference

Naturally you cannot link the file to itself, so N/A is shown against the file with which you are dealing. Against each other file name, you need only key in the first letter of the type required, D or L as appropriate (or leave it blank if there is no link).

A detail file is a sub-file which can contain multiple records which correspond to a single main file record; a look-up file allows a record in it to be accessed via a key (an item which exists in both the current file and the look-up file) to extract details.The record in the look-up file should be unique for that key; effectively the key is the prime index of the look-up file.A stock file is a look-up file when entering orders; using the stock code as a key, prices and descriptions may be extracted from the stock file into the order detail file.

You would not actually define a relationship as R – Reference – instead this indicates a ‘reverse’ lookup relationship. For instance, if you define file 1 as having a look-up relationship to file 2, then when you view the profiles for File 2 this displays that it is a reference file for file 1. Similarly, another marker is ‘M – Master’ where if File 1 has a detail relationship with File 3, then when you view the profiles for File 3, this displays that File 1 is it’s Master file.

When you have set the relationship for each file as appropriate you will be prompted

If you have defined any relationships as DETAIL or LOOKUP then further parameter screens are displayed allowing you to configure these relationships. If you have set the data file profiles for the CONSTANT database then the profiles for this database are displayed over further parameter screens.If neither of these options applies you are returned to the list of databases.

Defining a Master/Detail Relationship

Two screens are displayed for each detail file to which you have specified that your file links to.The first screen requires you to enter the items which enable the two files to link successfully, and the second screen permits automatic copying from the main file to the detail file whenever a new detail file record is added.

The first five parameter inputs ask for data items in the main file which define the link to the detail file. The data items that are in the main file are listed on the right but you can use the <F4-Select> key to search if required.

Main File Items: First Detail

Main File Items: Last Detail

Each record in the main file must contain two data items which automatically maintain the record numbers of the first and last records in the detail file which relate to this main file record. The data items must be Y-type and sized equal (or larger) to the number of records in the detail file – i.e. if you have reserved 100,000 records in the detail file then these items must be Y(6) or larger.

Main File Items: Record Count

Optionally, you may want to keep a count in the main file record of the number of detail records which belong to it. The data item should be an N-type or Y-type.

Main File Items: Accumulated Total

Optionally, you may want to accumulate values from the detail records into the main file record.The item to be accumulated is set within the detail file items; you set here the item to accumulate into. Data item type should be a K, L or M-type (consistent with the number of decimal places in the detail item to be accumulated).

Main File Items: Entry Total

Optionally, you can record entry numbers on each of the detail records – the first detail is ‘1’, the second detail is ‘2’ and so on. This item contains the entry number of the last detail added. The data item for this would be an N-type. Note that although similar to the record count, the entry total would not be affected by the deletion of a detail record whereas the record count would.

The next six items refer to items in the detail file. As the cursor reaches these parameters the data items listed on the right change to the detail file for review, again you can use <F4-Select> to search if required.

Detail File Items: Next Pointer

Each record in the detail file must contain a data item which is automatically maintained as the record number of the next record in the detail file that belongs to the main file record. The data item must be a Y-type and sized equal (or larger) to the number or records in the detail file.

Detail File Items: Prev Pointer

Optionally, if using a database you have created as a detail file, you may define a previous pointer data item. Again, this is automatically maintained as the record number of the previous record in the detail file that belongs to the main file record. The data item, if used, must be a Y-type and sized equal (or larger) to the number of records in the detail file. Setting this item allows you to configure screens and reports which show the detail records to list in LIFO (last-in, first out) order.

Application databases, such as the sales ledger etc., generally do not have a previous pointer field configured so if defining an application to report/maintain application databases this item will not be set.

Detail File Items: Main Index Code

The main index code must be present on each detail record, since this specifies which main file record any given detail record belongs to. This is usually the value held in the prime-index of the main file – for example on the sales ledger transaction file this would be the ACCOUNT data item.The item type and size must equal that of the prime-index of the main file.

If your main file has no prime-index then you would set this item to be the same as the next item.

Detail File Items: Main Code RecNo

The ‘Main Code RecNo’ contains the record number of the main file record that this detail belongs to (it is maintained automatically by the system when set). The data item must be a Y-type and sized equal (or larger) to the number of records in the main file – i.e. if 50,000 records in the main file then this item must be Y(5) or larger.

Detail File Items: Accumulation Item

Optionally, if you wish to maintain an accumulation value on the main file record you need to set here the item to be accumulated.

Detail File Items: Entry Number

Optionally, if recording entry numbers against detail records then you need to reference the (N-type) item on the detail file to be updated with the entry number. See ‘Entry Total’ above.

After entering the entry total, if appropriate, you are asked to confirm the above details are OK before a second screen is displayed allowing you to define up to five copy items from the main file to the detail file.

Although screens and reports can display information from the main file it can be useful to copy information from the main file to the detail file where you wish to change those details for specific detail records. For example, a salesman code perhaps when entering contact details, copying the default contact when adding a record but amending if appropriate – or for selection criteria purposes.

You enter the data items in the left-hand column of the items on the main file you wish to copy and enter the data items in the right-hand column of the items on the detail file you wish to update. The data items list on the right of the screen is updated to reflect the database to copy from/to as appropriate although <F4-select> is again available.

Defining a Lookup Relationship

One screen is displayed for each lookup file to which you have specified that your file links to.This screen requires you to enter the items which enable the two files to link successfully, and allows you to define copy items from the lookup file.

Lookup Item

For each lookup file specified, a data item in this file which corresponds to the lookup file must be specified. Usually this item corresponds to the prime-index of the lookup file – so if looking up to a stock file you need to reference the item on this file that will contain the stock code. You can lookup to a non-prime indexed file in which case this item must be referenced to an item that will contain the record number of the lookup record.

Mandatory Link

Set if the link must be present – in this case a record cannot be added to the file unless you enter a value in the lookup item that exists in the lookup file (i.e. on the stock lookup example you must enter a stock code that exists, and you cannot leave the lookup item blank). Leaving this item blank means that you do not have to enter the lookup item but if you do then the value in the lookup item still must exist in the lookup file (you can leave the stock code blank but if you enter a code it must exist in the stock file).

Allow Create New

Set if the operator is to be allowed to create a new lookup record if the lookup code entered by the operator in a screen does not exist in the lookup file (i.e. on the stock lookup example if the operator enters a stock code that doesn’t exist do you want to create that stock record). If you create a new lookup record you can only enter the description (assumed to be item 2 of the lookup file) on the lookup record. Leaving this item blank means the operator cannot create a new lookup record.

Copy Items

Although screens and reports can display information from the lookup file it can be useful to copy information from the main file to the detail file where you wish to change those details for specific detail records – a salesman code perhaps when entering contact details, copying the default contact when adding a record but amending if appropriate – or for selection criteria purposes.

You enter the data items in the left-hand column of the items on the main file you wish to copy and enter the data items in the right-hand column of the items on the detail file you wish to update. The data items list on the right of the screen is updated to reflect the database to copy from/to as appropriate although <F4-select> is again available.

The Force option relates to whether you wish to re-action the copy items on update of a record when the lookup value is unchanged. In our stock lookup example you may be copying the price from the stock file. If you update a record but do not change the stock code the price would not be recopied unless the ‘force’ option was set (if you did change the stock code it would automatically be recopied).

Ignore ‘Sel’ Records

This option allows you to filter out records from the lookup file so that when adding records to the file you cannot select these items. For example, in our stock lookup scenario you may want to omit stock records which have a zero physical quantity. Setting this item gives a pop-up form to allow entry of the filter criteria.

 Item – select the data item you wish to test.

Eq/Neq – select whether you are to hide the records when the record equals or doesn’t equal the value entered here.

Value – enter the value to test. If the condition is met then the record is hidden from display/selection.

Constant Database Profiles

The constant database is a one record file that can be used to hold values required by the application – for example it can hold the system date or, if the application is linked to a company, the current sales ledger period. This can be useful, amongst other things, for selection criteria.

The first screen allows you to reference items on the constant file that hold the date, day of the week or user-id.

Item Description

System Date (D) Referenced to a D/E-type date field this updates today’s date to the item on opening of the ProFiler application.

System Date (Full) Referenced to an X-type field (usually 20 characters) this updates today’s date to the item as text – i.e. 24 September 2006.

Day of Week (U/C) Referenced to a X-type field (usually 10 characters) this updates today’s day to the item as uppercase text – i.e. THURSDAY.

Day of Week (L/c) Referenced to an X-type field (usually 10 characters) this updates today’s day to the items as text – i.e. Thursday.

User-Id Referenced to an X-type field (usually 6 characters) this updates the user-id of the user who opens the application. If this item is in use then typically the ProFiler application would need to be restricted to one user at a time.

File Set Id This refers to the File number used when using the multiple file sets, i.e. if configure a payroll enquiry with the created backup files you could use the file set option to access the file set for a particular period. This item would be an X-type field of two characters.

If linked to the application company a further six screens allow you to reference items on the constant file to be updated from the company files. Items are automatically updated on open of the ProFiler application or change of company within the application.

Link to Company (1/6) – Company Details

Company ID Updates the current active company into an X(3) field.

Company Name Updates the current company name, as set within the menu option ‘Installation / Set Company Details’, into a text field.

Company Address 1-5 Updates the current company address into the set text fields.

Telephone Updates the current company telephone number into the set text field.

Fax / Telex Updates the current company fax number into the set text field.

Tax Reg No Updates the current company’s VAT registration number into the set text field.

Other The ‘Set Company Details’ allows you to define one ‘other’ item for the company – perhaps an email address – this text value would be updated into the text item referenced here.

Link to Company (2/6) – VAT Rates

Tax Rate 1 – 10 Updates the tax rates from the VAT table of the current company – i.e. 17.50% etc.

Link to Company (3/6) – Accounting Ledger Periods

Periods in Year Updated to an N-type field this is the number of periods in the current accounting calendar (usually 12)

Sales: Per No Updated to an N-type field this is the current period number for the sales ledger.

Sales: Start/End Date Updated to D/E-type date fields this updates the period date range for the current sales ledger period.

Purch: Per No Updated to an N-type field this is the current period number for the purchase ledger.

Purch: Start/End Date Updated to D/E-type date fields this updates the period date range for the current purchase ledger period.

Stock: Per No Updated to an N-type field this is the current period number for the sales ledger.

Stock: Week No Stock can be configured to split a period into (upto) 6 separate weeks. If so configured then the N-type item referenced here would be updated with the current stock week no.

Stock: Start/End Date Updated to D/E-type date fields this updates the period date range for the current sales ledger period.

Nom/Cash: Per No Updated to an N-type field this is the current period number for the nominal ledger and cashbook.

Link to Company (4/6) – Accounting Periods

Per No (!C9 usage) Premier only. In Premier calculated fields allow you to nominate a range of items – say Turnover-1 through Turnover-12 and use a !C9 function to extract the value relating to the current period. This item, usually cross-referenced against a period-number on the previous screen, tells the ProFiler application which period number to use for the calculation.

Period Names Start This option asks you to reference the first in a series of 12/13 text fields into which the current month period will be updated.The next n fields will then be updated with the subsequent/previous month/period.

Period Names Type Set how the series of text fields are to be updated:

1 – Updates with JAN/FEB/MAR/etc. Only applicable if using 12 periods related to calendar months.

2 – Updates with JANUARY/FEBRUARY/MARCH/etc.Again only applicable if using 12 periods relating to calendar months.

3 – Updates with 01/02/03. Updates with the month number of the current period.Again only applicable if using 12 periods relating to calendar months.

4 – Updates with P01/P02/etc. This updates the text field with the period number in text format prefixed with a ‘P’.

Period Names FWD Set if fields 2 onwards are to work forward through the accounting calendar – January / February / March / etc. leave blank to go backwards – January / December / November / etc.

Link to Company (5/6) – Job Costing

Heading From / To The Job Costing system allows you to define up to 30 cost category headings (subject to authority level). This option asks you to reference the first and last in a series of n text fields which will be updated with the cost category analysis headings on opening the ProFiler application.

Link to Company (6/6) – Payroll

Current Period Updates the current payroll period into an N-type field.

O/T-Rates From / To Payroll allows up-to six overtime rates to be configured with factors as set in the Payroll System Profiles – time-and-a-half / double-time / etc. This option allows you to reference the first and last in a series of n M-type data items which will be updated with the overtime-rate factors when opening the ProFiler application. Not applicable if using Payroll Plus.

O/T-Title From / To This option allows to you reference the first and last in a series of n text fields which will be updated with the overtime rate titles. Again not applicable if using Payroll Plus.

SW Title From / To Payroll allows up-to twenty switches – extra payments and deductions to the employee pay. This option allows you to reference the first and last in a series of ntext fields which will be updated with the switch payment titles. Again not applicable if using Payroll Plus.