3 Replies Latest reply on Jul 20, 2017 10:17 PM by mardikennedy

    Finding, totaling, confusing me


      Part A. How would do you find the invoice with the greatest amount for every day of the year (many invoice for each day), sum the grand total them and average them by the number of days searched?

      Part B. I need to continuously add to this found set and be able to sync the found set using another layouts range search and average them by the number of days in the range search.


      Thanks for any HELP!

        • 1. Re: Finding, totaling, confusing me

          Re. Part A:

          Summarized Export can be of help.


          Assuming you have these fields in a table Invoice:

          date ( type =  Date )

          id ( type = ? )

          amount ( type = Number )


          Add a summary field to the Invoice table:

          _s_max_amount ( type Summary = Maximum of amount )


          Assuming you found the date range for your invoices:

          1. Sort by date ascending and amount descending

          2. Export Records as in shown in attached image. The key here is to include the summary field in the export order.


          In this example it will result in 3 records (one for each day) which could be imported into a temp/result table.

          As source of Part B you can calculate averages and grand totals in this temp/result table and create relationships to other tables.


          You can script this process entirely which would add new days to the temp/result list and also update existing dates (import by matching date field).Screen Shot 2017-07-20 at 4.49.56 PM.pngScreen Shot 2017-07-20 at 4.51.04 PM.png

          • 2. Re: Finding, totaling, confusing me

            Very big help, thank you so much!

            • 3. Re: Finding, totaling, confusing me

              Here is a Part A alternative.  (I don't really understand your Part B, but this Part A approach might clarify Part B requirements.)


              Pseudo-script - see attached file for real script

              dialogue - ask user to enter a Start Date and an End Date, capture these in globals

              Set variable - StartDate,

              Set variable - EndDate.

              Set variable - TotalDays:  End Date - Start Date, plus one, assuming inclusive dates* *I'd use the globals rather than the var's;  all var's are a text output, but FMP is forgiving so will this result will be 'understood'

              Set variable - Counter: equals one


              Then some Loops.

              Primary Loop:  Exit loop when $Counter equals $TotalDays plus one.


              Inner loop:  Find invoices for $StartDate*  *error check for no invoices on that day, eg if found set is greater than zero

              Other inner loop: Find the daily maximum - Set variable - Dailymax


              Set variable - $MaxAdd  - $Dailymax plus $MaxAdd (this will be empty the first time around)

              Set variable - $Counter plus one ie 'update' the variable

              Set variable - $StartDate plus one, ie 'update' the variable


              End Loop


              Set variable - Average:  $MaxAdd divided by $TotalDays


              *should empty the global fields.



              1. It may seem like potentially a lot of Finds, but Finds are very fast
              2. Run the script on the right TO but a blank layout
              3. There'll be plenty of alternative approaches, but hopefully this is clear and relatively simple.