Included lists are a common feature of input screens. They are used in situations where it is necessary to list many pieces of similar information relating to a single main record.
In a Product record, for instance, Suppliers… is an included list (each Product can have several Suppliers). Similarly, the Invoice screen has an items… included list. In theory, at least, each included list can contain a virtually limitless number of items. Note, however, that records with a large number of included items will take slightly longer to appear on screen.
Some records, such as Companies, have a tabbed area, where each tab gives access to a different included list showing items related to that company - Contacts, Sales Orders, and so on, depending on what volumes the user has access to.
Included lists are identifiable by their grid-like appearance and the scroll bars on the right. Major included lists also have a button (known as the [Editable/Selectable mode] button) in the top left-hand corner:
There are three main ways in which you can work with an included list. You can add items to it, take items away and modify existing items.
To add an item to an included list, simply click the [+] button in the Buttons Palette (see below for a detailed description of this palette), or press the <Command-n> (Macintosh) or (Windows) key combination. A flashing cursor appears in the left-most field in a new line in the list. You can now enter details in the same way as for the main screen. <Tab>, <Return>, and function in the same way. When you get to the end of the line, click [+] or press <Command-n>/ again if it is necessary to add another line. Some included lists do not have an [Editable/Selectable mode] button. To add an item to such lists, you should first click the list to make it active before using the [+] button. The most important of such included lists is on the Transaction Batch screen.
There are two methods by which you can sort the items in an included list. They can only be employed in included lists which have an [Editable/Selectable mode] button.
The first method is to simply click on the heading of the column by which you wish to sort. The heading will be underlined to indicate that it is the sort criterion. This method is very simple, but can only offer a single level sort. It is only available for included lists that have the [Selectable/Editable mode] button in the top left-hand corner.
If you perform a sort and then add a new item, this will appear at the bottom of the list. Likewise, if you modify a value in the column by which the list is sorted, it does not re-sort automatically. Click in the heading area again for this to happen.
If you are in Selectable Mode and the included list is currently selected (ie at least one line is highlighted), and if one of the columns is sorted, you can type the first few letters of a field value to make the list scroll to display the first matching record. For example, if the Contacts list is Sorted by Surname, typing ‘S’ might scroll to a Contact called ‘Smith’. Alternatively, you could use the up and down arrow keys.
The [Sort] button in the Buttons Palette can be used in some circumstances to sort the items in an included list. This button does offer the possibility of multi-level sorts. Full details can be found in the Data Input Buttons section.
On the Sales Orders screen, you can also sort the items manually. In Selectable mode only, click anywhere within the line you wish to move and drag it vertically to its new position. The sort order of an included list is not saved when the record is saved. The only exception is the Sales Orders screen which will retain sort orders resulting from use of the [Sort] button or the manual sorting method.
It is possible for each member of Personnel to have their own included list settings for each screen (provided the included list in question has an [Editable/Selectable mode] button). Each setting is stored in Enterprise 6 as a separate record known as a ‘Definition’. Each time you alter a column width or move the location of a column, you are amending your definition record for that screen.
However, you can do much more than adjust columns: you can add or remove columns, include columns with formulae, include column totals and display related information. To do all this, you need to access your definition record for whichever screen you want to alter. Note that, to access any definitions record, your Personnel record in the Data Manager must have the ‘Allow Data Manager Additions’ check box switched on.
The [Editable/Selectable mode] button has been described above as means of toggling between the two modes: this is achieved by clicking. In fact, if you click it and hold the mouse button down, three options become available: Choosing ‘Selectable’ or ‘Editable’ selects one of those modes: it is the same as clicking the button to toggle between them. Selecting ‘Definitions’ will allow you to view your definitions for the screen currently visible and thus allow you to customise it.
On selecting ‘Definitions’, you are first shown a list of the current definitions. The ‘Available definitions’ pop-up can be used to show the definitions of other users, while the [Create New definitions] button should be used when you want to create a new set from scratch. Full details of these two features can be found later in this section.
Each item in the list represents a separate column in the included list. If there is a column that you do not wish to appear in the included list, select the corresponding item in the list and click the [Delete] button in the palette (with the wastebasket icon). If you would like to add a column, click [+]. If you would like to alter the settings for a particular item, double-click it. Alternatively, you could highlight the desired items in the list and press [Select] before clicking [√]. If you double-click an item, or click the [+] button, the following screen appears: This screen allows you to amend the specifications of a particular column in the included list. The fields on this screen are as follows:
File/field
When determining what information is to appear in this column, first select the File using the first pop-up. The File Number of the chosen File appears to its right. Then, specify a Field using the second pop-up. Again, the Field Number will appear to its right, as will the field type.
You can have up to 22 columns in an included list, and there are also maximum numbers of each field type that can be shown.
When you select a Field, the maximum number for that field type is shown on screen for ease of reference. All maxima are listed below:
- String 15 (up to 80 characters)
- Text 2 (up to 32,000 characters)
- Real Number 9 (decimals)
- Integer 4 (whole numbers up to 32,000)
- Longint 4 (whole numbers greater than 32,000)
- Date 4
- Boolean 2
Most included lists display records from main files. This is the case with the Contacts included list on the Companies screen and the Order items list on the Orders screen. However, in some cases, subfile records are displayed. This is the case on the Products screen, the Sales and Purchase Ledger screens and the Purchase Orders screen. When working with subfiles, specifying Files and fields as described above is not sufficient to display the information required. In such cases, the Field pop-up does not include the list of all the Subfile fields - it can only be used to select the subfile itself - ITEMS, as below: Once you have selected a subfile, you then have to enter the name of the subfile Field into the Formula field. No other form of Formula will work - they will only give error messages. The Column Title should also be amended because it will otherwise always read ‘ITEMS’.
It is possible to allocate a field to an included list from a File other than its norm. It only makes sense to do this for data that is related in a ‘Many to One’ way.
For example, a Contact record has a Status field, which is related to the STATUS file, so you could choose to include the field [STATUS]Status Name. You could not, though, usefully allocate ‘One to Many’ data, such as Contact’s related Invoice numbers. If you do this, the Column will simply be blank.
Cross-file fields cannot be Enterable. Cross-file fields will generally be filled in automatically if the right field is chosen. However, this will not necessarily be the case where the included list is displaying a subfile. These already employ exceptions in the way definitions are entered (see above), and there are further exceptions when specifying cross-file links.
For example, in the items included list on the Invoices screen, if you want to show the Account Name related to the Sales Account Field, you should choose the Accounts File and the Account Name field as normal from the Files and fields pop-ups, then type in the following to the Formula field: RELATE ONE([INVOICES]ITEMS’Sales Acc) In other words, you have to manually establish the specific link that you want to use.
Note that you need do this only once for each link. For example, for a Supplier on the Product screen, you might to display several items of Company information such as Company Name, Telephone Number and Postcode. You only need to attach the RELATE ONE command to the Formula field of one of these columns, specifically the one that is nearest the left-hand edge of the included list.
Column title
Enter here the title of the column as you would like it to appear in the included list.
Format
In the case of numeric fields, you can specify a format such as #,##0.00 governing the appearance of the figures in the included list. That simple example means that positive numbers will appear as 1,298.40, negative numbers as -1,298.40 and zero as 0.00.
If, for example, you instead used £###,###,##0.00;(£###,###,##0.00);0 then a pound sign is displayed, more digits are catered for, negative figures would appear in brackets, and zero would appear just as 0.
A further option is to use one of the built-in Enterprise 6 formats, accessed by typing eg |Accounts, which is basically the same as the latter example above, less the £. Other such formats include |Inverse Accounts, |AccountsNZ (the NZ stands for No Zero because any zeros appear as blanks), |Inverse AccountsNZ and |Percent.
Width
If you want to be very precise, you can specify here the column width in pixels. Otherwise, on returning to the included list, you can adjust the size of a column using the mouse - and this field will be updated automatically.
Font size
Choose between 9 and 10 point using this pop-up. It can be set using the definitions entry screen for a specific column, or it can be set for all columns from the listing of definitions using the Font Size pop-up.
Colour
The Colour pop-up can be used to change the colour of one or more columns to something other than the default - Black. You can choose from a set of eight basic colours. Only the foreground colour is changed - ie the colour of the text.
Footer
Use this pop-up if you would like a calculated total or average to appear at the bottom of a column.
The options available are: None; Count; Sum; Average; Highest; Lowest.
If the pop-up is set to ‘None’ for each Column, then no Footer area will appear. As soon as just one is specified, an extra grey strip appears, containing calculated figures as requested.
The Count option simply shows the number of lines in the included list (not the number of fields containing values in a specific column) - so there would be no purpose in including more than one Count Column in the definitions.
The Average, Highest and Lowest options insert the text ‘Av:’, ‘Hi:’ or ‘Lo:’ in front of the calculated figures in order to identify their meaning.
The Footer pop-up choice is displayed in the definitions listing screen in a numeric format - 0 to 5 in the above order. You can append Footer Totals to Formulae Columns, and these will not be any slower in operation than other Totals.
Enterable
Switch this check box on if you would like to be able to enter data in this column. Enterable columns will appear with a bold column heading, while non-enterable columns will have a column heading in a plain font.
Column no
Specify here where in the included list you would like this column to appear. This field is effectively used to determine a sort order for the columns in the included list and will be amended if you move columns manually. Do not worry if, when setting up a new set of definitions, you specify the same Column Number for more than one column: those with the same Column Number will appear next to each other in the included list and you can then move them manually if they are not in the correct position.
Is the last locked column
Switch this check box on if you would like this column to be the one appearing immediately to the left of the locking bar.
Formula
If you would like this column to contain calculated information, enter the formula here. You can use any 4D code that returns a value, providing that value is of the same ‘type’ as the field you have specified using the File/Field pop-ups at the top of the screen. ‘Type’ in this context means that a Date field should match a Date value, and so on for Real Numbers, Integers (or Long Integers), Boolean, and String (or Text). Integers (whole numbers) are an exception, because you can specify an Integer field but use a formula that returns a Real (decimal). The value that is returned replaces that loaded for the defined Field, so the latter is used mainly to specify the field type.
A possible use for a formula might be to display a margin percentage in the Order items included list on the Orders screen. Such a formula might read: (~Sales Amount~/[ORDER ITEMS]Cost Amount) * 100.
The simpler your formula, the faster the calculations will be. One of the best ways of making it efficient is to never include references to Files and fields - such as the ‘[ORDER ITEMS]Cost Amount’ in the example above.
Where possible, you should use a special syntax (used only for this specific task, and outside the 4th Dimension command set) to refer to the other columns of the included Layout. This syntax requires the Column Title of the relevant column to be enclosed in ~ symbols - such as ~Sales Amount~’. This example presupposes, therefore, that another column has been requested in the same set of definitions with the Column Title of ‘Sales Amount’, but that a Cost Amount column does not appear.
The calculation would be much faster if a Cost Amount column were to be shown. One possible method of doing this without displaying a Cost Amount would be to specify the Cost Amount as the Field in the column where you want the calculation to appear. Such a column would require a Column Heading appropriate to its final use, such as ‘Margin %’. The Formula would then (apparently illogically) be: (~Sales Amount~/~Margin %~) * 100.
Note that it is the Column Title, not the Field Name, that you use. Therefore, if you alter a Column Title remember that you may have to change Formula references to it.
Other Formulae could be as follows:
| Type | Formula | Comments | | String | [CONTACTS]Forename+" "+[CONTACTS]Surname |
| Date | [ORDER ITEMS]Delivery Date+10 |
| Boolean | ~Column A~=~Column B~ | Returns 1 if True, 0 if False |
| Integer | ~Quantity~*10 Real StockLev Fmula (~Product Code~;"P";"F") |
4th Dimension Functions can be used, as can Procedures that are part of Enterprise 6. One such Procedure is illustrated by the last example above: The Procedure StockLev Fmula is used here to return the value of Free Stock of Type P for the Product specified in the Product Code column. The Product Code and Stock Type parameters are required. If the third parameter is left out, or is "", it will return Total Stock; otherwise you can specify “F” for Free or “A” for Allocated. Three other parameters can also be specified - for Analysis, Currency and Layer Codes. These are optional, but you should specify any of these that your Stock Control is sensitive to. For example: if you’re concerned with Analyses, the formula should read: StockLev Fmula (~Product Code~;"P";"F";[ORDERS]Analysis Code;"";"")
You can also use Same Process Macros in formulae:
Real Macro Result ("[Formula Eg]")
Note that the ‘[’ character has been included in the name of the Macro. Enterprise 6 tries to optimise the speed of these Formulae by avoiding using records wherever possible. It does this by seeing if there are any File/Field references in the Formulae, which it judges by the presence of the character ‘[‘.
The example Macro text below does employ such Field references, so ‘[’ has been used in the Macro name as an advance warning to Enterprise 6 of their presence. If ([ORDER ITEMS]Quantity>1) vmResult:=[ORDER ITEMS]Sales Amount Else vmResult:=[ORDER ITEMS]Cost Amount End if Note the use of the variable ‘vmResult’: this is necessary to pass back the value (in this case a Real) from the Macro to the Formula.
Note: because you are programming Enterprise 6 when you attach a Formula to a column, it is possible to cause 4th Dimension error messages to appear or even in some extreme cases cause Enterprise 6 to crash if faulty logic is used. Formula columns are never Enterable - you are prevented from making them so.
Enterprise 6 is supplied with default settings for each screen. These are used when a screen is viewed for the first time. As soon as a modification is made, even a slight adjustment in the width of one of the columns, a new set of definitions is created for that user when the screen is closed.
This is reflected in the construction of the definition Reference. This is made up of a five-digit code referring to the File and Layout number, followed by nothing (in the case of the Default settings), your initials (if you have modified the existing settings), or characters you have specified (if you have constructed new settings from scratch). A maximum of seven characters can be specified and the combination of number and your reference must be unique. The definition Reference is not unique to each definition, but to each set of definitions (ie each set of columns as they appear in the included list).
Once a set of definitions has been established that contains your initials as part of the Reference, this set will be used by default when you first use a screen each day.
However, you could design several sets of definitions for each screen. The default set (with your initials) might contain a minimum number of fields to be used for fast data input work, whilst others might be used for more complex and occasional tasks and might feature Macros or Enterprise 6 Procedures. These might be used as in the example earlier in this chapter to display Free or Allocated Stock against Order items.
To switch between sets of definitions, go to the definitions listing screen using the [Editable/Selectable mode] button, select the appropriate option from the ‘Available definitions’ pop-up and click [√]. This set will then be used either until you change definitions again or until you quit Enterprise 6. Before clicking [√], the listed definitions can be modified or selected from (using the [Select] button). The pop-up list will feature, as separate options, your initials (the assumed setting), a ‘Default’ option (the settings as shipped with Enterprise 6), the initials of other users who have accessed this screen and who have defined their own set of definitions, and the names of any other sets of definitions that have been designed.
