How do I match one table to several tables?

[Fenton]

The right hand calculation will stop at a certain number of letters; however many you want to do. Using more than is needed wastes a little bit of disk space, and I'm sometimes a bit ruthless in that regard.

So, if I decided that 6 was plenty, and some go-getter typist put 7 letters, the portal would go blank.

If the right hand key's largest line (of its multiple lines) is:

Roberts

And they type "Robertson" into the global for matching, then there will be NO match, even though the actual last name in the NameLast field IS "Robertson"

So I would Validate the global to maximum 6 letters of length. I suppose I should be more profligate, and make my keys longer than anyone would reasonably type (a shocking 8 letters!). It's difficult to hint at the restriction by the size of the field, unless you used a monotype font:

"mmmmmmmm"

"iiiiiiii"

About the IDs. I though putting a letter prefix was fine. Especially since I tested and saw that FileMaker 7 respects the letter if you use a script to get/set the next serial number (not that I often have time to include that feature for updates).

I just didn't want beginners to get the wrong idea, that they should put lots of indentifying text into serial IDs. One sees this often enough as is. It's most just a waste of disk space (see 1st paragraph).

Also, since I am sometimes called upon to clean up screwed up IDs in files from clients.* I prefer them to be as short as possible; so I can visually scan a sorted list and get some idea of the damage.

I looked at the post you referenced. It's a tough decision whether to use a "date-time-random" key or a normal incremental serial ID. If you know at the start that there will be multiple updates needed, with imports, at multiple remote sites (such as shareware files), then the long random key becomes much more attractive. But it sure is ugly-looking.

*Most of my clients are small business people, many of them have an existing homemade solution (if you can call it that). If they've used relationships at all they're based on names; many duplicates, many misspellings, etc.. It takes quite a while to straighten it all out.

[RichardAustin]

I like the idea of a few letters for last and then first names. My lists probably won't grow to more than maybe 20 to 40,000 names. I usually don't have more than 3 or 4 dups of organization names (with different addresses) so a portal of even 10 names would probably suffice. If I include in a portal a few extra fields, I should easily be able to see the intended record.

This week I am going to see if I can complete a first draft of the database.