Calculation Help With Filtered Dates

[biggles1212]

I have one table with sales. The person who did the selling, the item and date.

 

While they are entering sales I want a summary of how much they have sold in the past month and past year.

How would I get a calculation to find the sales persons records and filter them by the date range and then add up the amount field?

 

[eos]

While they are entering sales I want a summary of how much they have sold in the past month and past year.

 

Basic idea is to create two calculation fields, type date: the first is Get ( CurrentDate ) - 30, the second is (you guessed it) Get ( CurrentDate ) - 365. Make sure to set both fields to unstored.

 

Create two relationships from wherever it is the data input happens to the Sales table, where cdMinus30 ≤ Sales::date, and cdMinus365 ≤ Sales::date, respectively.

 

You need to add to each relationship a predicate to only match the records of the current user. Since you don't say if there is a user table, or from which context “they” input the data, and whether the system knows who the currently logged in user is, I can't be more specific.

 

If you don't have a Users table and don't store the current user somewhere, you'd have to implement all of this as a self-relationship between two Sales TOs (since this would be the only place where a User name/ID resides that could do any matching), which mightn't be the best solution. 

 

Then create a summary field in Sales as sTotalAmount. On your data input layout, display this field two times as related field via the two relationships.

 

Note that you could do all of this with two unstored calc fields and ExecuteSQL() calculations that use the same logic as the relationship predicate(s) described above (but you don't need calculated match fields). If you're so inclined, you could even use two Web Viewers and fill them via ExecuteSQL(), which means no schema overhead at all (additional fields, TOs, relationships …)