I've also just had a look at the corresonding Table for "Requests".
My foreign key linking the Request to the Room details also remains empty.
How do I get the fk to populate automatically - as I can not (from what I know of) write a script as there is no funtion for me to "activate" it. I just wish for the information to appear automatically in the portal fields once a room is selected.
Karen, it appears from your relationship graph that you don't have the request––>>room relationship set up correctly. If you take a look again at the demo file I posted on your previous discussion (https://fmdev.filemaker.com/message/157824#157824) you will see that there needs to be a foreign key field in the Room table which matches to the primary key in the Request table.
My relationship is set up as follows:
Pk in RoomDetails table
Fk_RoomDetails in Request table
one request:one room
One room: many requests
I was taught that the fk usually goes with the "many".
Is this the wrong?
What you describe does not relfect your screenshot, that's all. You appear to have a portal (or two?) showing ROOM data on your REQUEST layout, which rather suggests the other way around—each request deals with many rooms. I think you ought to try to describe in words and diagrams exactly what the workflow is with this DB, then once you have that clear make sure the DB is constructed to facilitate that. They say good database design begins with pen and paper, and the computer OFF!
I've been doing just that - you should see the state of my desk.
A user makes a request - user starts at user layout/table
User is transferred to request layout where they must select a room (dependent on needs). To facilitate this process, I wished to have the key details of the room selected to appear in a portal (from the room details table). The user may then see a snap shot of the current conditions of the selected room - to confirm whether it suits their needs or not.
This is where I'm having the problem. The drop down allows for a room to be selected but the key details of the selected room are not appearing in the portal. I know I'm missing something but I just don't know what.
Oh. The two portals are from one table. I have one for "set-up conditions" and one for "standard specs" but both apply to the one room - does that make sense? I separated them just to make them easier to read - and to create a visual separation/differentiation.
1 request = 1 room
But ofcourse, in the end, 1 room may have multiple requests linked to it.
Does that help? Is what I am trying to achieve achievable?
I shall have a look now and provide feedback.
You should find the sample file provided by erolst helpful. A couple of comments on your latest posts:
1. Re: "To facilitate this process, I wished to have the key details of the room selected to appear in a portal (from the room details table). The user may then see a snap shot of the current conditions of the selected room - to confirm whether it suits their needs or not." I infer from this that you need a portal to display details of all rooms to enable the user to select the one that best suits their need. (I assume this si some sort of booking system you are building.) This would require a separate TO using a cartesian join to display ALL ROOMS—the portal on erolst's User Request layout does just this.
2. Re: "The drop down allows for a room to be selected but the key details of the selected room are not appearing in the portal. I know I'm missing something but I just don't know what." I assume from this that you are displaying a list of rooms in a dropdown list, perhaps. A portal, as suggested above, could be a better method of presenting this information to the user. And if you do use a portal, erolst's sample file shows you a method to select a room from a portal.
3. Re: "My foreign key linking the Request to the Room details also remains empty." Whatever method you end up using, its essential element must be to insert the room ID into the room foreign key field in the request table in order to link a specific request record with a specific room. If you use a dropdown list it should be set up on the room foreign key field with the room ID as the list's primary field (so that when a selection is made from the list, the ID gets entered). A common method is to construct the list to "also display values from …" and choose a more descriptive field, then also check the "Show values only from second field" option (see screenshot) so that the user is presented with a descriptive list, even though the value they will enter by selecting from the list is the ID.
4. Re: "set-up conditions" and "standard specs" Are these two sets of fields that apply to a room? And if so, are these two portals each displaying multiple fields from a single related room record? If not, then it sounds as if this is what you really need.
One thing you must recognise, again as demonstrated in the sample file by erolst, you cannot accomplish all the FM wizardry with a single set of relationships—you usually need additional TOs. Think of each relationship/TO set as different pathways for different purposes—one to display all rooms, one to display only the room associated with a specific request, etc.
I hope all of this helps you move along with your project.
" I infer from this that you need a portal to display details of all rooms to enable the user to select the one that best suits their need. (I assume this si some sort of booking system you are building.)"
Reasonable assumptions, and the best we can do at the moment.
Because Karen still has not responded to Erolst's request to actually forget the computer stuff - and describe what she's doing.
What IS this system you are building? What is its purpose?
What is the workflow? Forget layouts, forget relationships, forget the computer.
What is the user trying to do, what does the person making requests want to do?
Good points all, Bruce. And for the record, it was my suggestion to turn off the computer.
I am slowly working through erolst's file.
I am, most definitely, needing to change the way I think about tables and relationships - which will take me some time.
Table occurrences are also a new thing for me - so I'll have to get my head around this one also.
I will continue working through erolst's and your suggestions and let you know how I go.
You are correct - I was hoping that a single relationship would give me what I wanted.
I now understand that this is not the case.
Will continue working through your two responses,
User needs to book a room.
Rooms come in different shapes, sizes, conditions.
I, as the facilitator, must be able to interrogate the system to determine where there is available space (to offer as a potential space for occupation). Before I can allocate a room, I must know what the set conditions are (if currently occupied) or the standard specs (if unoccupied).
The database must allow me to produce reports (user number, what is being occupied, how much is being occupied, which groups are occupying a specific room, what percentage of the rooms is occupied by different groups to which the users belong).
I believe what I have now works. I simply wanted to add more features to make it more useable.
As is obvious, I am a beginner. I am learning as I go.
Does that help?
Just touching base.
In the end, how I related the two tables was wrong.
Fixing this gave me what I wanted.
Thanks for the advice offered.