Neater AxB join files: Is there a better way?

[ESpringer]

This must be a common experience:

What used to be a table (say, an attendance table, students x dates) acquires a new dimension, as I realize that what used to be a field (yes/no) comes to acquire its own matrix of data (attendance? excuse? participation level? comment?). Another example is: gradebooks used to settle for one box in which to record a score per student per assignment, but of course now I have a veritable world of parameters I like to record about every assignment submission.

Once I've become convinced, I regularly plump for making a new "join" table... With FM7, I can achieve a quasi-tabular look still (say, students as rows, date-by-date across), using "horizontal portals", and this makes me happy!

But a join file requires such maintenance compared to a table! Nothing about the structure of a join file constrains it to a tabular matrix. This is often good. But it also means that there's no record "there" to receive new data... Where I used to be able to "Replace Field Contents..." to do the equivalent of a "Fill Down" in a spreadsheet, now I find myself creating records tediously by clicking into empty portal records over and over just to "fill down" and confirm that each student attended today. And, of course, nothing about a join file prevents there from being multiple records linking a student to a date in my attendance join file. (This hasn't happened to me because of my portal structure, but I could imagine some duplication or modification creating redundancy in one AB matrix "location".)

I know the many-to-many flexbility is generally a good thing! I revere it as appropriate. But there are surely LOTS of applications in which a strict matrix is desired (exactly one AB record for each combination of A and :. What are the best ways to generate and maintain the right join file structure?

I assume the necessary ingredients for a nice join file matrix are:

(1) A script to allow creation of a "batch" of join file records: give me an AB for each found record A on this B (for each student on this date, say). Once this is done, a "replace field contents" does work even on related data. But what's the most efficient script for creating such a batch? There's still no group "duplicate records" is there? One could run this batch-of-As generation within a script that goes through each relevant B value...

(2) Using the option to delete related records: take away the AB record if the A or the B is being deleted.

(3) Ensure uniqueness by? (In FM6 the join file used a concatenated student-date identifier which had to be unique, but now it seems there's no need for concatenation, unless I need it for uniqueness confirmation?)

Has anyone found an efficient way to automate this so that there's always exactly one (perhaps empty) record located at each AB location?

Thanks!!

[Ender]

Don't know anything about tabular matricies, but here's some ideas:

>>(1) A script to allow creation of a "batch" of join file records: give me an AB for each found record A on this B (for each student on this date, say). Once this is done, a "replace field contents" does work even on related data. But what's the most efficient script for creating such a batch? There's still no group "duplicate records" is there? One could run this batch-of-As generation within a script that goes through each relevant B value...

I would use an import to pull the IDs from the found set of Students into the Attendance table with a Replace on Date (or use an Auto-Entered Calc to set the date from a global.)

>>(2) Using the option to delete related records: take away the AB record if the A or the B is being deleted.

I think you can do this with the relationship options.

>>(3) Ensure uniqueness by? (In FM6 the join file used a concatenated student-date identifier which had to be unique, but now it seems there's no need for concatenation, unless I need it for uniqueness confirmation?)

If you control record creation in the join table with scripts, then validation shouldn't be necessary. If an individual record is to be created (rather than a batch), you can test if the related record already exists before adding a new one.

[ESpringer]

Thanks, Ender.

Scripting with the import records option is, I believe, the best actual approach. Thanks for the idea! It seems that, with multiple TOs, I can define the import so that if I start from a given course's record, each record for a student related specifically to that course will prompt a new record in the attendance table, and I can have the join-file records auto-enter date from a global, which could be changed once per batch... I haven't worked it all out, but it looks promising. (Of course, if I add later to my A-table, nothing will automatically fill in the missing join-records -- unless I script that too.)

Although use of scripts makes it unlikely that join file records will double up, I'd love something structural rather than merely procedural to reinforce the matrix structure... I would love a validation option to work on more than one field: to make sure that the *combination* of field-A and field-B is unique. I suppose I could set up a script to check for duplicates, or a self-join relation that would flag any duplicates.

An alternative "wish list" solution would be for FM to have a "Replace Field Contents" command that could allow an optional "creating new related records as necessary" to handle cases where the field in question is a related field. :}