1 2 Previous Next 25 Replies Latest reply on Dec 31, 2012 7:27 PM by philmodjunk

    Using a drop down menu to add values to a field

    MaxwellRobin

      Title

      Using a drop down menu to add values to a field

      Post

            

           Hi All!

           So I have a field with a drop down menu.

           The menu gets its value list from a field in another table.

           When I click a value it populates the field as it should.

           If i click a new value it repopulates the field with the new value.

           What i would like it to do is add the new value to what is already populated.

           Also, i wuld like filemaker to recognize if the numbers are in sequence or not.

           for example:

            

           The user clicks the number "1" from the drop down menu.

           What the field should look like is this "1"

           The user then clicks the number "2" from the drop down menu.

           What the field should look like is this "1-2"

           The user then clicks the number "3"

           What the field should look like is this "1-3"

            

           The user then clicks the number "5"

           What the field should look like is this "1-3, 5"

            

           The user then clicks the number "6"

           What the field should look like is this "1-3, 5-6"

            

           The user then clicks the number "7"

           What the field should look like is this "1-3, 5-7"

           And so on.

            

           The user should also be able to click values out of numerical order and filemaker will order then in numeral sequence like what was stated above.

            

           Is this possible?

           If so some help would be greatly appreciated.

            

           Thank you!

        • 1. Re: Using a drop down menu to add values to a field
          philmodjunk

               It's possible if you use two fields, one for selecting a number and one for producing the list of number ranges. Then a script trigger on the drop down field can perform a script that adds the new value to those already present in the specified fashion.

               Are you using FileMaker 10 or newer?

               Have you worked with script triggers before?

               Have you created scripts before?

          • 2. Re: Using a drop down menu to add values to a field
            MaxwellRobin

                 FMP 12

                 Yes, I have worked with script triggers

                 Yes, I am an intermediate script writer

                 Will the user be able to edit the field if they select the wrong number or decide not to use a number?

                 Thank you

            • 3. Re: Using a drop down menu to add values to a field
              philmodjunk

                   If the script uses set field to modify the contents of a text field, the resulting text field remains fully editable after the fact. But editing the field could create issues for some scripts if the user modifies the text into a format that the script can't properly handle.

                   If you have 1-2 already in the field and select 2, should the resulting values change to "1"?

                   I'm thinking that an extra hidden field that stores the selected values as a return separated list of values may make this process easier to manage. Have you considered using a checkbox group instead of a drop down list? (It would make some of this much easier to implement.) If you have FileMaker Advanced, a combination of using a checkbox group and a recursive custom function could do this without any scripting needed, in fact.

              • 4. Re: Using a drop down menu to add values to a field
                MaxwellRobin

                     If you have 1-2 in the field and you delets the 2 it should say 1, the same goes for if you have 1-3 and delete 3 it should say 1-2

                     and the reason a checkbox wouldn't work is because the list can get fairly long. And I like the idea of having a smaller field and the list gets dropped down because the layout doesn't have much room for a huge checkbox set

                • 5. Re: Using a drop down menu to add values to a field
                  philmodjunk

                       First, let's look at how selecting a value from the drop down can add/remove it from a return separated list of values. Once you can do that, the script can loop through the values, building ranges for adjacent values.

                       If [ IsEmpty ( FilterValues ( YourTable::SelectedValues ; YourTable::DropDownField ) ) // Value is not in list ]
                          #Add the value to the list in ascending order
                          Loop
                              Set Variable [$K ; value: $K + 1 ]
                              Exit Loop If [$K > ValueCount ( YourTable::SelectedValues ) ]
                              Exit Loop If [GetValue ( YourTable::SelectedValues ; $K ) > YourTable::DropDownField ]
                         End Loop
                         Set Field [ YourTable::SelectedValues ;
                                 List ( LeftValues ( YourTable::SelectedValues ; $K - 1 ) ;
                                 YourTable::DropDownField ;
                                 RightValues ( YourTable::SelectedValues ; ValueCount ( YourTable::SelectedValues ) - $K + 1 ) ) ]
                       Else
                          #Remove the item from the list
                          SetVariable [$TheList ; Value: Substitute ( ¶ & YourTable::SelectedValues & ¶ ; ¶ & YourTable::DropdownField & ¶ ; ¶ ) ]
                          Set Field [ YourTable::SelectedValues ; Middle ( $TheList ; 2 ; Length ( $TheList ) - 2 // strips out leading and trailing returns ]
                       End If

                       Test and make sure you can get this to work. It's not the final answer, but if it works, SelectedValues should contain a return separated list of numbers sorted in ascending order where on ONObjectSave script trigger performing the above script correctly adds/deletes values from this list.

                  • 6. Re: Using a drop down menu to add values to a field
                    MaxwellRobin

                         is "YourTable::SelectedValues" the field under the other field?

                         or is "YourTable::SelectedValues" the field that the drop down list gets its values from?

                         and is "YourTable::DropDownField" the one on top with the drop down list?

                    • 7. Re: Using a drop down menu to add values to a field
                      philmodjunk

                           YourTable::SelectedValues is a new text field that you add to your table. It holds an intermediate form of your selected data--a return separated list of the selected values sorted in ascending order. During testing, put this field on your layout either sized many rows of data high and/or with a scroll bar so that you can watch the values change when you select from the drop down list.

                           YourTable::DropDownField would be the field formatted with a drop down list. You may want to make this a field with global storage specified as it will only hold the most recently selected value.

                      • 8. Re: Using a drop down menu to add values to a field
                        MaxwellRobin

                             ok yes this works perfectly. right now it puts a [return] between each number. whati would like is a "," unless the numbers are in sequence then a "-"

                             Example: 1-4, 7, 9, 13-16

                        • 9. Re: Using a drop down menu to add values to a field
                          philmodjunk

                          I realize that is what you want, We've just set up a prelminary list of numbers that can be converted into what you want.

                          If you just wanted a comma between each selected value, we could use the substitute function to turn the returns into commas, but condensing adjacent values into ranges with a hyphen will take something that can loop through this list--either a custom function created in FileMaker Advanced or via a script that loops through these values.

                          I had to test this one out in a test file.

                          YourTable::FinalList is the field that holds the list where adjacent values have been condensed into ranges.

                          Loop
                          Set Variable [ $J; Value:$J + 1 ]
                          Exit Loop If [ $J > ValueCount (YourTable::SelectedValues ) ]
                          Set Variable [ $Value; Value:GetValue ( YourTable::SelectedValues ; $J ) ]
                          Set Variable [ $TheList; Value:List ( $TheList ; $Value ) ]
                          If [ $Value + 1 = GetValue ( YourTable::SelectedValues ; $J + 1 ) // Adjacent value ]
                          Loop
                          Set Variable [ $J; Value:$J + 1 ]
                          Set Variable [ $Value; Value:GetValue ( YourTable::SelectedValues ; $J ) ]
                          Exit Loop If [ $Value + 1 ≠ GetValue ( YourTable::SelectedValues ; $J + 1 ) // NonAdjacent value ]
                          End Loop
                          Set Variable [ $TheList; Value:$TheList & "-" & $Value ]
                          End If
                          End Loop
                          Set Field [ YourTable::FinalList; Substitute ( $TheList ; ¶ ; ", " ) ]

                           

                           

                          • 10. Re: Using a drop down menu to add values to a field
                            philmodjunk

                                 And you would string together this last script with the first so that both are performed by the OnObjectSave script trigger.

                            • 11. Re: Using a drop down menu to add values to a field
                              MaxwellRobin

                                   Hey Phil! So I added the new script and new field and what is happening is:

                                   When numbers are in sequence it does not put the "-" it just adds ","

                                   When i deselect a number from the list it adds ", , , 1"

                                   Any idea what could be the issue?

                                   I rechecked my script with yours and it seems just fine.

                                   Thanks

                              • 12. Re: Using a drop down menu to add values to a field
                                philmodjunk

                                     I did test this last script on a demo file before posting and it worked fine for every test that I threw at it. I wonder if the first script is leaving an extra return somewheres. I then copied/pasted the script from a database design report (via script pretty to get the indents) so it shouldn't be missing any details.

                                     If you click into that field of return separated values, is there a blank line at the top? Can you click into space below the last selected value?

                                     I can't find my demo file, I think I did that one on a lap top at home. If I get time tomorrow, I'll see if I can't find and share it so that you can compare the actual working file to yours.

                                     You can also post your copy of this script if you want.

                                     To post a script to the forum:

                                       
                                1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
                                2.      
                                3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
                                4.      
                                5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                                6.      
                                7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format. (Use the HTML option on the database tab panel and paste the text into the forum's HTML editor.)
                                • 13. Re: Using a drop down menu to add values to a field
                                  MaxwellRobin

                                       when i select a value from the drop down it puts it in the selected values field and then in the final values field

                                       when i deselect a number it deletes the number in the selected values field but still leaves a [return]

                                       here is what it looks like in the FinalValues field after i delete a number:

                                        

                                       , 1, , , 1

                                        

                                       when i restore the deleted value it looks like this:

                                        

                                        

                                       1, 2

                                        

                                       but it should look like this:

                                        

                                       1-2

                                       If you could send me the file thta would be great! If not i dont mind sending you my script for you to look over.

                                        

                                       Thank You!

                                  • 14. Re: Using a drop down menu to add values to a field
                                    philmodjunk

                                         I tested the first half of the script and determined that it would insert an extra return in places where it shouldn't.

                                         Rewriting the set Field step to fix that issue produced this step:

                                         Set Field [YourTable::SelectedValues ;
                                                         Let ( Lft =  LeftValues ( YourTable::SelectedValues ; $K - 1 ) ;
                                                                  List ( Left ( Lft ; Length ( Lft ) - 1 ) ;
                                                                  YourTable::DropDownList ;
                                                                  RightValues ( YourTable::SelectedValues ; ValueCount ( YourTable::SelectedValues ) - $K + 1 ) )
                                                ) ]

                                         And here's a download link to the demo file where I tested this: https://dl.dropbox.com/u/78737945/ListCondenser.fmp12

                                    1 2 Previous Next