If...then...not getting expected results

[Corey H]

Hello!

I have a simple billing database linked to a Client DB. I'm trying to get a monthly summary of charges out of the billing DB that I can store somewhere, i.e., a calculation field called 'January Charges'. Here's what I use:

If (Date >= 1/1/05 and Date <= 1/31/05, Sum (Charge), 0)

Instead of getting a sum of charges for January, I get all charges for that client totalled up...January and afterwards. I must be missing something here. Can someone tell me where I'm going wrong?

THANKS!

Corey

[Ender]

Hi Corey,

The Sum() function will total all of the related field's values. So what you need here, is a relationship that limits the related values to the specified date range.

Since it's most likely you'd want to be able to change the date range over time to report on different periods, I'd recommend global date fields to hold the start and end dates of the range:

gStart (date, global storage)

gEnd (date, global storage)

With these fields created in the Client table, you can now set up an additional table occurence to the Billing table via this relationship:

Client <=> Billing 2

Client::gStart <= Billing 2::Date

AND Client gEnd >= Billing 2::Date

Alternatively this could be done by month name or number, but the date range relationship will be more flexible, allowing ranges for any period of time.

Now you can use this second table occurence to give you the Billing records in the range specified by the globals. You could see these records in a portal, or use aggregate functions like sum()

Total (calculation, number result) = Sum ( Billing 2::Charge )

You might consider adding a Bill or Invoice table, where each record is one Client's bill for one month. In this case you would use regular Date fields without global storage, located in the Bill table and related to Billing by the same kind of relationship. The advantage of this is having a more static record of the Bills over time.

[electrobase]

And for what it's worth.....

anytime you are using a sum(anothertable::fieldname ) calculation you can always create a summary field of the field being totalled, and then that remote file summary field will reflect only the values dictated by the current found set (this makes it incredibly useful. The relationship points made above still apply....

Have fun!