A *very* limited defense of repeating fields

[ESpringer]

Repeating fields are much maligned, mostly for good reasons... So I thought I'd post a vindication of two potentially useful contexts for them (beyond storing global graphics, and other uses I've seen experts recommend). This is partly to see whether you all can set me right before I become incorrigible.

(1) Data-entry and display convenience for multiple items from value list: I have an academic feedback database in which there is freeform text for prose comments, but also a repeating value field for "boilerplate" comments: explanations of standard grammar problems, observations about style or structure problems, etc. Students who get these comments recognize them as standard issues that I will expect them to avoid in the future. I can also get a rough and ready gauge of a student's writing skills improvement by glancing at how many repetitions are occupied in paper 2 vs. paper 1, etc., and seeing whether there are repeats (each starts with a distinctive two-letter code, easy to scan).

Why repeating fields here? Well, because (1) I use a relational value list for these sentence-long comments (prefaced by the distinctive code), and repeated fields let me zip along with a pair of keystrokes, <return>, another pair, <return>, etc. So, this works much better than entering comments into one long text field with returns.

Why not portal rows creating related records? because (2a) I want a record of exactly what text went on a student's report two months ago, although it may not perfectly match anything in my value list file now; I may have edited one or the other. For example, I have a comment, "You've confused homophones here. Notice you cannot trust spell-checkers to distinguish...", and for a particular student I might then hit <enter> and rattle off: "morning vs mourning". Furthermore, I sometimes want to adjust existing items in the value list file, without disturbing old records' contents. In other words, the value list serves to facilitate rapid data-entry, but I don't really want to track a "relationship" to a permanent comment.

Someone will point out (I now realize) that I don't have to treat the related file as a join file: it could just be a dangling child-file which shows all the comment-strings (frozen in time and all) for any given feedback record. OK... but when there's not more calculation going on, and no further dimensions to the data, it seems the only motivation for setting up a new file, a new relation, and a new portal would be to stick to a no-repeated-fields policy. No?

But another problem with doing this via portal is that (2b) I'd like to store the comment data *in* this file, so that I can use a portal to create comparative overviews of this assignment's list as compared to others (say, this assignment and feedback occupies the left half of the screen; the right half has portal glimpses into this student's other work, and/or other students' related work). Comparing one record's portal to another, though, makes for big layout limitations: list view only. Also, I can display the repeating-fields horizontally in these self-join portals, which brings me to...

(2) Horizontal display: I have attendance data that I *really* want to be able to survey and edit in horizontal mode: rows for students, dates in "columns". Why use repeating fields? Because they stack horizontally so nicely (it's crucial that for a semester's attendance, the number is fixed and manageable; it wouldn't work so well with indefinite data management). If I do it with a related file, those records are each per-student-per-day, which means I can't get a matrix layout there. I realize I could do a calculation in the students file which takes the attendance info and renders it in tab-delimited text "rows", but then I can't edit while browsing it.

I'm closer to ambivalent about the second case than the first. A related attendance file does much better with problems like "How do I add comments about lateness or excuses for-this-student-on-this-day without messing up calculations that tally attendance data?" If I had a separate file with records per-student-per-day I could also separately track lots of stuff like class participation comments or notes about miscellaneous communications on a given day.

Nothing urgent here, but happy to see others debate this and/or other controversial applications of this "obsolete" feature...

[DanBrill]

These seem like reasonable uses. I think the big thing is to go in with your eyes open (which you've clearly done in these cases) rather than thinking that RFs will do more than they are designed to do. They aren't a replacement for understanding related tables.

[Ugo DI LUCA]

Hi,

Repeating fields are surely a controversial topic.

When I moved to relationships, I sweared I'd let repeating fields behind, but then found some terrific other features they were offering.

Though I won't rely personnaly on them for data entry, I'm using a lot of them for storage and temporary fields (or global fields) in some parsing scripts and preferences settings.

They happen to be far more quicker than any relationship then, if this can be an issue.

ESpringer said:

Horizontal display: I have attendance data that I *really* want to be able to survey and edit in horizontal mode: rows for students, dates in "columns". Why use repeating fields? Because they stack horizontally so nicely (it's crucial that for a semester's attendance, the number is fixed and manageable; it wouldn't work so well with indefinite data management). If I do it with a related file, those records are each per-student-per-day, which means I can't get a matrix layout there. I realize I could do a calculation in the students file which takes the attendance info and renders it in tab-delimited text "rows", but then I can't edit while browsing it.

Seems to me you have it reversed because Students would still be listed as rows in a portal.

But anyway, you can have related data presented horizontally if you need to and have it slide when you want, and still can enter any value in it.

Check the "Dynamic Portal" (second attachment) I've posted last month in the Sample section. It does both a Horizontal slide in a Classic Portal and a real Horizontal Portal.

Thanks for this poll, which I'm quite sure will get us some good answers and solutions involving repeating fields.

[-Queue-]

Another understated use (and I believe Ugo would concur on this one ) for repeating fields is in recursive calculations and nested calculations that require a prior result before solving the next step. This can lead to a sort of GetField( ) on crack, a very powerful pseudo-scripting process. Check out various sample files from DJ, such as his demonstration of addition and cross-product of text fields, for some clever examples of their usage.

[ESpringer]

I'll check out your file. Thanks.

Ugo DI LUCA said:

Seems to me you have it reversed because Students would still be listed as rows in a portal.

But anyway, you can have related data presented horizontally if you need to and have it slide when you want, and still can enter any value in it.

What I meant was: I can use list or table view in students' file, and access attendance data across... *or* I can have portal rows standing for students, with the dates going across: a virtual column for each date. (Did I get something backward? Oh well... ) Ah, but this brings in the other convenience advantage: when I go to the Courses database (say), from which there's a portal to all enrolled students, I *can* display attendance data there together with grade or whatever in a mini-table-like view by formatting the repeat horizontally... This access wouldn't be possible with related file (without lots of shuffling), since I surely can't put an attendance portal *within* the student's portal *on* the course overview...

[johnmci]

Another limited defense...

I've got a report card solution for my school. It's fairly detailed, with each grade having 40 or so descriptors to mark. And the card is sent home 4 times a year.

So each descriptor is a field with 4 repetitions. Why repetitions?

1. A related file is out. The teachers like the report card in ONE file

2. Since it's in one file, one field with 4 reps is easier to maintain than 4 fields. With 40 descriptors, that's 40 fields instead of 160.

Other than this, I don't uses repetitions at all (other than storing graphics and such).

John McInerney

[The Shadow]

Just to stir up trouble:

Notice in FileMaker 7 there are several new features for repeating fields:

- A repeating field on a layout can display any range of the reps, not just always starting from 1.

- Summaries can summarize by repetition, if this option is chosen, the summary field becomes a repeating field also.

- joins occur accross all repetitions for a relationship.

Anyone see any other new repeating features?

[bruceR]

And reps are individually addressable by applescript.

I fully agree that reps can have great value, but people need to first understand basic relational concepts, and then look carefully at when reps make sense. A blind reflexive reaction against them isn't appropriate. I think it would be great to do a series of articles or a book or a Devcon session on effective and appropriate use of repeats.

[bruceR]

"joins occur accross all repetitions for a relationship."

Can you explain that? And is it any different than it was before?

[The Shadow]

BruceR said:

"joins occur accross all repetitions for a relationship."

Can you explain that? And is it any different than it was before?

Try out creating a text[2] (text field with two repetitions), and join this to a text field ("otherField") in another table with an = match:

In FM7: If *either* text[1] or text[2] matches the contents of otherField, the row matches.

In FM6: Only text[1] is joined to otherField, text[2] is ignored.

I can imagine using this as a convienent place to store an exploded multi-key for the data in text[1], as a simple example. Since the 2nd rep doesn't need to be shown on the layout, a user wouldn't notice it and I wouldn't need a new field for a multi-key; but I guess then it couldn't be a calculation, so maybe not.

Another repeating change: A rotated repetition field rotates "in-place" for editing when you click in. This gives a much better experience, IMO. FM6 rotates all repeats at once in the same situation, which gives the editing a "disconnected" feeling from the original field.

FileMaker Version: Dev 7

Platform: Mac OS X Panther

[bruceR]

The Shadow said:

Try out creating a text[2] (text field with two repetitions), and join this to a text field ("otherField") in another table with an = match:

In FM7: If *either* text[1] or text[2] matches the contents of otherField, the row matches.

In FM6: Only text[1] is joined to otherField, text[2] is ignored.

Regarding FM6, it's true that on the "left" side, the only match is on text[1]. But on the right side, if the right side is a repeat, the match can be anywhere. text[1] to text[n].

[The Shadow]

BruceR said:

Regarding FM6, it's true that on the "left" side, the only match is on text[1]. But on the right side, if the right side is a repeat, the match can be anywhere. text[1] to text[n].

Yes, that makes a lot of sense, as the relationships in FM7 are bi-directional, so now the match occurs across reps both ways - otherwise you would get different matches depending on which side was on the left.

FileMaker Version: Dev 7

Platform: Mac OS X Panther

[Michael Myett]

What if you wanted to search for records which contained two or more different items in the same portal.

In Find Mode data entry is only available in the first row, so you can only search for one item.

Creating multiple find requests, only finds all the records that contain any of the items, not the records which contain all items.

By setting up a duplicate search layout using a repeating field in the portal for the search field this can be accomplished.

Just one more reason to place some value on repeating fields, but certainly not as a replacement for relationships.