In the portal to "sales line item" in my Client database I can view all the sales records for that Client. I can see the total sales figure as well.

Any idea how I can break the total sales into Year to Date Sales, Last Year Sales, Two Years Ago etc...

I haven't been able to figure out the calculation or if it shouldbe within the Portal or not.

Thanks,

JK

For this type of report, where you need sub totals by this or that, a List with sub-summary parts is better. Sub-summary reports can only be viewed in Preview Mode or printed, but it's the easiest way to do this type of thing.

that thought did occur to me. I use that method with end of year reports. I am still hoping to do it right at the client record "data entry" layout, browse mode. I am a firm believer that FM Pro can do anything, even those things I haven't figured out yet.

You think I can make a field that would calculate it? That is where my head has been thinking.

JK

You could use a calc for each break, but it might be kind of slow calculating through the relationship. Try storing the sub-totals in a few static number fields (or even creating a sub-totals file.) Then use scripts to update the sub-totals periodically or when a user hits an update button.

If you're planning on keeping the previous years' records in the related file, you'll probably want to filter the relationship so it only shows a selected year or user-defined date range. Adding a selected year criteria to the relationship is pretty easy: A global Year field on one side and a Year(Date) calc on the other, put these into a concatonated key on their respective side of the relationship and use the calcs for the relationship keys:

SalesPerson Fields:

SalesPersonID (number)

gYear (global, number)

SalesPerson_Year_Calc (calculation, text result) = SalesPersonID & " " & gYear

LineItems Fields:

SalesPersonID (number, lookup from Invoice::SalesPerson)

InvoiceDate (Date, lookup from Invoice::Date)

SalesPerson_Year_Match (calculation, text result) = SalesPersonID & " " & Year(InvoiceDate)

To show records for a date range is more complex. The Smart Ranges technique is the most efficient, though fairly difficult to understand:

http://www.onegasoft.com/tools/smartranges/

I have almost this exact setup. I use Smart Ranges for showing subsets of records in a range, which are then sub-totaled and stored in a separate file.