Lookpu between two tables

[breicher]

I'm using FM6 for an inventory database. I've got it broken down into a few separate databases.

Rooms:

Building

Room

RoomID

PC:

Building

Room

RoomID

Serial#

etc

Monitor:

Building

Room

RoomID

Serial#

etc

I've got everything related by RoomID and have a lookup created to pull the RoomID when a building and Room are entered.

If I enter a new piece of equipment in the Room database, everything is ok but the Building and Room fileds are not populated in the PC or Monitor database. If I enter the equipment in it's respective database the room ID is not correct because in the look up it finds the same room number but for a different building.

How can I fix this? I'll attach the databases. It took a lot of help getting this setup so be gentle with me.

Thanks,

Brad

FileMaker.zip

[breicher]

Any help would be appreciated.

Brad

[QuinTech]

Hi, Brad.

If I enter a new piece of equipment in the Room database

Not to be too persnickety, but this doesn't seem like good structure. Enter equipment in the room database? Seems you should only be entering rooms in this file. I suspect maybe you're just misusing nomenclature, so i'll forgive you.

If the Building and Room fields in the PC and Monitor databases are lookups, then the behavior you're seeing is expected. If you want those values to change when the values of Rooms::Building and Rooms::Room changes, don't use lookups but put related fields on your layout.

it finds the same room number but for a different building

In other words, Smith Hall has a room 345 (ID number=Smi2) and Jones Hall has a room 345 (ID number=Jon2), and when you enter room 345 and building Smith Hall in, say, the monitors database you still get ID number=Jon2?

If that's it, check your relationship. You should be using a concatenated key, equal to

Building & " " & Room

or something like that.

HTH,

Jerry

[breicher]

Not to be too persnickety, but this doesn't seem like good structure. Enter equipment in the room database? Seems you should only be entering rooms in this file. I suspect maybe you're just misusing nomenclature, so i'll forgive you.

If the Building and Room fields in the PC and Monitor databases are lookups, then the behavior you're seeing is expected. If you want those values to change when the values of Rooms::Building and Rooms::Room changes, don't use lookups but put related fields on your layout.

Yes they are lookup fields. I can live with that functionality. We were just making changes for the first time and weren't doing it properly.

In other words, Smith Hall has a room 345 (ID number=Smi2) and Jones Hall has a room 345 (ID number=Jon2), and when you enter room 345 and building Smith Hall in, say, the monitors database you still get ID number=Jon2?

If that's it, check your relationship. You should be using a concatenated key, equal to Code:

--------------------------------------------------------------------------------

Building & " " & Room

--------------------------------------------------------------------------------

or something like that.

I'm not sure how to do this....

in my PC database I've got Room related to Room in the Room database. The relationship looks like Room=::Room and the related file is Room.fp5.

I appologize for my lack of understanding how to change this.

Thanks,

Brad

[Ender]

The concatonated key that Jerry is talking about is for building a relationship based on multiple criteria. To implement it, make a calculation field in the parent file:

Building_Room_Link (calculation, text result) = Building & " " & Room

If this relationship will be used for record creation (like in a portal), then the match field (in the related file) will need to be a text field:

Building_Room_Match (text)

If this relationship is not used for record creation, then the match field can be calculated from other fields:

Building_Room_Match (calculation, text result) = Building & " " & Room

The relationship then is Room::Building_Room_Link = PC::Building_Room_Match

Just an aside, I have a similar inventory database, and I find it easier to have all types of equipment in one file (PCs, Monitors, Printers, etc.) They can then be linked with a self-join relationship:

Equipment::Installed_With_Serial# = Equipment::Serial#

This allows inventory reports to show all equipment in the same report. You can find just a certain type, or sort by type if needed.