Hello, I am a new user and looking for some input on how to accomplish a task.

I am running a membership database.  After being a member for a year, you are now authorized to sponsor other new members.

On a user record, I typically enter who their sponsor is.  My goal would be to have this be a dropdown, or lookup.  However, I only want to show other members that are eligible to sponsor.

So, the criteria when someone clicks on the dropdown would be to look at all of the other records and only show the First and Last Name of records that have a membership join date prior to X.

Another option would be to flag user records with a checkbox that says they are eligible to sponsor.  I imagine there must be a way to run a routine nightly/weekly that looks at all records and updates my check box if the user is now "old enough" to sponsor.

My hope is to be able to:

1.  Have a dynamic list of who can actually sponsor that is a dropdown when I create new records

2. Be able to report on the grouping of members in relation to who sponsored them

Thoughts on how to go about this?

Thank you in advance!

--Rod