Best way for users to join many-to-many records

[The Bobster]

Hi there

Hope someone has some practical advice about the best way to allow users to enter data in a multi-relational FMP database.

I'm new to many-to-many relationships so will use the pupils/classes example quoted in the FileMaker manual, rather than boring you all with my industry-specific solution. Here goes ...

I have a many-to-many relationship, with a pupils database connected to a classes database by a join file (because, as they note in the manual, many pupils can sign up to a single teaching class object, and a single class can in turn point to many pupils).

However, when it comes to data entry in this solution, the teachers are less than happy, because - when they have to create a record saying that John wants to attend art class - they currently have to:

(1) open the join file

(2) enter the pupils database, do a find to locate John's pupil record, then copy and paste the unique serial number for the pupil

(3) paste the pupil's unique serial number in the relevant field on the join file

(4) open the classes database, do a find on the art class, copy and paste the unique serial number for the class

(5) paste the class's unique serial number in the relevant field on the join file

(6) bingo, the relationship is created and in the pupils database, the art class shows up in the portal on john's record, while in the classes database portal view, john is now shown as attending the art class.

Originally to make selection easier in the join file, I did try to define the serial no. fields for classes and pupils using drop-down menu value-lists automatically populated by the pupil/class names in the two databases, but as the class database contains 15,000 records and the pupils database contains 30,000 records, and a drop-down menu with 10,000 names runs REAL slow and is almost impossible to navigate, I quickly abandoned this.

It's never been a problem with a one-to-one relationship, as I always set up the portal view so that the user could create a new record (or edit an existing record) within the portal, so the 'teachers' never even needed to know they were making or editing a record in a different database.

However, with FMP's many-to-many concept, I haven't worked out a way to let a teacher painlessly connect a pupil to a class, or vice versa.

Any tips on how I could re-jig what I'm doing to make data-entry a little less painless?

Ta all.

Bobster

[CobaltSky]

Hi Bobster,

Welcome to the forum!

What you're describing is essentially an interface problem.

How I suggest that you approach it is to center the process on a particular file and have users perform all the necessary actions from within that file. The fact that the data will be sourced from another file or files and will be written into a join file is beside the point - the user need not be aware of that.

To provide an example, you could set up an entire enrolment process to take place (from the user's perspective) from within the pupils file. The user locates the record for the pupil that they want to enrol into a class. While still on the pupil's record, they then switch to a layout which presents a filtered portal which is linked to the classes file and they can enter some criteria and view a filtered list of corresponding classes.

From this point, when the user sees the desired class in the filtered portal, a single mouse click should be all that is required to complete the enrolment. This is on the basis that when the user clicks on the portal record for the desired class, a script is launched which writes the classID from that portal row, along with the pupilID from the current record, to a new record in the join file.

One way to do this is to have a 'special' relationship from the pupil file to the join file based on a concatenation of pupilID and classID, with the 'Allow creation of related records' option enabled for that relationship. The script that is attached to the portal row as outlined above can be as simple as:

Set Field ["gJoinKey", "PupilID & "-" & ClassFilter::ClassID"]

Set Field ["JoinFile::PupilID", "PupilID"]

Set Field ["JoinFile::ClassID", "ClassFilter::ClassID"]

- whereupon the enrolment is brought into existence and can be acted on directly in subsequent steps of the same script, to store any other information pertinent to the join.

A similarly simple procedure can be implemented in the class file so that users can efficiently select and place pupils into a given class. In some solutions, both options 'Enrol by Class' and Enrol by Pupil would be desirable - but in either case the process from the user's point of view will be quite a bit more straightforward than the procedure you're currently contemplating...

[Ugo DI LUCA]

Wouldn't it be time FMI consider an update to the Manual ?

The way it is right now is rather confusing, as quite no "Interface" is explained.

Bobster stricly followed FM Manual and the examples given, and there he is with some errors I'd consider as "Major" while developing a db :

- let the user work directly on the line item

- use copy and paste.

I visited a school last week, which is currently using an old FM4 db, probably "internally" developed. They use Macs, but the "C" and "V" on the keyboard are quite invisible, because of the repetitive shortcuts used to related records.