It's usually not a good idea to introduce dummy records into your business data tables. You're potentially falsifying results (as you have just noticed), and need to clean up later.
But having said that, I should mention the concept of “non business data tables”, aka Virtual Lists (search for it).
Depending on your (visual) requirements, a better approach may be to use either an additional calc field that you only populate (or display, or both) if necessary, or – if you have FM14 – use one of several methods that gives you a “layout calculation object” per record (dummy global field & placeholder calc; single-segment button bar & label calc).
theValue < statedAverage
GetNthRecord ( theValue ; Get ( RecordNumber ) - 1 ) > statedAverage ;
for a trailing display, or the reverse for a leading one, would give you a result only for the record directly above or below the threshold.
(Speaking of which: is that average “stated”, as you … well, stated – or is it calculated?)
For output, you can makes the body part taller, put the result “object” there and set it to slide; this gives you an additional header section. In Browse, though, it won't look that good …
Thanks! The average is a calculation field depending on what categories are being selected. If I'd like to create a dynamic list for that, is there a more general formula to use rather than stating the exact average?
If I'd like to create a dynamic list for that, is there a more general formula to use rather than stating the exact average?
Let me answer with “most likely” (since you can code just about everything in FM), but since I have no idea what exactly you mean, that's all I can say … Care to elaborate?
I'm making sorting by "year" "duration" "genre", etc. The records are the same, but say I want to sort by 2011 the average of that would be different from the sort from 2012. Or if I want to do a sort by hour longs vs 30 minutes the average would be different too. Eventually I'd like to sort by combined fields (eg, year and duration). Sorry I don't know how to be too specific/technical with this, I'm quite new to Filemaker.
The records are the same, but say I want to sort by 2011 the average of that would be different from the sort from 2012.
If you mean that you want to sort and group your records by year, then compare a record's value against the average of its group, look into GetSummary().
It returns a summary field's value for a sorted group; you can compare that value against individual record values; e.g.
year ... value
2012 ... 5
2011 ... 11
2011 ... 6
2012 ... 3
2011 ... 4
2012 ... 7
summary field sAvg (Average of: value): 6
Sorted by year:
I ended up using a Case calculation for "Above Avg" "Below Avg" and "Avg" to separate the categories instead.