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”

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.

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

Occasionally your index format is a combination of codes from two other databases – for example, the discount matrix file has a code format which is a combination of the customer account code and the stock code.The ‘S’ split option configures the index format so that it prompts for the code it two parts – the first part looking up (in this example) to the customer file and the second part to the stock file.

To use the ‘Discount Matrix’ style lookup you would apply the ‘Ignore Sel’ option to the matrix file. Select the prime-index of the database as the item, the Eq/Neq input would be ‘S’ for split codes. For the value you would enter the database number in the application of the two files that are to be used to make up the code. For example if your files were

1 – Discount Matrix

2 – Sales Accounts

3 – Stock Records

then the value would be ‘2/3’

An alternative application for this feature would be where using location stock – where the location code prefixes the stock code.To use the ‘Location Stock’ lookup you would apply the ‘Ignore Sel’ option to the Stock file. Select the stock code as the item, the Eq/Neq input would again be ‘S’. For the value you would enter the database number in the application of the location file, followed by the stock file and then ‘L’. For example if your files were

1 – Stock File

2 – Location File

then the value would be ‘2/1L’

Links to Other Data Files

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:

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

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.

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.

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).

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.

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.

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.

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.

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.

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

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.

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.

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).

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.

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.

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).

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

Article ID: 1187
Created On: Thu, Jun 28, 2012 at 2:22 PM
Last Updated On: Tue, Jun 20, 2023 at 5:38 PM

Online URL: https://kb.datafile.co.uk/article/index-pick-items-and-file-linking-1187.html