Jump to content

Using Value lists to bridge 2 tables


Moosh

This topic is 6560 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi!

I am building a property management database. There are 2 tables -- the Property Information and the Tenant information.

the Property Information table holds all the master info about a prop. Owner, mortgage, insurance, etc.

In the Tenant Info I have a value list set up, generated from the Property Info table, which automatically lists in a drop down each property address from each record in the Property Info table. So if Tenant Joe lives at 1725 N. 3rd, I just go to the Property Address field and select 1725 from the Property Info Value List.

Now's the tricky part. Once I select 1725 on the Tenant Page through the value list, I now want to autopopulate all the other property info on the tenant page -- like Address, City, State, Zip, etc., and have this content be pulled from the Property Info table.

So basically on the Tenant info, I want it to know that I've assigned the tenant to Property 1725, then I want the City field to know that it's Property 1725, and have the Tenant Info Table field City look up the Property Info City field which is a part of the record for 1725.

Does this make sense?

I am baffled and would really appreciate any help!

Thank you so much!

Moosh

Edited by Guest
Link to comment
Share on other sites

Table 1: Property Information

Record 1 (brackets indicate fields)

[Address] - "1725 N. 3rd Street"

[City] - "Harrisburg"

[state] - "PA"

Record 2

[Address] - "1409 Penn Street"

[City] - "Philadelphia"

[state] - "PA"

Table 2: Tenant Information

Record 1

[name] - "john smith"

[address] - this is a pull down list of Addresses from Table 1, so I pick "1409 Penn Street"

[city] - now that I pulled down 1409 in the Table 2 address field above, I want this field to automatically find the 1409 Record in Table 1 and populate this field with "Philadelphia"

Does this clarify this at all?

Thank you!

Edited by Guest
Link to comment
Share on other sites

Wait so you want to select a tenant for a property...? Just put a field in rentals called tenant ID or something, where tenant ID will be a foreign key for your tenant Primary Key. Then, just create a value list that displays all tenants names, but actually stores their primary key (maybe an id number or something).

Attach the new value list to the field in your Property Layout. Create a relationship between the table occurance that your property Layout is attached to, and a tenant table occurance via the foreign key field in your property table and the primary key field in your tenants table.

Then proceed to populate your property layout with fields from your related tenants table occurance. Populating the layout with fields from the related table occurance ensures that if the tenants details are updated in your tenants table they will be updated for your property layout and vice versa.

~Genx

Link to comment
Share on other sites

Kind of but not exactly. I don't want to select the tenant, I'm adding them in. What I want to do is select the property address for the tenant, and then have all the other property information populate into the tenant's record.

So if I pick the address from a drop down, I want the city, state, zip all that to auto populate. The city, state, zip info sits in another table which is the master property info table.

Thank you.

Link to comment
Share on other sites

hmmm, okay, well i was making it so you could attach tenants to a property vs look up a city for a property from your tenants table which is what you appear to want to do...

Basically, do everything i said but backwards. The theory is all there, i'd type it out again but if i do ill fail an economics test i've got tonight :P. Will help out later if you still have any troubles.

~Genx

Link to comment
Share on other sites

A lookup would do it. But you don't really need to populate fields in the tenant file with property info. You can just show the related property fields.

If you populate the property data in the tenant file you're duplicating data, which is not a good idea.

Link to comment
Share on other sites

so how do I show the data?

I also kinda want to keep the data separate, i.e., have a MAster property file.

I don't really understand how lookups actually work. So it looks something up, but then how do you then get it to populate the field?

Link to comment
Share on other sites

I think I know what you mean, since you're on fm7 aren't there neither autocomplete or popups sans keyfield shown, so we have to make it with a lookup to prevent the keyvalues being shown.

How ever should you consider if you not might grow out of a one property per tenant thingy, and consider turning it into a many-to-many relation instead, so you open up for several tennants on the same property as well as several properties hold by one tennant.

Investigate this template...

--sd

propertyTenant.zip

Edited by Guest
forgot to attach...
Link to comment
Share on other sites

Look up fields would be double storing data. Like i said, but backwards.. Put the related property fields i.e. state city etc. on your tenant layout, right click them, choose set field behaviour or whatever it is, and disalow entry in browse mode. This will mean that no one will be able to change the property details from the tenants table... and you will effectively have your master field. Look-ups basically copy data from your related TO to a local field, this doubles on data storage but may be required in some cases... likely not in yours.

~Genx

Link to comment
Share on other sites

hello,

thank you for the advice. the database is quite comprehensive, this is the "final touch" to get the various components. I feel that if I could understand how to do this then the applications would be tremendous.

the challenge is that i don't understand how to make the lookup function work. is there a simple example of a file that i could look at?

thank you!

Link to comment
Share on other sites

Hey Søren!

I didn't see the file you uploaded. That's *exactly* what I am trying to do. Holy smokes this helps a lot!! Thank you!

Thanks for everyone who has been helping! I just joined and you're all amazing.

Warm regards,

Moosh

Link to comment
Share on other sites

Look up fields would be double storing data. Like i said, but backwards.. Put the related property fields i.e. state city etc. on your tenant layout, right click them, choose set field behaviour or whatever it is, and disalow entry in browse mode.

Indeed but otherwise is the solution to have modality via new windows and scripted entries ...to avoid having shown ID's.

A way I personally would prefere, BTW!

--sd

Link to comment
Share on other sites

This topic is 6560 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.