Forms builder search temlaplate DQL conditionals

Hello,

Is there any way to make conditions to generate a query on a Forms Builder Search Template?

ex:

I want to perform the folowing query:

select object_name, number, r_object_type,r_object_id, owner_name, subject, status
from my_custom_folder where number=$number$' AND UPPER(owner_name) like UPPER('$owner$')
AND UPPER(subject) LIKE UPPER('$subject$') AND r_creation_date > date('$data_inicial$','mm/dd/yyyy')
AND r_creation_date < DATEADD(day, 1, date('$data_final$','mm/dd/yyyy') )
AND r_object_id in (SELECT i_folder_id FROM my_custom_document SEARCH DOCUMENT CONTAINS ('$words$') )

but the last AND should only be present if the parameter $words$ were present. If it's empty the last line becomes AND r_object_id in (SELECT i_folder_id FROM my_custom_document) and it's very bad fo the query performance.

Tried to use a join like below, but dql does't alow mix the 'SEARCH DOCUMENTS" in a join with other type

select p.object_name, p.number, p.r_object_type,p.r_object_id, p.owner_name, p.subject, p.estado, p.localizacao_atual, p.r_creation_date,p.especificacao
from my_custom_doc d SEARCH DOCUMENT CONTAINS ('$words$'), my_custom_folder p
where p.number='$number'
AND UPPER(p.owner_name) like UPPER('$owner$')
AND d.i_folder_id=p.r_object_id
AND UPPER(p.assunto) LIKE UPPER('$subject$')
AND p.r_creation_date > date('$data_inicial$','mm/dd/yyyy')
AND p.r_creation_date < DATEADD(day, 1, date('$data_final$','mm/dd/yyyy') )

:[DM_QUERY2_E_JOIN_SEARCH]error: "Your query specifies both a fulltext query and a multi-type join."

What I need to do is seach all custom folders (dm_folder descendents) that has the specified properties AND contais documents that contains the specified words (full-text). But when the user does not specify full text words, the search should ignore the documents inside the folders.

any help would be appreciated

Find more posts tagged with

Comments

DCTM_Guru

As a workaround, you can always create two search templates/forms - one with text field for words$ and one without it.

Tord

Hi tiago,

What you are trying to do is not unfortunately not possible in Forms Builder. The "automatic elimination" eliminates up to the preceeding FROM keyword.

Just speculating now since I haven't tried this, but you could try something like this (but do check the performance implications before you deploy this):

SELECT object_name
       ,number
       ,r_object_type
       ,r_object_id
       ,owner_name 
       ,subject
       ,status
  FROM my_custom_folder 
 WHERE number=$number$' 
   AND UPPER(owner_name) LIKE UPPER('$owner$')
   AND UPPER(subject) LIKE UPPER('$subject$') 
   AND r_creation_date > DATE('$data_inicial$','mm/dd/yyyy')
   AND r_creation_date < DATEADD(day, 1, DATE('$data_final$','mm/dd/yyyy') )
   AND r_object_id in (
        SELECT i_folder_id 
          FROM dm_document 
         WHERE r_object_id = '0000000000000000' 
            OR SEARCH DOCUMENT CONTAINS ('$words$'))

Having said that, Johnny's suggestion is a good one and I wouldn't necessarily call it a workaround. This is the approach that I generally recommend, that is, that you analyze what type of queries you users usually run and from that derive a couple of different search scenarios that you classify into primary and secondary depending on their frequency. You then design targeted search screens for each scenario and design optimized queries for each.

My 2 cents,hope it helps.

Thanks

--Tord