Keep in mind that the "context" can make a huge difference here. I can only respond to the specific example you've given. It won't generalize to all situations:
There are two fairly simple ways to do this type of report:
In Sales LineItems, create a summary report with a sub summary part "sorted by" the PartID field.
Remove the body layout part and just use the sub summary parts.
Your report will have one row for each unqiue partID--provided at least one item of that ID was sold in the time interval specified in your find that you used to find the records for this report.
Build the report in a different table, such as your products table where you already have one record for each unique partID. With the right relationships, you can reference and compute totals from the lineItems for a specified range of dates to get your totals for each item in the products table.
Your report will have one row for each unique partID with a total of zero showing if that item was not sold for the specified date interval.
So there is no way to escape a script to produce the list?
I was hoping there was a function that could produce it.
If there isn't, with the mentioned example, then probably relate the saleslinesitems to product table with a global field on the product table for the initial date range and another for the final date range, and use a go to related records and can work the script from there...
I don't describe any script at all here.
To find a range of records, you can enter find mode, then enter 4/1/2011...4/30/2011 to find all records from the first of April to the last day of April. You can do this manually or in a script.
I found a Custom Function that can produce the list!!!!!!!!!!!
I'm one happy camper!
The link is http://www.briandunning.com/cf/1093
The function is called GetFoundSet.
Just in case the link breaks sometime, here is the function:
/* GetFoundSet ( FieldName )
1) Fieldname MUST be in quotes
2) For related fields, use entire field path (i.e. "TO::fieldname")
3) For local fields (current found set), use field name only (i.e. "fieldname")
Author: Howard Schlossberg, FM Professional Solutions
IsEmpty ( $$RecCount ) ; Let ([
$$RecCount = 1;
$$TotalCount = Case(PatternCount(fieldname; "::"); Count(Evaluate(fieldname)); Get(FoundCount));
result = GetNthRecord ( Evaluate(fieldname) ; $$RecCount )];
Result & "¶" & GetFoundSet(FieldName)) ;
$$RecCount >= $$TotalCount ; Let ( $$RecCount = "" ; "") ;
Let ( [
$$RecCount = $$RecCount + 1;
result = GetNthRecord ( Evaluate(fieldname) ; $$RecCount )
Result & "¶" & GetFoundSet(FieldName) )
As long as you need a list of values rather than a set of records (two different things here), it should do the trick.
And where it instructs you to put the field name in quotes, I'd use the function GetFieldName to pass the field name to the function. Not only does that avoid possible typos in entering the name of the field, but ensures that the expression will still evaluate correctly should I change the field's name sometime in the future.