5 Replies Latest reply: May 24, 2013 8:41 AM by Andrew V RSS

    Duplicating a query across multiple fields

    Andrew V
      Hi

      We have a form for looking-up person information. It consists of a single-record block (PERSON) with fields like PERSON.PERSON_SURNAME and PERSON.PERSON_FORENAME. User's use the built in Enter Query and Execute Query buttons to find records. However, there are also fields such as PREVIOUS_SURNAME which users often don't bother to query which results in them creating duplicate records when they don't find the record they're looking for.

      I was wondering if there's a way to capture the query that the user has entered and extend it to other fields? For example, if they entered 'SMITH%' in the PERSON_SURNAME field, instead of the form creating the query:
      "select * from person where PERSON_SURNAME like 'SMITH%'"

      it would run:
      "select * from person where PERSON_SURNAME like 'SMITH%' or PREVIOUS_SURNAME like 'SMITH%'"

      Also, some users forget to include a % in their query so I was hoping there was a way to effectively add one to every query automatically.

      This would be fairly straight-forward if we used a custom query button but is there any way to do it while still using the built-in toolbar buttons?

      Thanks
        • 1. Re: Duplicating a query across multiple fields
          CraigB
          You would have to play around with it a little to make it work the way you want it too, but you can use the Module or Block level Key-EntQry or Key-ExeQry triggers to modify the Default Where clause of the block to include the extra criterea.

          Craig...
          • 2. Re: Duplicating a query across multiple fields
            InoL
            It's even easier I think to use the PRE_QUERY trigger here:
            :BLK.PERSON_SURNAME := :BLK.PERSON_SURNAME||'%';
            :BLK.PREVIOUS_SURNAME := :BLK.PERSON_SURNAME;
            • 3. Re: Duplicating a query across multiple fields
              Andrew V
              Thank you both for your replies. I tried Craig's suggestion and created this KEY-EXEQRY trigger:
              declare
                   xwhere   varchar2(2000);
              begin
              
              xwhere := null;
              if :PIN is null and :UPN is null then --User doesn't know specific details so do fuzzy search
                   if :PUPIL_SURNAME is not null then
                        :PUPIL_SURNAME := :PUPIL_SURNAME||'%';
                        xwhere := '(pupil_surname like '''||:PUPIL_SURNAME||''' or prevname like '''||:PUPIL_SURNAME||''')';
                   end if;
                   if xwhere is not null and :PUPIL_FORENAMES is not null then
                        xwhere := xwhere||' and ';
                   end if;
                   if :PUPIL_FORENAMES is not null then
                        :PUPIL_FORENAMES := :PUPIL_FORENAMES||'%';
                        xwhere := xwhere|| '(pupil_forenames like '''||:PUPIL_FORENAMES||''' or preffname like '''||:PUPIL_FORENAMES||''')';
                   end if;
                   if xwhere is not null and :PUPIL_DATE_OF_BIRTH is not null then
                        xwhere := xwhere||' and ';
                   end if;
                   if :PUPIL_DATE_OF_BIRTH is not null then
                        xwhere := xwhere||'pupil_date_of_birth = '||:PUPIL_DATE_OF_BIRTH;
                   end if;
                   SET_BLOCK_PROPERTY ('PUPIL',DEFAULT_WHERE,xwhere);
              end if;
              
              execute_query;
              end;
              The problem is, it seems to be disregarding the SET_BLOCK_PROPERTY function or overwriting it after the trigger. Adding the % seems to work (line 7) because I'm literally changing the contents of the field but the other fields aren't being queried. I don't believe simply setting
              :PUPIL_SURNAME := :PUPIL_SURNAME||'%';
              :PUPIL_PREVIOUS_SURNAME := :PUPIL_SURNAME;
              would work because forms will treat this as and AND query and I need it to be an OR.

              Any other suggestsions?

              Thanks

              Edited by: Andrew V on May 24, 2013 11:05 AM
              • 4. Re: Duplicating a query across multiple fields
                InoL
                forms will treat this as and AND query and I need it to be an OR.
                You are right, for got about that.
                That is actually the same reason why you shouldn't do this:
                :PUPIL_SURNAME := :PUPIL_SURNAME||'%';
                etc. with the other fields.
                You are giving the fields a value, basically the same as what I suggested in the PRE_QUERY. So, you are making an AND where clause again. This is the functionality of the default where:
                "These clauses are automatically *appended* to the SELECT statement that Form Builder constructs".
                Try this:
                :PUPIL_SURNAME := :PUPIL_SURNAME||'%';
                xwhere := '(pupil_surname like '''||:PUPIL_SURNAME||''' or prevname like '''||:PUPIL_SURNAME||''')';
                :PUPIL_SURNAME := null;
                ... and the rest too...
                • 5. Re: Duplicating a query across multiple fields
                  Andrew V
                  That did it! Thanks very much for your help.