multiple relationship muddle

[kiwiora]

Hi guys,

Developer Version 6

This is kinda hard to put into words so bare with me!! I'm feeling rather muddled!

I have several separate databases for each individual client. (lets call them client1, client2 & client3). The common fields that they all have are fileno, claimant and client

I would now like to create an "appointment" database which, upon typing in the fileno in a new record will display the claimant and client from the client1/2 or 3. What will then happen is in the respective client database, that appointment record will display in a portal.

The problem I am having getting around is how to make it so the new record picks up the information from the respective client database. Easy enough if you have one, but in this case it has to search out that db which contains the matter.

Any help would be most appreciated!

[ESpringer]

You should name your databases according to what kinds of records they have, first...

Are there three sets of clients? Or three sets of fields for the same set of clients?

In the first case, you should merge the records into one clients file. If the clients are of three kinds or three sources, then of course you need a new field to keep track of that variable.

In the second, you should make one into the real client file, generate the absent fields, and have it look up the parallel data from the same client's info in the other databeses.

[kiwiora]

We have several databases - one for each client. i.e NomDeft, AllAus etc. They all have varying reporting requirements and so they all have different fields EXCEPT for fileno, claimant and client.

I don't understand the last 2 points of your post. Could you please explain in baby steps. Especially the "merge the records into one clients file". I need to keep the databases completely separate and feed the info through to the newly created 'appointment' db.

i.e

NomDef.....)

AllAus.........).....Appt

ZurAus........)

Cheers

[ESpringer]

*Why* exactly do you need to keep the databases completely separate? You may be underestimating FileMaker...

Having different reports, different relevant fields, different scripts appropriate to each -- all this can be accomplished even while one table (file in FM6) lists ALL of the claims (I sense that "claims" is the best name for what you're tracking).

To "merge" the databases would mean (backing up carefully and then) creating one "master" claims file which replicates ALL the field definitions used by ANY of your clients, then importing records (from the existing claims files) so that every claim has a exactly one record in this new table (file). There would presumably be three different primary layouts (in different colors ) which highlight features of claims that are specially important to one client or another. Yet any structure you set up for claims will now be *accessible* for any other claim. Once you really have that file working, you should be able to relegate the three old clients files into frozen archives.

You should also then have a true CLIENTS file, which has only three records, and a few fields for notes, contacts, whatever! From within *that* file, you can easily have relationships allowing a portal to *only* the claims for that client.

FM7 may have some features that would actually help you in the long run, but perhaps it would be too confusing to try to convince you of *that* tonight...

____________________

Let me also say: if you're determined to keep the three separate files, you *can* work it so that an appointments file tracks appointments for all three. *However* it will be really messy to try to get layouts on the appointments side to display any related info about the nature of this claim. (There's no way to have FileMaker consult different files for a relation depending on some field variable.)

In the other direction, you could still make the relationship useful: all three of the clients' claims files could access the appointments table through parallel relationships.

[ESpringer]

Another "compromise" would be having the appointment file get three relationships to the 3 claims files, and it could at least *display* certain basic data about the claim with a complex calc. Suppose the three relations are called ClaimGroup1, ClaimGroup2, ClaimGroup3...

Then you could create a calc that simply strings together all of the data:

ClaimGroup1::Client&ClaimGroup2::Client&ClaimGroup3::Client&": "ClaimGroup1::FileNo&ClaimGroup2::FileNo&ClaimGroup3::FileNo&" for "&ClaimGroup1::claimant&ClaimGroup2::claimant&ClaimGroup3::claimant

So the typical result will be something like

NomDeft: F03-9983 for M. Davis

Since you're sure that only one of the three files will contain a client with this FileNo (right?), then in fact two thirds of this string would be blank, and only the working relation would supply text for the appointments layout to display.

However, you *still* would not be able to get a simple "Go to related record" to work for an appointment. So I still recommend a unified claims file if you can plan in that direction.

[kiwiora]

Thanks for your response and I appreciate your database comments. However, I feel some of your response is condescending and I am not underestimating filemaker at all.

I've inherited these db's and don't have the time to merge them into one. However, when I migrate to v.7 I had already decided that creating just one was what I was going to do.

I have already set it up the portals so in the relevant client db it displays the appointment. The compromise you suggested will work best until I upgrade the db's to 7 and I have written a script which will check the relationship and take the user to the appropriate client record.

Thank you for your assistance.