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.
Very big help, thank you so much!
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
Set variable - Average: $MaxAdd divided by $TotalDays
*should empty the global fields.
- It may seem like potentially a lot of Finds, but Finds are very fast
- Run the script on the right TO but a blank layout
- There'll be plenty of alternative approaches, but hopefully this is clear and relatively simple.
SimpleTest.fmp12.zip 116.6 K