I really hope that this is easy ...
I see that you are using !=
try <> instead
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.
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.
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
Name, Run_Time, Row_Time, Bike_Time < --Fields
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.
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.