Repeating Fields and Calc Result Repeating

[LDLayton]

Good afternoon Forum! First let me say I've read through the posts and you guys/gals are amazing. I thought I knew a thing or 2 about databases... there's so much I didn't know I didn't know.

Okay, here's the short version of my question: I have 6 repeating fields in one database: 1 for a Rotation Code; the other 5 are Topics in varying orders. In a related database, I have a calculation field which evaluates the Rotation Code and selects one of the 5 Topic fields depending on the code selected. The result of the calculation only returns the 1st value and I want them all (there are 5 max depending on the rotation. HELP!

If you'd prefer a longer/more-detailed explanation... here it is. I have 2 related databases. The Attendee database (MM) contains all the attendees and their relevant information. The other database (Project Manager) contains only 1 record and was designed to customize each MM database (each time a new event is scheduled, the PM database contains the specific info for that event; i.e., event location, dates, hotel rooms, etc.)

I want to create a Rotation Schedule for (example) 100 people. There are 5 meetings which will allow 20 attendees each. The meetings are numbered 1 thru 5; each group of 20 is labeled A thru E.

My idea was to use 5 repeating fields in the PM database, each with 5 values in the order that particular group would rotate through. So Group A's rotation would be Topic 1, 2, 3, 4, 5. Group B's rotation would be Topic 2, 3, 4, 5, 1. The MM database has a Rotation Code field to assign each attendee a code, A-E (using a value list defined by the field Rotation Code in the PM database). My calculation simply evaluated the rotation code and then returned the corresponding Topics. The problem is it only gives me the 1st repetition in the list. The calculation is set to have repeating values. The only thing I can see is I have to tell it which repetition to check in the (PM) Rot Code field against the Rot Code assigned in MM. If I just tell it to use the Topics1 field, it works fine. (Example of calculation i "planned" to use: If Rotation Code = (GetRepetition(PM::RotCode,1), PM::Topics1, "").

It would seem I was wrong when I thought I'd mastered Repeating fields. HELP!

Linda

[-Queue-]

If (Rotation Code = GetRepetition(PM::RotCode,1), PM::Topics1, "") is the same as Case (Rotation Code = PM::RotCode, PM::Topics1), since GetRepetition(field, 1) is the same as field, i.e. it defaults to grabbing the first repetition. To expand upon that calc, you'll have to use the Extend function (for non-repeating fields involved in the calc) and add '+ 0' for numbers and '& ""' for text results, due to a bug in GetRepetition( ). An example would be Case( Extend(Rotation Code) = GetRepetition( PM::RotCode, 2 ) & "", Extend(PM::Topics2) ).

However, I would suggest you review your structure and see whether a related file would be more useful for your purposes than repeating fields, which are usually limited in usage if you plan to further manipulate the results, sort them, find on particular ones, etc.

[LDLayton]

I considered yet another related file. We're already using any where from 2 to 5 per project and at the moment I can't publish them because of client security concerns (we're working on that) so if we've got 2 projects running simultaneously, it can get hairy with more multiples. Since each MM database is only used for about 90 days and the reports I need are pre-determined, there's no contemplated issue with sorting. It's really just 5 topics in 5 different orders. Seemed simple enough.

I'll run try your suggestion, if I understand it correctly. Thanks for the suggestion.