10 Replies Latest reply on May 16, 2011 10:41 AM by philmodjunk

    Need help displaying and totaling



      Need help displaying and totaling


      Here's what I need to do, and I'm a little out of my depth.

      I have a field where one can enter the initials of people working on the project. Let's say depending on the record, you enter:

      DP or JB or AC

      What I want to do is:

      (1.) For each initial that's entered to be displayed in the footer part in a list of some kind. It would be acceptable for the field to be a value list that you could edit if it makes it easier to do.

      (2.) Next to each initial in the list, display the total amount of time each initial garners. What I mean by this is that each record has a length of time associated with it. Below, in the footer, "A" would be displayed as "DP", "B" would be displayed as "AC" and "C" would be displayed as "JB":

      Right now, my calculations say something like "if (initials_field = "DP" ; seconds_length ; "") in order to tally the time of each initial, and you can see the 3 totals calcs in the footer. The problem is I have to specify initials using Manage Database and I want the user to be able to enter whatever initials they want and the totals display will be dynamic and update for whatever initials are used.

      Is any of this making sense?


        • 1. Re: Need help displaying and totaling

          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.

          • 2. Re: Need help displaying and totaling

            Working on this. it's slightly beyond my understanding but I'm trying. Will report.

            • 3. Re: Need help displaying and totaling

              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?

              • 4. Re: Need help displaying and totaling

                Perhaps I need to think of the Initials table (and layout) as a data entry point for initials.

                • 5. Re: Need help displaying and totaling

                  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.

                  • 6. Re: Need help displaying and totaling

                    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?

                    • 7. Re: Need help displaying and totaling

                      I just noticed this:


                      Is the relationship between Initials and TimeEntriesByInitial supposed to be a one-to-many?

                      • 8. Re: Need help displaying and totaling

                        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.

                        • 9. Re: Need help displaying and totaling

                          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?

                          • 10. Re: Need help displaying and totaling

                            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.