7 Replies Latest reply on Aug 8, 2013 8:21 AM by philmodjunk

    Collect all values starting with specific text

    RobertRichmond

      Title

      Collect all values starting with specific text

      Post

           Hi,

           I have two similar but different situations. Users in my database are able to click a button which takes them to a layout where the user can select buttons I have set up to quickly select groups

           I am wanting to enable my users to pre select options I am providing them with, without having to click every cell.

           In one situation where the user can select Senior school (for example) I use the option:

           

                Set File [Homework_Support_plus_Work_Variations::Globabl Academic Year; 10(return symbol)11(return symbol)12" 

           and this works well. However now I need to preselect the Tutor class, and the tutor class changes each year!!

            

           I thought pehaps in a script I could still use the "Set File" option, and set the value Homework_Support_plus_Work_Variations:: Global Tutor Class using a calcualtion, but I am not sure how to return a list like what I need.

           e.g. If the user selects year 11, it would check the field which contains this info (specifically  Student Current::Tutor Group) and filter out any Tutor groups which start with 11. e.g. 11A, 11B, 11D, 11P, 11O.

           It then needs to go through this list and provide it back in the format:

           11A(return symbol)11B(return symbol)11D(return symbol)11P(return symbol)11O

           Any idea on how I might achieve this?

        • 1. Re: Collect all values starting with specific text
          philmodjunk

               Can't quite figure out how your tables and fields are set up in your current design from your initial post. Can you describe what you have in more detail.

               And if you are entering multiple values as a return separated list into the same field, it's very possible that a better design might be possible that eliminates doing that. It sounds like you should be working with a set of related records, one record for each such selected value instead of putting all of these values in a single field.

          • 2. Re: Collect all values starting with specific text
            RobertRichmond

                 Hi Phil,

                 There are two tables involved here.  The first table is: Homework_Support_plus_Work_Variations The related table is: Student Current.

                 The field Student Current::Tutor Group, contains the tutor groups we use at the school. Each student (also in Student Current) has their own record in Student Current, which also has their specific tutor group they are a member of in the field Student Current::Tutor Group. 

                 So for example you may have something like this:

                 Student Name   Tutor group
                 Joe Bloggs         11A
                 Mary Smith         12B
                 Jason Taylor      11C
                 Henry Jones       11D
                 Robert Window  11C
                 Geoff Davis         8D

                 So here all I need back are just the tutor groups which contain the number 11 - which is all the year 11 Tutor groups  (I do need to do this for all year levels but I can figure this part out). I want to add this items into a global value seperated by return characters. The reason I want it like this is because I have a checkbox field which then displays the items selected to the user (image below).

                 So I'd need a list like

                 11A(return character),11C(return character),11D 

                 Each items is listed only once.

            • 3. Re: Collect all values starting with specific text
              philmodjunk

                   Yes but you can get your checkbox interface without packing all the values into a single field of a single record--which can make for complications when you then need to work with this data. Not saying it can't be done, just seems a less than optimum approach. But let's leave that aside for a moment. I'm still trying to understand how you need this to function.

                   Your example shows one record for each student:

                   Student Name   Tutor group
                   Joe Bloggs         11A
                   Mary Smith         12B
                   Jason Taylor      11C
                   Henry Jones       11D
                   Robert Window  11C
                   Geoff Davis         8D

                   And each student in your example is assigned to exactly one tutor group.

                   So why do you need a checkbox formatted field of all the tutor groups? Seems like a radio button format that permits only one selection for each record would make more sense. What am I missing?

              • 4. Re: Collect all values starting with specific text
                RobertRichmond

                     Hi Phil,

                     Yes. Each student is a member of exactly one form class. They can never be a member of more than one form class. The list of form classes you see above are where filemaker goes to the student current table, has a look for all the tutor groups and returns a single value for each unique record. 

                     This provides a list where the user can select the form class/es they want to search with. They ned to be able to search more than one form class at a time, as we have different heads of departments who may look after two different groups. Allowing it to be done this way creates a degree of flexibiliy.

                     So what I would like to add is a way for the heads of school (who look after year groups) to select the form classes they want in bulk. Year 11 is just the example here. I'd like a button which said "All Year 11", which then adds the values to a list which start with 11 (not they can't start with 1 and we have year 1 and year 10 and year 12) and have a letter after them. 

                     So if they selected "All Year 11" it would return to the value "Homework_Support_plus_Work_Variations::Globabl Academic Year"   11B(return)11C(return)11D(return)11K(return)11M(return)11P(return)11R(return)11T . 

                     The reason I don't just hard code it is that these tutor groups change each term and that would mean I'd have to come in each term and readjust the buttons.

                     It needs to go to Student current, find which values start with "11" in the Tutor Group column, and add those values to "Homework_Support_plus_Work_Variations::Globabl Academic Year"   as above. 

                     I hope I have explained this better.

                • 5. Re: Collect all values starting with specific text
                  philmodjunk

                       Yes but that still doesn't look like a format that benefits from the check box group.

                       You could, either manually, or via script enter find mode and enter 11 as your find crteria and be able to find all

                       Records where the tutor group field value starts with 11.

                       But if you want to use a check box group like you have in place now to find all records where the user wants all values that have a tutor group matching one of the values selected in the check box group you can use a scrpt such as the looping script example in the following thread to find those records: Scripted Find Examples

                  • 6. Re: Collect all values starting with specific text
                    RobertRichmond

                         Well I got it working...A little different to your suggestion however. Thought I'd share what I've done.

                          

                    • 7. Re: Collect all values starting with specific text
                      philmodjunk

                           I knew you can build a list in a check box field in that fashion. You can even make it simpler by using:

                           SetField [ Table::CheckboxField ; List ( Table::CheckboxField ; NewValueToAddHere ) ]

                           I simply remain unconvinced that doing so is a good idea.