12 Replies Latest reply on Dec 29, 2015 6:25 PM by quirkycrone

    Issue with Searches in text fields not working

    ColinWoytowich

      Title

      Issue with Searches in text fields not working

      Post

           Hi,

           We have a database of courses currently being taken by each student.  These are in a single text field for each student, with one course per line (ending with a return).  For example:

           ----

            
           English 10
            
           Spanish 10
            
           Social Studies 10
            
           Science 10
            
           Pre-Calculus 11
            
           French 10
            
           Physical Education 10 Outdoor Education
            
           Information Technology (11b) 10
            
           Planning 10
            
           ---

           I wish to tabulate the number of students taking a specific course.  For example, all students in French 10.

            

           So I set up a script to search for:

           ="

           French 10

           "

           This search differentiated "French 10" from "French 10 (enriched)" and from "IB French 10".  This worked fine under previous versions of Filemaker up to and including 11.  Under Filemaker 12 this fails.  

           Is there any way to accomplish this with Filemaker 12?

           Thanks in advance,

           Colin

            

            

        • 1. Re: Issue with Searches in text fields not working
          philmodjunk

               I just ran a quick test.

               I put:

               French 10
               French 11
               French 10a
               French 13

               in the text field of record1

               and

               French 11
               French 10a
               French 13

               If I enter find mode and specify:

               ="French 10"

               I find the first record but not the second.

               If I use:

               ="French 11"

               I find both records.

               So I'm not seeing any change in behavior here.

               From a design note, I would not list all theses values in a single field--especially if I need to count the number of records where a specific value was selected. I'd use a related table to record the list of selected courses so that I can more easily generate reports that count the course selections.

          • 2. Re: Issue with Searches in text fields not working
            ColinWoytowich

                 PhilModJunk, thanks for your response.  

                  

                 Firstly, this data comes into the system through a web form as a single field of text, we do not control what is returned.  So that is why it is stored this way.

                 Secondly, your test works with the limited cases you tried.  However, how does one differeniate between "IB French 11" and "French 11" as the search ="French 11" matches both.  This is the reason for the leading and terminating returns, where each line is a full course name.  So

                  

                 = "

                 French 11

                 "

                 should only match "French 11" and not match "IB French 11". Unfortunately under FMPro 12 this now fails.

                  

                 Thanks,

                 Coin

                  

                  

            • 3. Re: Issue with Searches in text fields not working
              philmodjunk
                   

                        Firstly, this data comes into the system through a web form as a single field of text, we do not control what is returned.

                   But that does not mean that you have to keep this data in this format. Once you have imported the data, a script can split this data into a set of related records quite easily.

                   Are you performing this find manually or in a script? Not sure if it's just my system or not (windows xp), but right now, I can't even enter that specific criteria for a manual find. Pressing the Return key is clicking the Perform Find button just like I would expect for pressing the Enter key...

                    

              • 4. Re: Issue with Searches in text fields not working
                ColinWoytowich

                     Hi PhilModJunk,

                     Again, thank you for your input.

                     When run interactively for a manual find, I write the search in a text editor and paste into Filemaker.  Works fine. When this is run from a Filemaker script the find fails.

                     I created custom ASCII and CHR functions with FMPro Advanced 12 so I could examine the raw values in the field.  For some reason Filemaker 12 shows ASCII 244 instead of 13 for a return character.  An additional null (ASCII 0) follows each 244.  Under Filemakers 9 through 11 the above script searches worked, however in Filemaker 12 I had to change my approach.  

                     The simplest solution was to recode the field to strip the 244 and nulls, remove duplicate returns, and generally clean up the field so that the regular searches would work.  Here is the script I used:

                • 5. Re: Issue with Searches in text fields not working
                  philmodjunk

                       Interesting, and that explains why I couldn't reproduce what you were doing.

                       This may be worth it for you to switch over to Report and Issue where you can report this change in behavior from FileMaker 11 to 12 as a possible bug to see what the tech support people say.

                       BUt I'd still give serious thought to exporting this list of data into a related table as you can then do quite a few nice, flexible reports based on this table that are difficult to impossible to do with such a list of values in a single field. And it would also provide a nice work around for the whole issue as you can then perform you find manually in a portal to this related table and not have to do any copy and paste from a text editor just to perform the find.

                  • 6. Re: Issue with Searches in text fields not working
                    ColinWoytowich

                         Hi PhilModJunk,

                         This is never accessed through the gui (except for debugging).  The goal is to simply get counts of the number of students who have requested specific courses through a web form. For example,

                         French 10 = 25

                         IB French 10 = 18

                         Math 10 = 32

                         etc...

                         While I understand what you are saying about breaking this field into a separate table, it does seem like a lot of unnecessay work and overhead to simply get a count of the number of records containing a specific string (which happens to be a course name) within a specific field.  Especially since this database was written more than 5 years ago, and has functioned without issue from Filemaker 9 until the upgrade from FMP 11 to 12.

                         Thanks,

                         Colin

                    • 7. Re: Issue with Searches in text fields not working
                      philmodjunk

                           Well it's your database but...

                           Actually, the scripting to count values selected from your existing field is many times more complex than a script that generates records in a related table. And once you have your table of related values, there are many simple report layouts possible that can be devised that can report on the values selected.

                           And this method will work in FileMaker 12.

                      • 8. Re: Issue with Searches in text fields not working
                        ColinWoytowich

                             Given the issues with Filemaker 12 and ASCII character handling, I do not see how the script to break this field into separate data records is going to much simpler than the script I used to clean up the data (posted above). It will also result in an additional table (and relationships to deal with the table) in an already large and complex system.  

                             As I said above, if we were doing anything more than what is essentially a simply cross-tabs for the data, then your comments make sense.  Given that this is simply used for planning and then purged, it makes little sense to do all this extra work

                             Thanks again for your feedback,

                             C

                              

                              

                        • 9. Re: Issue with Searches in text fields not working
                          philmodjunk

                               Yet if you don't do the extra work (and it's really not much extra work), you won't get this to work in FileMaker 12. The choice is up to you.

                          • 10. Re: Issue with Searches in text fields not working
                            ColinWoytowich

                                 Working fine per my notes above - posted April 17th.  

                                 Simply had to work around FMP 12's different behavior when parsing the data field.  

                                 [Which would have to be have been done to break the field into separate records anyway.]

                                 Thanks.

                                  

                            • 11. Re: Issue with Searches in text fields not working
                              philmodjunk

                                   The following script, adapted to your table and field names would be run once after importing records to generate the related table of records:

                                   Go to Record/Request/Page [First]
                                   Loop
                                      Set variable [$ID ; value: YourCurrentTable::PrimaryKey ]
                                      Go to Layout [ Layout for new table ]
                                      Loop
                                         Set variable [$K ; Value: $K + 1]
                                         Exit Loop If [ $K > ValueCount (YourCurrentTable::CourseList ) ]
                                         Set Variable [$Course ; GetValue ( YourCurrentTable::CourseList ; $K ) ]
                                         New Record/Request
                                         Set Field [NewTable::ForeignKey ; $ID ]
                                         Set Field [NewTable::Course ; $Course ]
                                      End Loop
                                      Go to Layout [original Layout ]
                                      Go to Record/Request/Page [Next ; exit after last ]
                                   End Loop

                                   Frankly we've spent more time and effort discussing this issue than it takes to write the script. And I suspect that this change to your database will make it possible to produce the report you need in much simpler fashion.

                              • 12. Re: Issue with Searches in text fields not working
                                quirkycrone

                                I could not find the script you used to strip out nulls etc. from your data. (per April 17th).  I would love to see it!

                                I'm having some mysteries with data that begins with an alpha not finding... Such as "A0009h" when "9009" finds perfectly. I can also find the records by doing a manual find on the layout.