[FileMaker Newbie!]

I'm used to using SQL for databases, and I really can't get my head
around the way Filemaker does it (it took me long enough to come to the
conclusion that in Filemaker parlance, "Database" means "table", rather
than "database" - though I'm still not entirely sure!).

Anyway, I want to have a database with two sets of data, "Contact" and
"Conference". A contact can attend many conferences, and a conference
can be attended by many contacts. I'd like to display all contacts
attending a given conference, and I'd also like to display all
conferences a contact is attending.

In an SQL environment I would have three tables:

Contact
-------
ContactID Unique number, not null.
FirstName Text
LastName Text


Conference
----------
ConferenceID Unique number, not null.
Subject Text


ContactConferenceLink
---------------------
ContactID Number, not null
ConferenceID Number, not null


and to find out all the attendees for a given conference I would do:

SELECT Contact.FirstName, Contact.LastName
FROM Contact, Conference, ContactConferenceLink as Link
WHERE Conference.ConferenceID = 7
AND Conference.ConferenceID = Link.ConferenceID
AND Contact.ContactID = Link.ContactID;


And to do the reverse, all the conferences a given contact is attending,
I do something similar:

SELECT Conference.Subject
FROM Contact, Conference, ContactConferenceLink as Link
WHERE Contact.ContactID = 89
AND Conference.ConferenceID = Link.ConferenceID
AND Contact.ContactID = Link.ContactID;


I'm trawling through the manual and trying things out, but I can't seem
to figure out how Filemaker does the equivalent to such a simple SELECT
statement.

Can anyone help me? I've a feeling it's because I'm entrenched in an SQL
mindset but I can't see any other way of doing it.

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

In article <1fzdn7o.1w1sog21m63az4N%me3 (AT) privacy (DOT) net>,
me3 (AT) privacy (DOT) net (zoara) wrote:

between fields in separate tables. its in the various options that the
flexibility comes in. I have set up a number of databases with similar
relational links to what you want to do, including auto fields that
update the original file. It really is as easy as you think. Throw some
dummy data in there, suck it and see, shouldn't take long.

this very ease of use is part of the whole point of filemaker after all.
It means I don't have to learn SQL to build a fairly sophisticated
database. I even learnt how to script buttons etc. Though in what I do
now the records are all kept in single Excel spreadsheets, eurghhh!!!

Peter

--
Peter Ashby
School of Life Sciences, University of Dundee, Scotland
To assume that I speak for the University of Dundee is to be deluded.
Reverse the Spam and remove to email me.

You are on the right track when you say you are 'entrenched in an SQL
mindset', and you have my sympathy. I think training in more conventional
databases platforms makes it harder to learn the FMP way than if you started
from scratch.

The rough equivalent of Select statements is filtered relationships, in
which calculated fields are used to concatenate various combinations of
fields; these calcs are, in turn, used to build relational keys.
Non-filtered relationships, which do not require calcs as keys, will return
'Select' results built on single criteria, such as, in your example, the
ConferenceID. The 'results' of these relationships are found by creating a
found set, using the script step 'Go to related record (Show only...[the
relationship])', or by displaying the results in a portal, a graphical
device that does just that.

The other way to emulate Select statements is with Finds, which can be
scripted or performed manually. They are not too complex, and the manual
will explain them better than I would.

It's also possible you are still shy one step in understanding FMP's
implementation of tables: each file is one table. Your example, therefore,
requires three Filemaker files (their conjoined entity is called a
'solution', but the term is often not used), one for each table.

In your first example, all you need is a relationship, in the Conference.fp5
file, to the join file ('ContactConferenceLink.fp5'), using the ConferenceID
as the relational key on both sides. If you create a portal, in
Conference.fp5, that displays that relationship, you can place, in each
portal row, the fields you need to identify the results (First, Last, ID).
But wait-- you haven't added the name fields to the join file, because they
live in their own Contacts.fp5 file. In Filemaker, it's difficult to look
two files away, and so you are better off adding the name fields to
ContactConferenceLink.fp5, even though Mr. Codd will turn over in his brand
new grave. In ContactConferenceLink.fp5, define a relationship, based on
ContactID, to Contact.fp5, and define the name fields to auto enter
looked-up values from that relationship. You will see that, when valid ID's
are entered into ContactID in ContactConferenceLink.fp5, the two name fields
will be immediately populated with their appropriate values.

Instead of (or in addition to) a portal, you can simply call the step 'Go to
related records(Show...)', followed by an External sub-script step in the
child file that goes to an appropiate layout. The effect of this routine is
to create a 'found set'-- equivalent to the results of a Select statement--
populated by the child records.

Similarly, in your second example, you would use the ContactID as the key on
both sides to ContactConferenceLink.fp5 to see which conferences the current
record attended.

If you want the ability to select, in either example, the left hand key on
the fly, rather than depend on the current record for its value, you can
format a global field, of the same data type as the ID, with a value list
populated by that ID field. Thus, in Contacts.fp5, that VL would display all
the ContactID's in the file; these VL's can also display (but not actually
include) an identifying field, such as a calc that concatenates the first
and last name. Now build a relationship from the global, instead of the ID
field itself, to the ID field in ContactConferenceLink.fp5; selecting a
value from the pulldown list will change the left hand key, and the results
will change accordingly. Obviously, this method is of little use with large
recordsets.

If you wanted to filter a relationship, say which attendees for a given
conference came from one state (assuming US addresses), you would build a
key:

ConferenceID & " "& State

....for the right hand key, assuming you had added a State field to the join
file. For a left hand key, you could either hard-code a calc:

ConferenceID & " " & "AZ"

....or, more likely, use another global field, populated with a list of
states, and a calc that concatenates that pulldown to ConferenceID, or a
global with a pulldown list of ID's.

You do not need to declare the null property of a given field, as you are
doing ('not null.'), although such field attributes (same thing with
uniqueness) can be added to the field definition in the Validation pane. Be
aware, however, that field validation is executed only after a value is
submitted, which means it cannot be headed off first.

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Associate Member, Filemaker Solutions Alliance


"zoara" <me3 (AT) privacy (DOT) net> wrote

PeterD <pd.news (AT) dsl (DOT) pipex.invalid> wrote:

I've done exactly that; it was there that I got stuck. I'm only able to
show the data in ContactConferenceLink, ie I can show that Contact
"Fred" is going to Conferences 7, 18 and 39 because
ContactConferenceLink contains the records

ContactConferenceLink
---------------------
ContactID ConferenceID
269 7
269 19
269 20

(where 269 is Fred's ContactID) but I can't show what those conferences
actually *are* because that info is listed in the Conference table:

Conference
----------
ConferenceID Subject
18 Databases and Spreadsheets - the differences
19 FileMaker Basics
20 Relational Databases

rather than the ContactConferenceLink table.



The Relationships in FileMaker only span across two files, not three, so
I'm stumped as to how I can make the relationship

Contact Link Conference
------------- --------------- -----------------
FName, CtctID <-> CtctID, CfrceID <-> CfrnceID, Subject

work in Filemaker, without resorting to some sort of kludge whereby the
attendees of a conference are kept in the Conference file itself, which
to my eyes is backwards as it isn't normalised to even 3NF, you get
duplicated data and difficulty in keeping the data clean, and it looks
scarily easy to get into a mess very quickly:

Conference
----------
ContactID Subject
332 Relational Databases
138 Relational Databases
269 Relatoinal (sic) Databases
269 Filemaker Basics

This is - to my eyes - a horrible kludge; maybe I'm missing something?

I've tried creating a Relationship in the ContactConferenceLink table to
the Conference table, so if I *open* the ContactConferenceLink file, I
can see the Subjects of the Conferences (based on the ConferenceID). But
if I try to then access this data through my previously set up
relationship between Contact and ContactConferenceLink, only the actual
Fields (and not the relationships) in ContactConferenceLink are visible
- so I still can't see the Subjects of the Conferences.

Do I need to perhaps use a lookup from ContactConferenceLink to get the
data from Conference, which will then be accessible via my Contacts <->
ContactConferenceLink Relationship? This smacks of even *more*
duplication, but is probably less error-prone.


I'm glad it's not just me who feels that way.

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

Peter Ashby <p.r.ashby (AT) MAPS (DOT) dundee.ac.uk> wrote:

That's not it though! I can't make that link, so either it's not
possible or I'm missing something obvious and fundemental. I can link
between two files easy, but not three.

Putting my question another way, I would like to have a number of
contacts each of whom can attend one or more conferences. How do I set
up a database(s) in FileMaker so I can display a) all the attendees at a
given conference and b) all the conferences that a contact has attended?
I'm familiar with creating Portals and Relationships, but can't see a
way to achieve this.

How did you do this?

I've got dummy data in Contacts and Conferences. From a Portal in a
Layout in Conferences I have a Relationship to ContactConferenceLink so
it will show the *ConferenceID* of all the Conferences of the Contact I
am looking at.

What I *can't* do, and want to do, is show the *Subject* of all the
Conferences a contact is attending (and, conversely, the Name and other
details of all the Contacts who are attending a given Conference). I
can't see any way of doing this, and I've been trying lots of different
things. The instruction manual doesn't appear to instruct on this
subject, and the Help isn't helpful. I've googled too. Now I'm stuck.

Though conversely, FileMaker's Ease of Use is what has got me stumped
coming from an SQL background. A join such as this is something I used
all the time when pulling data from databases with millions of records
in - it has become second nature to me; the SELECT statements and their
outputs were the interface, which were entirely separated from the data
tables themselves (which existed in a kind of limbo, somewhere).
FileMaker's method of having the data and the interface all in one file
is confusing to me, to say the least, as the actual contents of this
given table are entirely irrelevant to me unless joined with one, two or
a dozen different data sets.

What I tried to do was create a file called "Interface" which contained
the interface to all the data, but no actual data itself. This was
intended to pull data from other files and display it to the user, but
it didn't work and looked very much like a blind alley so I backed out.
What I'm wondering now is whether I should be looking into that route
again.




[this post now heads off topic, sorry]

When I used to work for a certain telecoms multinational, an entire
application was built in Excel. It took various data from the billing
database, applied rules to it, and after many stages of human
intervention it finally spat out a "These are the cheapest tariffs for
these customer" result.

Because it needed so much looking after just to keep it up and running,
I rarely had time to look into ways of improving it, though I kept
saying to my boss it would be worth me spending some time doing so. One
day I had a couple of hours to spare, so I moved one of the stages from
being worked out in Excel on the desktop machine to being worked out
directly from the billing database using SQL from some server or other.
The stage that used to take two and a half hours and made the desktop
machine unusable [1] took all of a minute and a half. Did I get praised?
Did I hell.

The person who took over my job dumped the lot and rewrote it from
scratch; what took 2 days in perfect circumstances (including running
overnight) now takes about half an hour. People never use the right tool
for the job. Excel instead of SQL and a bit of shell programming?
Morons.


-zoara-



[1] Not only did it slow the machine down (and it was top of the range
at the time, not an old clunker), it also used the system clipboard, so
you couldn't copy and paste. If you forget and use copy, Excel crashes,
and you have to start over.

--
"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

Build your relationships from Conference and Contact to the join file, not
to each other. Yes, you will need to add, as lookups, whatever data fields
you want to display, to the join file, even though it 'it isn't normalised
to even 3NF'.

If you keep trying to force the model to the SQL model, you will keep being
disappointed. While breaking basic normalization may seem like a crime, it's
actually not all that tough to manage, although there's no way around the
fact that there is no real enforcement of relational integrity. Two
(somewhat mutually self-exclusive) arguments: Codd's rules reflect a
necessity for economy in storage and processor power that is less important
now than in 1978; we have been asking for years for a more conventional
model in which the data ('tables') are separated from the meta-data, and we
hope that the next version, under development for some two years now, will
give us that, as well as the ability to look two tables away.

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Associate Member, Filemaker Solutions Alliance


"zoara" <me3 (AT) privacy (DOT) net> wrote

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.

<SNIP>

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 thread already has some good (great) responses, 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.)

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.
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.)
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.

-----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. 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.

Does this ring a bell with anyone else? It's been working really well for
me, but I haven't seen it used elsewhere. The advantage, of course, is that
it's a direct relationship. The only shortcoming I've found is the 65,000
character limit in a text field, but that's not a serious limitation in most
cases, since the key only needs the foreign keys of, say, all contacts at a
conference, and even with 9-character keys, that would allow about 6,500
related Contacts. I'm curious as to whether anyone else has used this
method, and whether they have found any other shortcomings or opportunities
from this method.

I'm also curious, Zoara, as to whether SQL supports compound keys, or
whether this is a fluke in FM. 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?

If anyone wants to discuss this, I could post an example.

--
Shadenfroh
shadenfroh (AT) yahoo (DOT) com


"John Weinshel" <john (AT) datagrace (DOT) biz> wrote

In article <1fzdn7o.1w1sog21m63az4N%me3 (AT) privacy (DOT) net>,
me3 (AT) privacy (DOT) net (zoara) wrote:

FileMaker has a GUI and no text interface.

You set up the three tables (in FileMaker they're files) just
the way you outlined then use the 'Relationship' menu item to
define the relationships between them. To show the related
records you use a 'portal' in a layout for your file. So you
can put a portal to the 'middle' file in a layout for your
conference file and show all the related entries in this middle
file.

Read the bit of the manual about relationships.

John Weinshel <john (AT) datagrace (DOT) biz> wrote:

Yes, I tried this and succeeded - took me a while (and several re-reads
of the posts in this thread) because I'm new to FileMaker but I finally
got there.

I know, I've dicovered that. But I've done *loads* of database work [1],
both designing and maintaining, and all of it's been in SQL (except
those few times I was forced to use MS Access, eurgh) so I'm very
entrenched in the SQL style mindset. I actually think in tables and SQL
statements! I'm finding Filemaker hard going.

I wasn't particularly bothered about those (the database will run to
hundreds of contacts, not millions) - I was more concerned with
integrity; ie not wanting to duplicate data (because updating an address
when it exists in several places is asking for trouble). Calculated
fields seem to have solved (much of) that problem for me, though I've
now run into another wall, which I'm starting a new thread about.

Oooh, I hope so too. That would help a lot.

-zoara-

[1] Well, relatively speaking. Not enough to be paid loads as a DBA, but
still a lot during my career.

--
"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