Searching across multiple filemaker files

[Jredeemer14]

Hello, I want to be able to search 2(or more) filemaker pro 9 files that have identical inventory tables for similar records. I assume I need to create some sort of script? Any Ideas?

[mr_vodka]

We need more info. If there is a file reference to another file, and a layout representing the TO of that table in the other file, you can do a search in both files.

However, I dont know what you mean by similar records.

[Jredeemer14]

My apologies, I've got 1 inventory table at one store and 1 inventory table at a different store, 2 different files. I want to create something so I can find something that could be at either store, one field to search across multiple inventory tables. I think you had the right thinking when you said:

"If there is a file reference to another file, and a layout representing the TO of that table in the other file, you can do a search in both files."

Could you elaborate more on how what you said in the quote above would work? Thanks a bunch

[comment]

Searching in two or more files (or tables) is not so difficult. The problem is presenting the results, since they too will be split across two or more windows (or layouts). The need for a combined search suggests that the data should be in one table to begin with.

[Jredeemer14]

Alright, imagine if you will, multiple retail stores across multiple states, it doesn't really make sense to have all those inventory items in one table. So, it is possible to search across more than 1 file, I just need to find a way to display that data. Any Ideas?

[Fenton]

Actually, it does make sense to have them in the same table, from a relational database point of view. They are all the same "entity," a product. Which store (stores being another entity, hence another table) they belong to is an "attribute" of the product, a foreign key.

By moving them into separate files (per store?) you've created an artificial structure. You are thinking this is easier, because of the size. But to search across them is actually going to be far more complex and slow than if you put them into the same table, no matter how large it would be. If the number of records is way into the tens, hundreds of millions, then perhaps FileMaker is just not the right tool for this job.

It is possible to do finds in separate tables, and present the results in another table, by capturing the IDs of the records, and putting them into a multi-line field of the "view" table; using those IDs as the originating side of relationships to the other tables, showing the results in portals.

This works OK when you only expect to find a fairly limited set of results. But it does not work so well if the results are in the hundreds, for two reasons. The first is that it takes time to gather those IDs. The second is that portals are a lousy way to view hundreds of records.

So it kind of depends on what the Find is, which we do not know, and which even you may have little control over.

Another possibility, not using portals, would be to do the Finds in the multiple files/tables, and present the results in separate windows. Not as unified an approach, but easier to deal with the records afterwards.