lookups have stopped working

[longjump]

Hi.

My lookup used to work fine, but now it fails most of the time. It's designed so that when you input a song title, it tells you what album that song is on.

FM 5.0v3

Mac OS 9.1

Match field in master file is a repeating text field with song titles.

Match field in related file is a large non-repeating text field containing track listings of entire albums.

When a new entry in the *song title* field matches anything in the *track listing* field of the related file, the *album title* in the related file is copied over to the *album title* field in the master file.

For some reason, this doesn't always work. When the *track listing* field of the related file is full of titles and other text, the lookup usually fails. Then if I manually add into that field a word that satisfies the relationship's requirements and try it again, it suddenly works, even though that word I typed was already in that field before I typed it again. -- but only sometimes.

I'm unable to figure out the pattern of when it works and when it doesn't.

TIA,

Longjump

[danjacoby]

I think your setup is a bit kludgy. Also, are you really using "lookup"? If so, don't -- it's totally unreliable.

I would set up the related file to contain one record per track. That way, when you enter a song title in the master file, the correct album title will show up. This assumes that the "AlbumTitle" field in the master file is an unstored calc pulling the related album title based on the song title.

[longjump]

Thanks.

If by "kludgy" you mean not elegant, I'm with ya. I'm self-trained, from back in the days before FM had 'relationships'. In researching this problem I'm starting to realize the un-necessity of lookups but I'm still a newbie in that regard.

What's most frustrating is that my setup used to work 100% but now it fails most of the time. I guess that's the unreliability you mention, although it seemed like something happened one day and the lookup suddenly started acting up. File recovery didn't fix it.

Re separate records for e/ track in the related file, I think you're right. But breaking track listings into separate records would be difficult. Here's the scenario: The main file contains individual song titles, in no particular order, by misc artists, with no info about where they're from. Some of these songs exist on compilation albums. So I build a database of likely compilation albums by looking them up on the internet and copy/pasting their track-listings, which are solid blocks of text, into that database, which is my related file.

Even when my kludgy lookup is working, matches are rare. So I don't know if it'd be worth the extra work splitting the track listings into separate records.

Re your assumption of "AlbumTitle" field in the master file as unstored calc, what do you mean by "pulling"? [sorry for my jargon-ignorance]

BTW, (at the risk of beating a dead horse) I have another related file with the exact same kind of lookup setup, parallel in every way except it's for regular albums, not compilation albums. The track listings in this related file are also a solid block of text, but manually typed in. That lookup works much more reliably than the one in question.

[-Queue-]

If you have any records with the same song titles, or similar as far as FileMaker indexes, then it makes sense that your lookups would be 'broken'. You need a unique field to match, preferably an ID number, if you're going to use lookups.

[Ugo DI LUCA]

Hi,

Song Title ?

Like "October" or "Where the streets have no name"

What about the title length being too long for being indexed properly.

[-Queue-]

Ugo DI LUCA said:

Hi,

Song Title ?

Like "October" or "Where the streets have no name"

What about the title length being too long for being indexed properly.

I don't think he specified they were all U2 songs.

But that is what I meant by 'similar as far as FM indexes.'

[-Queue-]

BTW I've developed a nice method for uniquely indexing fields with hundreds of characters. I may send you a stripped-down sample sometime, Ugo.

[longjump]

Yeah that was the biggest glitch even when the lookup was working.

But even then, when there was more than one record that matched, at least FM would pick one of the matching records and use it in the lookup.

Now it doesn't even do that any more, a lot of the time.

[longjump]

Yeah I thought about the too-long-for-being-indexed-properly idea. According to FM Help:

"If a match field is a text field, FileMaker Pro looks at the first 20

characters of each word in the field, up to 60 characters

(including spaces)."

But as I keep harping (sorry), it used to work fine even when the match field had many multi-word song titles and hundreds of characters.

Even now, when I have a match field that *should* match but doesn't, check this out: if I manually type in words that fit the lookup, FM changes its mind and decides the match is good, even though those words were already there, and even though the field is now even longer than before.

I guess maybe I'm asking FM to perform beyond its design limits, but for some reason it sometimes works anyway, but not dependably???

[longjump]

-Queue- said:

BTW I've developed a nice method for uniquely indexing fields with hundreds of characters. I may send you a stripped-down sample sometime, Ugo.

Ah, so! Very cool.

Me too please, if I may be so bold.

I'm beginning to think indexing is the problem here. I used to know how to view the index but I've forgotten. Is there an easy way to call that up manually, just to see what's in there?

[Ugo DI LUCA]

I consider this thread as a must see, when dealing with keys in relationship.

BTW JayTee, I'm ready for your tip...

[-Queue-]

Enter Find Mode, click into field, press Ctrl-I for Windows (Option-I for Mac?).

I'll work on that sample file. Maybe I'll have time tonight.

[-Queue-]

OT question: Does anyone know of pros/cons for using "_" instead of " " in concatenation calculations? I prefer using underscores as it's more blatant to me when I look at the result, especially if one of the referenced fields already has a space within its result.

[longjump]

-Queue- said:

Enter Find Mode, click into field, press Ctrl-I for Windows (Option-I for Mac?).

I'll work on that sample file. Maybe I'll have time tonight.

Excellent! Thanks.

BTW it's Command-I for Mac (aka Apple-I, aka Splat-I).

[-Queue-]

I really should dig out my iMac sometime and relearn this stuff.

The ironic thing is I never touched a Windows machine until I got this job in 2000. And now I can't recall much about my powerMac or iMac or Apple IIe or Commodor 64 or....

[longjump]

-Queue- said:

Enter Find Mode, click into field, press Ctrl-I for Windows (Option-I for Mac?).

Turns out it works in Browse mode too.

And I guess you already know the long-cut way to do it is to select "From Index..." in the Insert menu.

Thanks again.

[longjump]

Ugo DI LUCA said:

I consider this thread as a must see, when dealing with keys in relationship.

Great call, Ugo. Very helpful. I think I'm getting close to solving this.

It's starting to look like the difference between the old days when this lookup worked and the new problem lies in the index. In all my previous experience with multi-word text lookups, from FM 2.1 up to 5.0, I'm pretty sure a match was found as long as all the words in the master file's match field could be found anywhere in the related file's match field, in any order, and regardless of whether there were additional words in there as well .

Example: The related file has an album whose 'track listing' field contains: "I Love It When We Make Love".

Entering "Make It" in the master file's 'song title' field would satisfy the lookup, because both the words are in the related file's match field.

The difference between then and now, which I just discovered, is that now the lookup still works but only if the master file's 'song title' field contains the exact same string of characters, spaces and all, as any entire line of text in the related file's 'track listing' field (where lines are separated by returns).

In the View Index dialog box for the related file's 'track listing' field, the index consists of those entire lines of text as defined by the presence of returns. So apparently FM is indexing this field as lines of text, not individual words.

I assume this is because of the multikey nature of the text in this field in some of the records. I made some test records with just 2 very short words and no returns in this field, but still the lookup fails unless the entire string, including the space, is entered exactly the same in the master file.

I think I need a way to tell FM to go back to indexing the 'track listing' field one word at a time. I notice that when manually viewing the index, the check box allows you to view it as individual words. But this check box doesn't appear to affect how FM treats the relationship.

I found the following in FM Help:

To have two words treated as one by the index (for example, Jean Louis) Clear Show individual words [in the View Index dialog box], or press Ctrl+Space bar (Windows) or Option+Space bar (Mac OS) between the words.

That's very nice, but how do I get two words that are already being treated as one by the index, to be treated as two?

I tried retyping all the spaces as normal and also asOption+Space bar (Mac OS). No effect.

[longjump]

A side note re the indexing of big text fields, words and lines:

I notice that my lookup works with lines up to 66 characters in length, not the purported 60-character max.

Also-- if FM is indexing whole lines as one word, how does that relate to the 20-character-maximum word size?

[longjump]

Problem solved???

Hokay, I think maybe my earlier post was erroneous. TIA if somebody would check me on this.

I claimed that I had seen lookups that matched when the related match field contained all the words, in any order, that were in the master match field. Now I think I was wrong, that the two fields must be exactly the same, or in the case of multikey fields, lines must match exactly.

My error probably stemmed from thinking FM does text matches in relationships, the same way it does text searches.

I think probably the difference between earlier days when my lookup was working, and nowadays when it rarely works, is that in my mistaken belief that exact matches were not necessary, recently I had been getting lax in my data entry, allowing lots of extra text to get into the related match field.

Does this analysis seem sound?

Thanks to all the folks who contributed advice; I learned a ton. Of course this means that now I have even more questions, but I'll check the archives first.

Longjump