Trigger a script that will modify another related field conditionally
I have a set of Invoices that has an expiration date. I want to trigger a script that will change a value in a field in the customers table.
Here is what I have so far:
In a customer record there is a field that auto populates with "Visitor" in the field Customers::Member Status
When an invoice is created in the Invoices table, I want to evaluate the date in the field Invoices::Expiration Date. If the date is after today, I want to trigger a script that enters "Current Member" in the field Customers::Member Status in the Customer table. When the date is before todays date, I want a script to auto trigger to format the field Customers::Member Status with "Delinquent". Easy Right? Well...
Here's the kicker, the customer can have many memberships year over year, so the "Delinquent" part of the script would need to ignore the previous invoice when a new invoice is created with a current date.
I'm pretty unsure how to go about this one.