Let's call the Table you have in this layout "TimeEntries". Now create a new table, Initials.
We'll need to get a bit fancy with our relationships to make this work. We'll need the following:
TimeEntries::Anyfield X Initials::AnyField
Initials::InitialsFld = TimeEntriesByInitial::InitialsFld
TimeEntriesByInitial is an additional table occurrence of TimeEntries. You create it by clicking TimeEntries in Manage | Database | Relationships and then click the button with two green plus signs. (Double click this new table occurrence "box" to open a dialog where you can rename it.)
Now a calculation field defined in Initials, TotalTime can be defined as Sum ( timeEntriesByInitial::seconds_length ) to compute the total time for any one set of initials.
Now you can place a portal to Initials in a footer or trailing grand summary to display the total time for each record in Initials.
Note: It would be an even better design to add a serial number field to the Initials table and use it to link to the records in TimeEntries instead of by the actual initials. That way you can change initials (Say someone gets married) for a given person and you won't break the link to the timeEntries records. You can set up a value list for linking TimeEntries records to initials that lists the ID number in column 1 and their current initials in column 2 for this purpose.
Working on this. it's slightly beyond my understanding but I'm trying. Will report.
OK got it.
My one question is about the generation of initials in the Initials table. If I'm reading this correctly, the Initials table needs to have one record for every initial in use, right? At the moment, I have to create these records in the Initials table and manually enter the initial there instead of in the main table. Is that correct? I wonder if there is a more graceful way to generate these initials and records?
Perhaps I need to think of the Initials table (and layout) as a data entry point for initials.
Exactly, you can even define a value list that lists the initials from this table and you can use it in the original table as a way to enter the initials.
Now that I've implemented this in my main db, it brings up an interesting conundrum. Using this relational system, the totals-per-initial are accurately reflected dynamically in my portal. When I omit records, however, the "actual" timings based on initials for all records are still shown. Below, I omitted all records and created three new ones I did not assign to any initials:
Since this system does not work by tallying, if I want to show the totals for a found set of records, perhaps there is another way to do this. Honestly, though, I much prefer your method; doing a tally system would involve way too many fields (a field per initial, for example). Can I use a different calc than Sum, in order to make it a tally of found records?
I just noticed this:
Is the relationship between Initials and TimeEntriesByInitial supposed to be a one-to-many?
Yes, that's what that means. It really should have been posted as: TimeEntries>----Initials----<TimeEntriesByInitial
Relationships to TimeEntriesByInitial will not be changed by what records are present in TimeEntries unless you omitted all the records with that set of initials. Thus this method won't work if you want the totals to change when you omit a record from the found set of TimeEntries Records.
Question about this: In order to change the relationships, do I need to check boxes relating to being able to create records based on the relationship?
That option makes it possible to enter new records via the relationship and get the needed foreign key field updated from the current parent record's matching key field. This is usually done by placing a portal on the parent record's layout. With that layout and this option enabled, you can enter data into the bottom blank record of the portal row and a new record will be created in the portal's table with the matching value from the Parent record's defined key copied into the matching field of the portal record so that proper links are automatically created between the layout's record and the new portal record.