Hi,

Sorry if the subject or the question were not quite clear. It was
quite hard for me to phrase a question for Google search that made
sense.

Anyway, I'll try to explain my predicament and hope someone out there
can help.

I'm creating a DB with several tables, and I wish to create the
following:

Table Names:
Name
College Name (related to college table)
Campus Name (related to campus table)

Table College:
College Name
College website

Table Campus:
College Name (so you know which college this campus belongs to)
Campus Address

Now this is a simplistic view of the tables, but it illustrates the
essentials.

Here's what I'm trying to achieve:
On a layout, create a NAME, then select a College from a drop down
list, and if non-existant, create is using the "other" option. This,
I've got no problems achieving by using a simple 1-1 relationship.
It gets harder with the following step. After selecting a College,
I'll need to select or create a new Campus entry. The campus entry has
to be related to the College. This I have not been able to achieve.

The logic behind this approach (and there might be simpler or more
elegant ways) is to create a person record, link that person to a
college and then to a campus (and create them on the fly if needed).
So if the DB already has a College called ColOne, but only one campus
cals ColCamp1, but if need be, create another campus entry.

Sorry if I can't explain myself better. Any help would be appreciated.

Cheers.
George

On 03/17/2007 07:47:20 "jarjoor" <jarjoor (AT) gmail (DOT) com> wrote:

Your description sounds like you're mixing terms and concepts: "Name College Name (related to college table) Campus Name (related to campus table)" sounds more like fields within tables than something related.

Try this:

Table College, Field College Name
Table Campus, Fields College Name, Campus Name

Relate the two tables: College::College Name = Campus::College Name

Allow creation of records in Campus::College Name via this relationship

In a layout displaying records from College, place a portal displaying related records from Campus (probably just the Campus Name field).

Next, your Person table. Field College Name, related to College::College Name.

You can go through College to Campus via this relationship.

Try that setup, report back.

Matt

In article <1174132040.029464.313430 (AT) b75g2000hsg (DOT) googlegroups.com>,
"jarjoor" <jarjoor (AT) gmail (DOT) com> wrote:

It sounds like you're trying to create Dynamic Value Lists. For
example, you choose a College and then the Campus field only has the
campuses (campi??) for that particular college.

This is fairly easy to set-up, but you can not use the "Other" option
to create new choices on the fly via the pop-up menu / list field. You
have to use FileMaker's normal record editing to create new records in
the appropriate table(s) themselves.

What you would need to do is have a "Add Campus" btton next to the
field. This button runs a script to 'copy' the current College Name and
then goes to the Table Campus table to create a new record, inserting
the College Name and then waiting for the user to type in the Campus
details. When you go back to the Table Names table's data entry screen
the pop-up menu / list will then include the newly added option. (You
don't say what version of FileMaker - in older versions you'll need to
have this script run a second script in the other table / file to
actually create the new record.)

Although I've said above to 'copy' the College Name, it's usually not a
good idea to use the Copy / Paste functions. Instead use a Global field
in the Table Campus table to temporarily store the College while you
change tables.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Mar 18, 10:10 am, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
Thanks Guys.

Two points:

To Matt Wills. The way you described the setup is correct. It is the
way I had done it, just not the way I managed to express it. The
relationships were not th eproblem, the record creation was (or partly
was). By the way, I was trying to avoid having the College/Campus
created prior. When the numbers are large, the user will need to go
through the dropdown list before deciding if the college/campus is
existing or not.

To Helpful Harry: I agree with you, I might have to do it through a
script, but was hoping to avaoid it. However, your point about
creating a record using the "Other..." in a list is incorrect. I am
using FMPro 7, and I can do it for a simple relationship: Dynamic list
generated from field contents, have "Other..." included, and when you
select it, on committing the recod, the related record is also
created.

Anyway, I'll try the script method to create a campus for a college.
I'll let you know how I go.

George