9 Replies Latest reply on Apr 26, 2017 1:36 PM by philmodjunk

    Get total of certain related records from another table

    HB

      Hello

       

      I have a field in a contacts table that I would like to contain the total ReferralCredits available for that contact, which exist in the GiftCertificates table. It would be best if that field is automatically updated, but not necessary as I could 'refresh' or 'recalculate' its value the few times I need it. I usually need the value on only a few contact records at a time.

       

      Contacts table has fields named CustomerNumber (unique) and ReferralCreditsTotal

      GiftCertificates table has fields named CustomerNumber (multiple of each) and Type (we want all the "ReferralCredits") and CreditValue

       

      I tried a few things but didn't get anywhere.

       

      Please help - much appreciated.

        • 1. Re: Get total of certain related records from another table
          philmodjunk

          Please explain this in more detail:

           

          GiftCertificates table has fields named CustomerNumber (multiple of each) and Type (we want all the "ReferralCredits") and CreditValue

          What do you mean by "multiple of each"? Is this a text field with a list of different customer numbers?

           

          And you want the total of "CreditValue" over all the records in Giftcertificates that are linked to a given customer?

           

          That's simply:

           

          Sum ( GiftCertificates::CreditValue )

           

          which can be an unstored calculation field in the Contacts table.

          • 2. Re: Get total of certain related records from another table
            HB

            sorry - further clarrification:

             

            GiftCertificates table has fields named CustomerNumber (txt) and Type (txt) and CreditValue (number)

            There are many records for each CustomerNumber each having one Type per record and one CreditValue amount per record.

             

            (none of my fields are repeating type)

             

            I want the ReferralCreditsTotal field in the Contacts Table to be the sum of each Contact's related records in the GiftCertificates table whose Type = "ReferralCredits".

            • 3. Re: Get total of certain related records from another table
              philmodjunk

              Option 1:

              Create a new occurrence of GiftCertificates and link it to contacts by both customer number and Type. Put a calculation field in contacts that always has the value "ReferralCredits" to match to Type in this occurrence of the GiftCertificates table. You can then reference this new occurrence with a sum function to get the total:

               

              Sum ( GiftCertificatesByType::CreditValue )

               

              Option 2:

              ExecuteSQL ( "

              SELECT Count ( * ) FROM GiftCertificates

              WHERE

                     /"Type/" = 'ReferralCredits' AND

                    CustomerNumber = ? " ;

              "" ; "" ; Contacts::CustomerNumber )

               

              Option 3:

              Define a summary field in Giftcertificates that counts the customernumber (or any field that is never empty).

              Put a one row portal to GiftCertificates on your layout with this summary field in the portal row. Use this portal filter:

              GiftCertificates::Type = "ReferralCredits".

               

              Option 4:

              Use a script that finds all records for a given customer number and ReferralCredits type. Set a variable to Get ( FoundCount ) and then set a number field in contacts to the value of this variable.

              • 4. Re: Get total of certain related records from another table
                HB

                Thanks philmodjunk

                On Option 4 - wouldn't that just set the qty of records that have the desired CreditType - not the total amount of such credits?

                • 5. Re: Get total of certain related records from another table
                  philmodjunk

                  Yes, good point. You'd need instead to use the same summary field as described for option 3 as the value to set to the variable.

                  • 6. Re: Get total of certain related records from another table
                    HB

                    PhilModjunk  - I thank you for your response but looking thru your answers seems you are looking at count of records throughout - please let me restate my question trying to be as clear as possible...sorry i didn't do that initally...thanks so much.

                     

                    Problem: Once in a while I need to find out how much total credit a contact has of type "ReferralCredits".

                    I would like to store that in the Contacts Table in a number field called ReferralCreditsTotal

                     

                    My databases are laid out as follows:

                     

                    Contacts Table In File named Data04

                    Fields: ReferralCreditsTotal (Number) ; CustomerNumber (Unique txt)

                    (This table has one record per contact)

                     

                    GiftCertificate Table in File named Data03:

                    Fields: CustomerNumber (txt) ; Type (txt) ; CreditValue (number)

                    (This table has many records for each CustomerNumber -  1 per each time they receive a referral credit)

                     

                    Assuming both files are open, how would I get each contacts' total sum amount of CreditValue of GiftCertificate::Type "ReferralCredits" into the Contacts::ReferralCreditsTotal field.

                     

                    Again - it isn't necessary the data be live, as I could run a script to update when needed.

                     

                    I hope this is clear...thanks

                    • 7. Re: Get total of certain related records from another table
                      philmodjunk

                      Nope. except for the goof at the last option, all sum the value of your CreditValue Field.

                      • 8. Re: Get total of certain related records from another table
                        HB

                        Thank you so much - I used Option 1 - it worked - btw wouldn't it be easier to make the new field in contacts that is a calc field holding value "ReferralCredits" a text global field? & why cant they be calc global fields?

                        • 9. Re: Get total of certain related records from another table
                          philmodjunk

                          A global field has a single value that is the same for every record in the table where it is defined. How would you make that work for this using the methods of option 1? The calculations will automatically be unstored calculations if you use calculation fields to get the needed totals, but they would not be global.

                           

                          Option 1 isn't necessarily the easiest to implement, you have to add an additional relationship and match fields, after all, but it's generally the easiest for a "newbie" to understand and thus get to work correctly.