Go to 'Related'

[Tom England]

I have a client database - some of these clients are related (physically ie husband and wife).

Each client has a unique client reference eg ABC001

I want to create a script to switch between husband/wife records - the problem is that they are in no way related (in FM terms). I am really looking for a bit of advice as to the best way of approaching this.

The only method I can think of so far is to have a field 'RelatedReference' where users would input the 'related' Client Reference. I would then create a script (initiated by a button) that would just copy this ref --> Enter Find Mode --> Go To 'RelatedReference' and Paste --> Perform Find.

Is there a more 'appropriate' way of doing this??

Thanks

[IdealData]

How about SURNAME

[djgogi]

Hi Tom.

Well, first thing to make clear, II'll give you two methodes, which of them you should use depends on how many record you have at a moment a how fast that number will grow up.

The second one is in case of large number of records to deal with.

So, some preparatives

if you allready don't have it create an flag field "opositesex" with obvious values in it(M for female and F for male!).

Than create an relationship XXX::sex-->opositesexsex (you have the field sex don't you?)

Create an field, if you prefer it could be the one you mentioned "RelatedReference".

Create an relationship HusbWife::RelatedReference-->unique client reference (you didn't give the name of this field in your post)

OK, we have arrived at moment of separation.

First case when we won't have so many records

Make an value list husband_wife as only related values of field unique client reference (you didn't give the name of this field in your post) diplaying also values from field name ( surname or name & " " & Surname whatever you like) from relationship "XXX"

Attach this list to field RelatedReference.

For being able to set the "other" side creata an script

Set Field [HusbWife::RelatedReference,"field unique client reference (you didn't give the name of this field in your post) "]

Now to switch from an record to another you need just the step

GTRR [from relationship HusbWife. show only related]

Second case too many records so the list is not appropriate.

This case differ from first only in fact that instead of selecting husb/wife from list you'll make the selection from portal and whitin the same script you could set the other side ID.

The script( attached to portal row) could be something like this:

Set field [RelatedReference,"XXX::field unique client reference (you didn't give the name of this field in your post)"]

Set Field [HusbWife::RelatedReference,"field unique client reference (you didn't give the name of this field in your post) "]

In both case you could filter either list or portals using some other flags like "married" ect ect.

HTH

Dj

[Tom England]

Mark - There will always be the awkward wife that wants to keep her maiden name!!!

DJ - Thanks for the prompt and comprehensive reply. I shall give it a go and get back to you

[danjacoby]

Mark, what century are you living in?

In the first place, there are plenty of unrelated people with the same surname.

And in the second place, many women don't take their husbands' last name, and many more keep their "maiden name" professionally. (Doncha jus' love the term "maiden name", indicating that the unmarried woman is by nature a virgin?)

[keshalyi]

Actually, the term 'maiden name' is even funnier when you're a man who changes their name to match their wife... Look at me! I'm a 'maiden'!

Anyway...

Honestly, the problem with finding by some common criterion that denotes marriage is that is utterly inflexible, and non-circumventable. Marriage, or if you get broader, relationships are just too broad. In the example before (and I don't the exact purpose of your dbase, so it may not be pertinent) what happens if your company has two men as domestic partners? We had that with our employee database here, and it made hell with things like health insurance.

Be that as it may, I've foudn making the relationship as generic as possible is just plain wise. My favorite way is to just do a selfjoin, and create a field, repeating or simple, of related person(s). Then, have a selfjoin portal, where they can choose to relate the person, and choose, or enter a custom, relationship type. Just my two cents. Like I said, I don't know what you're building for, so you may not need that flexibility, but then, it never hurts to plan for the future. Who knows, gay marriage may be legal in a few years, and then you'd have to rebuild your whole database! Well, unless its, like, the membership roster for a southern baptist church or something .