14 Replies Latest reply on Sep 22, 2014 10:52 PM by karenf

    Autopopulating a portal after making a selection from a drop-down menu

    karenf

      Hi,

       

      If I could get guidance on the following scenario:

       

      Upon selecting a room from a drop down menu ("Request" layout) i wish for related fields in a portal to auto-populate (based on the selection made).

       

      I wish for this to be transient - ie. the information in the portal changes as my selection in the drown down menu changes.

       

      The "Requests" table and the the "Room Details" table (the table from which the information is needed to be taken to populate the portal) are related.

       

      I have attached images to assist.

       

      Thanks,

      - Karen

        • 1. Re: Autopopulating a portal after making a selection from a drop-down menu
          karenf

          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.

          • 2. Re: Autopopulating a portal after making a selection from a drop-down menu
            keywords

            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.

            • 3. Re: Autopopulating a portal after making a selection from a drop-down menu
              karenf

              Hi KW,

               

              Thanks!!! 

               

              My relationship is set up as follows:

               

              Pk in RoomDetails table

              Fk_RoomDetails in Request table

               

              Rationale:

              one request:one room

              One room: many requests

               

              I was taught that the fk usually goes with the "many".

               

              Is this the wrong? 

              • 4. Re: Autopopulating a portal after making a selection from a drop-down menu
                keywords

                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!

                • 5. Re: Autopopulating a portal after making a selection from a drop-down menu
                  karenf

                  KW,

                   

                  I've been doing just that - you should see the state of my desk.

                   

                  Workflow:

                  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?

                  • 6. Re: Autopopulating a portal after making a selection from a drop-down menu
                    erolst

                    Karen –

                     

                    here is a sample file with a few scripts and techniques that you may find helpful.

                    • 7. Re: Autopopulating a portal after making a selection from a drop-down menu
                      karenf

                      Hi erolst,

                       

                      I shall have a look now and provide feedback.

                       

                      Thanks,

                      Karen

                      • 8. Re: Autopopulating a portal after making a selection from a drop-down menu
                        keywords

                        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.

                         

                        Screen Shot 2014-09-17 at 10.41.39 am.png

                        • 9. Re: Autopopulating a portal after making a selection from a drop-down menu
                          BruceRobertson

                          "  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?

                          • 10. Re: Autopopulating a portal after making a selection from a drop-down menu
                            keywords

                            Good points all, Bruce. And for the record, it was my suggestion to turn off the computer.

                            • 12. Re: Autopopulating a portal after making a selection from a drop-down menu
                              karenf

                              Thanks KW.

                               

                              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,

                              Karen

                              • 13. Re: Autopopulating a portal after making a selection from a drop-down menu
                                karenf

                                Hi Bruce,

                                 

                                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?

                                 

                                Thanks again.

                                • 14. Re: Autopopulating a portal after making a selection from a drop-down menu
                                  karenf

                                  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.

                                   

                                  -Karen