If master-room-list has one record for each room, then you need a relationship that matches by more than building abbreviation. It should also match by room number. This is sometimes referred to as a "multi-predicate" relationship.
RoomInformation::BuildingAbbreviation = master-room-list::BuildingAbbreviation AND
RoomInformation::RoomNumber = master-room-list::roomNumber
With such a relationship in place, no scripting is needed. You can now use either a dynamic or static method to display the data from the master-room-list table. Which option is best depends on the needs of your users when using this system.
See this thread for a description of how to set up both methods and how they differ from each other: Auto Fill
Note that no scripting at all is needed to make this work.
Thanks for the quick response!
So your solution works, and I had tried it before but probably had some other variable out of whack at the time. It does screw up one thing I had going for me though. Before adding the 'Room Number' to the table relationship, when I would select the 'Building Abbreviation' it would auto-populate the 'Room Number' drop-down with the room numbers in that particular building. When I add 'Room Number' to the table relationship, this feature no longer works.
This feature worked by setting a value list for 'Room Number' using the settings shown in the image below. I know there's got to be another way to do this, but what would you suggest? Why does changing the table relationship cause this setup not to work?
Because this is a conditional value list that uses the "include" option, with the changed relationship, no room numbers appear in the value list because the new version of the relationship no longer matches to any records until a room number is present in the room number field.
This can be easily fixed.
In Manage | Database | relationships, make a new table occurrence of master-room-list by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as master-room-listByBuilding.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
RoomInformation::BuildingAbbreviation = master-room-listByBuilding::BuildingAbbreviation
Then you can update your value list to list values from master-room-listByBuilding instead of master-room-list and your value lists will work once again.
It took me a little monkeying around but I got it to work. I really appreciate it. I hope you get paid to answer all these posts!
I have another question. It's similar but different circumstances.
I want to be able to add pieces of inventory to this "Room Info" table by inputting a unique serial number I've called 'CTS ID'. Each inventory item is its own record in a separate table, "Inventory". When I enter an item into the "Inventory" table it assigns it a unique, 7-digit serial number in the field 'CTS ID'. So on my other layout (Room Info), I'd like to be able to put in the CTS ID for each inventory item in that room and have it fill in related fields from each record. The problem I'm up against is that it's returning the serial number for the first item I enter in each of the serial number fields that follow.
You can see in the screenshot the fields that I'm trying to populate. What I've done at this point is create a unique 'CTS ID' field for each type of item (so one for Mac, one for PC, one for Projector, etc.). Then when I input data in that particular 'CTS ID' field it can store the value in the "Room Info" table but also fill in the other fields (Mac_Serial, PC_Serial, etc.), grabbing the data from the "Inventory" table. Does this make sense?
I'm fairly positive this will take some scripting, and I'm not adverse to putting a little "Go" button next to each 'CTS ID' field that makes the lookup happen. If that's the method I go I'd like to script it in such a way the same script works for each object type instead of writing a script for each, but the script would have to know to only do the lookup for the current field, not all the fields on the layout. Thanks for any help...
You need to use separate records for each item instead of separate fields. A releated table listing such items will make things simpler and more flexible to work with in a number of different ways.
I don't disbelieve you, but my goal was for all this equipment to be contained inside the room record, so that a single room record would show all the room info plus all the equipment in that room. Is this unrealistic? I already have the single-item inventory records where each piece of equipment will have its own record.
A portal to the related table of equipment is one way to achieve that visual effect. List view layouts based on the equipment list and data from the related table of room information is another.