How would I relate records in the same database?

[gnarlydude]

Is this even possible? I am a total newbie and I need to be able to some how relate records in the same database. This is a basic Client managment/tracking database. For example: if Billy Bob Thorton is one of my clients and his wife Angelina Jolie (hummina hummina) becomes a client as well, I need that to show up on each others records. And yes they need to be separate records.

This is probably a really stupid question, sorry.

Joe

[wildsicilian]

Self joining relationships can be very useful.

Use field for relatives, home phone, or home address ( something that would be the same for related but separate clients )

Define a relationship to the same file comparing client name to relative name or home address to home address, etc.

You can add a button "go to related" or a script to go to related & return or

you can add a portal with fields you want to view for the relative on the client layout.

You could see "relatives" data that was related to your client or blank if no one matched up.

Zap! you're off and running

[gnarlydude]

Thanks Kathy!

I figured there had to be a way to do this. I'm just not smart enough to figure it out myself.

:

[gnarlydude]

I must be doing something wrong. I defined a new self joining relationship based on phone number. I then placed a portal (using the above relationship) on the layout and within the portal I place Last Name, First Name, MI fields. When I went to browse Billy Bob Thorton showed up in the portal on his own record. However, when I added Angelina as a client, Billy Bob showed up in the portal on her record.

What am I doing wrong?

Thanks

[gnarlydude]

I relize what is happening here. Both Billy Bob and Angelina are showing up in the portal on both records. Is there a way to not have the record your looking at show up in the portal?

Thanks

[goostree]

Anytime you use relationships it's best to use some field that doesn't mean anything to relate records to one another. Basing your relationship on the phone number is fairly easy to work with now, but anytime Billy Bob's phone number changes you have to remember to manually go back and change Angelina's phone number also. Otherwise your relationship will be broken.

It's better to set up an ID field with random numbers that don't mean anything.