1 2 Previous Next 29 Replies Latest reply on Jun 9, 2011 2:52 AM by LaRetta_1

    Count field with specific text

    AnitaWoods

      Title

      Count field with specific text

      Post

      I am relatively new to FM, a casual user with a home-based business.  In essence, I simply want to count the number of times a field contains specific text. I have searched this forum and many places on the internet, but I still can't make it work. 

      I have two text fields, Outcome_h and Outcome_p, that may contain "SAB" or "TAB".  I can count the total number of records (Count (Outcome_h) + Count (Outcome_p), now how do I JUST count the number of times "SAB" or "TAB" occurs?  I would appreciate VERY much specific help on how to make this work.

        • 1. Re: Count field with specific text
          LaRetta_1

          Hi Anita,

          Let's pin down the rules:

          1) If a word 'contains 'sab' should it be included?  For instance, "The sable fox is ..."  or should it be only finding SAB as a separate word (and would it matter if it was capitalized or not?

          2) Do you want to count the record as '1' if it has SAB at all in it or do you want to count a single record as '2' if it has SAB in it twice?  Or do you want it to count as 1 if it has SAB in one field and TAB in another or should it count as two? 

          3) Would you ever want to count other words besides SAB or TAB?  Would you want to be able to type ANY word into a field and produce a count of how many result?

          Well, here is a way which hard-codes your choices of SAB and TAB and will accept sable and table and any words where these two are a PART of it.  If these fields only contain a single word or multiline with single words then this should work for you:  Create a calculation (result is number) with:

          PatternCount ( Outcome_h & " " & Outcome_p ; "SAB" ) + PatternCount ( Outcome_h & " " & Outcome_p ; "TAB" )

          This produces the count on each record.  To then find the total of all records in a found set, add a summary field which would be 'total of this calculation'.

          If you wish to expand a bit more let us know. :^)

          • 2. Re: Count field with specific text
            AnitaWoods

            The rules:

            1) If a word "contains 'sab' " it should NOT be included.  For instance, "sable" or "table" would be zero.  SAB or TAB would be the only letters in the field.  Capitalization does not matter.

            2) Count '1' if it the entire field says SAB or TAB, it would have nothing else other than one of those in the field in order to be counted.  

            3) I do not want to count any other words other than SAB or TAB.

            I put in the pattern count code you provided (THANK YOU for the specific code, it is so very appreciated), and it counted zero even though there are three SAB records in the portal for the related record.  What did I do wrong?  Shouldn't it have counted 3?

            • 3. Re: Count field with specific text
              LaRetta_1

              Ah.  It wasn't mentioned that the fields reside in a related table.  That maks a difference ...

              A parent record can only see the first related record so this will not work if evaluated from the parent.  Instead, create the calculation and summary field in the child (portal) table instead.  Then put the calculation inside your portal to check the results.  And place the summary field below the calculation OUTSIDE of the portal to display the portal's total count.

              Let me know if that then gives you the right counts.

              • 4. Re: Count field with specific text
                AnitaWoods

                Excellent LaRetta, it is working beautifully with one caveat.  It is still counting any variation that contains sab or tab anywhere in the field (like "sable" or "table" or xxtab or xxsab, etc).  This is precisely what I have:

                In Table H:
                PatternCount ( Outcome_h & " " ; "SAB" ) + PatternCount ( Outcome_h & " " ; "TAB" )

                In Table P:
                PatternCount ( Outcome_p & " " ; "SAB" ) + PatternCount ( Outcome_p & " " ; "TAB" )

                These totals are appearing in a portal in a layout with yet a third table as the primary table.  In the spirit of the educational value, what is the space in quotes for?  Thank you so much for your help, it is very appreciated. 

                • 5. Re: Count field with specific text
                  LaRetta_1

                  Again, you didn't mention this third table.  I thought Outcome_p and Outcome_h were fields in a single table (a related table) because you said they were text fields. 

                  You created this:  PatternCount ( Outcome_p & " " ; "SAB" ) + PatternCount ( Outcome_p & " " ; "TAB" )

                  "In the spirit of the educational value, what is the space in quotes for?"

                  That isn't what I suggested that you do. It was:

                  PatternCount ( Outcome_h & " " & Outcome_p ; "SAB" ) + PatternCount ( Outcome_h & " " & Outcome_p ; "TAB" )

                  The purpose of the space was to take text in two fields within the same table and concatenate them then look for patterns within that single concatenation but now that I know that the two 'fields' actually reside in two different tables, it won't work.

                  You also said that, "SAB or TAB would be the only letters in the field" which is why I didn't test for patterns otherwise.

                  1) How are these three tables related?  Ideally, could you post a link to your file here?
                  2) Why do you have two tables which appear to hold the same information? Outcome_h and Outcome_p which hold either SAB or TAB?
                  3) What type of data exists within these fields, i.e. is it sentences?  Is it checkbox (multiline single words)?  Please describe.
                  4) Why are you attempting to isolate SAB and TAB?  Understanding the context and purpose will help me understand your solution and it is quite possible that you need a structural tweak to get you there.

                  We need to back up and regroup so if you could answer the above four questions and give me a bit of background about your solution, it will really help.  It is very difficult to 'design' only based upon words in a post and I know it isn't easy for you to explain what you need because, if you knew what you needed, you probably wouldn't be posting, LOL.  We'll work through it, Anita, hang in there. :^)

                  • 6. Re: Count field with specific text

                    You might consider adding a calculated field that evaluates to a true: 1, true, etc. if your conditions are met. Then you only have to look for that value in a search or even create a portal filtered for the 1 value to show only matching records.

                    case (

                    patterncount ( field; " Tab " ) > 0 ; 1 ;

                    patterncount ( field; " Tab." ) > 0 ; 1 ;

                    etc.

                    0 )

                    • 7. Re: Count field with specific text
                      AnitaWoods

                      I appreciate your patience, LaRetta.  I am a midwife.  My life before midwifery was in data entry & some very beginner database design in MS Access.  As a midwife, now I am trying to make my charts digital and put them on an ipad in FM Go.  I know EMR already exists, but I am in a small, solo homebirth practice, so paying $3000 + $400/mo for this is not possible.  I've used databases for 12+ years, but all on MS Access, just going Mac in November.  I brought my Access db into Filemaker with just a table of contacts and a table of births and some other admin stuff (accounting & mileage), and found how cool it would be to be able to make it all portable on an ipad instead of lugging around a briefcase full of paper charts.  Now pulling everything I have previously done both on paper & in Access, and embellishing it in FM.

                      I have the following tables:
                      Accounting
                      Homebirths
                      Contacts
                      Previous births
                      Mileage
                      Prenatals
                      Pregnancies
                      Files

                      And the following layouts:
                      Accounting
                      Homebirths
                      Contacts
                      Mileage
                      Prenatal chart set
                      Pregnancy
                      Files
                      (Labor charts forthcoming in the future) 

                      1) How are these three tables related?  
                      All tables have a ContactID that is related to Contacts:pkContactID


                      2) Why do you have two tables which appear to hold the same information? Outcome_h and Outcome_p which hold either SAB or TAB?  See below.

                      3) What type of data exists within these fields, i.e. is it sentences?  Is it checkbox (multiline single words)?  Please describe.  See below.

                      4) Why are you attempting to isolate SAB and TAB?  Understanding the context and purpose will help me understand your solution and it is quite possible that you need a structural tweak to get you there.

                      I am trying to automate a woman's gravida, a medical term for how many times she has been pregnant, accompanied by para, which means how many living children she has given birth to.  "TAB" is a medical abbreviation for therapeutic abortion; "SAB" stands for spontaneous abortion (aka miscarriage).  Homebirths with me are counted in the table Homebirths.  Previous births a woman may have had with other providers are in Previous Births.  In the Prenatal Chart Set layout, using table Pregnancies (new primary record for each pregnancy she has with me), are two portals, one showing all her Previous Births with child's name, DOB, and vital stats of the birth, and a second portal showing Homebirths with me, baby's name, DOB, and vital stats.  I didn't used to track previous births in my db, just the ones with me, but I now have enough repeat clients that I kept having to write in their paper chart all the vital stats of their obstetric history (and for moms with 8-10 children, this becomes tedious), not to mention re-writing all her demographic data, so I'm trying to make it easier on both mom and me.  Hence, my original table of Homebirths with all vital stats of her births, and I then started a second table for Previous Births with just baby's name, DOB, and a couple other items.  I am using TAB and SAB in the baby's name field to indicate a pregnancy that ended without a live child. So with your generous code, it is counting accurately, but a baby named "Sabina" or "Tabitha" is getting counted as an SAB or TAB, not as a living child, so her para comes out wrong.  I apologize for the confusion, what I meant was, I want to count "SAB" or "TAB" ONLY when those are the only letters that appear in the field.  I am storing the results of this calculation field in the Contacts record for that mom, but it could easily be stored in the Pregnancies table.  I know I could just put a checkbox field or something to indicate a TAB or SAB for a particular birth record, or just manually type her gravida & para into a field, but I've already got hundreds of fields in this db, I'm trying to automate as much as possible instead of making yet another field I have to complete.

                      To summarize:

                      Gravida = Homebirths:Outcome_h (baby's name or SAB or TAB are the only words in this text field) + Previous_Births:Outcome_p (same) + 1 (adding one for this pregnancy she is currently seeing me for, gives total # of pregnancies)

                      Para = Gravida - 1 (subtracting back out this pregnancy as it has not yet produced a living child) - Number of abortions or miscarriages she has had (TAB or SAB in Outcome).

                      So, a sample record for a mom in her seventh pregnancy might look like this:

                      Previous births:
                      Amanda
                      John
                      TAB

                      Homebirths:
                      Melissa
                      SAB
                      David 

                      Thus, she is a Gravida 7, Para 4.  I would like to automate this, thereby avoiding adding yet another task.

                      • 8. Re: Count field with specific text
                        LaRetta_1

                        Hi Anita,

                        You have provided a comprehensive description and it helps a great deal.  Normally I would suggest that SAB and TAB be different fields but I see your reasoning.  I would also say that Homebirths and Previous_births should be in the same table but again, I see your reasoning.  Just keep in mind that, having two 'like' sets of data (even if Previous_Births lacks much of the data as Homebirths), it will mean that reporting will be more difficult.  FileMaker does not  create left outer or star joins.  But for now, let's work within your current need.

                        In both Homebirths create a calculation (for this example called AB) with: Outcome_h = "SAB" or Outcome_h = "TAB"  (result of calculation is number).  Repeat with Previous_Births.  Remove the summary fields.  Then, similar to Jack's suggestion, in main table ...

                        Gravita would be:  Count ( Homebirths::Outcome_h) + Count ( Previous_Births::Outcome_p ) + 1
                        Para would be:  Gravita - 1 - Sum ( Homebirths::AB) - Sum ( Previous_Births::AB )

                        Let me know how this works for you. :^)

                        • 9. Re: Count field with specific text

                          Yep, one problem with a list is that you get focused in on the questors point of view: for instance, how can I locate a word in 550?

                          Well, it seems that word plays an integral part of the solution so I would now suggest after having time to digest this and a few sandwhiches, why not use either a checkbox or a radio button should it be one or the other not one or both or none.

                          Thus you would not have all of the search problems and you could even use that field has a report break:

                          Sab

                           ssss

                           sss

                          Rab

                             xxx

                             xxx

                          In fact it might be worth your time to stop asking questions about how you want to design it and ask how should it be designed. On one list a man came in asking how to deal with 8000 fields for his report on apartment conditions. After a bit I gave him the correct answer and how it could be designed with just two tables, some list fields, etc. Instead he was bombarded with irrelevant answers to his own questions which were founded on a lack of knowledge of Filemaker. Two weeks later he was still getting posts about custom functions, etc. None of which were needed. Just a nice solid outline in the table maker.

                          I would suggest using Microsoft Words outline function to help clarify your concepts. You can easily move words and ideas around when you see they fit better elsewhere and you will discover a two or three level hierarch that serves your purpose and can form three linked files: Parent, child and grandchild. Then there are extraneous topics that don't deserve a table of their own such as color: red, white, blue, green, yellow but do work nicely in a value list.

                          So, the first step is to separate Real Topics from Lists...

                          • 10. Re: Count field with specific text

                            Your comment on inserting SAB in a name field rather than having a checkbox field for this value points out how easy it is to clutter up your database and end up with convoluted scripts when a simple checkbox would do the job. And if you displayed it as part of a list view, it would prove far more valuable than mixing it in with text somewhere.

                            • 11. Re: Count field with specific text
                              LaRetta_1

                              Well, Jack, I have years of extensive relational theory and comprehensive FileMaker training and background.  I took the time to create her structure and consider many alternatives.  I never respond lightly.  And no, it is not ideal and yes, I told her so.  I also mentioned the reporting issue.  But she has many tables and this is someone who clearly understands the power of relational and is relationally sound in all other areas, even using appropriate keys.

                              I think you underestimate Anita.  And I also think you look down your nose on others on various forums when you have no idea of their background or expertise (or even who they are for that matter).  It is one thing to provide a solution to Anita ... I encourage it from everyone.  It is another to imply that, other than you, those responding are incompetent.

                              Unfulfilled births, not given a true name, are 'named' with this defintion.  I have no real issues with it. Either way Anita would still need to count the checkboxes just as she will now sum the calculation.  The two tables (which should be one) is another thing.  It *is* best to use one table.  It does not hurt to have information in most fields (if Homebirth) but have only a few fields completed if previous birth.  But down the road, Anita can merge the two tables if she chooses.  And she can always use virtual table for reporting - very simple indeed.

                              But that was not her question today.  I gave her an answer which, if it were a structure I inherited and couldn't change right away, I would not hesitate to implement.

                              • 12. Re: Count field with specific text
                                AnitaWoods

                                While I am far from incompetent, I do feel somewhat like a kindergartener who wandered into a university debate class on molecular physics, and I'm making every effort to keep up with the volley. I appreciate molecular physicists so very much for opening their brains for me and spending their precious time on my query, all for free.  (Handing a virtual plate of cookies to you both.)  Smile  I could sit here and defend the reasoning for each and every fashion of my db, why I did what where, or I could focus my energies on bringing it to the place where it is exciting and easy to use.  I have known Homebirths and Previous Births (and potentially the Pregnancies table) could be in one table, and I have attempted to avoid backing up three months and starting over.  Alas I have now sighed heavily, swore gently, and resigned myself that long-term, the solution is one table for all three.  I am spending my weekend combining three tables into one (dare I even approach the question of an automated way to do that in FM Pro, versus exporting each table into Numbers, combining columns & records, and importing back as one table).  Grace, mercy, patience and humility are appreciated.  Now pass the quad mocha latte...

                                • 13. Re: Count field with specific text

                                  The difference between infer and imply is quite important. One can infer that someone implied something that did not incur.

                                  However, the simplicity of using a checkbox and letting the user determine what is being sought is infinitely easier than deriving a caclulation to accurately find it in text. Since this is a user created db, that user should be able to make the determination easily and accurately.

                                  ... and using my 20+ years of db experience in other applications I would add this solution:

                                  3) I do not want to count any other words other than SAB or TAB.

                                  This is quite easily solved if you using ascii values, something part of my experience in other dbs but lacking, until recently, as part of Filemaker. Filemaker sadly does not distiguish between SAB and sab in searches, finds or patterncounts. I could easily find just SAB in other applications using a calculated find.

                                  To guarantee that the word in question matches SAB instead of sab you would compare it to the ascii numbers for S and A and B. Filemaker's search capability is limited compared to other applications but you can work around that.

                                  An ASCII chart will show that the upper case values are 83, 65 and 66. The lower case values are 115, 97, 98. Thus you could accurately determine with the sab or SAB met the requirements. You would have to grab the three characters, easy for any expert, and then get their ascii numbers to see if they match but this technique would produce a more reliable match. Since Filemaker only recently added CODE as a function, many Filemaker Developers may be unaware of its full potential or have overlooked it.

                                  Naturally the little problem of SABLE still exists and is sabotauging the problem and disabling an easy solution....  :)

                                  • 14. Re: Count field with specific text
                                    aammondd

                                    Anita

                                    You could use the scripting to do some of the "Numbers" work in filemaker itself.

                                    You could export/import the tables into a new temp Filemaker database where you could work with them if you dont want to do this work in the solution itself.

                                     

                                     

                                     

                                    1 2 Previous Next