Another Calculation based on another field's value problem
Using Filemaker 11 Pro. Sorry this got quite long to describe.
all simple "=" relationships there.
Tour::TourCompleted is a Y/N field
Tour::Points is a Number field
Though I know I'd prefer not to (trying to be all relational I expect would
be better), I copy those fields via a calculation like MT::P = Tour::P and
MT::TC = T::TC to MemberTour::Points and MemberTour::TourCompleted.
MemberTour::PointTotal is a Summary field = Total of Points
Layout using Tourer
Portal using MemberTour shows each Tour the Tourer has been on and the associatied points.
Simple field of MemberTour::PointTotal gives a total of all points. So far, so good.
Following PhilModJunk's suggestions on this thread:
I can do the 2nd method (using a portal) just fine to show only those tours that are completed (=Y). Problem is I want to use that field in more calculations on this form; addition and subtraction of points.
Not sure if it'll really help me or not to be able to use it in the calculations I need to do
on this layout, but for the life of me, I can't get the 2nd method to work.
I've tried all kind of things, but what I think I should have is this:
New TO of MemberTour called MemberTourCompleted and added ConstTourCompleted calc value "Y" returned as Text.
New TO of Tour called (for now) Tour2 (FM required it when I made the 2nd relationship shown below).
Given that, I would think that MemberTour|Completed would only show me tours that have been
completed. But when I try to show the summary field (NOT in a portal) I can't seem to get the
right # of Points. It is still showing me total points for all tours, even those where
the TourCompleted = N.
MemberTour::Points Unstored, from MemberTour, = Tour::Points
MemberTour::PointTotal Summary = Total of Points
Trying to display on the Layout:
This shows me a total for all tours, not just completed tours. I've created a 2nd portal from MemberTour|Completed (already had MemberTour) for my own comparison to see what's going on. What is odd is that the Name of Tour field for the TourCompleted = N does not show up, but the Points field does. I don't get it.
I'm hoping that if I can get this to work, I can then use that field to add and subtract points based on other fields on the layout, to get a point balance.
Any help would be greatly appreciated.