Constrain number of repeating fields displayed

[Ian Murphy]

I've wondered about this for years, but never really needed to answer it before now.

I'm preparing a database to help the library register people for programs.

There are about fifty programs per month. Most have room for eight or ten people; some are one-on-one sessions; some movies and presentations have room for 35; some outdoor events have room for 50. So I plan to reserve a field (called some shorthand for Number_Of_Spaces) where the organizer will manually enter the number of spots for registrants, and I need a solution that works for every variable from 1 to 50.

What I want is for the number of blank spaces on the roster to equal the number in that field. I want it to be impossible for a regular user to type in nine names after choosing eight spaces. (I don't mind the mess that'll get caused when there are ten people signed up, and we retroactively change the number to eight, and can't see the two live values that are now hidden - we aren't a huge organization, and I don't anticipate that mess very often.)

(The names getting entered are all related records, of course - when Jane Smith registers for Story Time in the fall and Crafts in the spring, I want to be able to tell it from her record. I plan to link via unique serial numbers.)

The only way I know to make this rock solid, I really don't want to do because it would be so much trouble to maintain. That would be a repeating field (Program_Attendance) that's related to personal records in another table (or database), with 50 repetitions, where after you set the field Number_Of_Spaces to 24, a script trigger moves you to layout 24, where 24 layouts are displayed.

This sounds horrible to me, not least because I'll eventually change something in the navigation or etc. and don't like the process of pasting sets of buttons 50 times. (Has FileMaker ever included a feature akin to HTML's 'include,' which would let me tweak some part of the interface and have it propagate to several different places?)

In my imagination, I can think of several different possibilities. I've heard of setting transparent tabs to one pixel wide, but maintaining 50 tabs sounds as labor-intensive as maintaining 50 layouts. I love portals, but have never found a way of parsing parameters to tell a portal, "display as many rows as I've asked for in field X."

Thanks in advance for any suggestions.

[Vaughan]

Don't use repeating fields for this. Use related records. Script the process of creating the related bookings records so that they can be limited to the maximum number.

BTW I would NOT limit the number of bookings. I'd issues a warning that the course is full, and use conditional formatting to indicate that the booking was in excess.

The reason to allow extra bookings is that people drop out or don't turn up. You need somewhere to store the extras.

[mr_vodka]

Do you really have the blank spaces already preset? With a portal, you could just allow your users to keep entering records until it hit your max. Then you can make it so that they cant any more.

If however, you need to it to show 50 empty spots, etc then you could do something like this.

signup_sheet.zip

[Ian Murphy]

Having thought about it a few days, I'd already figured that yes, related records are the way to go.

Vaughan, I take yr point that it's better to build the DB in a way that people can exercise judgment.

Mr_vodka, I haven't looked yet at your attachment, but will get back.

Thanks for pointing me this far - I'm sure I'll be back with particular questions.

[Vaughan]

Having thought about it a few days, I'd already figured that yes, related records are the way to go.

Vaughan, I take yr point that it's better to build the DB in a way that people can exercise judgment.

Good decision.

I've *actually built* a course booking system for a performing arts school that has over 3,000 students enrolled in over 300 courses spread across 20 venues, with over 100 teaching staff.

Seemingly minor details like where to record enrolments after the course is full are the difference between a system that works and a system that doesn't. :D