4 Replies Latest reply on Feb 9, 2011 11:34 PM by synergy46

    Total NOT based on global needed.

    synergy46

      Title

      Total NOT based on global needed.

      Post

      I have a Membershiip database.

      One table is "Setup" which has these global fields:
             Dues
             Assessments
             Other

      Into these fields are the 'current' amounts that compose the Total Dues.

      Another table is Dues with these fields:
              DuesAmt
              AssessmentAmt
              OtherAmt
              ExpectedAmt

      The Dues::ExpectedAmt field is the total of the global fields: Setup::Dues + Setup::Assessmeents + Setup::Other

      This works great... until I change the Setup::Dues , Assessments or Other fields.  Then, all the records in the portal also change accordingly.

      This means that if I enter 25 15 5 for Setup global fields  in  2009 (total 40) that is what shows in the Dues::ExpectedAmt field in the  portal row for 2009

      If there is a 2010 dues increase to say .... 30 15 5, then the 2009 and 2010 ExpecctedAmt shows the new value 50.

      Seems like I read an invoicing address problem that was similar to this but I can't get the details to come back.

      Any ideas?  What's the trick?

      (Setup Table shown below)

      ishot-463.jpg

        • 1. Re: Total NOT based on global needed.
          philmodjunk

          What records do you want to see update when you change a value in one of these fields?

          Since your fields are global, a change to their value affects all calculation fields that reference them. If this is not the result you want, then don't use fields with global storage.

          If you only want the current record to update, use regular storage fields defined in the same table.

          If you want a group of records all related to the same parent record, define the fields as regular storage fields in the parent record.

          • 2. Re: Total NOT based on global needed.
            synergy46

            Thanks for the reply.

            The global fields shown above, serve as the default values for the corresponsing portal rows.  This works.
            By that I mean I can change a portal row and over ride the default or update the global values and the defaults change as desired.

            I have a calculated field  called DUES::TotalAmt which is the sum of the 3 types of dues in the portal row.  This works.

            PROBLEM:
            The problem is that I want to have a calculated total of the above shown 3 global fields THAT, once entered in a portal row DOES NOT CHANGE WHEN THE GLOBAL FIELD VALUES ARE UPDATED. 

            If I create a DUES:TotalAmt field that sums the global::fields, I get the message that I can't store the values because they reference global values.

            So, "is there a work around for this?"

            Thanks...

            • 3. Re: Total NOT based on global needed.
              philmodjunk

              There is, but I'm having trouble following the details of your original post.

              You say:

              One table is "Setup" which has these global fields:
                     Dues
                     Assessments
                     Other

              Yet your screenshot at the bottom of the post shows three global fields with the names: Dues_Amt, Dues_Assessment, and Dues_Other

              I don't really see any connection between these two sets of global fields.

              That said, is Dues::ExpectedAmt a field of type calculation or a number field defined with an auto-enter calculation?

              I assumed from your original post that this is a field of type calculation. If so, you should be able to fix this just by changing the field type to number with your calculation as the auto-entered calcualtion. With auto-entered calculations, changes to a field from another table that is referenced in the calculation will not trigger the auto-entered calculation to update the field and this would seem to be what you want here.

              • 4. Re: Total NOT based on global needed.
                synergy46

                Got it to work.

                Turns out I needed to use a script trigger to run a script which pulls a global variable based on the total into the desired field.  Works like a charm... so far 8-)

                Thanks for your efforts.