4 Replies Latest reply on Aug 1, 2012 10:58 AM by robyne

Calculation field - referencing a field with no data

Title

Calculation field - referencing a field with no data

Post

I am trying to figure out a way to force a calc field to work when it references a field with no data.

Example:  The calc field is called Balance Due = Amount due - Sum(all payments made)

If there are no payments yet in the system, then the field "Balance Due" remains blank.   What I'd like to show is the the Amount Due if there are no payments yet.   Note, I have removed the check for "do not evaluate of the field is empty".  I've also tried Balance Due = Amount Due - [0+ Sum(all payments made)] which I thought would trick the system but the value comes out to 0.

Any thoughts?

• 1. Re: Calculation field - referencing a field with no data

This should only happen if Amount due is also empty. The "do not evaluate" checkbox only affects the situation where all fields in the expression are empty. In your case that would be when ammount due is empty and if there are no payment records in the related payment table from which you are summing payments. In cases where one field is empty and another is not, it should evaluate as though the empty field contained a zero.

Any chance that Amount due is also empty?

That would be consistent with the 0 result you get when you include the + 0 term.

• 2. Re: Calculation field - referencing a field with no data

That is what I thought should happen but it doesn't work that way.  There is data in Amount due.  I am assuming the problem is that the Sum field is referencing fields that have no data because technically the records don't exist until a payment is entered. The moment I enter data, it all works perfectly.

• 3. Re: Calculation field - referencing a field with no data

Yet consider this screen shot from a demo file: You can download the file at: https://dl.dropbox.com/u/78737945/SumOfRelatedRecordsTest.fp7

The calculation expression that I used was: amount + Sum ( Child::ParentID )

And I did not clear the "do not evaluate..." check box.

• 4. Re: Calculation field - referencing a field with no data

ah!  thanks.  I knew I was missing the obvious.  My relationship was backwards.  Sweet!