10 Replies Latest reply on Mar 31, 2014 11:43 AM by ShirleyMB4

    Convert Drop-Down List



      Convert Drop-Down List


           I have a drop-down list of membership years, from 2000 to 2020. I would like to create a calculation to take the years listed (some records have 2000 only; some have 2 years listed; others have 2000 to 2013, etc.), to convert it to a text field with the numbers separated by a comma if more than one year is listed. Thanks in advance.

        • 1. Re: Convert Drop-Down List

               Use Manage | Database | Fields to change the field type to text.

               Replace field contents could be used with this expression to convert existing return separated years into a comma separated list:

               Substitute ( Yourtable::field ; ¶ ; ", " )

               Replace Field contents will update every record in your found set and cannot be undone. It's a good idea to save a back up copy of your file before using this tool to modify your data.

          • 2. Re: Convert Drop-Down List

                 My drop-down list comes from a value list of numbers 2000 to 2020. If I make the calculation you suggest, only the first number from the drop-down list appears, with no comma following. 

            • 3. Re: Convert Drop-Down List

                   Correct. I assumed that you wanted to reformat the data in the field, not the drop down list.

                   Can you explain in more detail what you want to do here?

              • 4. Re: Convert Drop-Down List

                     I want to format the data in the drop-down list field, not the value list.

                     If the entries in the drop-down list shows 2000 only, then that's what should show in the new field. If the drop-down list shows


                     I want it to show 2000, 2012, 2014.

                • 5. Re: Convert Drop-Down List

                       Then what I have described, will do exactly that.

                       But if the field used with the drop down list is also the source of values for your value list, this will also affect what values you see in the drop down list of values. If that's the case, you'll need to choose a way to keep the source of values for your value list separate from this calculation that takes the return separated list and produces a comma separated list. That could be as simple as adding a calculation field that uses the substitute function to get a list of years separated by commas, but keeping your drop down list field unchanged.

                       Note: It's possible to hide the drop down list field behind the calculation field such that you click on the visible field and get the drop down list, but after selecting a value, the list disappears and you see the calculation field placed in front of it with commas separating the multiple values.

                  • 6. Re: Convert Drop-Down List

                         Thank you for your reply.  I'm unclear on your instructions.  The drop-down list field shows the values from the value list. All values are shown, and the user needs to select the value or values (which represent years) needed. I did make 2 calculation fields, but the way I did it isn't the ideal way. I first made fields to correspond to each year (e.g. GetRepetition (MEMBERSHIP YEARS ; 15), and from that calculation, I was able to get a list of all years listed. I know that there has to be an easier and more professional way, but the calculation you indicated above didn't work.

                    • 7. Re: Convert Drop-Down List

                           You'll need to describe what you have set up in much more detail. Get Repetition implies that you are using a repeating field. Is that the case here?

                           The calculation does work if the multiple values are all in the same field--which would be the case with a non-repeating field that has multiple values in it, but not necessarily the case with a repeating field with one year in each repetition.

                           I had assumed that you were selecting multiple years in the same field by holding down either the shift or control key will selecting multiple years from the drop down list. That can insert multiple values into the same field separated by a return character. This now appears to be an incorrect assumption.

                      • 8. Re: Convert Drop-Down List

                             My drop-down list is a repeating field (19 repeats).

                        • 9. Re: Convert Drop-Down List

                               The use of a repeating field--something originally used back in the flat file versions of FileMaker prior to version 3,is rarely the optimum design choice to use in current versions of FileMaker.

                               But this modification of the original calculation should work according to FileMaker Help:

                               Substitute ( List ( YourRepeatingFieldHEre ) ; ¶ ; ", " )

                          • 10. Re: Convert Drop-Down List

                                 Brilliant, as always! Thank you very much -- it works like a charm.