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

Sub-summaries can't sort on repeating fields


walter4e
 Share

Recommended Posts

Hi all. I am trying to create a FMP 7 database that will allow me to report on the students seen by 12 tutors. In the past I have created a new record for each student when s/he was first seen. That record contained fields for biographical info and a repeating field for each date the student was seen BY THAT TUTOR. If the student was later seen by another tutor I would create a new record.

 

I did this because an earlier attempt failed, wherein I maintained only one record per student and added another repeating field for "tutor seen;" it failed because I could not use that "tutor seen" field to sort by. (A sub-summary sorted by that field ignored all the repeats, taking info from only the first occurence).

 

At the end of term I need to provide lists to the 12 tutors, something like this:

 

DOE, JOHN (Tutor)

- Mary Smith 12 (number of tutorials)

- Mark Howard 6

- Lana Lang 4

- Paul Atreides 5

 

I also need to report on the different tutors seen by each student, such as:

 

SMITH, Mary (Student)

- Tutor 1 - 6 (6 visits to this tutor)

- Tutor 5 - 4

- Tutor 11 - 2

 

Can anyone steer me in the right direction?

Link to comment
Share on other sites

Ditch those repeating fields and never again use a repeating field.

 

You need a separate table for TutorSessions, related to the Tutor x Student table that you're starting from.

 

Oh, and I would also create "grandparent" tables, one for Tutors and one for Students. Then in your main table you select Student Name (or Student ID or Student Soc Sec No or whatever) from a dropdown list, which links it to the master record for that student; and you select the Tutor Name (or Tutor ID or whatever) from its dropdown list, which links the record also to the master record for that tutor. That way you can do a Find for all the tutor sessions of a given student regardless of tutor, sort them by tutor, and run a report on them; or, for that matter, a Find for all the tutor sessions given by a specified tutor, sort them by student name, and run a report on those.

 

Repeating fields were at their most useful ummm....FileMaker 1 and before. As long ago as FileMaker 2, you could avoid them, and, like chicken pox and day-old sushi, they're best avoided. As you've discovered, you can't sort on the damn things. If you continue to use them you'll discover dozens of other things they either don't do or don't do in an intuitive predictable fashion. Even in global container fields, where you'd think they'd let you define just a couple fields instead of a huge raft of non-repeating global container fields, they'll eventually manage to frustrate you.

 

[/rant]

 

 

 

And yes, I know people want advice on how to do the specific thing they're trying to do within the structure they've already got. I know it's frustrating when someone says "Well, I'd redo your entire architecture, your problem is that your design really sucks", especially when it looks like it would be a lot of labor to follow their advice and rebuild the freaking thing with a whole new design.

 

But this time I'm doing it anyway.

Link to comment
Share on other sites

Thanks for the response. Ditching repeating fields as ordered, Sir!

 

Seriously, I have no problem doing that and I will study your response and try to do what you suggest. You're right of course: the repeating fields go back at least 7 years in this dbase and I got used to them. Time to move on.

 

Thanks for the push.

Link to comment
Share on other sites

..... and never again use a repeating field.

 

 

Never say never or always. Simple, don't use absolutes as a developer.

 

Repeating fields has a value, you just have to know how to use them.

Proof is that the RF's has a new sort of live in FM 7/8.

 

At least you could actually listen and consider the idea why to use RF's. The best part of that is that you might learn something and so might the poster. Everyone wins.

Link to comment
Share on other sites

OK what about a conditional never:

-never use repeating fields if you can do it by a relational setup?

-never use repeating fields if you ever need to build a report from their content

-never use repeating fields that are part of the UI

-...

 

I really like rules of thumb.

 

kjoe

Link to comment
Share on other sites

Never say never or always. Simple, don't use absolutes as a developer.

 

Yes. The code speaks for itself:

 

Abs ( "never" ) = 0 //true in all versions of FileMaker ;o

 

Repeating fields has a value, you just have to know how to use them.

Proof is that the RF's has a new sort of live in FM 7/8.

 

At least you could actually listen and consider the idea why to use RF's. The best part of that is that you might learn something and so might the poster. Everyone wins.

Repeating fields are the closest thing FileMaker has to an array handler - and the tools available with which to use them for this purpose are stronger since the release of v7.

 

To neglect to use them when it's appropriate is every bit as problematic as to use them inappropriately. So I fully agree with Jan here.

 

Let sleeping dogma lie. Oh Really!

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use