Friday, March 22, 2013

D2 Query Forms - add dynamic filters to the DQL

D2 client version 4.0 does not include an advanced search, only quicksearch and query forms being available. (Advanced search was added in version 4.1).
Query forms however are not very powerful, because you must set a static DQL query based on values entered or selected on the associated property page.
If we define a property sheet with 1-2 attributes which are always populated, the DQL is quite trivial.
But what if we want a more generic query form, with multiple fields (filters) which are optional? How to specify in a static DQL which filters should be used (and skip the empty ones)?
You can do that by using a query like this:
select * from custom_type where ('$value(attributeA)'='' or attributeA='$value(attributeA)') and ('$value(attributeB)'='' or attributeB='$value(attributeB)')...and ('$value(attributeN)'='' or attributeN='$value(attributeN)')

So if attributeA field from the propertysheet is empty, the clause '$value(attributeA)'='' will return true, the second part being meaningless; if it's not empty, the second part will be evaluated filtering results by the value entered for the attribute.
In a simmilar way, you can also use other operators: >, <, like, etc. (but keeping unchanged the first part - check for empty string).

Now let's see what happens with Date fields. We must convert the string value into a Date:
('$value(attrDate)'='' or attrDate>date('$value(attrDate)','mm/dd/yyyy'))

But if the string is empty, the resulting DQL clause will be:
(''='' or attrDate>date('','mm/dd/yyyy'))
and this will fail because empty string can't be converted into a Date.
There's a workaround for that: define and use default values (for example: $TODAY, $NOW) for Date attributes.

8 comments:

  1. Do you mind to share how to configure the default value for field that is "Do not link to property" in the property page?
    which mean it is just a temp field used in query form

    ReplyDelete
    Replies
    1. Hi, This is a very valid use case. have you got any breakthrough on this...?

      Delete
  2. attributeA='$value(attributeA)') does not work with me... Any one know ?

    ReplyDelete
  3. what about using repeating attributes in "$value(attr)"?

    ReplyDelete
  4. This syntax doesnt work for me... Only when i give value for all attributes, then only it is resulting. If i provided value for one attribute ,it doesnt result anything for me.

    select * from custom_type where ('$value(attributeA)'='' or attributeA='$value(attributeA)') and ('$value(attributeB)'='' or attributeB='$value(attributeB)')...and ('$value(attributeN)'='' or attributeN='$value(attributeN)')


    select r_object_id,object_name,a_content_type from claims where ('$value(dcn)'= ' ' or dcn='$value(dcn)') and ('$value(doc_type)'=' ' or doc_type='$value(doc_type)') and ('$value(batch_name)'= ' ' or batch_name='$value(batch_name)')

    Please tell me what is wrong in this query

    ReplyDelete
  5. Hello, please give me example how to use default values for date attributes in case mentioned here:

    But if the string is empty, the resulting DQL clause will be:
    (''='' or attrDate>date('','mm/dd/yyyy'))
    and this will fail because empty string can't be converted into a Date.
    There's a workaround for that: define and use default values (for example: $TODAY, $NOW) for Date attributes.

    ReplyDelete