# Another Calculation based on another field's value problem

Question asked by JimBessette on Nov 22, 2013
Latest reply on Nov 22, 2013 by JimBessette

### Title

Another Calculation based on another field's value problem

### Post

Using Filemaker 11 Pro.  Sorry this got quite long to describe.

I have:
Tourer::pkTourerID-----<MemberTour::fkTourerID>-----Tour::pkTourID
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).

Tourer::pkTourerID---<MemberTour|Completed::fkTourerID

MemberTour|Completed::fkTourKD>--Tour2::pkTourid  and
MemberTour|Completed::ConstTourCompleted>--Tour2::TourCompleted

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.

My Calculations:
MemberTour::Points Unstored, from MemberTour, = Tour::Points
MemberTour::PointTotal  Summary = Total of Points

Trying to display on the Layout:
TourMember|Completed::PointTotal

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.
Jim
~