# Another Calculation based on another field's value problem

**JimBessette**Nov 22, 2013 10:59 AM

### 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

~