Repeating Field question - validation

[Ryan Prager]

I have a need to validate repeating fields. I know that in a regular field you can validate by unique value. Well, I want to do the same with repeating fields except I want them to validate with the other repeating fields within that record.

Here's the example...

I have a repeating field used for items shipped or returned. To avoid human error, I would like the repeating field to notice if I've already scanned in an item into that repeating field already.

[Ryan Prager]

Anybody have any thoughts on this?

I don't want a repeating field to have duplicate values.

For example...

I have a repeating field for laptops in a shipment. I don't want to accidentally have two of the same asset tag in that field. Is there a way to validate repeating fields maybe using a list or similar?

[Søren Dyhr]

Yes Boyce and Codd, when defining relational theory ...stop violating 1NF at least!!

Expecting someone comming forward with some serious workarounds is far fetched since filemaker have been relational ever since 1995, back then had a lot of people workarounds up their sleeves ...but today does the majority consider repeaters too much trouble compared to what's possible with relations.

I'm not saying that you won't nail it but please consider your endevour as a solo expedition. At least a bad habit you can corrupt new users with, by persuing it in public!!

--sd

[Søren Dyhr]

I don't want to accidentally have two of the same asset tag in that field

I realize that the way I put things might sound a little snotty...

There isn't as far as I know a way to put up such a validation, but instead could a script and a CF working together to avoid duplicates in a repeating field by removing them, I took it as a challenge to make it as fast as possible ...althoug I'm pretty sure the avoidance of dupe much easier is accomplished by letting each entry be an individual record a relation away.

I have therefore made you two templates, one that via script zaps dupes, and another that shows how much easier things are accomplished via genuine relations. Via some spit and polish could you make the tab-order make the cursor jump from cell to cell accordingly, what's missing is an argument to stay with repeaters - not that I really prove anything here, but rather lacks the imagination to make repeaters behave as nice and easy as cut up portals.

One thing you might examine further is what David Kachel calls Tiered tables:

Let’s say that for each contact, you need to store a few phone numbers, some mailing addresses and a couple of email addresses. The traditional approach is to create three related tables; one for phones, one for mailing addresses and one for email addresses. But you know you will never have a great many contacts, and it seems a waste to have three tables for so little related information.

You create one multi-purpose table and build three relationships (TO’s) to it from your contacts table. All of the fields you would have put into three separate related tables are put into one multi-purpose table. The multi-purpose table has three foreign key fields and three relationships from the contacts table, one each for phones, etc.

So even if a related table, only has the keyfield and the datafield, do I in my humble opinion think it's the way to break it out, with both cut ups and tiering!

--sd

Dedupes.zip

[comment]

There isn't as far as I know a way to put up such a validation

Hmmm... Not that I would recommend this, but as a challenge...

UniqueRepeats.fp7.zip

[Søren Dyhr]

Blast! I thought of it, but I think I confused myself dealing with empty repeaters along the line....

It works, for what it's worth ...but why didn't you chime in way before, you have all the expirience with booking systems utilizing repeaters required here ...remembering an old template of yours from our Compuserve days.

It's not for me stubling near to use repeaters ...can you enlighten us on their virtues and caveats here Michael???

--sd

[comment]

why didn't you chime in way before

I believe I post enough as it is. I hope Ryan will forgive me for saying that I don't find this thread particularly interesting (except the challenge your statement created).

The real answer here is that repeating fields are not well suited for the purpose stated in the original post. Unless this is being used as a device for rapid data entry. But then there will eventually be a script to create the actual records, and the script can eliminate the duplicates very easily: if the creating relationship is based on ParentID AND the current repetition's value, it is impossible to create duplicates.

[Søren Dyhr]

I believe I post enough as it is.

No no no, what gave you that impression?? You are always a great resource to read messages from, never a slippery footing!

It must be a wife, kids or a dog being too neglected here?

--sd

[comment]

By all means, feel free to step on my other foot as well...