pulldowns based on name in field

[kibber]

I have a property management company and need a way to stay current with the clients. The properties stay the same, my clients information changes.

I want to be able to type in the clients last name and have a listing of all property id's (in a pop up list) associated with that name, then when you select the ID the property information comes up.

I have one table (client info) with:

first name; LAST NAME; home address; home phone; PROPERTY ID; PROPERTY ADDRESS; PROPERTY NUMBER

I have another table (Property info) with:

PROPERTY ID; PROPERTY ADDRESS; PROPERTY PHONE; LAST NAME

I have related PROPERTY ID of my client info table to my property info table and have created 'options' within my client info table for looked up values back to my property info table. The proplem is I can't search through the last name, I have to know the property id.

I am having trouble with relating a value list to the clients last name.

EXAMPLE

Last name: Smith

Value List of property ID pop up= dislays Smith's (3) properties

Last name: Craig

Value List of property ID pop up= displays Craig's (5) properties

I think I am going to have to create value lists for each client, but how will I then relate multiple value lists to a single pop up list?

Does anyone have any suggestions - if you understand - about what I am trying to accomplish? I am an extreme beginner, so I did look this up but am having trouble relating my needs with previous explainations.

Thanks for your help

kibber

[Inky Phil]

Hello Kibber and welcome to the forum,

I think what you need to do is create another relationship based on last name to last name.

You will then be able to do a layout with client info:last name as a value list using values from client info and propertyID also as a value list based on the new relationship and only showing related values.

If you then choose Smith in the first value list then only Smiths properties will show up in the propertyID value list.

Having selected a property ID you can then use your original relationship to display your property details

HTH

Phil

[kibber]

Hi Inky Phil,

Thank you for taking the time to answer my question. I am glad I am moving in the right direction and that what I want is possible.

Unfortunately my experience level with FM is proving to make this task more difficult than necessary.

-The additional relationship, do I need to make a new property table or do I just connect the last names in the existing relationship between client and property?

-So I will need a pop-up list of all clients last names?

and a pop up list of all property ids? How will it be filtered out?

Again sorry for the lack of understanding on my part and thank you for your help. I will continue to fool around with it tongiht. I attached some photos of my database if you are so inclined.

-Kibber

[comment]

I think these two questions should help you establish the proper structure for your database:

1. Can a property have more than one client (this includes past clients - if you need to keep a track of those)?

2. Can a client have more than one property?

[kibber]

Hi comment,

I think ideally yes to both questions.

1. because the properties stay in the families so long that they will be left to the children and then a brother and a daughter will jointly own it, in addition to potentially having different last names.

2. Yes because almost half (40%) of all home owners in America own additional homes. Interestingly 25% of those 40% are RETIRED. Looks like I have a long way to go.

Thanks for all your help,

kibber

[comment]

In that case, I think you need to change your structure to three tables:

Clients

Properties

Ownerships

The Ownerships table is a join table - it links between Clients and Properties. The Clients table holds details about the client - and nothing else. The Properties table holds details about the property - and nothing else. All associations between clients and properties are written to the Ownerships table.

The fields in the Ownership table are, at minimum:

RecordID (auto-enter serial number)

ClientID

PropertyID

and any other detail about this ownership, such as start and end dates, percentage of ownership (if jointly owned), your contract details, etc.

Note that I use ClientID instead of client name. ClientID is a auto-entered serial number in the Clients table. Thus two clients can have the same name, and a client can change names, without breaking any links.

A portal from Clients to Ownerships will show all properties owned by this client. A portal from Properties to Ownerships will show all clients owning this property (with a bit of ingenuity, you can switch between present owners and full history).

[kibber]

Preface ----------------------------------

Yesterday I was all excited, after over a month and some help from the members of fmforums I had completed my database - save of course for a few minor touches to make things easier.

I began the final step:

Database entry, after weaving my way through a few clients I ran into a snag.

Did you know there are almost 3 million 'Smiths' in the united states...dwarfed only by 100 million 'Zhangs' in the world. Can anyone see where this is going?

I recalled my issue had been previously pointed out to me, but alas in my efforts with the beautification of my database I forgot about this nagging issue. I searched the forum for all my posts and came across this one where comment pointed out I need a new database.

Point of Post ----------------------------

So I have a few questions about Comment's prior comments:

My understanding of your last post is that you have to enter all your clients, then enter all your properties and then go to the ownership table and link all of them. Is this correct? or can I automate some of this?

It seems hard to ask somebody to remember an auto serial number so they can correctly relate the name to the property. How do you use the auto serial to define the relationship but not ask the user to interact with it at all?

I think my brain is just fried.

Thank you for all your help,

-kibber