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.