I'm developing an inventory database.

I have the following tables:

Room - contains fields for buildings, Room #s, and a unique room ID for each room

PC - contains PC info

Monitor - contains monitor info

Printer - contains printer info

In my PC database, I'm trying to enter a building and room# and have it populate the room ID based on the room database. Can sopmeone help me do this? I've created room ID in the PC database and linked it to the room ID in the room database. When I enter a building it populates the room and room ID with the first record of the room database and does not change the room ID when I change the Room.......hopefully that makes some sense. How is this done?

Thanks,

Brad

This looks like a backward approach. Why wouldn't you enter the PC, Monitor & Printer serial numbers into the Room database? Are there more than one of each in a room?

If you allow creation of related records the RoomID would autoo-enter. The Building and Room Number would follow from this.

I'm doing it this way because every room is different. Rooms have 0-30 PC and 0-5 printers. Doing it this way let's me do a portal of the equipment databases from the rooms database and get a summary of the equipment. Is that not the best way to do it? I'm pretty green to FM.

Portals can be used on both sides of the relationships. The problem with the way you are doing it is that you are not using the Room ID. You want to enter data and use that as means to generate the key.

In addition to database development, I also manage my organization's computer inventory. To me it does make sense to populate the room from the Inventory side. When equipment moves to a new location, I pull up the equipment and set the room, not the other way around.

To set the Room ID you could use a conditional value list of available rooms and their IDs. If there are a lot of possible rooms (over 100) then you might narrow this down by Building (select Building first, then the value list can show just the rooms for that building.) Performance can be slow over a WAN with conditional value lists, so if this is too slow, you could use a portal to select the room.

I would also suggest merging all computer equipment into one inventory file. You can link monitors or printers to a specific computer if you need to with a self-join relationship (Inventory::groupedWithSN = Inventory::serialNumber.) One inventory file is easier to manage reports in. Just add an Equipment Type field to differentiate between Computer, Monitor, Printer, Network Hardware, etc.

Ender said:

To set the Room ID you could use a conditional value list of available rooms and their IDs. If there are a lot of possible rooms (over 100) then you might narrow this down by Building (select Building first, then the value list can show just the rooms for that building.) Performance can be slow over a WAN with conditional value lists, so if this is too slow, you could use a portal to select the room.

How do I go about setting that up? I'd love to be able to choose a building from a drop down and then a room. How does a conditional value list work?

I would also suggest merging all computer equipment into one inventory file. You can link monitors or printers to a specific computer if you need to with a self-join relationship (Inventory::groupedWithSN = Inventory::serialNumber.) One inventory file is easier to manage reports in. Just add an Equipment Type field to differentiate between Computer, Monitor, Printer, Network Hardware, etc.

The reason I have each item in a seperate file is because I have detailed statistics for each item (CPU, memory, dot pitch, pages per minutes, etc) and it wouldn't be very clean to try and do all of that in 1 file........unless I guess there was some type of lookup based on the type of equipment and model#?

I don't want to seem like a leech but if you guys could point me in the best direction to get started I'd appreciate it.

There are numerous discussions and examples of conditional value lists in the forums. Rather than reinvent the wheel, I'll direct you to try a search for +conditional +value +list.

Fenton had a simple example in this thread.

Ugo had an example of cascading conditional value lists (with repeating fields) in this thread.

If you script all of the navigation, then combining the databases into one should be no problem. Just have one layout for Computer entry, one layout for Monitor entry, and one layout for Printer entry. Some reports could be specialized with fields that are specific to the type of equipment, but many reports use common fields like Serial#, Equipment Type, Model#, Model Name, Equipment Cost, Acquisition Date, Retirement Date, Status, Current Room, etc. So in this design, printer records don't have some computer-specific fields populated, and computer records don't have printer-specific fields populated. I don't know how "clean" this is, but to me it's easier to manage.

Ok, here is what I've been able to figure out.

In the PC database, I've created a drop down list for buildings that works great. I've also created a drop down list for Rooms but it shows all rooms regardless of the associated building. I can't figure out how to restrict that base on the building. Will someone take a quick look and see what relationship I'm missing?

Thanks,

Brad

fm.zip

The fields you use in PC.fp5 are related fields from Rooms.fp5. This way you manipulate directly the records in Rooms.fp5 and don't store information about the PC.

You have to define two new text fields in PC.fp5 ("Room" and "Building") and place them instead of the related fields. Attach the building value list to "Building" and the Room list to "Rooms".

The Rooms list in PC.fp5 needs also to be corrected: First you need another relationship between PC.fp5 and Rooms.fp5; name it "Building" and relate "Building" in PC.fp5 with "Building" in "Rooms.fp5". Now open the Define Value List Box and your Room list: Select "Use values from field" (instead of "Use value list from another file"); in the following pop-up window select "Only related values" and choose the "Building" relationship (building | ::building) you just made. Voil

Ok, that makes sense and worked. Thanks.

I'm now trying to get the RoomID field in the PC file to match the Room ID in the Room file after. When the Building and Room are changed the Room ID does not. I assume this has to do with the field definition but I can't figure it out.

Thanks,

Brad

fm.zip

I see what you mean (didn't look at the ID field myself

Building and Room are a function of Room ID. You realy want to change Room ID, and let Building and Room be related data. Your interface can let the user enter a Building and a Room number but what they are realy entering is the Room ID.