Effects of ERROR 1147 - "Target is engaged in relation."

[marka1966]

I have setup the following relations.

*************************************
SELECT 1
USE d:\database\comp.dbf SHARED
SELECT 2
USE d:\database\name.dbf SHARED
SELECT 3
USE d:\database\forn.dbf SHARED
SELECT 4
USE d:\database\misc.dbf SHARED
SELECT 5
USE d:\database\data.dbf SHARED
SELECT 6
USE d:\database\canc.dbf SHARED
SELECT 5
SET ORDER TO TAG Nameid OF d:\database\name.cdx IN name
SET RELATION TO nameid INTO name ADDITIVE
SET ORDER TO TAG Nameid OF d:\database\forn.cdx IN forn
SET RELATION TO nameid INTO forn ADDITIVE
SELECT 2
SET ORDER TO TAG Companyid OF d:\database\comp.cdx IN comp
SET RELATION TO companyid INTO comp ADDITIVE
SELECT 3
SET RELATION TO companyid INTO comp ADDITIVE
SELECT 2
SET ORDER TO TAG Nameid OF d:\database\misc.cdx IN misc
SET RELATION TO nameid INTO misc ADDITIVE
SELECT 3
SET RELATION TO nameid INTO misc ADDITIVE
SELECT 6
SET RELATION TO nameid INTO name ADDITIVE
***********************************************

The resulting relational setup looks like what I want BUT when I "SELECT data" I get the "Target is engaged in relation" error.

What are the affects of this error on the system?
How should I restructure to avoid the error?

*************************************************
Additional Info:

Data - unique "Record Id" for all names in the system (plus
other "grouping" data)
Name - Demographics for Domestic addresses
Forn - Demographics for Foreign addresses
Misc - Common info for both Foreign and Domestic addresses
(phone, fax, email, etc)
Canc - unique lookup keys for all names in the system (once
canceled) Removed from "Data"
Comp - Company names both Foreign and Domestic

 

[JRB-Bldr]

First let's remove all the un-necessary code and SELECT the tables by their ALIAS() rather than trying to remember workspace numbers...

USE d:\database\comp.dbf IN 0 SHARED
USE d:\database\name.dbf IN 0 ORDER Companyid SHARED
USE d:\database\forn.dbf IN 0 SHARED
USE d:\database\misc.dbf IN 0 SHARED
USE d:\database\data.dbf IN 0 ORDER Nameid SHARED
USE d:\database\canc.dbf IN 0 SHARED

SELECT Data
SET RELATION TO nameid INTO name ADDITIVE
SET RELATION TO nameid INTO forn ADDITIVE

SELECT Name
SET RELATION TO companyid INTO comp ADDITIVE

SELECT Forn
SET RELATION TO companyid INTO comp ADDITIVE

SELECT name
SET RELATION TO nameid INTO misc ADDITIVE

SELECT Forn
SET RELATION TO nameid INTO misc ADDITIVE

SELECT Canc
SET RELATION TO nameid INTO name ADDITIVE

Now notice that you have no ORDER set in tables:
Comp, Misc, & Forn which are supposed to be Child tables in the Relationships.

Doing a SELECT Data, by itself should not generate a "Target Already Engaged In Relation" error.

But, not knowing if any relations were set prior to this code, setting the new relation MIGHT be duplicating relation previously set.

Use the Trace window and Break prior to the above code. Look to see if previous relations were already set up. If so, then SET RELATION TO to eliminate it.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[DSummZZZ]

I have always found it easier to work from the 'bottom' up on relations.
But first, back up a little. In order to keep everything clear, start with opening all tables and indexes at the beginning. And rather than using work areas, use aliases. That way, you know exactly what table you are referring to later on. And one more little detail, if you use SELECT 0 rather than a particular work area number, you will always open the tables in an unused work area. That way you don't have to remember where you left off:

SELECT [COLOR=blue]0[/color]
USE d:\database\comp.dbf SHARED ORDER Companyid 
SELECT [COLOR=blue]0[/color]
USE d:\database\name.dbf SHARED ORDER Nameid
SELECT [COLOR=blue]0[/color]
USE d:\database\forn.dbf SHARED ORDER Nameid
SELECT [COLOR=blue]0[/color]
USE d:\database\misc.dbf SHARED ORDER Nameid 
SELECT [COLOR=blue]0[/color]
USE d:\database\data.dbf SHARED
SELECT [COLOR=blue]0[/color]
USE d:\database\canc.dbf SHARED

*... They're all open, now select the alias(es).  
SELECT [COLOR=blue]data[/color]
SET RELATION TO nameid INTO name ADDITIVE
SET RELATION TO nameid INTO forn ADDITIVE
SET RELATION TO companyid INTO comp ADDITIVE
SELECT [COLOR=blue]forn[/color]
SET RELATION TO companyid INTO comp ADDITIVE
SELECT [COLOR=blue]name[/color]
SET RELATION TO nameid INTO misc ADDITIVE
SELECT [COLOR=blue]forn[/color]
SET RELATION TO nameid INTO misc ADDITIVE
SELECT [COLOR=blue]canc[/color]
SET RELATION TO nameid INTO name ADDITIVE

Now the error. As I see it, you have two tables related into 'misc', and two tables relating into 'name'. Also, 'data' and 'forn ' are related into 'comp'.
So the effects are that moving the record pointer in say, 'data', moves the record pointer in 'name, but becomes out of whack when the focus is set to 'canc'. That's why Fox is confused.
You may have to open some of the tables using another alias, in another work area in order to properly set the relations.
For example:

SELECT [COLOR=blue]0[/color]
USE d:\database\comp.dbf [COLOR=red]AGAIN[/color] SHARED ORDER Companyid ALIAS comp1
SELECT [COLOR=blue]0[/color]
USE d:\database\comp.dbf [COLOR=red]AGAIN[/color] SHARED ORDER Companyid ALIAS comp2

Now, try these relations, working from the bottom up:

SELECT [COLOR=blue]name[/color]
SET RELATION TO nameid INTO misc ADDITIVE
SET RELATION TO compid INTO comp[COLOR=red]1[/color] ADDITIVE

SELECT [COLOR=blue]forn[/color]
SET RELATION TO nameid INTO misc ADDITIVE
SET RELATION TO compid INTO comp[COLOR=red]2[/color] ADDITIVE

SELECT [COLOR=blue]data[/color]
SET RELATION TO nameid INTO name ADDITIVE
SET RELATION TO nameid INTO forn ADDITIVE

Since I don't have your table structures, I hope I got all the names right.

The only issue I see now, is the 'canc' table.
The only thing I can think of is to set a relation from 'data' into 'canc' to see if there are records in 'canc' that correspond to 'data'.
Otherwise, you would have to open all the related tables again using another alias in order to have 'canc' relate into them without another cyclic relation.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[marka1966]

For additional information:

The relations are the first files opened during the start of the App.

I have orders set for all child tables.

I didn't have any problems with the relations until I added the "SET RELATION TO nameid INTO forn ADDITIVE" line.

I will try the suggestions and report back.

Mark1965