10 Replies Latest reply on Nov 17, 2016 12:46 AM by IanRenwick

    Using FileMaker for Text Analysis

    sccardais

      I'm wondering if the experts in this forum have an opinion about using FMP for analysis of free form text.

       

      We receive thousands of Support Tickets every month. Each ticket includes a block of text - average 50 words each -  completed by our customers that describes their question, issue, problem, etc.

       

      This is a gold mine of very important information but because it is free form, it is extremely difficult to identify trends and group common issues for prioritization.

       

      We could run the text through a Word Cloud service but we want to use phrases rather than individual words.

       

      I'm wondering if we could create a table of Search Phrases in FMP and run a report that scored "hits" and ranked them according to frequency.

       

      I think we'd want to hold about 1 year's worth of data in the table holding the Free Form text - less than 100,000 records. The table holding search phrases would probably hold no more than 200 - 300 search terms or phrases.

       

      I have no idea if this is too much for FileMaker and I'm hoping someone on this forum can tell if this is realistic.

       

      Thank you.

        • 1. Re: Using FileMaker for Text Analysis
          Mike_Mitchell

          No, I wouldn't think this would be too much for FileMaker. You'd probably need to script it to collect the data (since a relational match likely won't work), but you could loop through the phrases, perform a Find, and then store the found count in a separate record in a table. (I suggest you create a table specifically for the report, with the phrase ID, the date / time the report was run, and the record count. That way, you can have a history.)

           

          It shouldn't really take long at all. You're only running, at most, 300 searches (one for each phrase) and then storing the data. Once the free form text field is indexed, FileMaker should return the results quite quickly.

           

          HTH

           

          Mike

          • 2. Re: Using FileMaker for Text Analysis
            sccardais

            Thank you, Mike.

             

            I think it will help a lot.

             

            This is encouraging.

             

            Scott

            • 3. Re: Using FileMaker for Text Analysis
              golife

              If you want to go into more details of analysis, I suggest using LiveCode for such kind of task exporting to a file or database and reimporting results. It is the best tool when it comes to processing chunks of text and it is free: wwe.livecode.com.

              • 4. Re: Using FileMaker for Text Analysis
                sccardais

                golife:

                 

                Thanks for the tip but after visiting their site, it's not obvious to me that their software can be used to analyze and categorize free form text.


                Did I go to the right site?  www.livecode.com

                 

                Thanks again for the suggestion.

                • 5. Re: Using FileMaker for Text Analysis
                  golife

                  Yes. I use this for 20 years. Especially for text analysis I did. Of course, it needs a bit of understanding the syntax which is English-like. The learning curve should be not too high. Of course, it depends on your specific requirements. Let me know and I can show to you demonstrating on some text input.

                   

                  - List of words (chars, number of chars, phrases, sentences, paragraphs, articles, many articles)

                  - Count of words (and other chunks of text)

                  - Words appearing next to similar words

                  - Words appearing in paragraphs or other blocks of text

                  - Build lists of text or store in arrays

                  - Do any comparison of words or strings

                  - Go into statistical analysis

                   

                  I would think there is anything that you could not do with text especially in LiveCode.

                   

                  Example: To get all words from a text (simplified version) building a tab-return delimited list

                   

                  Code snippet of fully functional handler using 1 input field, and 1 output field

                   

                  on mouseUp     // You pressed a button which you placed on the "layout"

                    put field "Textinput" into myText

                       repeat for each word myWord in myText

                          if myWord is not in myOutList and myWord is not in (a, the, ...) then // Use an exception list as well

                            put myWord & return after myOutList  // Build a simple list of words

                          end if

                       end repeat

                    delete last char of myOutList // delete the last char which is a return character

                  put myOutList into field "Wordlist"

                  end mouseUp

                   

                  This should be understandable logic. It is also pretty fast using this construct.

                   

                  Using a few more lines of script, you could add a counter and the occurrences of my myWord and add it to each line of myOut, or whatever you want to do.

                   

                  You could also use Regular Expressions in LiveCode - but that needs some time to learn well. For me it is too cryptic and would need much time to do right.

                   

                  Then export the result to a text file (UTF8) and import to Filemaker for further processing, or save it to a database (SQLite, MySQL, ...) or export as XML ... or even publish on a website (LiveCode now has a new way of creating HTML/JavaScript just using LiveCode).

                   

                  For me it is the best tool for parsing any text or any list, extract whatever I need, and then possibly use it in Filemaker (if not directly in LiveCode) preparing it for import.

                  • 6. Re: Using FileMaker for Text Analysis
                    beverly

                    Thank you for this, golfie. I haven't used livecode, but it seems like overkill for just text analysis.

                     

                    I don't think I can do all this with BBEdit (but it hasAppleScript & shell command support, so perhaps it can and I just didn't need to do all these). And I've used BBEdit since there has been a bbe (20+ years). I also do a lot of web work, so perhaps that's why this is my Text Editor of choice.

                     

                    But I'm open to new things. Can you describe more about this:

                     

                    - Go into statistical analysis

                     

                    beverly

                    • 7. Re: Using FileMaker for Text Analysis
                      golife

                      Yes, it really depends on what you are going to do. I am even using MS Word for similar tasks). Or a text editor. Or FileMaker. Or LiveCode. And sometimes there is no way around having something done usine C# or Assembler which I am outsourcing ).

                       

                      It is a bit off-topic now, but LiveCode script ( derived from SuperCard and originally from HyperCard) developed into its own Integrated Application Development framework. And now there is a new language branch called LiveCode Builder which uses a text editor writing the code, and basically, it can do anything you want. But learning curve is higher and it is just sprouting.

                       

                      Show me any language with this ease of use and integrated GUI builder allowing to do certain tasks in a fraction of time. If it is more than just counting words, or replacing them, I always go for LiveCode to do such tasks which require more than what all other tools are also doing. Some people even replaced it for FileMaker as it gives them so much more freedom to do what they want and the community version costs nothing. And there are migration tools around to migrate FileMaker apps to LiveCode.

                       

                      To not get me wrong: I am using FileMaker as it still gives a framework to more quickly deploy at this current moment in a business environment. LiveCode would still require substantially more development effort in the first place. And I am also waiting for the version 8 to be really stable and out on the market with a lot of widget support and it's own additional LiveCode Builder language.

                       

                      But text analysis... uhhh. I see hundred possibilities to be developed and deployed easily just now.

                      • 8. Re: Using FileMaker for Text Analysis
                        beverly

                        Ok, do you have any examples ?

                        • 9. Re: Using FileMaker for Text Analysis
                          golife

                          Not currently at hand, or I would have to search old files, or ask the community. But give me a task ). We will if that is solvable quickly. 

                          • 10. Re: Using FileMaker for Text Analysis
                            IanRenwick

                            Hi golife,

                            I just posted a discussion about the LiveCodeForFM plugin that was announced today.

                            I suspect you may also have received a similar announcement in an email.

                             

                            LiveCode for FileMaker