1 2 Previous Next 17 Replies Latest reply on Apr 18, 2011 11:23 AM by KristineGentry

    performing calculations on textual data in FileMaker pro

    KristineGentry

      Title

      performing calculations on textual data in FileMaker pro

      Post

      I have several columns of text that I want to run some calculations on and I don't know how to get started.

      I have 9 columns that have text in them.  I want to search the nine columns and get the frequency with which certain words appear.  The columns are populated with text from a values list, so I would like to see how many times each choice shows up across the 9 columns.  And, ideally get an average from that too.

      I'm new to FileMaker and got the database set up and the data input, but I can't figure out how to get started using FMP to analyze any of it.  Any guidance would be greatly appreciated. 

        • 1. Re: performing calculations on textual data in FileMaker pro
          philmodjunk

          Are the words in separate fields of separate records or do you you have large blocks of text where the word you are counting is included one or more times?

          How do you want to set this up on your layout?

          Do you want to see a report with a break down of every word or do you want to select one word and see the counts/averages for just that one word?

          • 2. Re: performing calculations on textual data in FileMaker pro
            KristineGentry

            I have 9 columns (9 fields) in about 400 separate records that all have the same values lists which are possible answers (about 20).  

            I would like to see a report with a break down of every possible answer.

            Thanks!!

            Kristine

            • 3. Re: performing calculations on textual data in FileMaker pro
              LaRetta_1

              And what happens if you want to add another question, Kristine?

              Those 9 fields should be related records instead.

              "I want to search the nine columns and get the frequency with which certain words appear. "

              If these were records, you could perform a search for the words and your found set would be the answer.  This is only ONE of the reasons that survey solutions should be structured with the questions as records. :^)

              • 4. Re: performing calculations on textual data in FileMaker pro
                philmodjunk

                I agree. Restructuring your database here can greatly simplify this task and many other tasks you may need to perform with this data.

                I did work out a scripted method for a technical writer that counts every instance of a specified word in a given text field, but I don't recommend that approach unless you simply have no alternative to doing so.

                Keep in mind that neither LaRetta nor I have anything close to a detailed understanding of what you have set up here and why you've made the design choices you did. Feel free to respond back with more information if you need to explore these concepts further.

                • 5. Re: performing calculations on textual data in FileMaker pro
                  KristineGentry

                  I don't understand the comment about adding another question.  Why would I add another question?  This is to analyze a survey that has been completed, so I won't be adding any other questions.

                  I don't understand how these would work as separate records or how to set that up.  I have a table with 400 records already.  Are you suggesting I create a separate table that just handles these 9 values?  I don't get what the records would be - the 9 fields are each a new record?  Or every choice in the 9 fields becomes a new record? 

                  Sorry, but can you please explain the suggestion to restructure the database.  I am not understanding what you mean.

                  Thanks!

                  Kristine

                  • 6. Re: performing calculations on textual data in FileMaker pro
                    philmodjunk

                    Please give an example of what you have here with actual text so that we can better understand what you are trying to do.

                    • 7. Re: performing calculations on textual data in FileMaker pro
                      KristineGentry

                      I have a question: "Describe the organization."  The full answer from the survey is automatically in a box showing up on my layout.  Then next to that box, we have 9 fields that have a values list with about 20 different options. We manually coded the description.  So if they said "focus on children" then that might be the option chose in the first field.  Then if they said "adotpion" that could have been chosen in the second field.  Some responses have only 1 or 2 values chosen so the remaing fields are empty.  Some have a longer response and there is a value chosen for each of the 9 fields.  Some said things in different orders, so "focus on children" might have been chosen in the 8th field.  I need to see how many times "focs on the children" shows up for all 400 records across those 9 fields. 

                      I will also be sorting the records to compare different segments of the respondents.  So, what did the employees say versus the clients when asked to describe the organization.

                      • 8. Re: performing calculations on textual data in FileMaker pro
                        philmodjunk

                        Were the responses strictly limited to the values in the value list?

                        That would suggest that a calculation such as:

                        ( field1 = "Focus on the children" ) or ( field2 = "focus on the children" ) or ( field3 = "focus on the children" ) //continue for all 9 fields

                        will flag each record where the respondant selected "focus on the children" in at least on of the 9 response field.

                        Make the above a calculation field that returns "number" as its return type and then you can define a Summary field that computes the Total of this new calclation field to produce a total of how may records have this response in at least one of the 9 fields.

                        If you ever plan to issue another survey, there are other ways to design this that make analyzing the responses far easier than this cumbersome approach.

                        • 9. Re: performing calculations on textual data in FileMaker pro
                          KristineGentry

                          Yes, the responses are limited to the values in the value list.

                          So, where do I put the calculation?  Is that a new field I add?  And then I write that formula in the field?  If I want to get the same information on the other choices in the values list, do I need to have a new field for every choice?

                          I do plan to issue many more surveys and will probably continue to use FileMaker Pro if I can figure it out.  I would greatly appreciate advice on how to design this more effectively in the future.

                          • 10. Re: performing calculations on textual data in FileMaker pro
                            philmodjunk

                            Yes, a calculation field and then also a summary field. Yes, you'll need these for every response you want to "count" in this way.

                            Please first search this forum using "survey" as your keyword. There are several excellent discussions of how you might structure your database to make this process much simpler. If you still have questions after that, then feel free to post them here for further discussion.

                            • 11. Re: performing calculations on textual data in FileMaker pro
                              KristineGentry

                              Also, I have 6 different questions set up this way with 20 or so possible responses.  The solution presented above means i have to add this calculation in 20 times for different questions.  That is going to take forever. 

                              Is there a way to restructure my database so this analysis isn't so time consuming?  It seems I might be better of doign this in excel or numbers rather than in FMP.

                              • 12. Re: performing calculations on textual data in FileMaker pro
                                philmodjunk

                                Well restructuring is what LaRetta and I both recommended earlier. Wink

                                Each question should be in a separate record. Where one field identifies the question and another field records a response. A third field records the ID of the respondant. You can have multiple records for the response to one question by one respondant.

                                There are ways to design such a table and then write a script that parses your existing data into this new table. Can't layout all the details for this as I don't yet know whether you use separate records for each question or one record with a set of fields for each question and response.

                                Then a summary report can group your reponses by question, then by response to produce a break down counting each response.

                                • 13. Re: performing calculations on textual data in FileMaker pro
                                  KristineGentry

                                  I realize you recommended restructuring, but I didn't know what you were talking about - hence the follow-up questions.

                                  I currently have each question as a separate field.  I basically have one huge table with the questions as separate fields, then I have added fields with the codes for the answers in the question fields.  I used the "research notes" databse to set this up, so on the layout view, each question is in a separate tab.

                                  So, I've searched surveys and see that I should have set this up as three separate tables.  It sounds like I need to create a new database set up wtih 3 tables. Is that correct? 

                                  Then I need to import the data from my current table into the appropriate tables in the new database, right?  Is that something I should be able to figure out with some assistance? 

                                  As I wrote initially, I am very new to FMP.  I'm also in a bit of a time crunch.  Does it make more sense to create a new database at this point and import the data so I can quickly analyze or try to do the calculation you listed previously?  I"m thinking new database, but I'm afraid I am going to get in over my head (or even further over my head than I am currently).

                                  • 14. Re: performing calculations on textual data in FileMaker pro
                                    philmodjunk

                                    There isn't a clear cut answer that I can give you here as I don't have a detailed understanding of your database and how many records are involved.

                                    You can certainly start with a new database or modify the existing one by defining the new tables inside your existing file. (You can still use Import Records to move the data either way.)

                                    Import Records has a lot of different options, so read up on it in Help and make frequent back up copies while learning how to use it.

                                    One thing you may need to add: If you do not have a field that identifies each respondant with a unique identifier such as a serial number field, you'll first need to add a serial number field to your existing table and then use Replace Field Contents to load this field with a serial number. This field will allow you to keep responses by the same person grouped together after dividing them up into the different tables and records should you need that.

                                    1 2 Previous Next