(Possibly Stupid Question) Searches/queries For Many-To-Many Relationships?

[OptimusPrime]

Hello all,

I have a question regarding a database which I have built on a friend's computer.

Part of this database is a number of many-to-many relationships. Example:

Table_Books includes the following fields

Book_ID (primary key)

Book_title

Book_author

Book_topic

Table_Topics has only two fields:

Topic_ID

Topic_Name

Finally there is Table_Book_Topic, which includes:

Join_ID (auto generated)

Book_ID (from table_Book)

Topic_ID (from table_Topic)

Hence, one topic is covered in more than one book, and one book can touch on more than one topic. So far so good.

I now wanted to create a query for returning a list of all books which include the topic, say "fiction", or a list of all topics from a specific book. I have a (small) MSAccess background, so I figured I'd just make a query. Turns out that in FM you have to write these things yourself. Okay. I can learn new things.

Problem is though that every FM book I found in stores does *not* deal with this problem. I have *very* limited knowledge of AppleScript - I can (and have) copied and pasted scripts from others which have been shared online and figured out which bit does what by try and error. But putting this sort of query together is pretty much beyond me.

I have done a search on this website for relevant threads, but have come up empty so far. Apologies if this has been posted before.

So my question really is - how do I do this? Has someone posted a solution to this before? Is there a really obvious solution that evades me? If neither of those is the case, what is a good book or source from which I can learn how to create my own queries?

Thanks,

OP

[comment]

a list of all books which include the topic, say "fiction", or a list of all topics from a specific book.

You can place a portal on a layout of Topics, showing records from Books. This will list all books that include the currently viewed topic.

You can also have a button set up to Go to Related Record [from Books, Show related only]. This will create a found set of the related books.

And, of course, you can also perform a find - searching the Books table (or the join table) for a value in a related field.

[OptimusPrime]

Hey,

Thanks for the response

I know how 'find' works, I've used it. What find gives me (at least the way I use it) in a layout is the first record of the set of records which the find function has returned. I can then browse through the records by using the 'next record' button top left (or through a button which I've put in to have that function).

What I was looking for -sorry, I didn't make that properly clear- is within the layout of the books table, a field where I can type or (probably easier) select from a dropdown/popup menu the specific topic. There should then be within the layout, below said field a list of books which deal with said topic (i.e. fiction). This will likely be a portal I guess. I then want to be able to click on one record of that list and bring this to the books layout. In other words, I need a quick reference list without leaving the record I'm on at that point. My guess is that I would require a self-reference in the relationships for that.

Also - eventually I'm looking for being able to search for more than one criterion, i.e. 'return a list of all books which have topic: fiction AND topic: philosophy' or 'return a list of all books which have topic: fiction AND author: Mr. X'. However that's more of a future project for when I know more about FM and applescript. The datasets are (will be) rather large, books/authors/articles/quotes/etc.

Part of my problem is that I'm good with database theory (normalization) and inexperienced with execution and building databases. But I'm working on it.

Thanks

OP

[comment]

I know how 'find' works, I've used it. What find gives me (at least the way I use it) in a layout is the first record of the set of records which the find function has returned. I can then browse through the records by using the 'next record' button top left (or through a button which I've put in to have that function).

That will happen if you are in Form view. If you switch to a List or Table view (or - preferably - go to a layout designed for List view), you will see a list of the found records.

What I was looking for -sorry, I didn't make that properly clear- is within the layout of the books table, a field where I can type or (probably easier) select from a dropdown/popup menu the specific topic. There should then be within the layout, below said field a list of books which deal with said topic (i.e. fiction). This will likely be a portal I guess.

That is possible, too - see the attached.

FilteredPortalNav.zip

[OptimusPrime]

That will happen if you are in Form view. If you switch to a List or Table view (or - preferably - go to a layout designed for List view), you will see a list of the found records.

Yes - I don't want to leave form view really to work the database. It's not just made for me, but for others who aren't computer literate.

That is possible, too - see the attached.

Awesome! This is pretty much exactly what I was looking for. Thanks a million!!

I now need to reverse engineer what you've done there and apply it to my database... May need some help with that, so I might be back.

Thanks again!

OP

[OptimusPrime]

OK - so I incorporated this into the database fairly easily, I figured out how it worked (first time I used global fields... still dont quite get how/what they do. But it works )

Now, I have created a second attribute which objects are connected to - features. Hence there's an ObjectsFeatures table. (attached) Is it possible that the results for BOTH searches are displayed in the same portal?

I thought maybe by inserting a table objects on the one hand and objectAttributes and ObjectsFeatures on the other. However this would probably confuse the primary keys and moreover violate normalization I think. I toyed around with it a little but could not make it work. Any ideas? Appreciated!

OP

FilteredPortalNav2.fp7.zip

[comment]

It is possible - but you are gradually approaching a point of diminished returns, where performing a find could be more efficient. Keep in mind that a scripted find can be triggered upon modification of the "search" fields. However, presenting the results of a find in a portal is far from trivial.

---

P.S. I took "BOTH" to mean an OR search; if you want an AND search instead, you need to use the FilterValues() function to get a list of common values only.

FilteredPortalNav+.zip

Edited December 17, 2010 by comment

[OptimusPrime]

It is possible - but you are gradually approaching a point of diminished returns, where performing a find could be more efficient. Keep in mind that a scripted find can be triggered upon modification of the "search" fields. However, presenting the results of a find in a portal is far from trivial.

---

P.S. I took "BOTH" to mean an OR search; if you want an AND search instead, you need to use the FilterValues() function to get a list of common values only.

Thanks a ton for this - I'll try and get my head around how the calculation field works. I'll be expanding on that for other tables/attributes. This is really helpful.

you are gradually approaching a point of diminished returns, where performing a find could be more efficient. Keep in mind that a scripted find can be triggered upon modification of the "search" fields.

Hmm. Maybe I'm not using find correctly. The way I use it, I get either (in form view) records which I can click through one after the other - not a list. Or in List view, just like form view but scrolling instead of clicking, and table view, which is a list - what I'm looking for - but the list is too detailed (I just need the book names, not every other detail) and it goes away from the pretty layout which the user won't appreciate.

However, presenting the results of a find in a portal is far from trivial.

I'll probably stay clear of this then for now. I doubt that I'll need anything vastly more complex than what I have now, with your help and input. I'll try to get the AND search going. Once that is done, the database is by and large where I want it to be, at least until I feel that it needs more features!

Thanks again, I really appreciate the help!

OP

[comment]

. The way I use it, I get either (in form view) records which I can click through one after the other - not a list. Or in List view, just like form view but scrolling instead of clicking, and table view, which is a list - what I'm looking for - but the list is too detailed

You just need to create your own List layout and design it to your satisfaction. Each table can (and probably should) have multiple layouts - for viewing a list, for viewing a detailed form, for printing, etc.

[OptimusPrime]

Okay - I will work on developing better layouts. Thanks!