Calculations with a loop?

Discussion created by velvetmantis on Sep 22, 2016
Latest reply on Sep 22, 2016 by philmodjunk

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!