Problem with relationships between tables

[Rhonda L]

Hello - I am very new (totally new) to building databases and am trying to build a database for a nonprofit group. I have four tables: Contacts, Donations, Accession Lots and Artifact Catalogue (see attached). I have had pretty good success on the 'individual side' - contacts and donors as these tables are about individuals. I have used several layouts from the same table and this has worked beautifully. I even added a button for a popup with contact info for a donor coming into the Donor Table from the Contact Table. Tah dah!

Now my problem is that the other 2 tables, Accession Lots and Artifact Catalogue are about items donated to a museum. I need to tie in the information about the artifact donor, but a donor may make artifact donations several times over multiple years. Accession lots are the 'clump' (such as a boxful) of stuff they bring to donate. Then each item in the box needs to be detailed in the Artifact Catalogue, but need to pull over the Accession No. (which would tie to the person who donated them). The way I built this results in only one record per individual through the Contacts table - so if I try to add a second Accession Lot, it replaces the first record. I know that I should have a new set of records based on the items, but tried to do a lookup relationship as well as a related table relationship using FileMaker's instructions in help (and it couldn't find the record no matter what I tried). I am using FM 11.

I don't want to think how many hours I have spent trying to figure this out and running out of time to get it functioning properly in addition to getting somewhat grumpy about it. Likely that I am missing something obvious, but would very much appreciate any and all help!

Tables FM.doc

[comment]

First and foremost: do not use names as matchfields in relationships. In each table, define an ID field as auto-entered serial number field, and use it as the matchfield to child tables, e.g.:

Donors::DonorID = Donations::DonorID

Next, this part doesn't make sense:

info for a donor coming into the Donor Table from the Contact Table.

You don't have a Donor Table.

Now, are all of the following statements true:

1. One donor may donate many accession lots;

2. Each accession lot is donated by one donor only.

3. Each accession lot may have many artifacts;

4. Each artifact belongs to one accession lot only.

[Rhonda L]

First and foremost: do not use names as matchfields in relationships. In each table, define an ID field as auto-entered serial number field, and use it as the matchfield to child tables, e.g.:

Donors::DonorID = Donations::DonorID

Next, this part doesn't make sense:

You don't have a Donor Table.

Now, are all of the following statements true:

1. One donor may donate many accession lots;

2. Each accession lot is donated by one donor only.

3. Each accession lot may have many artifacts;

4. Each artifact belongs to one accession lot only.

Thanks for assisting! Yes the statements you have are correct. Sorry a slip up with the quote - I meant the donation table. So I would create the Donor ID as a calculation field (text)? I am thinking I would put that as a field in the Contact Table and then use that ID field for the Accession Lot Table? Sorry to be really naive, what does :: mean?

[comment]

So I would create the Donor ID as a calculation field (text)?

No. You would create a DonorID in the Donors table (or a ContactID in the Contacts table, same thing) as a Number field, and set it to auto-enter a serial number in the field's options. Next, you would define a DonorID field in the Donations table as a plain Number field (and a DonationID field as auto-entered serial number).

Note that auto-enter works for new records only, so you'll need to renumber any existing records using the Replace Field Contents... command.

what does :: mean?

The full name of a field is written as TableName::FieldName.

Back to your original question - I am not sure where the problem lies. You describe a structure of:

Donations >- Donors -< AccessionLots -< Artifacts

This translates to the following relationships:

Donors::DonorID = Donations::DonorID

Donors::DonorID = AccessionLots::DonorID

AccessionLots::LotID = Artifacts::LotID

[Rhonda L]

Revised relationship graph.doc

No. You would create a DonorID in the Donors table (or a ContactID in the Contacts table, same thing) as a Number field, and set it to auto-enter a serial number in the field's options. Next, you would define a DonorID field in the Donations table as a plain Number field (and a DonationID field as auto-entered serial number).

Note that auto-enter works for new records only, so you'll need to renumber any existing records using the Replace Field Contents... command.

The full name of a field is written as TableName::FieldName.

Back to your original question - I am not sure where the problem lies. You describe a structure of:

Donations >- Donors -< AccessionLots -< Artifacts

This translates to the following relationships:

Donors::DonorID = Donations::DonorID

Donors::DonorID = AccessionLots::DonorID

AccessionLots::LotID = Artifacts::LotID

Oh I am getting so much closer! Thank you. I used Contact ID as it seemed less confusing between Donor and Donation.

So, I have

Contacts::Contact ID (Indexed, Auto Serial Number)=Donations::Contact ID (indexed number)(this relationship is working)

Contacts:Contact ID(Indexed, Auto Serial Number) =Accession Lot::Contact ID (indexed number)(this relationship is working)

Accession Lot::Lot ID (Auto Serial Number)=Artifact Catalogue::Lot ID (indexed number)

I apologize for being so dense - a lot of new terminology for me -

Now one small hiccup (another attachment). I get fields with 'yellow' magnifying glasses that will not let me enter in those areas in the Browse view. My contact information (about the individual)transfers over but I have the table as Contacts. If I put the table as Accession Lot, everything is yellow but the Accession No. field.

If I have the table as Contacts, then the Accession No. field has the yellow glass but I can enter into all other fields.

You do understand how the information should be flowing between these areas-I'm just not too swift at making the relationships talk to each other apparently. Thanks for your patience.

[comment]

I get fields with 'yellow' magnifying glasses that will not let me enter in those areas in the Browse view

The yellow magnifying glass indicates a field that is less than optimal for searching - in this case a field from a record in a related table. You probably do not want to modify such field in Browse mode - for example, the contact's name on a layout of Donations. In any case, the field can be entered if you set it as such in the Inspector (Data > Behavior > Field Entry).