I believe you'll need to back up and rethink here. If you can define a relationship to the records being summarized, you might be able to do this with a sum function wrapped in turn with an evaluate function.
I think, though, that if you can avoid this sticky detail: Most of the products come in three variations (c, b, s) There is a field for each, as well as a combined calculation that totals them (all) (four fields per product)
If you use a table of related records instead of dedicated fields, this level of indirection may no longer exist at all.
I think I am having a little trouble understanding what you mean by avoiding the sticky detail, and I think I may have misled you as to my relationship setup.
I have a table of 'Products' and each product has a unique code (ie pr_c_2030 = 20x30 colour print) used as a foreign key in a join table relationship with an orders table (line items join table). By necessity, a colour product (c) is seperate to a b&w (b) or a sepia (s) [obviously a photography buisness]
What they are trying to achieve is a simple selection of say a 20x30 product and display a total of (c), (b) and (s) products and a total of (all) within a given date range. It then has to find the 'Orders' in that range and calculate the four totals from the related 'Line Items' table. This is why I went down the dynamic field name path thinking I could use the field names in the calculations.
I have read in online help and the users guide about 'Evaluate' but am not sure how I can use it in this context, any further help would be appreciated
I think you have this structure to your relationships, though I may have a diferent table name here or there...
Here's the key detail I need to understand: "By necessity, a colour product (c) is seperate to a b&w (b) or a sepia (s)"
It sounds like you have a separate field for Colour, a separate field for B&W and a third field for Sepia. I don't see the need for this.
Define a single field, PhotoType and put either "Colour", "B&W", or "Sepia" in this field in Products to identify the photo type. For faster results in your report, you can define a matching field in LineItems that looks up this value--but I wouldn't do that unless I'm pulling together a large number of records in my report and am experiencing visible delays in getting the report to come up.
With such a field, you can get summary sub totals for all three categories with a single summary field. On a layout based on LineItems, you can place such a field inside a sub summary part when sorted by PhotoType to get a sub total for each category. You can perform Finds to limit the records in your report by date range and also by photo type if you need to.
There are also ways to see these totals from an invoices layout, but that requires more design work to pull off.
OK, I'm beginning to see what you mean. I actually have each one as a seperate record in the table of products. I'll change it to just have the one and add a field to 'Line Items' table to define type. I can make it selectable during the order process from a pop-up. Should work for me.
Thanks again PhilModJunk, i will get to work on that now.
You can keep separate records in Products for each type if they have unique ID's. Just use the Phototype field to group product or line item records by the values in this field.