Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Repeating Fields vs. MultiKeys

Ron Steriti

Recommended Posts

I've been using repeating fields in my database and am wondering if it's important to switch to multikeys, especially after reading a few posts about repeating fields.


Are repeating fields problematic (or just antequated)?

I've noticed problems when the number of repetitions don't match exactly.

Right now I have a portal that doesn't show one match it should. The relationship is between two repeating fields, one with 5 the other with 20 repetitions.


Would it be OK to calculate a multikey from the repeating fields and match the calculated text fields?


Is the best solution to get rid of all repeating fields and switch to multikeys?

(Repeating fields do display nicely, but that seems to be the only advantage).



Link to comment
Share on other sites

Oops ... the problem with matching repeating fields mentioned above was due to a mistake in relationships ... it works fine now.


I'm still wondering if it's best to switch over to multikeys.

Link to comment
Share on other sites

The main problems with repeating fields:


•*You have to guess in advance how many sockets' worth of data you're ever going to need. If you need more, you're stuck (at least unless you have access to Field Definitions and Layout Mode). And if you don't need that many, you have the unused/empty sockets sitting there taking up room nevertheless.


• They behave weirdly in Finds. If you're only using a single repfield in lieu of a multikey, you might not have anticipated searching for values in that field itself anyway, so this might not be applicable to your situation (and in fact many of these objections may not apply to your situation), but the most common use for repeating fields was akin to how we'd use a portal to a child-table nowadays, so you'd have several repeating fields arrayed to make a row-and-column grid. It would look like a set of rows of related records. Let's say the real record is a Client record and the repeating fields are for Office Visit Date, Invoiced Amt, Insurance Co, Insurance Paid, Remaining Due. But there's no way to do a search for all clients who use Insurance Company "AllState" who have a Ramining Due amt > 0. If it were a portal, you could do that search in the child table easily enough.


• You have to make awkward accomodations to deal with repeating fields in calculations. You can't just multiply repetition 9 of a repeating field by nonrepeating calc field X, you have to "Extend" the nonrepeating field. And if you ever want to multiply repetition 9 of one repeating field by repetition 2 of a different repeating field, it's a mess. Doable, but a mess.


• You can't delete a repetition like you'd delete a portal row. You can clear the values from all the fields in the repetition "row", but they still sit there like an empty socket. Which brings me to


• Moving all values up or down one repetition is a total pain in the ass. And if you ever anticipate adding a value to the TOP or deleting a "row" and moving all the values below it UP, you have to cope with that. You have to set each and every repfield's repetition's value to the GetRepetition(SameField, ), and it takes a separate script step for each repetition of each field in the "array". Ugh.


• If manual data entry is available to end users, repeating fields don't behave dependably in looping scripts. With relationships, you can either go to related record and loop through the found set or Go to Portal Row and loop through them until you hit the first empty one — either way, you know you've done them all when you're done. With repetitions, the end user can clear all the values out of repetition 3, making it a blank line. So in your loops, you don't know that there isn't valid data to be concerned with in repetion 4 just because repetion 3 of all relevant fields is empty. So you have to do a lot of additional & cumbersome testing to avoid either missing valid data or doing (or trying to do) things with empty data.


• They spread like cancer. Once you've got a critical mass of repeating fields in your solution, you find yourself needing a calc field that references existing fields, and one of those fields is a repfield so you make your calc field a repfield, and the next thing you know you've got repfields all over the place just because one component in a calculation was set up as a repfield.


• They can be conceptually difficult to visualize. Aside from repfields, individual tables consist of records which have fields which have values in a neatly descending hierarchy. Repeaters are odd. Ooh, we have repeating fields, so the fields are in the record but it's like the record contains multiple records itself, but not in another table, just...umm...kinda floating in midair. It's a record and a related set of records. It's an air freshener and a dessert topping. It can make it hard to stay clear on what you're doing. I know what to expect if I ask for Count(Related::::fieldname), but what is the meaning of Count(Related::Repeater)?


Weird little buggers. Never liked them. I've never yet found an occasion when I couldn't accomplish what I wanted to do without them, and every time I've used them I've ended up frustrated by their peculiarities and limitations.


They don't really play nicely with the rest of the FileMaker architecture. They haven't been vital since FileMaker 1 and you could avoid their use as long ago as FileMaker 2. In FileMaker 8 they are Telex machines in an Email world, Super-8 film rolls in a roomful of DVDs, 126-cartridge Instamatics in an 8-megapixel photographer's studio. Sure, they still work, but you'll spend a lot of time accomodating their antiquated presence.


As I said before, though, a lot of this may not be applicable if you're only using them to knit together two tables as you would use a multikey.

Link to comment
Share on other sites


  • Create New...

Important Information

Terms of Use