ddlRelate() Examples

ddlRelate() Examples

First, let's review the steps to obtain a report. They are

ISOLATE         - Select the records of interest.

ARRANGE      - Arrange them in the order needed for the report that you have in mind.

REPORT         - The input is the result of the arranging step and the output is the report. The report will have whatever display that you specify.

If your isolation involves only one table, you do not need RELATE for the arranging step; SORT will do the job.

When the isolation involves more than one table, RELATE is used for the arranging step.

Inter-file relationships can be quite interesting and are best seen through examples. We will use some of the tables in the EXAMPLE database to illustrate relationships between tables. Even though the examples are specific, keep in mind that the relationships shown are generic and can be found in any database.

Here are tables that will be working with:

    CUSTOMERS              STATES          SALES           INVENTORY

   -----------             ------          -----          -----------

    cust #                 state           store #          item #

    name                   stname          item #           description

    street                                 cust #           type

    city                                   pdate            mbcode

    state                                     yr            quan_on_hand

    zip                                       mo            unit_cost

    age                                       dy            order_cost

    discount                               descr            reoder_point

    status                                 quan_bot

    hair_color                             price

    handed

Table Relationships
Records in different tables relate to each other in one of the following ways:

> one-to-one
> one-to-many
> many-to-one
> translations
> no relationship

One-to-one, one-to-many, many-to-one

Whenever two tables share a common field they relate to each other in one or more of these three ways.

Look at CUSTOMERS and SALES. The common field is CUST#. A specific customer makes many purchases in a given time period, so you expect that for each customer record there are many sales records that "belong" to it. These records all have the same value in the CUST# field.

Another example is INVENTORY and SALES. The common field is ITEM#. For a given inventory record, you expect many sales records to "belong" to it. All of these records would have the same value in the ITEM# field.

Suppose you want to produce invoices for June. The isolation step is

> CLEAR A
> FIND A SALES WHERE MO = 06
> MATCH A SALES THRU CUST# WITH CUSTOMERS

CLEAR ensure that the collection is cleared of any residue left over from previous operations. FIND selects the June sales records, and the MATCH selects the customer records that go with those sales. These are the records that we want to work with, so the isolation step is complete.

Now the idea is to merge this data into one ordered file for the purpose of reporting.

Since invoices are on a per customer basis, it is the one-to-many relationship that we are after.

> RELATE A CUSTOMERS SALES OVER CUST# PDATE

The result of this arrangement is what the REPORT command sees when it produces the invoices. That is a file with the following order:

           CUSTOMERS

                   SALES

                   SALES

                   SALES

           CUSTOMERS

                   SALES

                   SALES

                   SALES

           CUSTOMERS

                   SALES

Each CUSTOMERS record is followed by the SALES records that belong to that customer. The command to process this file and produce the invoices might be

> RPR A NAME DESCR PDATE
> RPR/R A NAME DESCR PDATE
> PRINT A NAME DESCR PDATE
> PRINT A NAME DESCR PDATE EVERY CUST# SPACE 1
> PRINT/M A NAME DESCR PDATE

Guidelines For RELATE
In the RELATE command you supply a list of table names and a list of field names. The list of table names is the context (what to combine) and the list of field names is the rule (how they are to be combined).

The order in which the names appear in these two lists determines the results. For example, any time you have a one-to-many relationship there is the converse many-to-one relationship:

> RELATE A SALES CUSTOMERS OVER PDATE CUST#

This produces a different file that is useful for some other report (perhaps a report that shows daily activity patterns of your customers) but not useful for invoices.

RELATE is a command that you have to play with for a while at first, but it won't take too long for you to see what it can do for you. When you are experimenting, you will observe that not all the relationships that can be produced are useful.

Here are two guidelines to help you:

1. Think of the report that you are after from the top down and list the table names in that order. In our invoices example it was the customers one-to-many relationship that we were after, so the context was CUSTOMERS SALES.

2. Follow the order of the table names when supplying the field names. For example in the RELATE just above, since SALES came first in the context, PDATE (which is a field known only to SALES) came first in the rule. This guideline is especially true for global field names (such as CUST#).

More examples of this in the many-to-many cases are discussed further below.

TRANSLATIONS

Another way to view the relationship when two tables share a common field is as a translation. For example, look at CUSTOMERS and STATES.

We could form one-to-many or many-to-one hierarchies just as we did for CUSTOMERS and SALES and get several useful reports. Instead, lets see STATES used as a translation. Suppose that a two character state code won't do. The isolation step would be

> CLEAR A
> FIND A SALES WHERE MO= 06
> MATCH A SALES THRU CUST# WITH CUSTOMERS
> MATCH A CUSTOMERS THRU STATE WITH STATES

The collection was cleared of any residue, the June sales records were selected, followed by the customers who made those purchases, followed by the state records that go with those customers.

All of the records needed for the production of invoices have now been selected so the isolation step is complete. The arranging step would be

> RELATE A CUSTOMERS (STATES THRU STATE)
> SALES OVER CUST# PDATE

The translation appears in the syntax as a parenthetical insertion following a table name. This translation says, in effect: " every time a CUSTOMERS record is read follow it immediately with the appropriate STATES record." The results file looks like

                   CUSTOMERS

                           STATES

                                SALES

                                SALES

                                SALES

                   CUSTOMERS

                           STATES

                                SALES

                                SALES

And the report that processes this file and produces the invoices has access to the full state name each time it needs it.

Translations may be specifies for each or any table name specified as the context of a RELATE. Translations always show up as parenthetical insertions with the form

... tablename (tablename THRU fieldname) ...

Translations can be nested. To illustrate this we have to introduce a new table that is not in our EXAMPLE database, so you cannot do this one as you read about it.

Suppose that for some reason there is a requirement to categorize STATES as to whether they went Republican or Democrat in the last election.

Suppose further that this information is to be displayed in our invoices report. The database is modified as follows:

           STATES                 PARTIES

           ------                 -------

           State                  party

           stname                 partyname

The STATES table had been modified to add a third field. This new field is an X(1) field and contains the value R or D. The new table PARTIES has only two data records in it and is used to supply the full party name for those reports that require it.

(Editor's note:) We are really stretching for the sake of this example. You would have to be a real fanatic about saving storage space in order to design a database this way. Since there are only 51 STATES records it would have been easier all the way around to have just made the STATES table look like.

                STATES

                ------

                state

                stname

                partyname

But then we wouldn't have had a translation within a translation within a translation to demonstrate. The need for it does arise in other real-world databases.

The isolation step becomes

> CLEAR A
> FIND A SALES WHERE MO = 06
> MATCH A SALES THRU CUST # WITH CUSTOMERS
> MATCH A CUSTOMERS THRU STATE WITH STATE
> MATCH A STATES THRU PARTY WITH PARTIES

And the arranging step becomes

> RELATE A CUSTOMER -
> (STATES THRU STATE (PARTIES THRU PARTY)) -
> SALES OVER CUST # PDATE

The results would be

                 CUSTOMERS

                         STATES

                              PARTIES

                                    SALES

                                    SALES

                                    SALES

                 CUSTOMERS

                         STATES

                              PARTIES

                                    SALES

                                    SALES

We see a translation within a translation accomplished by a parenthetical insertion within a parenthetical insertion.

There can be more than one translation at the same level. For example, if the PARTIES were associated with CUSTOMERS rather than with STATES the situation would have been

                 CUSTOMERS                  STATES                    PARTIES

                 ---------                  ------                    -------

                  CUST#                      STATE                    PARTY

                   -                         STNAME                   PARTYNAME

                  STATE

                  PARTY

And the RELATE to produce the same results as shown above would have been

> RELATE A CUSTOMERS (STATES THRU STATE) -
> (PARTIES THRU PARTY) SALES OVER CUST# PDATE

Note that this time translate clauses follow each other and that there are no nested parentheses in the syntax.

Many-to-many

A many-to-many relationship can be recognized by looking for the following pattern:

                TABLE1         TABLE2        TABLE3

                ---            ---           ---

                FIELD1         FIELD1         FIELD2

                 -             FIELD2          -

                 -              -              -

                 -              -              -

In our EXAMPLE database one of the occurrences of this pattern is in

               CUSTOMERS             SALES             INVENTORY

               ---------            ------             ---------

                 CUST#               ITEM#              ITEM#

                  -                  CUST#                -

                  -                   -                   -

                  -                   -                   -

There IS A MANY-TO-MANY RELATIONSHIP BETWEEN customers and INVENTORY. At first you may think that there is no relationship at all because these two do not share a common field. But there is another table, SALES, where information from these two comes together.
This third table represents data that was captured when a real event took place (a customer making a purchase).

This kind of relationship abounds in the real world. Consider EMPLOYEES and PROJECTS.

               EMPLOYEES                        PROJECTS

               ---------                        --------

                empl_no                          proj_no

                empl_name                        proj_name

And the third table that describes the event that brings these two together might be TIME_CARDS.

              TIME_CARDS

              ----------

               empl_no

               proj_no

               work_data

               hours

               rate

When an employee spends time on a project the event is captured in a data record that identifies which employee, which project, how many hours were worked, the data, and the rate per hour for this employee.

Another might be SHIPS and PORTS:

             SHIPS                  PORTS

             -----                  -----

             ship #                 port#

             ship_name              port_name

               -                      -

               -                      -

And event that establishes the many-to-many relationship between these two occurs each time a ship visits a port.

            VISITS

            ------

             SHIP

             PORT

             VISIT_DATA

             BBL_LOADED

             PBB1

Here we assumed that it was an oil tanker and the event was "On such-and-such date ship so-and-so visited port thus-and-so and loaded so many barrels at this price per barrel."

As we look at CUSTOMERS SALES and INVENTORY remember that the example is generic.

Suppose we want an internal report that is very much like the invoice report except that we want to show cost information associated with each sales record and with the total invoice. Suppose further that we require the full state name in our report. The isolation step might be

> CLEAR A
> FIND A WHERE STATUS = OK
> MATCH A CUSTOMERS THRU CUST # WITH SALES
> MATCH A SALES THRU ITEM# WITH INVENTORY
> MATCH A CUSTOMERS THRU STATE WITH STATES

We find a particular subset of customers, the sales records that go with that set of customers, and the states records that go with that set of customers. Finally, since we need cost information associated with each sales record, we select the inventory records that go with those sales.

> RELATE A CUSTOMERS (STATE THRU STATE)-
> INVENTORY SALES OVER CUST# ITEM#

This is still an invoice-type report and is still on a per-customer basis, so CUSTOMERS is still on top. But instead of grouping the sales under the customers, we want them grouped under inventory so we can include the cost information associated with each sales.

Following Guideline 1, the context is CUSTOMERS INVENTORY SALES.

Following Guideline 2, the rule is CUST# ITEM#. The result is

           CUSTOMERS

                   STATES

                        INVENTORY

                                SALES

                                SALES

                                SALES

                        INVENTORY

                                SALES

                                SALES


           CUSTOMERS

                   STATES

                        INVENTORY

                                SALES

                                SALES

                        INVENTORY

                                SALES

For each customer record the sales records are grouped under the appropriate inventory record so the cost information is available to the report when it is needed.

Finally, the REPORT command to get our record might be

> REPORT A ACCORDING TO C_INVOICE

If you are trying these examples as you read about them, the report definition C_INVOICE does exist. Also, you can use the RAWPRINT command to examine the results file created by the RELATE command.

Try

> RPR A NAME STNAME UNIT_COST DESCR QUAN_BOT

The other side of many-to-many:

We have seen one side of a many-to-many relationship, the side with CUSTOMERS on top. Here we saw many INVENTORY records associated with a given CUSTOMERS record. How about the side with INVENTORY on top? Here we see many CUSTOMERS records associated with a given INVENTORY record.

Suppose we want to profile the profit performance of a particular inventory item. The isolation step is

> CLEAR A
> FIND A WHERE DESCRIPTION = BOOZE
> MATCH A INVENTORY THRU ITEM# WITH SALES
> MATCH A SALES THRU CUST# WITH CUSTOMERS

This report is to be on a per-inventory basis, so INVENTORY is on top. We need to group the sales under the appropriate customers records so that the discount information is available. So, following Guideline 1, our context is INVENTORY CUSTOMERS SALES. And, following Guideline 2, our rule includes ITEM# CUST# stated in that order.

ITEM# and CUST# are the global fields and follow the spirit of Guideline 2 exactly. PDATE is a local field known at the lowest level of detail (SALES). For fields like this we can vary the breakouts we get by how they are placed in the rule. We can take liberties with Guideline 2 when we are dealing with local fields at the lowest level of detail. For this report we want it first.

> RELATE A INVENTORY CUSTOMERS -
> SALES OVER PDATE ITEM# CUST#

The results file is indicated below. But without the PDATE variation to Guideline 2 we could not have made the comment shown below. After you have viewed the suggested report, try the RELATE and PDATE last in the rule and see the difference in the results file. You get a file useful for some other report.

   INVENTORY

            CUSTOMERS

                     SALES

                     SALES

                     SALES

            CUSTOMERS

                     SALES

All January detail occurs before February, etc., so the report can get monthly breakouts.

Finally, the report we are after can be viewed with

> REPORT A ACCORDING TO RFRPT

There is a whole series of profile reports available in the EXAMPLE database. You can get at them via the PROFILES Menu. Don't forget that you can view the content of any Procedure or Report definition by using the List command.

PRESENT BY

The PRESENT BY part of the RELATE command syntax is used to control the primary order of presentation for any report. For an example, lets look again at the first invoice report that we achieved earlier. It was

>CLEAR A
>FIND A SALES WHERRE MO = 06
>MATCH A SALES THRU CUST # WITH CUSTOMERS
>RELATE A CUSTOMERS SALES OVER CUST # PDATE
>REPORT A ACCORDING TO P_INV

The question is: When you are presented with the whole stack of invoices, what order do they have as you are flipping through the pages? The answer is: The pages occur in CUST# order since that is the way that this RELATE left them. What if you want this same report but you want the pages to come in alphabetical order by customer name?

>RELATE A CUSTOMERS SALES OVER CUST# PDATE -
>PRESENT BY NAME

First the hierarchies needed for the report are formed, and then they are rearranged according to the PRESENT BY criterion.

PRESENT BY is always last in the RELATE syntax, and it too is a list of field names. We don't need guidelines for these since they simply act as a final sort. Do note, however, that a field name in the PRESENT BY list cannot come from the detail table(s).

For example, in this case it must be something that occurs only per package of information for a customer, NAME satisfies that requirement.

To see more discussion and example of RELATE, see the STEPS Menu in the EXAMPLE database. STEPS can be found on the DESCRIPTIONS Menu.