4 Replies Latest reply on Nov 22, 2013 1:15 PM by JimBessette

    Another Calculation based on another field's value problem

    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
           ~                                                                                                                                                            

        • 1. Re: Another Calculation based on another field's value problem
          philmodjunk

               I think you have:

               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.  
               A portal to  TourCompleted should only show records that have been completed.
                
               A summary field defined in tour will return the correct total. A sum calculation defined in MemberTour can sum or count records in Tour2, but then you must be careful to select MemberTour|Completed in the "context" drop down found at the top of the Specify Calculation dialog box.
          • 2. Re: Another Calculation based on another field's value problem
            JimBessette

                 I've got this working, but I'm still confused.  I do have the relationships you mention.   However, I thought that the MemberTour|Completed table would show ONLY tours that were completed, but it's actually the Tour2 table that show only the completed tours.  Does that make sense?  If so, any chance you could try to explain it to my feeble FM mind?

                 Thanks,
                 jim

            • 3. Re: Another Calculation based on another field's value problem
              philmodjunk

                   Welcome to "tunneling"--a concept unique to FileMaker relationships so far as I know.... "Tunneling" is where FileMaker "tunnels through" one related table occurrence to access records in a third table linked to the first related table occurrence.

                   The relationship from Tourer to MemberTour|Completed is

                   Tourer::pkTourerID = MemberTour|Completed::fkTourerID

                   So it matches to all records in the join table regardless of whether or not the tour is complete. It matches records just like you do from Tourer to MemberTour and thus shows the same records.

                   But the relationship from MemberTour|Completed to Tour 2 includes the extra pair of match fields that match only to records in Tours 2 that have the specified value in the completed field.

                   The relationship from tourer to memberTour|completed to Tour 2 thus matches only to completed tours.

                   The portal to MemberTour|Completed could be limited to just records linked to completed records in Tours 2 if you defined a portal filter that limited the records shown by the same criteria that you have set up with the added match pair linking MemberTour|Completed to Tours 2.

              • 4. Re: Another Calculation based on another field's value problem
                JimBessette

                     THANK YOU AGAIN!  Just so you know I don't just post here willy-nilly, I want to say that I've spent probably 8 full hours trying to figure this out.  I worked through your tutorial on summary reports, thinking that would help.  I tried and tried only to have to resort to asking here.  I probably have 15 worthless fields  in my DB now that I have to go clean up.  Funny that Rimsky-Korsakov's "Flight of the Bumblebee"  came on just about 10 minutes ago.  It always reminds me of "Mighty Mouse".  I'll just have to assume you know who/what Might Mouse was, and I thought "Phil is today's Mighty Mouse" for me.  Saved the day!

                     Jim