Relational Database Setup Help Please

[Jason T]

Hello everyone,

I've been using Filemaker in a flat file format for a couple years and just recently am attempting to create relational databases. I'm creating a hardware asset database to track all my schools assets. Right now I have the following tables and fields. To make it easier to follow the tables extensions are .t and the fields extensions are .f as well as bold instances of tables.

Tables and Fields

Building.t building_id_p.f building_name_p.f

Room.t room_id_p.f building_name_c.f room_number_p.f

Asset.t asset_id_p.f building_c.f room_number_c.f asset_type_p.f brand_p.f model_p.f

Relationships

Building.t building_name_p.f -=- Room.t building_name_c.f

building_name_p.f is a unique auto-entered serial field

room_id_p.f is a unique auto-entered serial field

asset_id_p.f is a unique auto-entered serial field

What I'm looking to do is in my asset.t table I'd like to have a value table assigned to the building_c.f and room_number_c.f fields. Where building_c.f's value list would populate itself with Buildings from the building.t table and the room_c.f's value list populates itself with rooms from the room.t table. Furthermore when I choose a building I would like the room value list to only show rooms that are in that choosen building.

How would I setup my relationships. I just can't seem to get a grasp as to how to properly set this up. I've purchased a couple books but unfortunately none of the examples in these books are anything close to what I'm trying to do.

Thanks for the help.

Jason

[aaa]

Create val list in Building.t then use it in asset.t for buildng_c.f.

Create relation for asset.t and room.t by building_c=building_name_c.f.

Then in Room.t create val list based on this relation.

[Jason T]

I did the following and it does work but I'm concerned because when I look at the relationships graph I now see "one to many" relationship between building and room tables (as I would have assumed), but between room and asset tables I have a "many to many" relationship denoted by crows feet on both sides of the relationship. Is this going to cause problems for me since that an asset can't belong to more than one room and I would think that because of that fact I should be seeing a "one to many" relationship between room and asset tables. Thanks for the help.

Jason

[Fenton]

The table occurrences (TO) and relationships are from Building to Room, on building-name. Then from Room to Asset on Room_ID; this would be one-to-many.

For the value list to filter the rooms from asset.t, there is another table occurrence, with a relationship from building-name in Asset to building-name in the Building table; but this is not the original Building TO. This one is many-to-many (though in practice it would be many-to-one; but FileMaker doesn't know that, 'cause you jumped the Room TO).

Is this what you have? You could attach a screen shot or print-to-PDF of your Relationship Graph.

BuildingsGraph.pdf

[Jason T]

Fenton,

I'm attaching to separate screen captures one named NHCS_Asset_ver1.pdf which has the database setup based on the reply I received from my initial post. In the NHCS_Asset_ver1 database when I'm in the Asset table and attempt to create a new record I'm able to select a building from a pull down value list and then a room from a pull down value list, but the rooms listed are only those rooms in the prior selected building. This is exactly what I want, except in the relationship graph shown in NHCS_Asset_ver1.pdf you can see a many to many relationship between room.t and asset.t which I wouldn't think is right.

That being said when I change the database to follow your example as seen in NHCS_Asset_ver2.pdf I can still create a new record in the Asset table and have a list of Buildings show up but after I choose a Building and then click into the room field no pull down menu appears. Although if I change the Rooms value list to only use values from field room_number_p.f of table Room.t and switch the "Value List Content" option to "Include all values" and then go back and create a new Asset record, choosing a building then clicking in the room list field I get a pull down menu with all rooms not just the buildings from the prior chosen building field.

I've also included a copy of the database based on your example so that you can get a better idea of what I'm trying to do.

So it looks like I'm still having problems. I hope that the attached files will help you help me.

Thanks for everyones help thus far.

Jason

Archive.zip