The mess I'm in...

[alex.v]

Hi there,

Im new at making FMP relational. Im trying to create a database for my research on designed objects. Kind of like a museum/library DB. At the moment I have four main files:

AUTHORS.fp5 - Has information on the Authors of Publications and Objects.

OBJECTS.fp5 - Has information on designed Objects in the museum.

PUBLICATIONS.fp5 - Has information on books etc, published by Authors.

QUOTATIONS.fp5 - Has quoted text taken from Publications.

These have a number of other minor related files. It seems that I can handle the one-to-many relationships, but Im not sure about the many-to-many. For example: one Author can have many Publications and one Publication can have many Authors. The same for Objects and Authors. I have these portals to minor files, some are strictly many to many. The many to many relations seem to work anyway, except they create a lot of records.

If anyone could take a look at the zipped files I have attached, and explain to me where you think Im going wrong, it would be much appreciated. That is if you can understand what Im trying to do.

Im on a different time zone, so may be slow to respond. I have FMP 5.5 on Mac OS 9.2.

Research.zip

[Fitch]

It's the nature of many-to-many relationships that the join file will have a lot of records. I wouldn't worry about that. The question is, where do YOU think you're going wrong? Is there something you're trying to accomplish that isn't working? Let's start with something specific.

[Ugo DI LUCA]

Hello,

I just had a quick view over your files. Your many to many relationship is not a many to many relationship.

The way it is build right now, you are creating duplicate records instead of relationships.

In order to have it work you would need (for one of your relationship at least) a kind of "line item".

Basically :

OBJECTSTO AUTHOR (Line Item)

Obj_k

Auth_k

OBJECTS - Obj_k (related to OBJECTSTO AUTHOR through Obj_k showing all Auth_k attached to it)

AUTHOR - Auth_k (related to OBJECTSTO AUTHOR through Auth_k showing all Obj_k attached to it)

OBJECTTOAUTHOR hold one line per "join" and you can create related records through the portal in Author. Instead of creating a new record in the Author file, it will create a new record for Objects in the Line Item, then when populated by the Auth_K, it will create a new record for Auth_k and Obj_k. As these 2 keys are related to Author and Objects, a new record will be created in these two files.

Does it make sense ?

[alex.v]

In reply to Tom:

I spent a bit of time planning the DB and being my first attempt at a big relational one, there was a lot of swopping and changing of fields and defining new files etc. In my mind I have pretty well defined basic objectives: Data that needs to be stored (fields), groupings of that data (files) and ways I want to view that (layouts). The details, Im hoping will be resolved as I build it and learn FMP.

The problem I saw, was that a lot of records were being created in the way I had built it.

In reply to Ugo:

Thanks for the concise explanation and pointers. I will try to build it as you have said and get back to you.

Watch this space...

[alex.v]

Ugo,

I have tried to build what you described. So far no success.

In using the DB, I want to create related Author entries whenever I add new authors in the Objects file. Does what you described mean that I must add new records in the line file?

I understand that I need a line items file between the Objects and Authors files. Also, I must create two relationships FROM the Objects and FROM the Authors TO the ObjectsAuthorsLine file. I have done this, but records added in the Objects file, appear in the line file but not in the Authors file.

You mentioned a portal in Authors file: What does that portal show?

(Im finding this more difficult than I imagined.)

[alex.v]

I forgot to add the icon in my last post ;-)

FMP help descibes a typical Classes and Students many to many relationship and recommends line items file and a calculation to solve that. In their version, you add entries in the line items file. Im suprised you have to use a calculation to set up a relationship. And Ugo doesnt mention calculations for my problem: Are there many ways to skin this cat?

[Ugo DI LUCA]

Hi Alex,

In using the DB, I want to create related Author entries whenever I add new authors in the Objects file. Does what you described mean that I must add new records in the line file?

Rather you ask for creating a new Author (non-existant in the Author file) in the Line Item.fp5 or add a Author to the Object file. This may only be some term confusions, but I'll try to make it clearer.

I understand that I need a line items file between the Objects and Authors files. Also, I must create two relationships FROM the Objects and FROM the Authors TO the ObjectsAuthorsLine file. I have done this, but records added in the Objects file, appear in the line file but not in the Authors file.

1. You didn't mentionned how you entered the records in Line Item.fp5, but I assume that your portals and relationships use the Option Allow created records. That's the way you should do it if not.

2. Authors entered in the Line Item File from Objects.fp5 portal must have been created first in Author.fp5. Objects entered in the Line Item File from Authors.fp5 portal must have been created first in Object.fp5

If you are adding James SMITH to an Object in the LineItem.fp5, make sure James SMITH is an existing record in the Authors.fp5. The same is true for the new Objects.

Line Item allows to create related records, that is add an Item to James SMITH's object list, but doesn't allow to create a new record in the Author file.

You must create the new record in Author.fp5 first.

3. In order to "link" Author and Object using the Line Item, Author_ID and Object_ID should be on the same line of Line Item.fp5.

Line#1 AuthorN

[alex.v]

Ugo,

Thanks a lot for your lengthy reply. I read it many times.

I managed to get the many to many relation between Authors.fp5 and Objects.fp5 to work in principle. I can see the way its linked with the line file.

From what you said, I would add new records for the Objects and Authors in their own respective files. And then I must link them in the line file.

But

I havent been able to get portals to show me the information I want to see. For example, I cant make a portal in Objects.fp5 file show me the Authors linked to it.

I have attached what I have so far. After an entire day trying!

Any help appreciated.

Archive.zip

[Ugo DI LUCA]

For the moment Alex, your portal display the Author_ID. If you want the Author Complete Name to appear, just makes it a lookup from the Author file using the relationship Line Item::Author.

I don't know why but your fields in portals are set to not allow entry. Uncheck this option, and enter your records there. Enter a new 50 and you will see John Johnson coming by and say hello next to his ID in the Portal.

I'll wait and see for your future problems.