2 Replies Latest reply on Feb 3, 2013 8:32 AM by CharlesDestrempes

    Dual criteria filtered value list question



      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?

        • 1. Re: Dual criteria filtered value list question

               Another option you may wish to implement is to use a filtered portal to list available animals. The portal filter expression need not be limited to indexed fields and either a a button can be placed in the portal row or all the fields in the portal row can be setup as a button that performs a script to take the ID number of the clicked portal row and enter it into the desired field.

          • 2. Re: Dual criteria filtered value list question

                 Thank you for the suggestion - I hadn't thought of approaching it that way.  I created another relationship between Adoptions and Animals which would always start with the full list (I did a global to global since the relationship actually does nothing but pull all records) and added the filter.


                 I'm going to do a little testing on performance however I don't see how it will be an issue as the record count will only be in the few thousands...