Invoice Totals through Portal

[josephkola]

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

[Ender]

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.

[josephkola]

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

[Ender]

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.