6 Replies Latest reply on Sep 22, 2016 1:39 PM by philmodjunk

    Calculations with a loop?

    velvetmantis

      I have a database with two tables, X and Y

       

      Table X

      Name, Event Type A Time, Event Type B Time, Event Type C Time < --Fields

       

      Table Y

      Name, Event Type, Time, Join (Of Name and Event Type), Dupes <--Fields

      Joe, Event A, 4:30, JoeEvent A,

      Joe, Event B, 4:45, JoeEvent B,

      Emily, Event A, 5:01, EmilyEvent A,

      Fred, Event A, 2:12, FredEvent A,

      Fred, Event B,4:12, FredEvent B,

      Fred, Event C, 9:06, FredEvent C,

      Fred, Event C, 10:01, FredEvent C, X

       

      I have a simply script that marks the dupes based on the Join field (which is Name & Event Type).

       

      The problem I am trying to solve is to auto populate the Table X Event Type A, Event Type B and Event Type C fields through auto-entry calculation.

       

      For instance, For Event A, I would have the following calculation:  If(Table Y::Event Type="A";Table Y::Time,"")

       

      For Event B, I would have the following calculation:  If(Table Y::Event Type="B" and Dupes != "X";Table Y::Time,"")

       

      Lastly, For Event C, I would have the following calculation:  If(Table Y::Event Type="C" and Dupes !="X";Table Y::Time,"")

       

      Obviously it doesn't work. I think it is because the records at the beginning of Table Y are all Event A's - which results in Table X getting the Event Type A Time populated for every record. However, for Event Type B's and C's, it doesn't;t pull the right value. I'm guessing this is because it sees the the related Name in Table Y for the calculation, but the first record listed in an Event Type A and it just stops...

       

      It should noted that the Dupes comes into play because Event Types B and C can occur more than once - I'm just looking for a timestamp for the first one - hence the Dupes field (which I use a script for). Event Type A never has a dupe.

       

      Any help you could provide would be great. I am a neophyte at best, but this kind of issue comes into play for me a lot.  Thanks again!

      VM

        • 1. Re: Calculations with a loop?
          David Moyer

          Hi,

          I really hope that this is easy ...

          I see that you are using !=

          try <> instead

          • 2. Re: Calculations with a loop?
            velvetmantis

            I'm actually using the not equal sign (equal with the line through it), but couldn't find it on my keyboard to post. Unfortunately, that's not the issue I don't think.

            VM

            • 4. Re: Calculations with a loop?
              philmodjunk

              I see potential problems in your data model.

               

              If(Table Y::Event Type="A";Table Y::Time,"")

              This calculation can only reference data from a single record in table y. It will reference the "first related record"--which is either the first related record to be created or the first one in a sort order specified for the relationship. It won't/can't reference any other records.

               

              So what relationship are you using to link these two tables?

               

              And a side note: using letters of the alphabet for table or table occurrence names is very poor database design and actually makes it harder for people reading your post to understand what it is that you are trying to accomplish. Names that actually describe the purpose for the table make it easier.

              • 5. Re: Calculations with a loop?
                velvetmantis

                Phil,

                Thanks for the input - I'm definitely a newbie with Filemaker, or any database programming for that matter. As for the relationship link, its tied to the "Name" field in each table.

                 

                Hopefully this is a better example

                 

                Results Table

                Name, Run_Time, Row_Time, Bike_Time < --Fields

                 

                Event Table

                Name, Event Type, Time, Join (Of Name and Event Type), Dupes <--Fields

                 

                Joe, Run, 4:30, JoeRun,

                Joe, Row, 4:45, JoeRow,

                Emily, Run, 5:01, EmilyRun,

                Fred, Run, 2:12, FredRun,

                Fred, Row,4:12, FredRow,

                Fred, Bike, 9:06, FredBike,

                Fred, Bike, 10:01, FredBike, X  <----the only dupe in this sample - marked with an X via a script

                 

                 

                The problem I am trying to solve is to auto populate the Results Run_Time, Row_Time and Bike_Time fields through auto-entry calculation.

                 

                For instance, For Run_Time, I would have the following calculation:  If(Event::Event Type="Run";Event::Time,"")

                 

                For Row_Time, I would have the following calculation:  If(Event::Event Type="Row";Event::Time,"")

                 

                Lastly, For Bike_Time, I would have the following calculation:  If(Event::Event Type="Bike";Event::Time,"")

                 

                Hopefully this sheds light on the problem I'm trying to solve. Thanks again for looking at it.

                • 6. Re: Calculations with a loop?
                  philmodjunk

                  As I said in my last post, you have data model problems.

                   

                  Matching only by name, the calculations that you are using in the record in Results with the name "Joe" can only access data in this record:

                  Joe, Run, 4:30, JoeRun,

                   

                  The second record with "Joe" as it's name cannot be referenced by your calculations and thus any data in it does not appear in your results record.

                   

                  A simpler approach might be to not use the results table at all. You can set up a report based on the events table to list all events, participants and their times. I assume, however, that you want a display with one row per participant with their events and times listed in columns--a typical "cross tab" type report.

                   

                  What you can do is discard your calculation fields and put one row filtered portals on your Results layout. A filter expression can then filter for a specific event name such as "run" or "bike".

                   

                  Events::EventType = "Run" would limit the records from Events to just those of eventType "run". Your relationship limits this further to just events for a specific participant of that event type.

                   

                  You should also consider not using names to link up participants as this can cause problems. An auto-entered ID from your results table is much safer.