Dual criteria filtered value list question
Core Database Structure:
Table 1: Animals Represents list of animals in a shelter
Tabel 2: Adoptions Record tracking if an animal has/had an adoption attempt and whether or not it went through
An adoption record is related to the animal via the standard ID type link (Adoptions::AnimalID->Animals::ID). An adoption record may have a status of "Pending" (interest expressed), "Adopted" (adoption went through), or "Canceled" or "Returned" both of which mean someone had an interest, but is no longer interested.
An animal may not have an adoption record (it may have just arrived at the shelter).
An animal may have multiple adoption records (as multiple people were expressing an interest).
Inside of animals I use several filters (via relationships) to create an AdoptionStatus calculation field which displays the following: "Available" (no adoption records OR records exist but none are "Pending" or "Adopted"), "Pending" (1 or more adoption records exist which are "Pending"), or "Adopted" (an adoption record exists whichhas a state of "Adopted").
I'm trying to create a filtered value list of Animals which would display animals that meet the following criteria:
- No Adoption records
- Adoption records exist (n records) but none of them have status of "Adopted"
I tried using the AdoptionStatus field I've created inside of Animals for the self-reference relationship to create the filtered value list...however since it is a calculation field based on relationships it cannot be indexed...so the value list doesn't work.
I can't start the value list filtering from Adoptions (since I need to include Animals which have no adoption records at all).
So...does that mean I need to not use any of the automatic value lists and switch this over to a script/global based solution where I can do the appropriate queries, populate the global, use the global as the field source for the value list, and then find the ID values behind the scene so I can populate the key fields for the relationship beween Adoptions and Animals?