Finding general over numerous fields in numerous DBs

[chlowden]

Hello again,

Question of the day. Is it possible to search over numerous dbs and fields, a general piece of info that is not field specific. Example, I am looking for all Davids in 3 dbs in any of the fields in the any of the DBs. I also have value list check boxes that define certain qualities for David that correspond to each db i.e. thin, blond.

I wish that the result be displayed in a new window and the results have either all or any of the above criteria.

I presume this can be done, but for the moment, I can only get a result via one db and through specific fields that corespond, not via a single, global field.

Thanks in advance

[mr_vodka]

This thread is related

[LaRetta]

Well that thread really doesn't provide a solution. And there is one. Simply, which DB will you end up in? Somewhere you need to end up with a set of RECORDS from one db. Here's an example:

User enters criteria into global fields. They are searching for CUSTOMERS who meet criteria from several other tables. So you need a list of found CustomerIDs which match. Create a gCustomerID which will accumulate the CustomerIDs as you move from db to db.

User - I want all customers with:

FirstName "David"

Addresses::City "Boston"

Numbers::PhoneNumber "541"

... etc

Script (untested):

Allow User Abort [ Off ]

Set Error Capture [ On ]

Freeze Window

Go To Layout [ layout based upon Customers ]

Enter Find Mode [ ]

Set Field [ Customers::FirstName ; globalFirstName ]

Perform Find [ ]

If [ Get ( FoundCount ) ]

Loop

Set Field [ gCustomerID ; gCustomerID & CustomerID & ¶ ]

Go to Record/Request [ Exit after last; Next ]

End Loop

End If

Go To Layout [ layout based upon Address ]

.... repeat starting at Enter Find Mode[]

You will end up with a multiline of CustomerIDs. A simple GTRR to those Customers will provide your resultant found set in a new window based upon Customers.

As for searching every field ... oh, don't do it. You have no need to search your CreateDate field (and many others). I would question your structure if you wish to find someone's name in several tables. I suggest you instead use a layout jump (to each table). The fields don't have to be on the layout and the globals in which Users type their criteria doesn't have to be from a table which is related.

So the question is, what records from which db do you want as your found set? Just accumulate that table's unique IDs pulled from each of the tables.

LaRetta

[Ender]

Not to detract from your well thought out script, but I'm afraid I don't see a practical implementation for this type of search in FileMaker. Perhaps someone can enlighten me on how the results from such a search can be assembled in a an intuitive, yet efficient manner.

While were at it, I still have trouble seeing a good reason for such a search. In my mind, it's more intuitive for the user to navigate to the table they wish to search. Is this for a special case I haven't thought of, or is this just a notion that beginners think up?

[LaRetta]

Hi Mike,

Example:

User wants to see a list of Customers based upon multiple search criteria, but that search criteria resides in several tables. They want a resultant list of Customers. They can enter anything they wish, ie, part phone, part city, part Contact (from the Persons table), product (searching the LineItems table), and end up with their list of found customers.

This resultant global multikey of Customers can then quickly GTRR and even establish a relatioship for displaying summaries (based upon this new relationship). As you know, a global multiline of IDs can be re-used throughout a process, such as removing an ID as User completes a task; and it remains multi-user friendly. And because the IDs reside in global, they can be assessed from anywhere.

The first question I ask in a search, is what do they want to end up with? A list of Customers, a list of Invoices, etc. Script takes it from there and produces the correct found set on the correct layout (based upon the desired resultant table).

LaRetta

[Ender]

User wants to see a list of Customers based upon multiple search criteria, but that search criteria resides in several tables. They want a resultant list of Customers. They can enter anything they wish, ie, part phone, part city, part Contact (from the Persons table), product (searching the LineItems table), and end up with their list of found customers.

If that's all that's needed, then why not just enter the find criteria on a Customer layout using the related fields?

It seemed pmworld was asking about searching for the same thing in multiple tables. I guess this is what I'm puzzled about.

[LaRetta]

"If that's all that's needed, then why not just enter the find criteria on a Customer layout using the related fields?"

I performed extensive speed test comparisons, Mike, on this exact process. Maybe vs. 8 is now quicker. Additionally, this easily provides an OR search. Agreed that I question pmworld's structure. But sometimes we can have an identical table which contains older archive data and thus may wish to search those invoices as well. If our Invoices and LineItems are archived, we could still search both and produce the correct customer results in a multiline (I never archive customers).

Different strokes, I guess. If scripting is properly structured, it's all pretty dynamic and fast.

L

[chlowden]

Thank you to all of you for your responses. Firstly, the world is not perfect and neither is my DB situation.

In short, I have 3 FM dbs that were designed to be exploited in 3 different applications and were created to be seperate for security reasons and also because one of the DBs is in French which complicates homogenised layouts dramatically. Yet within these dbs there is info that is uniform i.e. company name etc plus check list value sets that define generic info i.e. contacted, nice person, nasty etc.

The idea is to be able to do a global search, over a network, of a selection of predefined fields in all the DBs i.e. I am looking for all "nice person" called David or people that worked at the David company (who are not called David) who are "nice person"s.

I would like the results to open in the pertinent DB where they are found so that I can identify the source, via a new window in a predefined layout.

So the DB I wish to create is not really a DB at all but more of a dedicated search page, on the same principle as web sites that search best prices of numerous other sites.

To restart a new master DB could be very complicated from a human point of view.

Leretta, thanks for the code. With my limted knowledge I will try to decipher and apply your solution.

Thanks