I sounds like you are using the wrong relationship operator. Seeing the first related record repeated is one sign of this. Why is the relationship cartesian? Maybe I am not understanding the situation you are explaining.
CAL is just a calendar where you can choose a goto_day and it will start the calendar from the previous Sunday. cal_dates is a repeating field with 7 repetitions.
It is related to the LEG table through a Cartesian product relationship.
LEG is related to MSN table through an id_number (auto-generated).
What are trying to accomplish with the cartesian relationship? Why is this not "=" ?
It was an "X" rather than "=" because I can have multiple legs on different days related to single msn_id_number.
cal_dates is a repeating field (7 reps) and I have portals assigned to each day that displays LEG table info. In a separate repeating field beneath the portals, I'd like to have a gantt-like chart that shows which msn_mission_number (through the original calculation above) is associated with the legs from a start date (msn_dep_date) to an end date (msn_return_date). These dates are not necessarily contiguous, but I'd like it to fill in gantt-like.
It is starting to sound like you might need a join table between CAL and LEG rather than a Cartesian relationship.
Multiple days for the same leg and multiple legs on the same day sounds like a join table.
If you're in doubt what related set a given relationship will return, put a portal using the related TO onto the layout, or use the data Viewer with
List ( TOInQuestion::primaryKey )
and see what (and how many) you get.
I created a portal and the correct results were there. Then, just to check, I created 7 separate portals side-by-side, each filtering with the original calculation but for one day only in the week. And it works.
As inelegant as this seems, I'm just going to run with it.
Thanks to both you and bigtom.
I think the goal you would look for is the correct records showing up in the portal without filtering.
If you use the "x" join you will get all records in the portal and need to filter.
I changed the relationship from a cartesian to a >= for the dates. I get the same result with the repeating field (only the first created record displays) and with the portals workaround (they work correctly).
Ok, I got it figured out. To show it all on the same repeating record, you have to do a repeating summary field of the original repeating field (cal_aircraft_activity) with repetitions calculated individually as a list. The first created record was the only one that showed up because each record after that was in it's own separate related record.
erolst, your suggestion of using a portal to check my results was what led me to this. Thanks.