velvetmantis

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!

VM

Outcomes