Many to many relationships on the same form 1

[virgo]

I am dealing with a number of many to many relationships. The tables are all indexed on the same field.

I need to be able to link 2 or 3 tables at a time.

Is there a good way to deal with these?

I have never done this before.

 

[DSummZZZ]

See the SET RELATION command in your documentation.
If this does not help I will be happy to provide an example.

 

[virgo]

I have looked at the SET RELATION command. I also have the Microsoft Press books on Visual FoxPro.

No help.

This is my first go-round with VFP. However, I have done a lot of programming in a lot of other languages.

I did not design these tables. They are large, from 10 to 700+ megabytes. They are poorly indexed. There are no such things as primary or foreign keys. There is a unique key of id number in most of the tables.

TIA

 

[DSummZZZ]

Here is the basic format for relating several tables.
Say you have four tables in roughly the following format:

Customer: (customer info)
CustId, Name, Address, CSZ, etc.
Indexed on CustId

Tx: (monetary transactions, payments etc.)
txCustId, txAmount, txType, etc.
Indexed on txCustId

Letter: (letters sent to customer, late notices, statements, etc)
ltrCustId, ltrType, ltrDate, etc.
Indexed on ltrCustId

Descript: (letter descriptions, late notice, statement, etc.)
descType, descText
Indexed on descType

To create a relation to customer transactions and letters sent, do the following:
USE customer ORDER CustId IN 0
USE tx ORDER txCustId IN 0
USE letter ORDER ltrCustId IN 0
USE Descript ORDER descType IN 0

SELECT customer
SET RELATION TO CustId INTO tx
SET RELATION TO CustId INTO letter ADDITIVE
SELECT letter
SET RELATION TO ltrType INTO Descript
SELECT Customer

This will establish a link like this:
Customer -> Tx AND
Customer -> Letter -> Descript

Now you can do your browse, processing, whatever.

SCAN
* do stuff
ENDSCAN

So, moving the record pointer in Customer will also move to the associated records in Tx and Letter, which will move the record in Descript to the correct record.

Note that including the ADDITIVE keyword is needed when setting relationships to multiple tables from a parent table, because if you don't, the original relationship is broken.

 

[virgo]

Thank you. I understand what you are doing.

One other possible problem. The only decent key that I have in all the tables is called id. I have just been using id. Things might be a bit confused. Could I use a full designation to separate things, for example mbfil1.id, mbfil5.id, and so on?

 

[DSummZZZ]

Absolutely, if you like the readability factor, you can do it a couple different ways:

USE mbfil1 ORDER id IN 0
USE mbfil2 ORDER id IN 0
USE mbfil3 ORDER id IN 0
USE mbfil4 ORDER id IN 0
USE mbfil5 ORDER id IN 0
SELECT mbfil1


SET RELATION TO mbfil1.id INTO mbfil2
SET RELATION TO mbfil1.id INTO mbfil3 ADDITIVE
SET RELATION TO mbfil1.id INTO mbfil4 ADDITIVE
SET RELATION TO mbfil1.id INTO mbfil5 ADDITIVE
Results in:
mbfil1 -> mbfil2
mbfil1 -> mbfil3
mbfil1 -> mbfil4
mbfil1 -> mbfil5

-OR-

SET RELATION TO mbfil1.id INTO mbfil2
SELECT mbfil2
SET RELATION TO mbfil2.id INTO mbfil3
SELECT mbfil3
SET RELATION TO mbfil3.id INTO mbfil4
SELECT mbfil4
SET RELATION TO mbfil4.id INTO mbfil5
SELECT mbfil1

Results in:
mbfil1 -> mbfil2 -> mbfil3 -> mbfil4 -> mbfil5

Since you are setting the relation from a field into a file with an open index, you don't specify the field you are relating into. Fox realizes that you want to use the relation to point to records which you have the index order set to in the child table.

When you want to refer to a field within a certain table later in your code, you can however, reference it with the table.id designation, without selecting that table. As in:
STORE mbfil1.id TO nSomeVar, or
STORE mbfil3.lastname TO cNameVar, or whatever.