One(A) to Many(B) One(B) to Many (C)

[Cass]

New User! I've read that FMP cannot do a one to many with a subsequent one to many but that there is a workaround. Could someone guide me to such a work around? For example, one street has many house numbers and one house number has (may have) many families (though usually not at the same time). One CD has many tracks and one track has many artists. I know in this latter case you can use many to many. :confused:

[Ugo DI LUCA]

Hi,

Welcome here.

Very very large topic, and many directions....according to your situation.

One advice...

What is exactly your case ?

Trying to "summarize" with another example may lead to some confusions...

[Fitch]

Take a look at Nightwing's site. The first article "Matrix modeling" answers your question.

[Fenton]

As Ugo says, a big subject. One main concept is that of a "join" file. This file has many records, with any necessary ID's (or other data) needed to tie it to the other relevant files.

It seems like a lot of disk space, but the ID's are a condensed form of data. A unique ID is generated by each of the other files, which can be thought of as "shells," containing or connected to the inner join file(s).

Music is a good example. There are CD's, there are Artists. On any given CD there is probably either a primary artist or "band," group of artists. But there would be other files "below" this. CD_Songs would have an entry for each song on the CD. CD_Song_Artists would have an entry for each unique entry for CD + Song + Artist (since there could be any number of guest artists on any particular track).

There should be "shell" files CD, Songs, Artists; each generating a unique ID for every entry. CD_Songs is also a join file, since it is only unique as a combination.

The designer's job is to make sure that the relevant ID's are correctly passed down the line. Usually Portals are used for this, to pass data 1 layer down.

And you need to make the data easily viewable from above; join files are good for reports, but not the best way to browse data.

*"Shell" is just my own term. You could use "entity," but it may be that every file has an "entity" of some sort. The "shell" file would appear to be the top of the line; but you could go further, with PublishingCompany putting out many CDs. It can go in several directions from there.

[Cass]

Thanks Ugo and Tom for your replies. Here is what I (volunteer, amateur)am actually trying to do for a resident association: Using FMP nomenclature (which I'm still getting used to) I have a DB of street names called, quaintly, Streets.fmp5 It has two fields, Street SN and Street Name. There are only 91 records. The second DB is called Houses.fmp5 and has four fields, House SN, Street SN (for relating to Streets.fmp5) House Number, and a calculated field, addresses (e.g., 745 N. Allyson Dr.). This DB has about 2000 records. The third DB is called Names.fmp5 and has the following fields: Name SN, House SN (for relating to House.fmp5), the calculated address field, and the usual first name, phone number, e-mail address, etc. This DB also has a field called Member which shows whether dues have been paid for the current year. Since all houses are in the same zip code, I can use global fields for city, state and zip. There is also a fourth DB called Checks.fmp5 for recording membership data (check no., date, amt.) but this is not part of the problem. Everything works beautifully! I really like FMP6 compared to other DBMSs I've used in the past. Much easier. But what I want to do is produce a report and a file that I can export that shows member name by street, e.g, all members on N. Allyson Dr. I believe I can figure out how to produce the report if I can link Streets.fmp5 to Names.fmp5.

One way to solve the problem is to put a Steet SN and a Street Name field on the Names.fmp5 DB. Since there are only 91 streets, I could manually enter the Street SN and have Street Name be a lookup. Not very elegant. Another way would be to use FMP's many-to-many outer join, but I studied the help file for that and find that cumbersome. Besides, I don't have many names to link to many streets as in the classic class enrollment application (many students in many classes).

Finally I actually did the CD case using Borland Delphi. My view shows the CDs. Click on a CD and the user gets a "portal" showing the tracks with related data, click on a track, and the user gets a portal showing the artists with related data. Conversly, the user could search for CDs containing a particular track (song) or CDs containing a particular artist. Unfortunately (or fortunately) I can't use Delphi for this application.

If you read this far, thanks for your attention and Tom, I will look up the reference.

Cass

[Cass]

I just downloaded the matrix model article. A quick scan and I think it's what I need. Thanks much.

Cass

[Fitch]

"...what I want to do is produce a report and a file that I can export that shows member name by street, e.g, all members on N. Allyson Dr. I believe I can figure out how to produce the report if I can link Streets.fmp5 to Names.fmp5."

Yup, I would just pipeline that Street SN down to Names, either when the Name record is created, or via a calc field in Names (Houses::Street SN).

[Ugo DI LUCA]

>>Yup, I would just pipeline that Street SN down to Names, either when the Name record is created, or via a calc field in Names (Houses::Street SN).

Surely the easiest solution.

BTW, is a pipeline bigger tha a tunnel ?

Tunneling, Pipelining, meanless to seek in FM help for this....