Calc relationship summary

[goostree]

I'm having problems with one of my calculations.

What I'm trying to do is this:

Database 1 records individual transactions. I have a relationship from Database 2 to Database 1, and that relationship is called "NDirectMutualFunds." I have a calculation field on Database 2 that uses the following calculation:

"Case[NDirectMutualFunds::PayDate >= DateRange1 and NDirectMutualFunds::PayDate <= DateRange2 , Sum[NDirectMutualFunds::AmtExpected],0)]"

NOTE: The brackets in that calculation represent parentheses, but for some reason I couldn't use the parentheses in this post.

My goal is for the calculation -on Database 2- to summarize a particular field -AmtExpected- which resides on Database 1. The trick is that each record on Database 1 contains a date field -PayDate-, and I only want the calculation to summarize those records whose PayDate field falls in a date range which I set. You can see I tried to express that in the case statement at the beginning of the calculation. FYI: DateRange1 and DateRange2 in the calculation are the fields I use to enter the date range.

The calc is so close to working, but it has one little glitch. It only evaluates the PayDate field on the first related record. If, on the first record, the Paydate field is within the date range, then all the records are summarized regardless of whether or not the PayDate fields on the other records are within the date range. If, on the first record, the Paydate field is not within the date range, then the calc returns "0."

How do I get it to evaluate the Paydate field on all the records instead of just the first one?

Please let me know if this doesn't make sense. It's a little difficult to explain.

[danjacoby]

In DB1, create a calc field that returns the AmtExpected if the PayDate falls within the correct range (BTW, you can use the "If" statement rather than the "Case" statement, since it's only one "if"). Call this field AmtReallyExpected (or whatever).

In DB2, create a calc field:

Sum(AmtReallyExpected).

That oughta do it.

[Fitch]

Put your calc in Database 1 instead:

AmtByDate=

Case(PayDate DateRange1 and PayDate DateRange2 , AmtExpected)

Then in Database 2, all you need is Sum(NDirectMutualFunds::AmtByDate)

Of course, if your DateRange fields have to be in DB2, it's a little trickier. Are they global fields? If so, you can create a constant relationship to use them in the calc above.

[goostree]

Thanks! That did it. I can stop mumbling to myself now.

[danjacoby]

quote:

---

Originally posted by Fitch:

Put your calc in Database 1 instead

---

I thought I said that.

"In DB1, create a calc field..."

[Fitch]

"I thought I said that."

Looks like our posts crossed while I was pondering the problem. We can't all think as fast as you, Dan.

[Niklas]

I used the technique above for a similar task. Now I would like to do it in a relationship (to show a portal). Is there any way of doing that? When I try it says something about the calc field not being indexable..