Searches/Finds in related databases

[jpsven]

I'm new to this forum. I'm wrestling with an FM-search issue that I am not able to solve on my own.

I have three separate databases where I have information about ports and port details. What I'm trying to do is to create an interface (an FM file) from which I can enter my queries,search the other databases and have the search results presented in one single page, possibly in one or two portals.

Unfortunately, I haven't found any way to do searches from one file into another. Regular portals will present matching records from other files into a portal. But those won't do as those matches are too strict - since they're based on a strict match between matching fields. My need is to be able to search not only for a strict match, but for a substring in a related field and have those result(s) presented through a common interface.

How should I go about to interface against those couple of databases?

Looking for your kind help.

Peter Svensson

Stockholm

[Vaughan]

M first question is, why are the records separated between three files?

[jpsven]

These databases are all of different in character and provenance. This is why we keep them separate.

/Peter

[Fenton]

A fairly quick way to do this is the Copy All Records technique. After running the Find in a file, go to a layout with ONLY the Serial ID for the file, Copy All Records, go to a layout with only a global text field (in the originating file), paste.

Now you have all the IDs in global field. A relationship from that field to the ID of the found set file will show those records in in a portal.

There is a 64,000 character limit on a field. Divide your average ID length into that for the number of records you can safely handle. It should be plenty to show in a portal.

Repeat for each file. You have to either put the Find criteria into globals first, or do some fancy scripting and layout work to go to each file, pause and continue the process (tricky if they can cancel in the middle).

[BobWeaver]

These databases are all of different in character and provenance.

Well, I would probably add a "Character" field and a "Provenance" field, and then merge them together. The fact that you need to search among different files for similar information is an indication that perhaps you should redesign your system.

[jpsven]

Thank you for your suggestions, So far, if I understand it correctly, you're suggesting is that FM is not useful for making an interface to present results from different databases simultaneously unless you first to copy all the records into one single database?

[Vaughan]

What we're saying is that the correct, most efficient and reliable way to go is to amalgamate all databases that contain similar information into a single file. (The only exception is an "archive" file where old records are stored after they aren't needed any more to up the main database and speed up finds, sorts and summary reports.)

Sure you can use FMP to make an interface to multiple files, but it's going to be a huge, ugly, unnecessary mess that's going to be a right royal pain to modify in the fulure.