Hello.

I've been struggling with this for days, reading everything I can but still not being able to pull this off. :qwery:

I'm creating a simple (or so I think) music collection database.

I have the following tables:

Songs - A list of all songs

Artists - A list of all artists

Albums - A list of all albums

AlbumTracks - A table containing a SongID, ArtistID and AlbumID (since the same song may occur on several albums, and be performed by different artists).

What I'm looking to do is to filter a portal of records in AlbumTracks based on a search string that could be a part of the song name (stored in Songs), the artist name (stored in Artists) or the album name (stored in Albums).

I've created four fields in AlbumTracks for this operation:

- searchableString (calc) - Songs::Name & " " & Albums::Name & " " & Artist::Name

- searchString (global) - where the user types in his search.

- searchResult (calc) - =If(PatternCount ( searchableString; SearchResult);1;0)

- positiveResult (calc) - =1

I'm then using a self join relationship (searchResult <-> positiveResult) to perform the search.

I'm guessing that the main problem is that since searchResult references a global field and searchableString (which itself references related tables) it can't be indexed.

Am I doing something conceptually wrong?

You can take a look at this sample file on JMO's site and see if you can implement the technique.

On your child table, you would need a Stored key of the three name fields sperated by the "¶".

Thank you John.

While that works, it has some serious limitations that if at all possible I would like to avoid:

1) It only works if the search string is a part of the beginning of the match. I want to find "hit me baby one more time" by searching "on" or "by", and not only by searching "hi".

2) Because of that, it requires three different search strings for each criteria (by name/by album/by artist)... I would like the same search string to find matches in all those fields - just like google or any search engine.

Am I asking for too much? (i miss SQL...)

Well this would be a lot easier if you had advanced and you were able to take advantage of recursive custom functions.

Here are a few links that I think you would find interesting.

Ender's Post

Discussion

Custom Function

Queue's solution without Custom Function