3 Replies Latest reply on Mar 18, 2014 8:42 AM by philmodjunk

    Search/Scan Multiple Words in text field and summate

    AimenYounus

      Title

      Search/Scan Multiple Words in text field and summate

      Post

           Hello,

           I am facing a problem to sum up result of specific words in a Number field.But i am failing.

           I am using a command :

           If ( Month = "April"  and "May" and "June" ; Sum(US amount) ; " " )

           But still getting no result .Although the US amount field has values.Month is a text field having all months information

           please provide some help.

        • 1. Re: Search/Scan Multiple Words in text field and summate
          philmodjunk

               The above Boolean expression: Month = "April"  and "May" and "June"

               Will never be True.

               I think you want:

               Month = "April"  OR Month = "May" OR Month = "June" ;

               But even then, this calculation is unlikely to produce the result that you want. With the above change, if the text in Month is "April", "May" or "June", Sum (US Amount ) will return the value of US Amount from the current record. If Month has any other value, the field will be blank.

               I usually see this kind of If expression when the developer wants the sum of all values for a given field from all records where the Boolean expression is True (All records for the Months of April, May and June). But this expression will not return that result.

               Let me know if I am correct in what you want, and I'll list several methods that do work.

          • 2. Re: Search/Scan Multiple Words in text field and summate
            AimenYounus

                 It more like that i want to save record for a quarter.I did it by creating a new layout and then linking it with the parent database and then summating the values there.It worked wonders but it only has one issue.It isnt automated i.e for example fr the month of march ,april and may i get a new entry information which doesnt exists in the database.It would be missing there.For this problem,i am writing a script in the a button which would save any new entry there.

                  

            • 3. Re: Search/Scan Multiple Words in text field and summate
              philmodjunk

                   Please explain what you mean by this:

                   

                        It more like that i want to save record for a quarter.

                   What do you mean by "save"?

                   If you perform a find for all records in a quarter, a summary field can correctly report the total of US Amount for that set of records. You can even sort your records by quarter and produce a report that uses that same summary field to report a sub total for each quarter as well as a grand total of this field over all quarters.

                   This would be done with a Creating Filemaker Pro summary reports--Tutorial.