FileMaker Pro 6 and relational databases

#11

Shadenfroh <shadenfroh (AT) yahoo (DOT) com> wrote:

Quote:

Instead of using lookups in the interface file, you could use a calculated
result, which will update, for instance, if the name is changed in Contacts.

This is the route I eventually took.

Quote:

This thread already has some good (great) responses,

Yes, and thanks to everyone; they've all been helpful.

Quote:

but here's how I would
describe it, in case a different explanation might help.

1)Contacts has a one-to-many relationship to Link, by Contact ID.
2)Conferences has a one-to-many relationship to Link, by Conference ID.
3)Link has a relationship back to both Contacts and Conferences, from which
you can show calculated results of attributes of each (i.e. a calculated
Conference Name, First Name, Last Name, etc.)

Yep.

Quote:

Using these, you can:
1)Show a portal from Contacts and/or Conferences to Link, so that attributes
of the related Conferences are visible from Contacts, and vice-versa.

Yes, I've got this far. You can now open the Conferences record for
"FileMaker and You" and (as it has a portal to Link, which itself has a
calculated result for FirstName, LastName etc in Contacts) it shows, by
name, which contacts are attending the Conference.

I've also done this the other way around (ie a portal from the contacts
file), so I'm confused as to your suggestion (below) of using compund
keys to discover which conferences a contact is attending. How are
compound keys 'better' than a portal and calculated fields in the
related file?

Quote:

2)Create two-step navigation scripts from Contacts to Conferences and back
for a particular related Contact or Conference, as a "drill-down" button -
(i.e. from Conferences, Go to related in Link, then perform a script in Link
that goes to relate Contacts.)

This sounds useful; the idea being that I can click on a contact in the
portal in Conferences.fp5 and it will show me the full details of that
contact?

I've happily created a button in my Portal that will go to the related
record in Link.fp5, and I've created a script that will (manually) get
from there to the relevant contact, but how do I get Filemaker to do
that second step automatically? Can I do it without the Link window
appearing onscreen?

Quote:

3)Create navigation from a Contact to all related Conferences - Create a
value list that shows all the Conference ID's from the related Link records.
To navigate, use the valuelistitems function to set all those keys into a
text field (as a compound key), then go to related Conferences directly
where the compound key equals the Conference ID in Conferences.

*whoosh!* That went right over my head. Let me read it again.

No, I still don't understand it! Bear in mind that I have effectively
never used filemaker before the end of last week...

Let's go through it again and I'll comment and ask questions.

Quote:

3)Create navigation from a Contact to all related Conferences - Create a
value list

Yep, value lists I can do.

Quote:

that shows all the Conference ID's from the related Link records.

Right, got that (I think).

Quote:

To navigate, use the valuelistitems function

The what? I can't find anything like this, where should I be looking?

Quote:

to set all those keys into a text field (as a compound key),

That makes sense.

Quote:

then go to related Conferences directly
where the compound key equals the Conference ID in Conferences.

Would this be another button/script?

I can almost understand what you're saying, but not quite

A little
help would be appreciated!

Quote:

-----Slightly different topic
For many-to-many relationships where the relationship itself has no
attributes, I've been making more and more use of compound keys. In the
example given, I would have a "choose" script where the user can choose or
add Conferences to Contacts, f'rinstance.

Oooh, ooh! *excited* that's exactly what I'm after, and exactly where
I'm stumbling at the moment. I can browse the current content, but can't
add new relationships in an easy way. What I want to be able to do is:

a) From the contacts window, have a button that says "invite to
conference(s)" (or whatever) which, when pressed, will display the list
of conferences and allow the end user to check those they wish to invite
the contact to.

and

b) From the conferences window, have a button that says "invite
contact(s)" (or whatever) which, when pressed, will display the list of
contacts and allow the end user to check those they wish to invite the
to the conference.

At the moment I can't figure out how to do that.

Quote:

As they are added or chosen, the
compound Conference key in Contacts is updated. The relationship is then
direct, where the compound Conference key matches the Conference ID in
Conferences. It's a little trickier to do the same from the Contacts file,
since the compound is on the other side of the relationship, but since the
adding and choosing is all within a script, it's pretty easy to control, and
if you get it right once, you can use it wherever you need this type of
relationship.

It sounds useful. I can understand the concept, but not how to do it in
practice.

Quote:

I'm also curious, Zoara, as to whether SQL supports compound keys, or
whether this is a fluke in FM.

I think it probably doesn't; leastways if it does I've never used it. To
be honest, I'd always have a link table in these circumstances, and
every database I have seen as a link table. I can see what it's doing,
but a link table means that you have an 'equal' relationship on either
side (ie it's as easy to get "all attendees at this conference" as it is
"all conferences this contact is attending") whereas compound keys seems
a bit one-sided.

Quote:

If you have a text field in one file with
multiple values separated by returns, like this

1
2
3

and you relate (join?) that to another file's field that has a single value,
will the compound relate to records 1, 2 and 3 in the foreign file?

I'm not sure I understand what you mean here. If I understand correctly,
SQL would treat that "compound key" as a single string, ie "1<CR>2<CR>3"
where <CR> is a carriage return character. So it would only match to
those records in the foreign table where the joined value is (literally)
"1<CR>2<CR>3".

-zoara-

[huge snip]

--
"I'm not sure how useful this is, but it's bloody clever."
- Jonathon Sanderson in uk.comp.sys.mac
Are you posting responses that are easy for others to follow?
http://www.greenend.org.uk/rjk/2000/06/14/quoting

#12

Glenn Schwandt <schwandtgat (AT) aoldot (DOT) com> wrote:

Quote:

Consider doing all of this from the middle. Create reverse relationships
from the join file back to both the Conference and Contact databases. Now,
to find all attendees for a given conference, just perform a find on the
Conference ID in the join file and use the two new relationships to fill in
the appropriate details on your layout. Find on Contact ID and go to a
different layout to get the list of conferences for a particular contact.

Hmmm.

I'll give that a go. I've got it working the 'other' way, but can't
actually *enter* new relationships (ie invite a contact to a
conference), at least not in a user friendly way.

This way might work. I'll look into it tonight, hopefully.

Thanks.

-zoara-

--
"I'm not sure how useful this is, but it's bloody clever."
- Jonathon Sanderson in uk.comp.sys.mac
Are you posting responses that are easy for others to follow?
http://www.greenend.org.uk/rjk/2000/06/14/quoting

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

FileMaker Pro 6 and relational databases

comp.databases.filemaker comp.databases.filemaker

Re: FileMaker Pro 6 and relational databases - 08-13-2003 , 11:55 AM

Re: FileMaker Pro 6 and relational databases - 08-13-2003 , 11:55 AM