3 Replies Latest reply on Jul 27, 2015 2:18 PM by philmodjunk

    Dynamic List from Pop Up -- Why am I making this difficult?



      Dynamic List from Pop Up -- Why am I making this difficult?


      Ok, first off thanks for the answers on my last question Phil and Guy.

      My issue is this:

       I have two tables.  Table 1= Clients    Table 2 = Doctors.

      I want to do the following:

      I. Using a pop up (why drop downs dont work at all baffles me) menu I want to choose a doctor (from Doctors table) from the list of doctors and populate a field on the client layout.

      II. I want to show the Doctors Name - the data in the 2nd field and not the Keys.

      III. I want to then in a separate field show the Doctors Phone Number - I want this done automatically.

      IV. I would also probably need to in a report show the Doctors name again and not the Key field which is stored in the field from step 1.  When I am doing this I keep getting the fK# stored in the field in Step 1.

      Is there a way to do this without a scripting night mare. I attached screenshots of the database I made for this example.

      Any insight is appreciated. Thank you in advance.


        • 1. Re: Dynamic List from Pop Up -- Why am I making this difficult?

          Drop down lists work just fine. What do you mean by "they don't work at all"? They don't work the same as a pop up menu, but they DO work as they have been designed to work and might well be better than a pop up menu for use here.

          The simplest way (not necessarily the best way), is to use a pop up menu and in the value list set up, specify that it only show values from the second field. This will automatically show the doctor's name in the popup menu field once a value has been selected. Drop down lists do not do this, you have to use a different approach--I could describe two options here for that.

          To get other data from the doctors table is simply a matter of having the correct relationship in place. If the ID field that matches Clients to doctors by doctor ID is the field that you've set up with this pop up menu, you can add any field, such as the doctor's phone number to the layout and it will update to show the doctor's number once you have selected a doctor in the pop up menu.

          If you used a drop down list, you would use this method to add the doctor's name field to your layout--even placing it on top of the drop down list formatted ID number field, to show the name of the selected doctor.

          And there is also a way to use an auto-complete drop down list of doctor names---not ID's to look up (copy over) the doctor ID into the ID field in the client record--but this method does require some scripting to pull off. The advantages though are:

          a) you get a value list of names that auto-completes
          b) the same script can manage two or more doctors of the same exact name where the other options require unique doctor names.
          c) If you enter a new doctor name, the script can detect this and offer to create a new record in the doctors table.

          • 2. Re: Dynamic List from Pop Up -- Why am I making this difficult?

            Drop down lists, I was under the belief through experience and somewhere I read on a forum will NOT display the second field when doing a dynamic list that pulls from the Key+2nd field. It will only display the first field... whereas popups will. or am i wrong.  Wont be the first time. :) thanks for the quick reply I am going to pour through what you wrote.


            • 3. Re: Dynamic List from Pop Up -- Why am I making this difficult?

              That's hardly "it won't work at all" wink When using a drop down list, you have to add the name field from the related table if you want to see the name (field 2) value after you exit the field. The last option that I mention avoids this issue entirely as it doesn't use ID's in the value list.