Searching One Database from Another and Displaying Basic Results

[Simong]

Hey folks. Bear with me here, I'm very new to filemaker and really enjoying learning about it and experimenting. [CLIFFS NOTES at the bottom.]

I work for a small entertainment company that rents a lot of DVDs and also maintains an internal DVD library for our executives to borrow from.

We have a DVD Library database that lists all of the DVDs we have, and I'm now in the process of writing a database to log and track all of our rentals from outside stores (Let's call it Video Rentals database). What I'd like to do is have a way from within my new rental database to search the dvd library for similar titles. Basically, we want to make sure that people don't rent videos we already own.

My first attempt went like this:

I set up a look-up field at the bottom of the rental database that looked up the rental title in the dvd database through a relationship and then displayed whether that title was found. But the problem with this is that it only finds exact matches. So if someone typed "Blues Brothers" in the Video Rental database it wouldn't match "The Blues Brothers" in the DVD Library.

I'd like something more flexible, or maybe just a button that the user could press that searches the titles in the other database and then displays the results. My gut says this would involve a script using find mode or something like that, but I honestly just don't know. Another thought I had was that there must be a way to make a portal that displayed results. Again, I'm a novice so I'm not even certain where to start.

CLIFFS NOTES:

Two databases, DVD Library and Video Rentals

DVD Library has a field DVD Title

Video Rentals has a field Rental Title

While in the Video Rentals database, I'd like to be able to search the DVD Title field in the DVD Library for similar titles and bring up the results in the Video Rentals database.

Thanks in advance for any help anyone can provide.

Edited May 5, 2009 by Guest

[BilesterM]

I see 1 database 3 tables

Titles (all titles) detail title data

Library - purchase and location data

Rentals - dates and returns data

link Library and Rentals to Titles and you can filter what has been or is rented and what is in Library. You can conditional format the titles to easily see what is in the Library and what is Rented.

WM

Edited May 6, 2009 by Guest

[comment]

I think you need to be a little more specific about what you would consider as "similar". In your example, "Blues Brothers" could be considered similar only to "The Blues Brothers" (ignore all articles, prepositions, etc.), or also to "Brothers in Blues" (match all remaining words), or to "Brothers in Arms" (match any word) and even to "The Bleus Brothers" (typo).

All of any of these could be implemented, with varying degrees of complexity.

See also a somewhat related demo here:

http://www.excelisys.com/web/downloads/index.php

[Vaughan]

Soundex might be useful for this, but with any "fuzzy" search like this it's easy to be inundated with matches that are next to useless.

Get the Soundex custom functions from Brian Dunning's web site. Relatively easy to implement.

Either way, train the users to standardise the way titles are entered. That'll go a long way to easing the problem.

[comment]

Soundex might be useful for this

I don't think so: Soundex was designed to deal with similar-sounding names (of people). It will not match "The Blues Brothers" with "Blues Brothers".

[Simong]

Thank you all for the replies. I'll try to be more specific about what I'm trying to do. I appreciate all your replies.

BilesterM, that's a good idea and it's something I've thought of myself, but two things are preventing me from making this all into one database with three tables. One, bureaucratic nonsense I won't bore you with, and two, I don't want everyone to have access to the Rental Database. The DVD Library database is open to everyone but the Rental database will need to be open to a smaller group (those capable of placing rental orders). I'm sure there's a way to do all that in one database with permissions, but for convenience sake I'd like to just keep them separate so that wayward execs don't screw anything up.

comment,

The nice thing about this problem is that these are fairly small databases so I'm not too worried about false matches. It's fine with me if several entries come up because the important thing is just that the renter realize we've got it in the dvd library at a glance. In the examples you provided, I'd probably be happy to have it match any word.

What I'm imagining right now after a night of reading is a portal in the layout and after the user types in the film title, results show up in the portal from the dvd library (that instant search comment linked to is really cool but way beyond our needs). I just don't have enough knowledge yet to even kludge together this search, so any pointers about how it would be done would be helpful.

What sort of script steps would I use? What sorts of relationships? etc

[comment]

So basically you have two options here: either a search (which could be triggered or user-initiated), or a portal.

The search would have to be scripted: it would look at the entered phrase, and create a find request for every individual word.

For the portal, the best thing would be to use a custom function that would create a "multikey" (placing each word on a separate line) on both sides of the relationship. Since you don't have the Advanced version, you could use repeating calculation fields instead.

Let us know which method you prefer.

---

In both cases, I think you'd want to maintain a table of ExcludedWords (such as "the", "a", "an", "in", "of", etc.) to avoid false matches on those.

[Simong]

Well, comment, at first look that second method sounds like what I'm looking for. I'm such a novice I'd never even heard of the concept of multi-key but now that I've looked it up I have some sense of what you mean.

If I understand you correctly, I'd create a calculation field in each database that broke up the titles so that each word was on its own line, but with common words like "the" excluded. Then I'd set up a portal using a relationship between those two calculation fields but displaying the regular title field, and I'd get results for any matching words.

Do I have that right? Anymore detail you can provide I'd really appreciate, specifically what the calculation fields would look like.

Again, thanks so much, this is all really helpful.

[comment]

Yes, except you need to use a repeating calculation field instead of a multi-line (it works the same way). For the simple version (before excluding), you could use a formula =

MiddleWords ( Extend ( Title ) ; Get ( CalculationRepetitionNumber ) ; 1 )

Make the result Text and set the number of repetitions to the highest number of words a title can ever have.

[Simong]

Wow, that works great. There's still a lot of loose ends for me to figure out in terms of how I want the interface to work but you just solved the biggest piece for me. Thanks so much.

So what would I be doing to handle words like the, of, etc?

Some sort of text substitution that replaces them with nothing?

[comment]

Something like this?

WordSearchR.fp7.zip

[Simong]

Wow, comment, that's amazing. I can't believe how much work you did on that.

I'm gonna spend the next day or so looking that sample file over so I can understand it and I'll let you know if I have any questions. Thanks again, I feel really close now to having this database just the way I want it.