Wierd Calculation Problem

February 8, 200718 yr

This should be simple - but it's mystifying me.

field:t_DeptChair? If record is for a Department Chair, the user clicks off "Yes" in this field (Yes is the only choice -- it is left blank if not valid).

field:t_calc_Active - this calculation looks at the end date of the record and returns "yes" if that end date is AFTER today's date, meaning that Title is currently active. If the end date has already passed today, the calculation returns a "no" value.

field: t_calc_CurrentDeptChair = If ( t_DeptChair? = "Yes" and t_calcActive = "Yes"; "Yes"; "No" )

On the Titles layout, the calculation works fine. However, when I place that field in another, related layout, sometimes it works, sometimes not. For example:

Bob's Department Chair title started on 7/1/2006 and ends 6/30/2007. Active = Yes, DeptChair = Yes, and in layout:Title, calc_CurrentDeptChair = Yes. In the related layout:People, if I place the field in a portal, it reads "Yes" correctly, but if I move it out of the portal (which I must), using the same relationship, it reads "No". This is happening without rhyme or reason - some folks that have calc_CurrentDeptChair = Yes show as "yes" on the related layout, some as "no".

Any ideas what's happening here? Thanks!

February 8, 200718 yr

Once you move it out of portal and put it on the layout itself, it is only referencing the first related record as a default. Maybe the relationship should have a default descending sort of the EndDate.

February 8, 200718 yr

Author

Well - the way titles are listed in the portal are based on other criteria, so I can't change the sort order. However, I did create a new relationship, sorted by end date, and am using that relationship as the origin of this field on the layout. Still ain't working.

The funny this is that this works fine in FM6. It's just a calculation field, so why would the result change when it's pulled to another layout?

Still stumped.