12 Replies Latest reply on May 23, 2014 10:25 AM by notJames

    Replace all drop down list selections

    notJames

      Title

      Replace all drop down list selections

      Post

           I have a table of students in a martial arts school. Each record contains a drop down list with all the different colour belts. After each promotion test, every promoted student moves on to the next belt.

           Is there a way to change every record at once so that the colour belt field shows the next option on the list? If this involves a calculation, will I have to assign a number value to each colour belt in the list?

        • 1. Re: Replace all drop down list selections
          philmodjunk

               If you can pull up all your students to be promoted and only the students to be promoted into your found set. Replace field Contents can be used to promote your students. This will be easiest to do if you can perform a find for all students to be promoted to one specific color. Then you can simply select the next color for one student and use Replace Field Contents to update all the others to match.

               Finding all students of all levels that have qualified for promotion and then promoting them will be more complex and assigning a number to each level would make this process simpler as the replace field contents calculation can then just be a calculation that adds one to this number field.

               Note: Replace Field Contents can modify literally every record in one table all in one go so it can be a very good idea to make a back up copy of your file just before experimenting with this option to learn how it works just in case you get an unexpected result.

          • 2. Re: Replace all drop down list selections
            notJames

                 Thanks for the quick reply. If I try option 2 and do assign a number to each level, can I still have the drop down list show just the colour names, or will it be the assigned number + colour name (i.e. 1_white, 2_yellow, 3_green, etc)? Or just the number itself?

            • 3. Re: Replace all drop down list selections
              philmodjunk

                   If you change it to a popup menu and set up a table where you have one record for each level, one field in that table can be the number and the other field can be a text field with the color.

                   Then you can set up your value list to enter the number, but display the text for the color name.

                   In Manage | value lists

                   create a value list and select the "use values from a field" option.

                   Select the number value for field 1, select the "also display values from" option and select the text field for field 2.

                   Click the "show only values from the second field" option.

              • 4. Re: Replace all drop down list selections
                notJames

                     As soon as I posted the question, I figured the answer would be more or less what you described. I guess that means I'm learning.  ;)

                     Thanks again.

                • 5. Re: Replace all drop down list selections
                  notJames

                       Everything's working great so far. Now, if I have my number values from 1 to 24, is there a way for my replace field contents calculation to exclude the numbers 18-24? Right now, the calculation is:

                       BeltID + 1

                  • 6. Re: Replace all drop down list selections
                    philmodjunk

                         I'm not sure that I understand. Doesn't that just limit your values to 1-17 instead of 1-24?

                    • 7. Re: Replace all drop down list selections
                      notJames

                           Yes. 1-17 are the non-black belt ranks who all get promoted at the same time. 18-24 represent the black belts, who test at different times, so they won't get promoted with the non-black belts. So this global change would really only affect 1-17, hence my need to be able to change the field for those records at once.

                      • 8. Re: Replace all drop down list selections
                        notJames

                             So is there a more elegant solution to do this, apart from splitting the two levels of belts? I would prefer to keep them in the same field.

                        • 9. Re: Replace all drop down list selections
                          philmodjunk

                               If you review my original posts, I said "if you pull up all the records for students that need to be promoted in a found set...."

                               You shouldn't be pulling up black belt level students in your found set in the first place and thus they will not be promoted by the Replace Field Contents action.

                               You could use a calculation option with If or case to keep from changing the value when it's a black belt student record, but that seems unnecessary as you should be able to easily exclude those records from your found set.

                          • 10. Re: Replace all drop down list selections
                            notJames

                                 So what would be the best way to find all the records for the students ranked 1-17 at once?

                            • 11. Re: Replace all drop down list selections
                              philmodjunk

                                   A script could do it like this:

                                   Enter Find Mode [] --> clear the pause check box
                                   Set Field [Students::Rank ; "<18" ]
                                   Set error capture [on]
                                   Perform Find []

                                   But I don't know enough about your school and your database to know what additional criteria might be needed to omit records for students below rank 18 that should not be promoted.

                                   You could also do this manually if you added a copy of the rank field to your layout without the value list formatting so that you can enter <18 manually after entering found mode.

                              • 12. Re: Replace all drop down list selections
                                notJames

                                     I think I might be better off just adding an "eligible for promotion" check box to the layout. That way, I can just check off all the students that are ready to be promoted, then do a find for that check box and use the original script for replace field contents.

                                     But I'll keep this other script in mind too just in case. Thanks for all your help.