Filter lookup field by multiple criteria

Filter lookup field by multiple criteria

Hi Everyone,

I am setting up an appointment form that allows the user to select an interpreter, based on several criteria (see screen shot).



The interpreter lookup field should filter the list by the following criteria:

  • Language
  • Method
  • Availability (Start Time & End Time)

It is quite easy to restrict the interpreters by a single criterion, such as language, as I simply add script to the User Input of the Language field (see code below):


//Requery the AsscoLang combo box to show only those interpreters that speak the requested language
if (input.cboLanguage  !=  "-Select-")
{
    if (count(frmAssocLang[cboLanguage == input.cboLanguage])  !=  0)
    {
        for each varLangList in frmAssocLang  [cboLanguage == input.cboLanguage]
        {
            cboAssociate:ui.add(varLangList.cboAssocName);
        }
    }
    else
    {
        clear cboAssociate;
    }
}
















             


The problem is how do I further restrict it by the other variables.

Note: I've tried to set this database up using proper normalization:

tblInterpreter (InterpID, Name, Addres, etc.)
tblInterpLang (ID, InterpID, Language)
tblInterpMethod(ID, InterpID, Method)
tblEvent (ID, InterpID, Method, Language, Date/Time, etc.)

So my question is: Can I restrict the interpreters in the lookup field to those that match all the required criteria, even though the data is in multiple tables?

Thanks