Search one field across multiple files

[lonntj]

Hey All,

We have a number of different tables that store prospects, each table has very different field definitions, layouts, etc. so putting them all in one table is not an option. However when we are contacted by a prospect we do not necessarily know which table their information is stored in. I need some way to search across these 6 tables.

More Information:

Each table has an address field which is unique to (nearly) every record, and that is how we identify a prospect when they call/write/email. This is the field I want to search. I had thought of putting together some kind of joinfile, populated with every address we have, do the search, and then go to the related record. I am unsure, however, of how to go about populating the joinfile with all the current addresses, and then keeping it current -- I would like to do thie without scripting the upkeep, since the contents of the 6 source tables changes frequently.

Also, I don't know if this makes a difference but it is a multiuser solution.

Thanks

Lonn

[transpower]

Use a script with a custom dialog (if you're on v. 7) to prompt for the name (in whatever way your fields are set up). Then perform a find (with the name you entered) in each table, one table at a time, in a separate script step. Have a prompt in between to continue or halt the script.

[neuronbob]

I have a similar question and would like more detail on the solution stated above. I use FMP 5.x still (though I'm considering upgrading to FMP 7). I have multiple, identical files with my patients' medical information. Each file covers one month. At the time I started doing this, I thought having a database for each month would be more efficient than having one huge, bloated, MBs long database. However, to search for a specific patient, I first have to figure out what month I saw them, then go to that DB. Or, more commonly, I have to check JFile on my Palm Tungsten C, to which I sync all my FMP files.

So in my situation, would your solution work, transpower (or anyone else)--a script to prompt for a specific field name? If so, how do I do that?

TIA

[transpower]

Neuronbob:

I'd recommend you change your database architecture. Have names in one table and appointments in another. Use a portal to see all appointments.