Computers: Universe: Imaginary Fields

One obvious omission from our CUSTOMER.INVOICES file layout is a field to hold the value of each invoice. It will be apparent, though, that the file holds enough information to calculate the value of the invoice without such a field: so it could be argued that to actually store it is redundant, wasting disk storage when the value, when required, could be derived by taking the product of the UNIT.PRICE and QUANTITY fields at any time.

This approach is fine in UniVerse Basic programs, but what if you wanted (as you surely would) to show the value of each invoice in an enquiry? Without a dictionary record, the word VALUE could not be used in an enquiry.

UniVerse provides a type of dictionary record which allows you to reference a field like VALUE which is not physically stored in a record, but can be derived by applying a formula to other fields which are. It is called an imaginary dictionary record, I-type dictionary record, or simply an I-descriptor.

Create a VALUE I-descriptor like this:

>ED DICT CUSTOMER.INVOICES VALUE
New record.

----: I
0001= I Value of invoice
0002= UNIT.PRICE * QUANTITY
0003= (Enter a space and press ENTER)
0004= Value
0005= 10R
0006= S
0007= (Press ENTER)
----: FI
"VALUE" saved in file "DICT CUSTOMER.INVOICES".
>

You may find this record familiar, as it is very similar to the dictionary records we created for PRODUCT, UNIT.PRICE and QUANITY in the last section (see Data fields). In fact, only the second line of an I-descriptor is significantly different. The first, as do all first lines of dictionary records, consists of the type (an I) followed optionally by a description. The third, fourth, fifth and sixth lines are all as described before.

In a D type dictionary record, however, the second line contains the number of the field being referenced. For instance, the dictionary record UNIT.PRICE contains a 3 on its second line, as it can be found on the third field of every CUSTOMER.INVOICES record. As the VALUE of an invoices does not appear in a CUSTOMER.INVOICES record, such a number cannot be used in an I-descriptor.

In it's place is a UniVerse Basic expression which evaluates to the desired value. In this case, as the invoice value can be calculated by multiplying the unit price of the product by the quantity bought, it is UNIT.PRICE * QUANTITY (* being the UniVerse Basic multiplication operator).

To gain full advantage from I-descriptors, it is therefore necessary to understand UniVerse Basic, which is covered at length near the end of this course. However, must I-descriptors are relatively simple, and the most common types are explained below:

Constant I-descriptors

You can create an I-descriptor which always returns the same value, regardless of the record it is evaluated with. For instance, perhaps the simplest I-descriptor formula of all, 1, can be usefully totalled within UniVerse enquiries to provide record counts: as it returns one for each record listed by the enquiry.

Synonym I-descriptors

I argued in the previous section (see Data fields above) that is was good practise to maintain only one D type dictionary record for each field in the data file, thus giving each field a unique and consistent name. If you wish to set up a synonym of the CUSTOMER.NAME field, giving the customer name a wider column to live in, create an I-descriptor called WIDE.CUSTOMER.NAME with a formula of CUSTOMER.NAME. One advantage of this tidy approach is quickly apparent: should the file ever be redesigned, pushing the customer's name onto the second field, editing the CUSTOMER.NAME field is sufficient to accomodate the cange. If CUSTOMER.NAME and WIDE.CUSTOMER.NAME were both D types, they would both have to be modified.

Arithmetic I-descriptors

I-descriptors often apply arithmetic operators to other fields in order to derive new values. VALUE is one such. The UniVerse Basic arithmetic operators are:

String manipulating I-descriptors

Many I-descriptors operate by adding strings together, taking subsections of strings, or otherwise operating on them. UniVerse Basic is extremely strong at string manipulation, as is appropriate to a database management system that stores all data as strings. You will learn more about its features later, but for now the most commonly used string manipulations in I-descriptors are:

Translation I-descriptors

A common requirement is to create a value which reports a value from another UniVerse file. For instance, you might decide to run discount schemes for favoured clients: scheme 'A' for the most lucrative, 'B' for the fairly lucrative, and 'C' for anyone else. You could then add a DISCOUNT.CODE field to your CUSTOMER.INVOICES file, showing which discount you have decided to apply to each invoice. This code would be a key to another file, called DISCOUNT.SCHEMES, which in turn would have a field called PERCENTAGE.DISCOUNT.

To show the DISCOUNT.CODE for each invoice is simple: you can just enter the command LIST CUSTOMER.INVOICES DISCOUNT.CODE. But what if you wanted to show the actual percentage discount? You can't say LIST CUSTOMER.INVOICES PERCENTAGE.DISCOUNT because the dictionary record PERCENTAGE.DISCOUNT is on the dictionary of DISCOUNT.SCHEMES, not the dictionary of CUSTOMER.INVOICES. But you can create an I-descriptor which 'translates' the percentage discount from the DISCOUNT.SCHEMES file.

To do this, you use a UniVerse Basic function called TRANS(). This function is an ordinary part of UniVerse Basic, but is so useful for I-descriptors that you'll rarely find it anywhere else. In the example above, you could create an I-descriptor called DS.PERCENTAGE.DISCOUNT on the dictionary of CUSTOMER.INVOICES. The DS. at the beginning of the name is a reminder to anyone using the field that the data associated with it is actually being obtained from DISCOUNT.SCHEMES. Such prefixes for translation I-descriptors are entirely optional, but useful and very common.

The formula on line 2 of the DS.PERCENTAGE.DISCOUNT I-descriptor would look like this:

TRANS('DISCOUNT.SCHEMES', DISCOUNT.CODE, 'PERCENTAGE.DISCOUNT', 'X')

Taking the four parameters in turn:

1. 'DISCOUNT.SCHEMES' is the name of the file which contains the information you want to translate.

2. DISCOUNT.CODE yields the key to DISCOUNT.SCHEMES. Note that it is not in quotes: this is because it is not the string 'DISCOUNT.CODE' itself which is the key, but the 'A', 'B', or 'C' contained in the field. Remember that field names in the formulas of I-descriptors are replaced with their values: a little like variables in a program.

3. 'PERCENTAGE.DISCOUNT' is the name of the DISCOUNT.SCHEMES field which contains the value you want to retrieve.

4. 'X' is a special code which means 'if the record can't be found, return an empty string'. The three other possible codes are 'V', which does the same as 'X' but displays an error message to highlight the failure, 'C' which returns the key to the record which couldn't be found, and 'N' which returns the key to the record if the value found is an empty string.

Conditional I-descriptors

A common requirement is to take a simple coded field, such as a GENDER field on a PERSONNEL file, and provide a friendlier description of the code for a report: so instead of showing M for male and F for female, you can show the full words Male and Female in your enquiry. The UniVerse Basic expression to achieve this is quite intuitive:

IF GENDER = 'F' THEN 'Female' ELSE 'Male'

(C programmers may notice the similarity to their beloved condition ? trueresult : falseresult syntax).

In fact, this formula is a bit dodgy. What if the GENDER field was empty, or someone updated it incorrectly, entering a W for 'woman' instead of an F for 'female'? In both these cases the formula would yield Male. Fortunately, you can nest IFs:

IF GENDER = 'F' THEN 'Female' ELSE IF GENDER = 'M' THEN 'Male' ELSE 'Unknown'

You now need only to decide if a woman is more likely to be offended by being called 'Male' than by being classified as 'gender unknown'.

Subroutine I-descriptors

It's possible that your I-descriptor may be very complicated. For instance, you might wish to put I-descriptors on the dictionary of your CUSTOMER.INVOICES file showing the value of the invoice in different currencies. You may recall the original figures were in €uros, so you might call your I-descriptors DOLLAR.VALUE, YEN.VALUE, STERLING.VALUE and RAND.VALUE. The calculations might be quite complicated: they would have to access some kind of CURRENCY.RATES file to find today's exchange rate, apply charges for currency conversions, and so on. It is unlikely that such a computation could be expressed neatly, if indeed at all, in a single UniVerse Basic expression.

In such situations, you can use an extremely hand function called SUBR(). Like TRANS() (see above), this is a standard UniVerse Basic function, but is not terribly useful outside I-descriptor formulae: within them, however, it is wonderful.

SUBR() call a UniVerse Basic subroutine of your choice. Subroutines can take a number of 'parameters', similar to parameters in most programming languages (C, Pascal, PL/SQL and so on) and used both to pass values values into the subroutine, and to get values back. All the information you need to create subroutines will be provided later, in the section on UniVerse Basic, but for now it is useful to now that the first line of each subroutine states the subroutine name and the parameters it takes. For instance, a subroutine to convert a Euro value to another currency might look like this:

SUBROUTINE GET.CURRENCY.VALUE(CURRENCY.VALUE, EURO.VALUE, CURRENCY.CODE)

Returning to the formula of your I-descriptor, the SUBR() function takes the same number of parameters as the subroutine you wish to call. The first parameter is the name of the subroutine, and the others provide values to pass into the 2nd parameter in the subroutine onwards. The subroutine must return its result through its first parameter. This is one of those things that is difficult to describe, but simple once you've grasped it. The following implementations of the foreign currency I-desciptors mentioned above may help.

As you can see, the formula is the same in each case, with only the currency codes varying. The same subroutine is being called in each case, receiving the €uro value and currency codes in the second and third parameters respectively, and returning the result via the first.

A final note: you may have noticed that the I-descriptor VALUE appears in the formula for the I-descriptor DOLLAR.VALUE. In fact, I-descriptors can refer to I-descriptors just as easily as D type data fields.