AnsweredAssumed Answered

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
     ~                                                                                                                                                            

Outcomes