Can you retrieve a Repetition Number?

[sulmax99]

Hey everyone, this is my first post, but I’m in real need of help. I am creating a club directory that has two tables; a table for all individual members and another related table for family groups.

Each record in the family groups table will list all members of a single household in a “family members” field with 15 repeating fields (if they have more than 12 kids and a set of grandparents then I guess I’m in trouble). The relational link is one to many and keyed on a field in each table named “head of household”. When a members individual record is created the user will enter the name of the head of household, and if a family record with that head of household already exist then the individuals first name is set in the next available repetition of the “family member” field. The repetition number is then recorded by lookup in a FamRep field back in the Individuals table.

I want to use the repetition number in the FamRep field to identify and clear the name from the correct repetition of the “Family Members” field if the individuals record is ever deleted. Once a name has been cleared a looped script will move the remaining

Family names up in the repeated field eliminating the blank repetition and thus changing the repetition number for that family member. This is necessary because I am also using a recursion routine in a custom function to display the names in a linear fashion under a family photo.

My problem is that once the blank space is eliminated I have been unable to update the FamRep field for the remaining family members in their respective individual records so that any subsequent deletions can not be properly handled. I know you can return the contents of a repeated field according to the repetition number, but is the inverse possible? Can you retrieve the repetition number according to the contents?

[Delta Tango]

Hi, why don't you use related records instead of repetitions?

It is much easier.

Create a table called families and a table called people. Then link them together by family name or the contact or something.

Then you can have as many individuals in a family as you like.

[sulmax99]

Actually, I just came to the same conclusion after talking with a co-worker. What led me astray was the fact that I had already created the "Individuals" table when I received a request for the "family" table to be added, so I unwittingly trapped myself into going from the many to the one.

However, I am still curious to know if anyone has ever found a way to retrieve a repetition number if you supply the contents of the repeated field. Thanks for the help.

[comment]

You should definitely NOT use repeating fields for this. To answer your theoretical question - yes, it's possible in a number of ways, but it cannot be done reliably, because there might be more than one repetition with the same content. You avoid this when working with RECORDS by assigning a unique serial ID to each record.

[sulmax99]

Well noted. It seems to becomes more apparent that the use of repeated fields should be limited to special situations such as invoice line items and similar.

Thanks again -KS

[comment]

The use of repeating fields should be indeed limited to situations where they are appropriate (just like any other tool - nothing "special" about that). Invoice line items is a prime example of a situation where using repeating fields is NOT appropriate.

Edited October 3, 2007 by Guest
Added my exception to the term "special"

[David Jondreau]

the use of repeated fields should be limited to special situations such as invoice line items and similar.

Line items in an invoice are not a special situation and you would use a related table for that as well.