FM7 Design Question, Relations + Portals

[bAttitude]

Hi All- I am brand new to FileMaker, although I have a broad, nonDB programming background. I am creating a test DB modelling the following - A list of Presentations, each with One Author, a Person, and Several Documents associated with the presentation. I have a good start on this, but am confused about the Docs.

In the enclosed example file, I have a relation to get the Person data, data entry layouts for the 3 source tables, and a pretty display layout for a final product to an audience. I am not sure how to make the List of Documents show up in the final display, nor how to make the list of docs known to the Presentation. I am trying to list the docKeys in the PresentationTO layout. Once I have that working, I will add the list of docs to the final display.

Perhaps someone might throw some advice out there.

thanks in advance -Brian

FileMaker Version: 7

Platform: Mac OS X Panther

testP.zip

[Reed]

I have attached your file (modified)... your relationship between presenation and doc tables should be between two fields that have equal values. I created an extra field in the DocumentTO to match the presentation index. I also modified the relation to allow creation of records. Now you can add records to documents from the portal by simply entering text into the doclink field.

Hope this helps

FileMaker Version: Dev 7

Platform: Mac OS X Panther

testP.fp7.zip

[bAttitude]

What I think you are telling me is that when there is a Table 'Docs', from which several elements will be listed in a related Table Occurance, 'Docs' has an additional field added to it, containing an ID or Index referring to that outside Table Occurance.

In other programming environments, when an object is added to a list, it is the [color:"blue"]list holder's responsibility to store the reference or pointer or copy etc, not the object being listed.

Taking the example further, when a Table 'Documents' has more than one element listed in 4 other layouts, there will be 4 additional fields added to 'Documents'. If a list of 'Documents' occcurs in 17 other layouts, there will be 17 additional fields added to 'Documents', each on with an ID showing where the record belongs in the outside layout.

Forgive me if I am sloppy with the terms, I _am_ new to this.

The only way I can reconcile this way of doing things is to think of a Database Table as so compact, that there really is no place to add the constructs needed to hold a list of other records from another Table. I am not surprised I had not found this myself.

Comments on this welcome. -Brian

[Reed]

That's typically how it works... Each table has one primary key value that uniquely idenitifies a record. a foreign key value in a related table can be used to relate records from separate tables. You don't need to add fields for every layout that references a table, only for other tables. You can always add any field, related or not, to any layout. The portal is just a way that filemaker allows you to view multiple records from another table.

[bAttitude]

I just had a spirited discussion with our programmers group here in Berkeley. Basically, the consensus was that they right way to solve the problem I am trying to describe is as follows:

* a list of documents exists as a 'bottom leaf' table. it will be referred to in possibly many layouts, or Table Occurrence Groups in fm7.

* a particular Table Occurrence is the Presentation, which wants to have a list of documents associated with it.

Rather than adding a new field to the document record structure holding the key of the presentation in which the document appears - which would solve the One To Many problem once - the problem is solved using the same structure as a Many to Many solution, but only happens to associate One presentation with 0 to many docs..

That solution is to create a small, third association Table specific to that layout, holding just key pairs for document and presentation. This makes far more sense to me. The document record structure is not touched, whether it is included in one or zillions of other layouts. The Table Occurrence Group reflects this easily, with the only limitation that there be unique names given to all the Table Occurrences. We would say that the Table Occurrence namespace is flat, so every name must be unique, whether in the same TOG or not. Unique names are easily given by defining some naming conventions and sticking to them.

comments on this welcome. -Brian

[Reed]

The use of a join file is the typical way that a many-to-many situation is represented. whether it be a classes-registration-students, or products-line items-invoices or whatever.

There are some good techbriefs available on the filemaker website

http://www.filemaker.com/upgrade/techbriefs.html

The second article on migration has detailed information on how the relational model works in FM7

[bAttitude]

After speaking with a real FM implementor in person yesterday, an interesting twist was mentioned. When there is a 'bottom leaf' table, like a list of documents in my case, which is only a source of data to all other layouts, adding a foreign key to place its elements in a one to many layout somewhere else seems like very bad design. Particularly when there may be [color:"blue"]one or more linked layouts, each one of which then requiring a foreign key of its own.

The proposed solution is to build an association table explicitly, even though it is being used just for a One to Many relation.

The problem pointed out by the fm author was when it comes to data entry time, a data entry person has to understand the use of the association table, and how to enter data into it. Using the foreign key in a bottom leaf table is easier fot the data entry person.

I am new to FM, so what sort of services are automatic, or not, is new to me as well. I don't know yet the lengths that folks use to make data entry easier, or smooth over abstract concepts into usable data entry screens. I also am not yet familiar with the way that FM creates join table when necessary...

comments welcome -Brian

FileMaker Version: 7

Platform: Mac OS X Panther