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.
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.
Do you mind to share how to configure the default value for field that is "Do not link to property" in the property page?
ReplyDeletewhich mean it is just a temp field used in query form
Hi, This is a very valid use case. have you got any breakthrough on this...?
DeleteI could no do it work...
ReplyDeleteattributeA='$value(attributeA)') does not work with me... Any one know ?
ReplyDeletewhat about using repeating attributes in "$value(attr)"?
ReplyDeleteSolved using $repeatingvalue(attr)
DeleteThis 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.
ReplyDeleteselect * 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
Hello, please give me example how to use default values for date attributes in case mentioned here:
ReplyDeleteBut 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.