8 Replies Latest reply on Sep 19, 2012 4:20 PM by philmodjunk

    Moving calculated results from one Table to a separate Table

    BobO'Neal

      Title

      Moving calculated results from one Table to a separate Table

      Post

           I have a Table listing 3 group types of students. I keep attendance records of each student in the Table. 

           I have written scripts to Find, sort & Get(FoundCount) of each group to print the number of studens in each group & to count their attendance to print  each week. I count the number of students and the number of class days for each group. I have this working so I print out the attendance and the count results for each week.

           I now want to save/store these weekly count results for each group (totals, not per student). I want to save results from each week, so I can later sort them by a weekly date even after the student list has changed. I assume I have to start another Table to add each weeks results as a new Record, but I don't see how to set the counted results from my script to the new Table. Can this be done? Am I missing a simpler solution?

            

        • 1. Re: Moving calculated results from one Table to a separate Table
          philmodjunk

               It can be done, but I have to ask why you want to do it that way.

               It should be possible to log student attendance in the same table for each student for each class that they attend and then you can pull up a report for whatever criteria is needed to show attendance statistics without copying the data to another table. Such can be done, but it complicates your design and thus shouldn't be done unless there is a specific reason for doing so.

          • 2. Re: Moving calculated results from one Table to a separate Table
            BobO'Neal

                 It is a little more complicated than my example. These are some clients taking a series of required classes that they might move from group to group or leave after a few weeks. When I print the weekly attendance counts, I want these weekly numbers available in a Table for comparison over a few weeks. Some of the clients may have been removed from the Table before the weekly totals are evaluated. I thought the best way to save the weekly results was to save them as records. All records in the current Table are the clients. Don't know how to start a table of weekly records without making a new Table.

            • 3. Re: Moving calculated results from one Table to a separate Table
              BobO'Neal

                   After studing this problem more, i think I can state better what I want to do.

                   I have a Table of client records tht are statused daily in the table. Once a week I want to run a series of scripts that Sort, Find counts & totals of groups of these clients, and place these results in a Table with a new Record for each week. I have not figured out how to tie the Tables together so I can set the Scripted results from the client Table into new Record Fields in the new Table each week.

                   Any help appreciated.

              • 4. Re: Moving calculated results from one Table to a separate Table
                philmodjunk

                     Yet all this can be done without adding the additional table and putting this Summarized info into it. The details, however, depend on the structure of your database. The key to tracking your attendance data is to create a record for each student for each time they attend a class. The record in this attendance table links to the customer record, and another field links it to a record for the class that was attended and a date field records the date that they attended. One variation of this only creates records for those that attended. Another option creates a record for every registered student and sets a value in a field to mark that person absent or present for that class on that day and (if needed) time.

                     You can then perform finds and sorts on such a table at any time to get counts of how many were present and how many were absent. And these totals can be for any number of different groupings depending on your finds and sorts. (Show attendance record for just one individual, a group of individuals, attendance for one class, all classes, all classes taught by a specific instructor and so forth.

                • 5. Re: Moving calculated results from one Table to a separate Table
                  BobO'Neal

                       Thanks for the reply Phil. If I understand the multiple Records per client, with many clients in many combinations of groups, the additional Records would be cumbersome over the several weeks I want to save. I still need to look at adding the additional fields to identify the type clas & attendance.

                       In the meantime I am still looking at adding the weekly results (one Record per week) to a Table. I am trying to use a script to "Set Field"  from the attendance Table to the Weekly summary Table. This appears as a method of moving a data field from one Table to another. Just starting to try this. Do you think this will work? I really want to be able to view these weekly attendance summaries in a table form to prepare a Monthly summary. 

                       I am attaching a snapshot of the attendance table to show you what I work with. Each client starts at different dates, so the attendance records vary.

                        

                         

                        

                  • 6. Re: Moving calculated results from one Table to a separate Table
                    philmodjunk

                         Given that you can literally have millions of records in a table, I'm not sure why it would be "cumbersome" provided you set up layouts, scripts etc. to manage this for you.

                         To move data from one table to another with set field requires one of two basic approaches:

                         1) If there is a valid relationship, you can move the date in a single set field step such as:

                         Set Field [Table1::Field ; Table2::field]

                         or

                         Set Field [Table1::Field ; Table2Field + Table1::Field]

                         2) If there is no valid relationship, copy the data into a variable or global field, change layouts, find or create the record and then copy the data from the variable or global field into a field in the record on this layout:

                         Set Variable [$Value ; value: Table2::field]
                         Go to layout [Table 1]
                         New Record/Request
                         Set Field [Table 1: $Value]

                    • 7. Re: Moving calculated results from one Table to a separate Table
                      BobO'Neal

                           Thanks Phil. I had already found (with some trial & error) that the Set Field command let me move the fields from my attendance Table to a new table. Haven't finished but I am almost there.

                           The main reason I was wanting a new table is the attendance table is already set. You saw from my uploaded pix I have it set to record attendance for individual client records, with weeks of status already entered. i didn't want to start over & redesign that table, just add one for recording some weekly summary data.

                           Thanks again for your help.

                            

                      • 8. Re: Moving calculated results from one Table to a separate Table
                        philmodjunk

                             I suggest that you keep that redesign in mind as a future project for improving your database. I suspect that you'll need to eventually. The screen shot, doesn't really tell me much about the structure of your tables and relationships as any number of different set ups could be used with the layout that you show.