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?
Sum ( GiftCertificates::CreditValue )
which can be an unstored calculation field in the Contacts table.
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".
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 )
ExecuteSQL ( "
SELECT Count ( * ) FROM GiftCertificates
/"Type/" = 'ReferralCredits' AND
CustomerNumber = ? " ;
"" ; "" ; Contacts::CustomerNumber )
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".
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.
On Option 4 - wouldn't that just set the qty of records that have the desired CreditType - not the total amount of such credits?
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.
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
Nope. except for the goof at the last option, all sum the value of your CreditValue Field.
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?
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.