how do I search across relationships?

[J__]

The title sounds kind of funny (at least to me).

I have a master-detail relationship.

In both of those tables I have a creator_name - the person who created the record. I select the name of the creator from another table called authors using a drop down list. Pretty standard i think.

I'd like to have a layout with a menu list/popup containing the authors and a portal, which will contain Master records. When I pick an author from the list, then the master record which contain that name for an author appears in the portal - the really tricky part - or at least not obvious to me (sorry) - IF the master record has a detail record with that same author name, then show the related master record in the portal.

A thought i had was to use relationship between the Master table and the popup list,but then how would i includ the other table too?

thanks for any tips/help in advance,

sincerely,

J__

[Søren Dyhr]

I think it's confusing you're talking 'bout "both tables" when you only mention a portal of master records...in what??? Is it a selfjoin???

--sd

[Ugo DI LUCA]

Hi,

A Table Master and a Table Details...I would think Soren.

Then another Tables Authors, from which the value list takes its values.

There's an author field in either the Master and Details records.

You want a portal in Master listing either the Master records or the Details records, according to the content of a global field.

The global is populated by a value list of authors and when the global meats the content of the Author field in the current Master record, you want a list of related records.

If it doesn't, you want a list of all Master records with this Author value in the Author field.

What you have in mind isn't feasable because a portal only points to one Table and this Table *records*, although you can display *related data* from these Table records.

Not sure if this is clear...RECORDS vs DATA.

Anyway, an alternative would be to structure the Details Table such that you can mimic the initial goal.

You would just need the Master Author to be either looked up or auto-entered in the Details Table, and then use a trick to filter and retrieve only unique occurrence whenever the global author field is not equal to the current record Author value.

You may have 2 Author fields in there then...strange, but we don't have enough information to make it otherwise I'd think.

[J__]

I'm sorry I wasn't clear.

there are 2 tables Master and Detail. Master table is a Songs table. Among other fields, It has an author field - who wrote it. The detail table is a performer table and in that table I have a field which is Performed_by - the person who performed on the song. A Song can have 1 or more Performances so it's 1 to many.

in the drop down list are authors/performers (it's above the portal on the layout and contains all authors in the authors table. In the portal i only display songs

when I pick a name from the authors drop down list, what I want to do is to display in the portal

is a list of songs that have the authors name in the Author field of songs OR in the performed_by field of Performer table.

so say I pick Jimmy Page from the Authors list

in my songs table i have the song "stairway to heaven" and also a song called Jeff's Blues. Jimmy page performed on Jeff's Blues and he wrote stairway to heaven. So, Jimmy's name appears in both tables in the author and performed_by field respectively.

I would expect to see both songs listed in the portal when I select Jimmy Pages' name from the drop down list because he Wrote one of the songs and performed on another.

thanks and I am sorry I wasn't very clear about it.

sincerely,

J__

[Søren Dyhr]

This is a perfect example of where this tip works:

http://previews.filemakermagazine.com/videos/513/DataTagging_full.mov

...it havn't occured to you that you might have several notes, so they also needed to be related??? You're thinking you filestructure too flat!

--sd

[J__]

I listened to a little of it great stuff!

I'll have to listen to the rest of it to get the full gist.

to see if it does fit in, but the organization and data tagging while cool and a great concept.

I still haven't gotten to the searching part, which is what I need.

thanks,

J__

[Søren Dyhr]

I still haven't gotten to the searching part, which is what I need.

Is it, isn't it rather to search the record for Jimmy Page, and see all the notes ...or du you need to go from Yardbirds to Jimmy???

--sd

[J__]

thanks for the response, i really do appreciate it.

I have a drop down list of songwriters and a portal.

the portal displays all songs that have the songwriters name in it in either the Songs Tables author field or in the related table (1-many) Performers, which has a player field in there.

I pick jimmy page from the drop down list

and I see 2 songs appear in the portal. They are "Stairway to Heaven" and "Jeff's blues"

Stairway to heaven - shows up because he has an author credit in the songs table.

Jeff's blues - shows up because he has a player credit in that table.

I feel like this can be done, but i just some help getting started.

thanks so much for your time,

sincerely,

J__

[Søren Dyhr]

I feel like this can be done, but i just some help getting started.

And I feel that I could say that these notes belongs as separate records in the portal not as flat structured at you wished it to be ...I fail to explain this any better, so I smacked together a template for you. Then if you feel like it could you implement Matt Petrowski's filtering later???

--sd

JimmiPage.fp7.zip

[comment]

what I want to do is to display in the portal

is a list of songs that have the authors name in the Author field of songs OR in the performed_by field of Performer table.

The way you have it structured, you can't have it in a single portal. You CAN have two portals, one showing songs authored by the selected person (from Songs), the other showing songs performed by the same person (from Performances). -- see attached --

You could re-organize your structure into 3 tables:

- Songs

- People

- Events

The Events table would record every instance of a person-song interaction, such as:

Jimmy Page -- Stairways to Heaven -- author

Jimmy Page -- Jeff's Blues -- performer

IMHO, this is the proper way to do it, since here you can base your relationships on serial-numbered ID's. Otherwise you're headed for a disaster, since (a) there are many songs that have the same name, and ( a simple misspelling will break the link.

So actually the Events table could like this:

P426 -- S128 -- author

P426 -- S697 -- performer

songs.zip

[J__]

Thanks to all for the responses

And they make some good points. And the examples too thanks!

However, a Song has one or more performers on it and as a result, it seems to me to imply a relation there.

for example, The Song Stairway to Heaven had 4 performers on it - John Bonham, Robert Plant, Jimmy Page and John Paul Jones. Another song, "You Really Got Me", by the Kinks had 5 performers on it. You might be surprised to find this out, but Jimmy Page played the lead guitar solo on You Really got me, so he is listed as the 5th performer on that song.

A song also has a the Author, which could be 1-M too, but I'm ignoring that for now, publisher associated with it, when it was recorded, where it was recorded, copyright information, and who the song was written by.

hope that explains why i need a 1-M relation with Songs to Performers.

- So Jimmy Page and Robert Plant wrote Stairway to Heaven, so the Author field has "Jimmy Page and Robert Plant"... but let's just say Jimmy Page, ok?

and "You Really Got Me" by the kinks , although not authored by jimmy page does have him listed as a performer (it was a secret to the world for years actually)...

So when I pick his name from a drop down list, I want the portal of Songs to display all the songs which he either authored or performed on.

if I could do this in sql I'd say

SELECT Songs.Name

FROM songs s, performers p

WHERE (s.Author = p.author) AND

(s.Author = aDropDownListValue) ;

but I don't know how to convert this to filemaker or at least what I am trying is not right.

I get the part about doing relations between 1-M tables, but the 3rd table i want to join on i don't follow.. yet...

thanks again for your suggestions and help, hopefully this clarifies why I can't do it the other way you suggested - which has some good value to it , but not for what I am trying to do.

sincerely,

J__

[comment]

SELECT Songs.Name

FROM songs s, performers p

WHERE (s.Author = p.author) AND

(s.Author = aDropDownListValue) ;

I believe that is a FIND, not a portal. A portal shows the relevant (i.e related) records dynamically. No trigger is required. A Find creates a static found set based on a request.

You can do this with no problems in FMP. Simply go into Find mode (in Songs) and search for records that have a related author OR a related performer with the desired name. And you can also automate the find via a script.

[RalphL]

What you have is a many to many relationship between people and songs. So you need a join table which Comment calls events. Now you have one to many relationship from people to events and one to many relationship from song to events. In this table you have the 2 foreign keys and field that shows what the person did in that song.

You now can have person be a performer or an author for 1 or many songs. You can have a song performed by 1 or many performers and written by 1 or many authors.

A portal in the people table will show all song written or performed by. A portal in song table will show authors and performers. I think this is what you want.

[comment]

a join table which Comment calls events

I should have called it "Credits". That would stress the first point here, namely that it makes no sense to separate authors' credits from performers' and other credits.

It is actually possible to do what he wants without a joint table, by linking gPerson to Person in Credits, and then linking "back" to Songs via SongID. But all it takes is "Jimmie" instead instead of "Jimmy" and the whole thing collapses.

[J__]

Ralph,

you are right! That is exactly what I want!!

could you provide some 'baby steps' on how to do this or maybe you might be able to refer me to a sample?

thanks to everyone for commenting and suggesting things I really appreciate it. But I do need to have 2 tables and they have to be related. I know it may not be the best way, but it seems to make sense to me and Songs Has a Performer seems like a resonable relationship.

Ralph you mention Events, can it be a list of Performers?

thanks for the help and any tips in advance,

sincerely,

J__

[comment]

I am confused: is your reply meant for me or for Ralph?

[RalphL]

I have a set of sample files for a separation model many to many relationship in the separation model fourm. It will show the basic details of the many to many. In the People Table make a field People ID which is an auto-entered serial number. In the Song Table make a field Song ID which is an auto-entered serial number. In the join table Events or Credits, make a field People ID, a field Song ID and a field Credit. Make a relationship from People ID in People to People ID in Credits, i.e., Performer or Author. Make a relationship from Song ID in Song to Song ID in Credits.

Use the methods from my files to enter data into the join table. If a song has 2 authors that will require 2 records in the join table, if it has 3 performers that will require 3 more records. A portal in Song will show all authors & performers. A portal in People will show all songs the person has performed or written.

[J__]

comment - thanks for the response. The user would never type in the name "Jimmy", they pick it from a drop down and filter on it. So, there would not be an error there. Certainly an ID link approach would be more robust.

Ralph, thanks, I'll check out those files. I have noticed the separation forum a few times and just haven't had a chance to go in there. Thanks for the tip.

sincerely,

J__