Searching records in two databases

[jerepjohnson]

Hello all,

I cant seem to find a solution for my problem though I have RTFM'ed for a couple days. I have two databases with two groups of customers. I want to be able to search by name through both groups of customers. Is there a way to do this with filemaker relationships? Also it would be good if the results would also tell which database the customer is in. Thank you.

Jeremiah

Edited July 29, 2009 by Guest

[jamesducker]

Not with relationships alone, I don't think.

Simplest way I think would be to have layouts of both tables in the same file (either of the two files, or a third file, as you wish), then have a script do this:

set $name to "John Smith"

go to layout ( customers_group_A )

enter find mode [don't pause]

set field "customers_group_A::name" to $name

perform find [don't restore]

new window

go to layout ( customers_group_B )

enter find mode [don't pause]

set field "customers_group_B::name" to $name

perform find [don't restore]

You'd then have two found sets on your screen.

I am however wondering why you have two different databases with two sets of customer data in, though? Would suggest it might be better to have all the records in one table and distinguish them by means of another field that is set to either "Group A" or "Group B".

Hope that helps.

James

[David Jondreau]

The manual only tells you how to use Filemaker, not how to design a database properly. You probably shouldn't have two sets of customer records. What are you trying to accomplish by doing that?

[jerepjohnson]

I understand that it would be better to have all customers in one database, but in this situation they must remain separate. Perhaps you can think of a security scenario where it might be desirable to have two separate databases.

Edited July 29, 2009 by Guest

[jamesducker]

Fair enough. Could think of a few situations where that might be the most appropriate.

Depending on how often the data change, you could maybe have a third file and run a server-side script every hour (or whatever) that deletes all in it and then imports all records from both the other two. Do your searches in that, and your results are never more than an hour old.

Hope that helps.

J

[comment]

Perhaps you can think of a security scenario where it might be desirable to have two separate databases.

Not really - esp. when they are so interconnected that one is allowed to search both at the same time. In any case, there aren't too many options here. You need to search both, and either show the results separately, or import them to a common table.

The latter option also needs to provide different found sets for multiple users, incl. deleting a user's found set each time a new find is performed. Editing the records is another issue in such arrangement. Much simpler to keep them in the same table to begin with, and use account privileges to restrict access to records by type.

[David Jondreau]

All right...you've got my curiosity piqued. And security isn't my strong point. What are you doing with two databases?

[normanicus]

The person is asking for a solution to a problem not to be told his design is wrong. Global search, the lack of, is a real Filemaker weakness. Where you are right is in indicating that the only way to get global search is to design it in from the start.

[comment]

Sometimes the real solution is to be advised that the design is wrong. In any case, I believe the alternatives have been sufficiently covered too.

[jamesducker]

I understand that it would be better to have all customers in one database, but in this situation they must remain separate. Perhaps you can think of a security scenario where it might be desirable to have two separate databases.

Actually yes, I can. A very common scenario would be where you have one client database that is making a website work, and another client database for internal use. The website one would only contain the bare minimum of client information, whereas your internal one could have credit card numbers etc in. I would (almost always) keep these two as completely separate files, running on separate servers, even though the data in them may be similar and in most cases duplicated. The discipline then is in having a good set of synchronisation routines so that changes to either database are reflected appropriately in both. One may quite reasonably have reasons to want to search in both - as per the original question.

This may not of course be the original poster's situation, I am only speculating, and his reason for separating the two databases may or may not be valid.

I agree that it is an expert's responsibility to advise when he sees flaws in a design - but only once in possession of the full facts.

But yes, out of curiosity, why ARE you separating them? :-)

James

[LaRetta]

The person is asking for a solution to a problem not to be told his design is wrong.

If you see someone walking towards a cliff and you don't speak up, shame on YOU. It is our responsibility to speak up. And no, we may not know all the facts - we NEVER DO. And that is true also when we give solutions here. But that doesn't change reality that we must advise based upon what we DO know. They may have hidden wings; they may wish to commit suicide ... but we STILL should speak up. I can see it now ... someone jumps off a cliff and you didn't say anything and you tell the police, "Well, I didn't say anything because I assumed he had hidden wings. I never thought that maybe he didn't see the cliff." Good grief.

And neither do I buy the other reasons given about separating into two files. If one must search both files for a customer then the customers should be connected together in same table. DOH.

Edited July 30, 2009 by Guest
Added two sentences

[LaRetta]

I agree that it is an expert's responsibility to advise when he sees flaws in a design - but only once in possession of the full facts.

By the time we get all of the facts about a solution, the person will have left to do their thing. By the time they give all of the facts about a solution, we all would have wasted hundreds of hours when we could have properly re-directed them from the start.

When we see a 'probable' design issue, we must speak up. Then the person can explain why they are handling it that way. This person rates themselves as a BEGINNER! They make think there are good reasons for doing something the way they are but that's because they DON'T KNOW a different way.

If you don't wish to re-direct them, that's fine. But don't criticize others when they have the guts (and are willing to take their time) to question.

[jamesducker]

I do like a bit of good-natured debate :-) In response...

By the time we get all of the facts about a solution, the person will have left to do their thing. By the time they give all of the facts about a solution, we all would have wasted hundreds of hours when we could have properly re-directed them from the start.

Very true. That, I guess, is the difference between the service one receives when getting help on a forum and the service one receives when paying for it! :-)

When we see a 'probable' design issue, we must speak up.

And I did, in my first reply to the question :-)

This person rates themselves as a BEGINNER! They make think there are good reasons for doing something the way they are but that's because they DON'T KNOW a different way.

Good point... I should take notice of that. Although I suppose there are lots of people who are new to FileMaker but very knowledgeable about database theory.

If you don't wish to re-direct them, that's fine. But don't criticize others when they have the guts (and are willing to take their time) to question.

Questioning is to be applauded (indeed, I did so myself). What I slightly objected to was people coming straight to the conclusion that the original poster has got his design wrong.

Incidentally, and perhaps this is a topic for elsewhere, but would you not consider separating files in the (hypothetical) web vs internal situation that I described earlier? There are undoubtedly cases for and against, but personally I am very paranoid about security when a database is opened to the wider web - in my view, the less that's in that file the better. I'd rather have two databases on completely separate servers than rely on privilege sets alone.

James

[comment]

What I slightly objected to was people coming straight to the conclusion that the original poster has got his design wrong.

And who are those people? Are they in this thread?

You probably shouldn't have two sets of customer records.

Not really [can think of a security scenario where it might be desirable to have two separate databases].

When did this become "telling someone his design is wrong"? Not that I would hesitate telling someone that - once I had sufficient facts to form such opinion. So far, all I've seen is a raised eyebrow and an invitation from the OP to speculate about his reasons - on which I shall pass.

[jamesducker]

If you see someone walking towards a cliff and you don't speak up, shame on YOU. It is our responsibility to speak up. And no, we may not know all the facts - we NEVER DO. And that is true also when we give solutions here. But that doesn't change reality that we must advise based upon what we DO know. They may have hidden wings; they may wish to commit suicide ... but we STILL should speak up. I can see it now ... someone jumps off a cliff and you didn't say anything and you tell the police, "Well, I didn't say anything because I assumed he had hidden wings. I never thought that maybe he didn't see the cliff." Good grief.

Interesting philosophical debate, this...

... to intervene or not to intervene?

... when does intervention become interference?

... the walker didn't ask for anyone's help, so should we offer it?

... does the walker have sole responsibility to look out for cliffs?

... are we more culpable if he lands on someone else and injures them?

... does our viewpoint change if the walker is holding a small child?

... does our viewpoint change if the walker is holding a spider?

... has nobody considered the feelings of the cliff? ;-)

I really should be doing some work...

[LaRetta]

Incidentally, and perhaps this is a topic for elsewhere, but would you not consider separating files in the (hypothetical) web vs internal situation that I described earlier?

No, I won't get into it here. Suffice to say that that is NOT what this discussion is about.

I have two databases with two groups of customers.

It's one thing to have credit card information in a separate file which is NOT web enabled but this thread is about customers in two different tables. Putting oneself in a position of synchronizing in an attempt to protect data (instead of simply using proper Accounts & Privileges) is like blindfolding an ugly person so they will look better.

[comment]

blindfolding an ugly person so they will look better.

Thanks, that works.

[David Jondreau]

My viewpoint on a few issues:

1) I wouldn't use a separate database for web data. I would ensure I'm using the proper Filemaker Access Privileges to prevent web clients from accessing information they shouldn't be. I would also not store credit card information in a database, there's too much liability. And if I did, I would encrypt it with a plugin.

2) I don't see how I can interfere with anyone posting on a forum, either they take my advice or they don't.

If someone asks me how to get on Route 28, and I know Route 28 goes off a cliff and lots of people make that mistake, I'll check to see where they think Route 28 will take them. If they can't give me an answer, then no, I won't tell them how to get there.

By helping, I believe I assume some moral responsibility for what the result is.

I like helping people, but it's conditional. I don't help people that are abusive or don't provide specific information or use improper capitalization :.

I also don't think having two databases is the metaphorical equivalent of driving off a cliff, but it's the current analogy.

[bruceR]

The person is asking for a solution to a problem not to be told his design is wrong. Global search, the lack of, is a real Filemaker weakness. Where you are right is in indicating that the only way to get global search is to design it in from the start.

Not true, and I can't see what the foundation would be for such a statement. This was even the subject of a Devcon session and a new search utility now being marketed as an easy add-on product.

[Søren Dyhr]

Although I suppose there are lots of people who are new to FileMaker but very knowledgeable about database theory.

I know I have seen this before, but could you please tell me if this initially was a fully normalized system you're talking about here ... and why you think it's only a filemaker specific deployment blunder?

http://fmforums.com/forum/showpost.php?post/331477/

One of the reasons for setting out for a normalized structure is to prevent syncronisation, and when continuing and deliberately denormalize a solution again is it usually done with speed in mind. Which are yours?

There is an interesting analogy to the problem here:

Suppose I ask you to retrieve two sets of books from a library: one set of five books and one of ten. If I ask you which set will take you longer to retrieve, would you know? When I ask practitioners this question, many realize that the time required for set retrieval depends not on the number of books per se, but on the characteristics of the library: how the books are physically stored and retrieved. It is entirely possible for 10 books to take less time if, for example, they are all stored together while the five are scattered across the library; or if the 10 are on the first floor and the five on the sixth; or if the 10 are indexed in a catalog, but the five are not. (If any of the books were being used or borrowed by others, retrieval could take a very long time indeed.)

From this: http://www.information-management.com/issues/20020601/5251-1.html

--sd

[RodM]

The UK group charges $100 us per hour!!

Consider spending your $100 using US consultant

[comment]

I don't think it's appropriate to discuss your private financial dealings with other members in public. Nor do I think this is the place to express your patriotism. Most of all, it has nothing to do with the topic.