Search Multiple Databases

[mjm7496]

We are a small sheriff's office that uses filemaker for everything. We have individual databases for several different collections of data including the following:

1. Criminal Reports

2. 911 Database of all registered phone users

3. Warrants

4. Civil Process

... And many others

What we currently do is open each individual database and then go to a name field and search. Well some of our forms have names in several spots so we are forced to search each database multiple times. I think I found a way to enter multiple seach strings for a single database, but is there a way to search multiple fields in multiple databases from one place?

Ideally what we want to do is create a master name database that would then search each of the other databases and display the results in a way that we could click on the results and view the information. Kinda of like a hyperlink search reult in a web search engine.

Also... A side project: We have now added notebooks to our network. We would like to find a way to do 2-way synchronization. We need a complete and current copy of our database in our car and then we need the ability to sync anything we add to that database in our car with the main server (running FM server by the way). I know there is a product out called Syncdek (http://www.syncdek.com) that might do this, but I was wondering if anyone else had a solution for this.

Thanks in advance!

[Fenton]

We really need a little more information. Mostly what I wonder is how you are getting the "name" in all these different files, but cannot see the person's linked records after doing a Find in a central "People" file?

It sounds as if you do not have a central names file, or are not using it completely, and are just entering people's names as text in the other files, hoping to find them again. If so, then... well, you're going to miss connections sometimes, typos, different spellings, etc..

But as to your question, one technique that may work is to do your Finds in the other files, passing the value via a global field between the files, scripting all the Finds. You can then view the results in all the files. Or use a more advanced technique to capture all the IDs and view the results in portals in a central file.

But without knowing more I couldn't quite say. I understand that you have difficult problem, in that a person may play many different "roles," hence end up in several different places. But still, they should have only one ID, which should be chosen from a central people file. Nothing else is really relational.

Perhaps you don't have the ability to ID a person out in the field. Then the data should be held in temporary files until the people can be indentified. If you want to move into an automated synchronization routine, you really need to positively identify people.

[Mariano Peterson]

Yep, Fenton is right on. Also, synchronization is quite a difficult task to implement. You are best looking at their solution, the latest version of which was demo'd at MacWorld SF and offers a much easier to use, nearly turn-key solution.

[mjm7496]

Yikes...

Sorry I was so vague. I am pretty new to FM. I am just starting to learn and unfortunately wasn't the one that created all of these individual databases. My job is actually as Deputy Sheriff, but I am wearing a 2nd hat as the IT guy. My background is in hardware and networking so this database stuff is really greek to me.

Anyway I'll try to exlain it better. Essentially what we have is several completely individual databases that have been created over time. There was no rhyme or reason to their creation other than to just start storing data. This is both a blessing and a curse. The blessing being we have TONS of wonderful data. The curse being we have no cohesion whatsoever.

For example:

If I want to do a search for a person's phone number or address I open up our "rural emergency" database and do a search in the name field. If I then want to see if a person has a criminal record I open up the "criminal reports" database and do a search in the name field. THEN if I want to see if that person has a warrant out I open the "Warrants" database. Obviously this can get a little time consuming - especially if they are sitting in the car next to you.

There was never any thought put into using a "customer ID" for each of our contacts so therefore some of our databases may have a social security number associated to a person's name, but some do not. Being we have thousands of contacts at this point I don't want to go back into each database and add the SSN to each contact that doesn't have it.

Anyway... You are right on Fenton. We do NOT have a master name file or master person file. I suppose the 1st step would be to somehow create this by pulling the information we have into that central file. Now the million dollar question is where do I start?

[mjm7496]

Oh and thanks for the help guys! I hope I didn't sound unappreciative.

[DanBrill]

Hi mjm,

Welcome aboard. First, fmforums.com are a great place to start. You'll find some great people here, two of whom have already answered you.

If I was you, I think I'd leave the system that's there in place for a while while I took the time to get grounded in relational database theory. Nothing too fancy. But you need to have an idea of how many tables (or files in the FM case) you need, and what the relationship between the records are. Once you can recognize a many-to-many relationship and what to do about it, you've probably grasped enough to get started.

Then just start drawing pictures of how everything fits together in a perfect world. What are you trying to keep track of, and how does it relate to everything else?

Then build your system. A slick user interface and really nifty scripts can get added over time. The important thing is to get the basic architecture right at the outset.

When that's set, import that mountain of data from the old system and deploy it in the new properly-structured format.

This is, of course, a perfect-world solution. Time and money always prohibit these, and I'd hate to take you away from your main gig of catching bad guys. (But I also want your database to work right so I don't get thrown in jail in a case of mistaken identity. ) In any case, get some background in the proper structure of databases, and life will be easier.

Good luck, and keep us posted.

Dan

[Fenton]

We'll let you off with just a warning this time -/

As you've noticed, a system that was not built with correct relational structure doesn't work as well as one that was.

At this point you have two objectives, and they are somewhat interelated. The first most pressing objective is to build a multi-file Find script.

The 2nd is to create a central People file. This file would have ALL people you deal with (other than those clearly within your own organization). Each would have a FileMaker generated unique serial ID (one of the auto-enter options), as well as any other "IDs" you have (SS#, drivers license).

So, the first task. You need in each file a global field, type text, _gName; or maybe a couple, _gNameFirst and _gNameLast, if you want to search independently in first and last names.

Then you need a Constant1 caculation field, = 1, result number, in each file. Create a relationship between the files based on that field. This is just for passing data, which you can't do unless you have some kind of relationship.

[Create the People file, with a Constant1 field. You'll need it. Read on.]

After entering the name into one of these globals (in the People file), on a Form View layout, you will trigger a script that first Finds for that value in the appropriate name field(s), then calls the "multi-Find" script in another file, where it does the same thing again, until all files have done the find.

Another little glitch is that each file has to know which other files have run the Find. To simplify this, you could always run the multi-Find for a person from the central People file. So create that file. Create one record. We just need a place to put the global field to start and control the process. Don't worry about putting the people it yet.

You could "pull" the global with a relationship, "People_Constant," which would be from each file back to the central file, pulling the data from People into the file doing the Find.

The "Multi-Find <--People" script in each file would look something like this:

Set Field ["_gName", "People_Constant::_gName"]

Exit Record/Request

Enter Find Mode []

Set Field ["Name", "_gName"]

Set Error Capture ["On"] <-- suppress error dialog

Perform Find []

Notice [] <--No restores. We want to set our own.

The controlling script in the People file would look like this:

Exit Record/Request

Perform Script [External, "Rural Emergency"] <--Multi-Find Person

Perform Script [External, "Warrants"] <--Multi-Find Person

Perform Script [External, "Criminal Reports"] <--Multi-Find Person

*It would then run its own

Enter Find Mode []

Set Field ["Name", "_gName"]

Set Error Capture ["On"] <-- suppress dialog

Perform Find []

This would leave you in People, with either a Found set or 0 in other files. You could use its script to switch each to a certain layout. But basically you'd have to go look at them. There's fancier ways to present the results, but this is a good start.

Next we'll figure out how to ID all those people and bring them into the People file. The good news is that you can probably get 90% of them using existing IDs, SS#, matching names, etc.. The bad news is the other 10% will take some tedious work. But all that will have to wait, as I have other (similar) fires to extiguish at the moment.

[mjm7496]

Great help guys! Well one thing you have made easy for me is the decision to go ahead and hire a filemaker developer to do this work. I think I'd be getting in way over my head. I really don't have the time to do this AND implement all the new hardware we just purchased. (We just decided to switch from Mac's to PC's)

Anyway... I'm going to print out these posts so I can discuss this when I talk to a Filemaker Dev. (Anyone know one?) Thanks again for the help!