Try this expression instead: Case ( Holdings Prio::Q3'09 > 0 ; Holdings Prio::Q3'09 ; 0 ) / 1000000 )
Note that ? is filemaker telling you that "I can't display the value". Sometimes this is due to the width of your field being too narrow or it needs a different number format that'll round the result to a smaller number of decimal places.
That didn't do it, same results. FWIW, I've also created an Error field which is equal to:
EvaluationError ( Evaluate ( mycalculationfield ) ) and for the records where Holdings::Q3'09 > 0, the value in this field is 0. For the records where there is no corresponding record in Holdings::Q3'09, the value is 102.
Well, it works for me. Just to be sure, I made the following test file to confirm.
I defined two tables, Parent, Child
In Parent I have two fields:
ID (serial number)
Calculation : Case ( Child::Value > 0 ; Child::Value ; 0 ) / 100
In Child I have two fields:
Parent::ID = ChildID
Here are the values I got:
<no child record> 0*
*Do not evaluate if all referenced fields are empty must be cleared otherwise Caclulation is empty.
At no point did I get a question mark.
See any differences between your design and mine?
I'd check to be sure the values you are getting from the related table are the expected values. Could there be more than one related record? Is Q3'09 a number field, summary field, calculation field?
Still not working. I added an extra field in my layout to just give me the Q3'09 value in the holdings table, and I am getting the right values there. The big difference I see with your test and my actual database is the nature of the relationship. You have a simple 1-1 relationship and I have a 3-table relationship where:
Holdings Prio::CompanyID = Firms::CompanyID
Holdings Prio::Ticker = Firms::ClientTickerKey
Firms::ClientTickeyKey = Market Data 3
However, that relationship seems to work since I am able to get results in all instances except where there is no record in the Holdings table:
Holding Value Calculation
<no record> ?
Perhaps its the way the Q3'09 field is managed in the Holdings table? It's indexed (All), validated only during data entry with no requirements/restrictions/auto-entries.
I don't think that's the issue. I've been reviewing the thread from top to bottom and decided to check out the EvaluationError function and the 102 error code.
Here's what I found in the help:
EvaluationError(calculationField) returns 102 (Field Missing) when a field [In the calculation expression] has been deleted or renamed.
That suggests that one of your fields in your calculation is misnamed rather than that there is no related record. It would also explain the ? you are seeing.
I'd check things over for that type of error carefully. If any of the terms in your calculation are themselves calculations, check them over for errors as well.
Okay I took the calculation apart and tested it bit by bit and it turns out the issue was (of course) in a completely different area than I was focusing on. It was the Lookup() function for the Market Data table. I took out the lookup() function and it worked fine. My final calculation ended up being:
Market Data 3::Last Price * Case ( Q3'09 > 0 ; Q3'09 ; 0 ) / 1000000
Thanks for all the help.