Complex relationships

[danjacoby]

I have three files, call them "Main", "Related" and "Third".

Main has several one-to-many relationships with Related. Each is based on a concatenated field in Main consisting of a particular global field and a field unique to each record. This is done because there is one more relationship, based on the unique field only, that shows all the related records from all the other relationships.

So far, so good.

The problem is that one of the relationships from Main to Related has to have the ability to create records (in Related) from a portal in Third. The problem is putting data in the field in Related that is the basis of the relationship with Main -- whenever I set up an auto-calc, I keep getting a "circular reference" message.

Any suggestions?

[djgogi]

Wish I could help you but I'm a little bit confuse with exact meaning of "...one of the relationships from Main to Related has to have the ability to create records (in Related) from a portal in Third..."

Maybe it's just my english. However could you be more specific in explaining the exact structure of your DBs.

Dj

[LiveOak]

Me too. I'm confused about how a relationship from Main to Related could be used in Third to create records.

-bd

[danjacoby]

The records exist in Related, and can easily be created from a portal in Main -- but I want to be able to create them from a portal in Third as well.

The problem is that, since I have to use a concatenated field in Main to relate to Related (in order to be able to create a "group relationship" containing all the relationships I've created)...

and this concatenated field is based, in part, on a unique field in Main...

I can't use this field to create related records in Related from the portal in Third.

Hope this explains it more clearly.

[The Bridge]

You could try a script to assign the concatenated key in Main to a global in your Third file, which would form the left-hand side of a relationship with Related.

[danjacoby]

Perhaps I should explain that the problem is with the right side of the concatenated field -- the one that contains the unique ID in the Main file. Any attempt to auto-calc this in the Related file results in a "circular definition" message.

I've been stewing over this for three days (and nights!) to no avail. I've tried every workaround I can think of, plus several I can't think of, but I have a sinking feeling it can't be done.

Of course, I could be wrong

[Vaughan]

Time to re-think the data design, I fear.

[djgogi]

OK, the only thing that we know is that exists some kind of rel between Main and Related but you didn't explain what is the relationship between Third and Main, and Third and Related.

Without knowing that my best shot would be:

Create an constant rel between Third and Related allowing creation of related records

(auto enter 1 in Related auto enter 1 in Third)

Put the right key from Related into portal in Third and attach the script:

Set Field [ConstRelThirdRelated::right key, relThirdMain::left key]

HTH

Dj

[goostree]

I agree with DJ.

More info on the other relationships you're using would help greatly.

For instance, how are your records created in Third? How do they relate back to Main? Will the records in Third relate back to the same Main record as the records you are trying to create in Related?

[danjacoby]

Restating the problem:

Main file, containing names of people, along with a unique key identifier.

Related file, containing many, many records.

Multiple relationships from Main to Related, based on a combination of one of several global fields (one for each relationship) and the unique Key field. Each has its own layout and portal.

One relationship, from Main to Related, based solely on the Key field (in Related, the "Key" field is a calc, parsing the combined field), with the portal showing records from all the other relationships.

When creating portal rows in one of the relationships in main, I need to pull data from a unique field in a Third file, using a value list. As a result, a relationship can be created from Third to Related, based on this field.

Records in Third are created as necessary from with Third; there are only a couple of fields.

So far, so good.

The problem is that, while I can also go to Third and create related records in Related, these records do not contain data in the Key field used by the relationship from Main, because this is a generated field that the user never sees -- and wouldn't be able to use anyway.

A digression: The user sees a name in each record in Main, and uses that as his/her identifier. When creating related records from Third, therefore, the first related field will be the Name field (that also exists in Related, of course).

How can I create related records from Third, and get the unique Key field to enter the data based on the name being used? Right now, every attempt I make gives me a message box saying I've created a "circular definition".

There is no direct relationship between Third and Main. The only "direct" connection is that each file has a value list containing the contents of a field in the other file.

I hope this is clearer than the mud I seem to have posted here to date.

[djgogi]

OK, so the real problem is indexing (or better unable to index the list).

My suggestion is:

Repeat the global (that you are using in Main for the relationship that allows creation of records in Related) in Third (or, if you prefer, access that global from an const relationship with Main)

For creating relationship from Third to Related that allow creation of related records, instead of name, use unique key from main (accessed in Third from the value list defined as use values from field ID in file Main) concatanated with the global field.

The rest is simple, in Third define value list as use values from field theFieldThatYouWantTo Use, attach it to the related field in portal (Third-->Related).

In main define value list as use list from file Third and attach it to the related field in portal(Main-->Related)

Since both relationships allow creation of records and both have the same left key I would say:

Les jeux son fait!

HTH

Dj

PS I've tested it. I could send you the files if you need them.

[danjacoby]

The problem is that I have to use the Name field when creating related records in Third -- the user won't know what ID stuff to look for, since the ID field is auto-generated.

I can now send sample files to anyone who wants to take a closer look.

[djgogi]

This is the same old story, unable to use key and present only some meaningful value like name in your case to the end user. Well if you don't want to display both values in the pop-up list (key and the name) than I don't see to many options other than creating rel from Third to Main based on name field in both (with every limitations of such a choice).

However, maybe I'm overlooking something so if you want me to give a look at your files my email is:

[email protected]

HTH

Dj