3 Replies Latest reply on Mar 12, 2013 11:51 AM by philmodjunk

    summary script by date

    HelderSantos_1

      Title

      summary script by date

      Post

      I want to build a script that does the following: sum all values where date is equal and less than a certain date.
           ex: on 4 presents the sum total of this the beginning of the sales until the 4th
           choosing another date must do the same

        • 1. Re: summary script by date
          philmodjunk

               If you specify the 4th, do you want all records in your table prior to this date, all records from the first of the year to this date or all records from the first of the month to this date?

               How do you want the resulting sum displayed?

               What version of FileMaker are you using?

               Your script can pull up a summary report based on this data that includes the total based on those records.

               Your script could display the total in a custom dialog.

               Or a calculation field could update on your layout without any script involved to show the total.

          • 2. Re: summary script by date
            HelderSantos_1

                 1º: I want see all records from the first of the month to this date.

                  2º: i want to display total in numbers.

                 3º: I am using FM12 pro and Adv

                 i just want to get this information, it can be by scripting or by calculation field...

                 the easiest way will be great.

            • 3. Re: summary script by date
              philmodjunk
                   

                        2º: i want to display total in numbers.

                   that's a little vague. The devil is in the details and there are several different ways to present the information. The best approach can depend on the design of the layout and your data model.

                   Let's assume your table is called Numbers with a field in it named Values and another field named Date. You wan't to select a day and see the total of Values for all records dated from the first of the same month to the selected day.

                   Define another field, SelectedDay of type date. You may want to use global storage for this field.

                   You can then define a calculation field:

                   ExecuteSQL ( "SELECT Sum ( \"Value\" ) FROM \"Numbers\" WHERE \"Date\" <= ? AND \"Date\" >= ?  "; "" ; "" ; SelectDay ; SelectDay - Day ( SelectDay ) + 1 )

                   To return the total.

                   You can also set up a self join relationship in Manage | Database | Relationships and then a calculation field using the Sum function can also return the same total without using ExecutSQL.