Problem Setting Flag in Parent based on Values in Related Child Records
I have two tables USERS and SURVEYS that are related on a unique field - UserID.
I want to identify records in USERS if we have received a Survey from a User in the previous 12 months.
SURVEYS::DaysSinceSurvey calculates the number of days between the current date and the date the Survey was received. (SURVEYS::SurveyDate)
USERS::FlagUnder12Months is a calculation field that enters an "X" if the value in SURVEYS::DaysSinceSurvey is less than 367.
This works unless we have received more than one Survey from a User. If more than one survey has been received, USERS::FlagUnder12Months is left blank even if SURVEYS::DaysSinceSurvey is less than 367.
Is there a better way to flag records in USERS if any records in SURVEYS from the same User have a value of less than 367 in SURVEYS::DaysSinceSurvey?