2 Replies Latest reply on Nov 11, 2016 12:56 PM by erolst

    Two possible Values in one Field for Find Request

    goutam

      Hi,

       

      I am trying to do a find request which has two possible values for a single field. For eg, I am trying to query a table for records that have either  the values "Submitted" and "On hold" for the column Status and an other Id value for the other field _fkStudentId. The following image shows the script. But the result is having values that do not have the _fkStudentId. Is there something wrong with the script?

       

        • 1. Re: Two possible Values in one Field for Find Request
          philipHPG

          The New Record/Request starts a new, independent "or" request so essentially your search is for:

           

          ( tblHours::Status = "Submitted" ) OR (tblHours::Status = "On hold" AND tblHours::_fkStudentID = ...)

           

          Hopefully you can see that the _fkStudentID applies to only the second request.

           

          If you want the _fkStudentID to apply to both parts of the request you need to have it before and after the New Record/Request script step. (In some cases where I have multiple AND conditions that are combined with multiple OR condition, I use Duplicate Record/Request so that I don't have to repeat all the AND conditions.)

          • 2. Re: Two possible Values in one Field for Find Request
            erolst

            philipHPG wrote:

            [ ... ] so essentially your search is for

            ( tblHours::Status = "Submitted" ) OR (tblHours::Status = "On hold" AND tblHours::_fkStudentID = ...)

            Yes; what goutam wants is to find all records where this compound expression is true ...

             

            tblHours::_fkStudentID = ... AND ( tblHours::Status = "Submitted" OR tblHours::Status = "On hold" )

             

            .. and that means that the id must be included in both queries.

             

            Depending on other factors, it could even be preferable to do this (pseudo-code):

             

            find by id

            if found records

            constrain by Submitted OR On Hold

             

            This would especially be useful in terms of performance if id was stored, but status was not. Otherwise, it could make the script shorter, especially if there are many OR criteria.

             

            OTOH, premature optimisation is the root of all evil ...

            1 of 1 people found this helpful