Sorting Repeating Fields in FM7

[JavaVoodoo]

I have a small database that creates and stores my companies work orders. Often times the work may run over multiple days, so I used repeating fields for the details, like date, work to be performed, location, etc.

Now I'd like to be able to do reports showing what's pending, grouped by date. The problem is FM7's default sort checks only the first instance of a repeating field. Is there a way around this?

Thanks for the help!!

forum.pdf

[bruceR]

This exactly the problem with repeating fields. You can't subselect, sort, etc. You need to go drop the repeating fields and go to a real relational design. You will have much more reporting flexibility.

[JavaVoodoo]

I was afraid that was going to be the answer. Thanks though!

[bruceR]

Not sure why you should be afraid. If you haven't got a handle yet on basic relationship concepts, well, it's about time. This is what databases are all about, and your universe will open up enormously once you get a few concepts under your belt.

Running Filemaker 7 but using repeating field data structures is kinda hard to comprehend; you might as well use Appleworks or Filemaker 2.1. Repeating fields became obsolete for the purpose you are using them for when Filemaker 3 came out, what 7 years ago now.

[MoonShadow]

I'm afraid I disagree about repeating fields. They have never been more in my favor and I believe we will be finding very creative ways of using them to their full advantage thanks to FM7.

Proof of FMs belief in the power of repeating fields ... FM7 does not need to maintain backward compatibility but yet repeating fields stay! Why? They could have array'd them out during conversions (or even placed them in a related table for us) so repeating fields were meant to stay. And they have added support and manipulation options to them also. I think they know what they are doing ... and I think they have been ahead of us all along. We just need to open our minds once again to their uses.

This is particularly true with globals which can now also be calculations and/or repetitions. The possibilities of this one little 'upgrade' alone, still leaves me a bit breathless.

[bruceR]

What you say is true but irrelevant to this thread.

With repeating fields, you cannot sort, sub select, individually delete, insert, etc., without a lot of trouble. In the hands of somebody who knows what they are doing, can be very useful utility devices, reporting tools, etc. I am VERY experienced with repeating fields, and I see that they have been improved in FM7. You can even write to specific reps with applescript, though not with standard scripting.

But for basic data relationships, they lead to exactly the problem this user has encountered and their very existence has prevented him from learning necessary, basic, absolutely vital relational concepts. Beginning and intermediate users should not use repeating fields until they have mastered relational theory 101.

[MoonShadow]

BruceR said...Beginning and intermediate users should not use repeating fields until they have mastered relational theory 101.

I would agree and your point is well taken. Your previous post could have been misinterpreted to mean *never* use repeating fields (particularly that last sentence). FM7, of course, has opened up our possibilitites ... I just didn't want anyone to close their minds to those possibilities.

[bruceR]

You're right, I wasn't clear, and I agree very much that repeats shouldn't be ruled out. I think a book could probably be written on using repeats effectively. There is a knee-jerk reaction these days NOT to use them, which I sort of agree with because so many people get themselves in trouble by not using real relational designs. But you can have 32,000 repeats now, and you can split them off when you display them, etc.

[JavaVoodoo]

Thank you Bruce and MoonShadow, this has been quite enlightening.