7 Replies Latest reply on Sep 17, 2013 7:06 AM by LoyleneRuppert

    Show limited choices in a pop up field



      Show limited choices in a pop up field


           I have a table that lists both clinical staff and physicians.  For the physicians, I have defined a radio button that is chosen to indicate they are physicians.  I have defined a field that combines (using calculation) the first, middle, last and suffix for each name - both clinical and physicians.  In some of my main form I need to pop up a list of just physicians.  In other I just need clinical names.  I have tried all sorts of stuff and still just get the master list on the pop up.  I'm sure there is a trick to this, but I certainly can't figure it out.  Can someone please help?  Here is what the calculation is for the name field that is populating the value list of names from the table:

           Trim (nameFirst & " " & nameMiddle & " " & nameLast & ", " & nameSuffix)

           I am new to FM and, even with 2 days of work on this I can't figure it out.  I've written a million scripts to no avail.  Please help!!!

        • 1. Re: Show limited choices in a pop up field

               What you describe is called a conditional value list. Here are some links on the subject:

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

               The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

               Note that if you defined two calculations for the full name, one for physicians and one for clinical staff, you could use an If function to return no name if the person is not a doctor in the first case and no name if the person is not clinical staff in the second. Then these could be used to implement the "option 1" mentioned in the forum tutorial to get two values lists, one of physicians and one of clinical staff.

          • 2. Re: Show limited choices in a pop up field

                 OK ... so if I define 3 fields in the table.  One that calls up both physician and clinical staff, one that calls up only physicians and the other only staff, what does the calculation look like?  I've tried a million things and can't get it to work.

                 Is this even possible.  I had no luck with the other approach.  I thought this was a great solution, but I might be completely wrong.

            • 3. Re: Show limited choices in a pop up field

                   One more thing, in the string 

              Trim (nameFirst & " " & nameMiddle & " " & nameLast & ", " & nameSuffix)

              is there a way for the comma and unneeded spaces to be eliminated if the field is blank?  

              • 4. Re: Show limited choices in a pop up field

                     I did mention an If function. Note that both Option 1 and Option 2 as spelled out in the forum tutorial can be used to get the value lists that you want. Option 2 requires a relationship and an added match field. Option 1 only requires a calculation field, but option 2 can be more flexible.

                     using that radio button field, your calculation field to be used to get a value list of only physicians would look like this:

                     If ( YourRadioButtonField = "Physician" ; FullNameCalculationFIeldHere )

                     Make this an auto-entered caclulation on a text field and specify the "Unique values" validation. (You don't want two physicians with exactly the same name.)

                     Note that this field is empty for clinical staff. Then you set up your value list to list ID's from your table as field 1 and the above field as field 2 with the "show values from second field" option selected. This will automatically drop out all the clinical staff entries from the value list.

                     For your Full Name calculation to correctly handle empty fields, you can modify it something like this:

                Substitute ( List (nameFirst ; nameMiddle ; nameLast ) ; ¶ ; " " ) & If ( Not Isempty ( nameSuffix ) ; ", " & nameSuffix)

                     The list function combines the listed fields with a return between each non-empty field. The substitute function then replaces the returns with spaces. And then the IF function tacks on the comma and name suffix, but only if the nameSuffix field is not empty.

                     I recommend that you put Trim(Self) as an auto-enter calculation on each of these name fields as a way to remove any leading or trailing spaces from each of the individual fiels.

                • 5. Re: Show limited choices in a pop up field

                       OK ... progress!  I got the fields to work!  I have cFullName for all staff - clinical and docs.  I have cFullNameStaffOnly for non-physicians and cFullNamePhysOnly for physicians.  All are working great.  I even made Table Occurences and pointed the pk_staff to the fk_OrStaff or fk_phys as needed.  I do have a field in the database that needs both physicians and clinical staff to be referenced together so I kept that in the main clinical staff table.  

                       Now the pop up returns nothing but a ?.  I can't even put it back to the working state it used to have.  I've manipulated data all over the place and nothing.  What in the world am I missing now?

                  • 6. Re: Show limited choices in a pop up field

                         I can't tell for sure what you are missing without knowing exactly how you defined the value list for your pop up menu. Either upload a screen shot of that dialog box or list the options that you have selected and then I can take a look at why it doesn't work.

                         One possibility comes to mind: Make sure that "Text" is selected as a result type for your cFullName calculation.

                         Hmmm, and I just noted an oversight in my last post. I recommended that:

                         If ( YourRadioButtonField = "Physician" ; FullNameCalculationFIeldHere )

                         Be an auto-entered calculation so that you could enforce a Unique Values validation option, but forgot to mention that such a field does not automatically enter a value into existing records when you first add this calculation nor will it update automatically if you modify it at a later date. It will only enter a value when either a) it is a new record that you have created or b) you modify one of the text fields referenced by the full name calculation.

                         Here's a link on a slick way to force this field to update your existing records: Updating values in auto-enter calc fields without using Replace Field Contents

                    • 7. Re: Show limited choices in a pop up field

                           That was totally it!  I had made them a calculation but hadn't changed it to text instead of number.  Once I did that they worked like a charm.  Thank you so very very much!