5 Replies Latest reply on Oct 16, 2012 12:33 PM by veramilo_1

    Using a summary count in one table to perform calculations in another

    veramilo_1

      Title

      Using a summary count in one table to perform calculations in another

      Post

           I have an ATTENDANCE (ATT) table where I'm tracking registrations (head counts) for various workshops and a WORKSHOP (WS) table where I'm estimating expenses for overhead and student supplies for each workshop.  I'm breaking down expenses for student supplies per student so this total will change with each new registration.  

           In the WS table, I have one repeating field for student supplies (static cost per student) and another for other expenses.  In the WS table, I want to take the total of the student supplies and multiply it by the head count that's in the ATT table.  I also want to be able to add this value to the other expenses as well as displaying it separately.  I've spent hours trying a number of things, including get summary, but I can't seem to work it out.

           I'm sure I can accomplish this with portals, but I don't want to add anymore tables or records for a non-critical feature.  I hope this is possible, but if it isn't, I hope someone can let me know, in which case I will pass on the expense tracking.  Or maybe I'll try a script to calculate expenses.  I just thought of that now, but I'd still rather the totals update dynamically.

           Thanks! 

        • 1. Re: Using a summary count in one table to perform calculations in another
          Sorbsbuster

               I'm guessing you have one record per workshop, with a repeating field (not usually Best Practice in this scenario...) with various units of supplies costs.  You can create a calculation field that adds all of those unit costs.

               Presumably you have a field in your Attendance Table that links to this Workshop record, so you could include a Count calculation, using the relationship from the Workshop Table to the Attendance Table, to count an always-filled field, such as the AttendanceID, and multiply the total of the costs by that.

               So:

               Unit Costs (your repeating field)
               Wood   $1.00
               Nails    $2.00
               Glue     $4.00
               Total     $7.00  <- calculation in the Workshop Table

               Workshop Total Costs = Total Unit Costs x Count (Workshop_Attendance::WorkshopID )

          • 2. Re: Using a summary count in one table to perform calculations in another
            philmodjunk

                 Sum ( RepeatingField )

                 is the syntax you can use to get the total of all values entered into a repeating field.

                 BUT

                 Sum ( RelatedTable::Field )

                 also computes the total, but uses a related table for a set of values--such as your work shop expenses list, and it is a much more flexible approach than using a repeating field.

            • 3. Re: Using a summary count in one table to perform calculations in another
              veramilo_1

                    

                     
              •           
                                 
                •                     
                                      
                                           I realize repeating fields are generally to be avoided, but this particular feature isn't worth adding a table since I don't need reports from it.  I just want it as a quick calculator but with persistent data that's that's there for reference.  I set up a simple script before wrapping up yesterday which has solved the problem, but I have to call it and that itsn't ideal so I intend to keep looking...
                                      
                                            
                                      
                                           Sorbsbuster &  PhilModJunk:  Thank you for the help -- it's much appreciated.  I have to switch gears for a few days but will be looking into all your options as soon as I can.  I'll try to remember to post the results.
                                      
                                            
                                 
                •           
                     
              • 4. Re: Using a summary count in one table to perform calculations in another
                philmodjunk

                     I'd still go with the related table myself. Keep in mind that a repeating field limits you to a specific maximum number of items in your list where a related table has no such limit. You get a new workshop with a very large list of items and you can end up having to redesign both your database and your layouts where you may not have to make any change at all when you use a related table.

                • 5. Re: Using a summary count in one table to perform calculations in another
                  veramilo_1

                       That won't be a problem.  The workshops are designed to have as few supplies as possible because they have to be lugged from school to school.  And in most cases, this feature wouldn't even be used.