Use one interface to search several tables

[loren s]

I read this in a book.

A find only searches in one table. You can set up an advanced find to look into several tables, but it will only return results in one table at a time.

I am looking to use one exact copy of an interface layout to search into and pull up all records that have a mathing search term in all files.

Example, I use find and search in a same name field for the word Beta, that should be able to pull up from several tables the matching field and all other feilds that were in those tables. The only way I know how to do this would be to combine ALL of my old table files from FM6, but I fear that would be a a mess of one huge file and b not a secure way to store that kind of data. Plus it would take weeks to get it jammed in like that.

Anyone have other ideas ? As I need it to have a search all method instead of a one at a time method. Example agaion would be like a message forum: a searched word will search all forum tables in the mysql database and return alll results in a nice neat display of repeating layout forum posts.

[Fenton]

First, FileMaker is not mySQL; which probably creates a table "on-the-fly" to show that one list of results. You could do something that looked exactly like that in FileMaker, but it would be kind of a pain. So I'll propose something which I think is about the same.

When you do a Find it always happening in one table at a time. It is possible to "capture" that found set however. There is a technique which uses the Copy All Records step, on a layout with only the unique IDs, to copy/paste all the found IDs into a global field.

The global field can be in whatever file or table where you want to view the results. It is a multi-line text field, matching any matching record (ie., the found set in the other file-table). A relationship back to the original table will allow you to see that found set in a portal (which is FileMaker's version of a "view").

You could have several of these portals on one layout (as space permits) in any file-table; to show the results from your Find(s) in several other file-tables.

So, rather than one big long list, you get the results in several portals, organized by which table the records are in.

If you really want the "one list" method, you could do the Finds in each table, then import the found IDs into a special "Found Sets" table. This would be slower than the Copy All Records technique, but not too bad.

Each searched table would have its own ID field in the Found Sets table, with a relationship back to its source table. All other data would be related, by ID.

The related data fields would have to be "layered" on top of each other on the layout row, transparent fill. Only 1 relationship would be valid for any 1 record, so only that data would be visible.

You would have to either Delete All Records before each Find, or manage Found Sets by name somehow. I'm not really crazy about these massive create-delete routines; there is a limit to how many the file can handle; but the number is insanely high in version 7:

"Number of records per table: 64 quadrillion total records over life time of file."

[transpower]

You could use ODBC and SQL for this problem.

[loren s]

You could use ODBC and SQL for this problem.

Hmm, that sounds good but do you have any tutorials that I could check on this with filemaker 7 ?

[loren s]

First, FileMaker is not mySQL; which probably creates a table "on-the-fly" to show that one list of results. You could do something that looked exactly like that in FileMaker, but it would be kind of a pain. So I'll propose something which I think is about the same.

When you do a Find it always happening in one table at a time. It is possible to "capture" that found set however. There is a technique which uses the Copy All Records step, on a layout with only the unique IDs, to copy/paste all the found IDs into a global field.

The global field can be in whatever file or table where you want to view the results. It is a multi-line text field, matching any matching record (ie., the found set in the other file-table). A relationship back to the original table will allow you to see that found set in a portal (which is FileMaker's version of a "view").

You could have several of these portals on one layout (as space permits) in any file-table; to show the results from your Find(s) in several other file-tables.

So, rather than one big long list, you get the results in several portals, organized by which table the records are in.

If you really want the "one list" method, you could do the Finds in each table, then import the found IDs into a special "Found Sets" table. This would be slower than the Copy All Records technique, but not too bad.

Each searched table would have its own ID field in the Found Sets table, with a relationship back to its source table. All other data would be related, by ID.

The related data fields would have to be "layered" on top of each other on the layout row, transparent fill. Only 1 relationship would be valid for any 1 record, so only that data would be visible.

You would have to either Delete All Records before each Find, or manage Found Sets by name somehow. I'm not really crazy about these massive create-delete routines; there is a limit to how many the file can handle; but the number is insanely high in version 7:

"Number of records per table: 64 quadrillion total records over life time of file."

Hmm or I could just jam them all into one fat file and search from that eh ? I could then later just split them again if a better version comes out. I mean filemaker 7 'is supose to' handel 2 terabytes.

[transpower]

I wrote a review of Sams Teach Yourself SQL in 10 Minutes for Amazon.com. This book is probably the easiest way to learn SQL! For Windows, I use MSQUERY to perform SQL on multiple FM tables; MSQUERY comes with many Microsoft products. I'm sure there's something similar for Mac OS X.

[Fenton]

You can create the routines to do what you want without recreating the entire database solution in one huge flat file. The Copy All Records technique is very fast. It doesn't require any creation/deletion.

The only drawback (from one point of view) is that it presents the results in separate portals. You could also say that's organization, much like intelligent web search engines present their results in separate "folders."

Or use the import/delete routine, if you must have 1 list. It is "one fat file," in the sense that it has a record for every record found. But only the ID.

I guess it all depends on what your solution is supposed to do. I work mostly for small businesses, who generally don't have huge amounts of data, but who have somewhat complex business rules and routines. Complexity of interaction requires the correct relational structure; that's FileMaker strength. Lightning-fast searches across multiple tables and fields returning flat results is not; it can be done, just not "lightning" and preferably not "flat."

[loren s]

You can create the routines to do what you want without recreating the entire database solution in one huge flat file. The Copy All Records technique is very fast. It doesn't require any creation/deletion.

The only drawback (from one point of view) is that it presents the results in separate portals. You could also say that's organization, much like intelligent web search engines present their results in separate "folders."

Or use the import/delete routine, if you must have 1 list. It is "one fat file," in the sense that it has a record for every record found. But only the ID.

I guess it all depends on what your solution is supposed to do. I work mostly for small businesses, who generally don't have huge amounts of data, but who have somewhat complex business rules and routines. Complexity of interaction requires the correct relational structure; that's FileMaker strength. Lightning-fast searches across multiple tables and fields returning flat results is not; it can be done, just not "lightning" and preferably not "flat."

Aw thnak you that explains a lot of filemaker for me now. We have thousands of old FM6 records each divided by show and each shows has hundreds if not a thousand records per file with several differet layout views but thesame layouts per file. In these records hold images which bring up an entirely different problem of importing...

Anyway then there are several other database files with there own problems. All of this is nicely stored on a computer server but really how everyone gets to the files is via the Computers window finder in OSX. The brolem is we need to clean it up so it is a one access screen interface for all of these databases so we can then stick it out onto the webserver for extenal editing for others. A find would bring up the show and then the user could do what ever with that data add edit delete whatnot.

That is why I don't think The Copy All Records technique idea will work since the data would then need to be edited. Could be wrong, I will see if I can get some examples and make it work... Thank you.

[Fenton]

First, the way your networking is set up sounds like an accident waiting to happen. Sorry to be blunt. But you should NEVER access FileMaker files through the OS navigation. They should only be accessible via the Open Remote step in FileMaker. The folder the files are, in fact the entire machine the FileMaker files are on, should NOT have File Sharing on (some people just turn off sharing for the folder, but officially its the whole darn machine; or at least the drive; it's controversial; but all agree NOT the folder).

Yes, FileMaker is guilty of not telling people this flat out, on page 1. But every FileMaker tech will tell you. And yes, it's different from everything else. On the plus side, it works well, cross-platform seamlessly, with really no setup at all. Just open port 5003 in your Firewall settings, and turn off File Sharing. In FileMaker turn on networking, TCP/IP (very easy to do in 7).

You could web-serve the database, but that's not "file sharing." You'd be using Instant Web Publishing probably, which is OK, but nowhere near as fast or functional as FileMaker networking.

If you use the Copy All Records technique, the results are in portals. You can certainly edit in the portal row (though it's only so big). Or you can click a row and go directly to the file/table/record in Form view. Or, in 7, you can pop up a new window in Form view with the clicked-on row, edit it, then close it. Or you can set a global field and show the "detail" data on the layout itself, if there's room (not likely with multiple Finds in multiple tables).