Saturday, November 8, 2008

ADF Query Component and View Criteria Functionality with Custom Query Listener in 11g

Nice new component in ADF 11g is ADF Query - af:query. This component allows to implement Search functionality in your applications with much smaller effort comparing to Search functionality implementation in 10g. In conjunction with View Criteria, ADF Query provides declarative way for Query Criteria forms development. There is no need anymore to have transient attributes for Query Criteria, since we can base it on View Criteria attributes from View object. In this post I will describe how you can implement user-friendly Search functionality in 11g and how to use custom QueryListener for ADF Query.

You can download developed application - ADFSearchApp.zip. This application provides sample page with ADF Query component and results table. Additionally, this application contains custom behavior - when there are pending changes in results table, re-query is not performed and user is forced to commit or rollback existing pending changes.

Good news about ADF Query - it can use List Of Values defined in Model layer. So, if we have List Of Values declared for DepartmentId attribute:


DepartmentId in ADF Query automatically will appear as List Of Values, since this attribute is declared in View Criteria on View object:


Sample page contains two main components - af:query and af:table with ID = resTab. First component implements ADF Query and second - table for results:


If you will select af:query component in Structure view, Property Inspector will show you all properties of af:query. You should pay attention to ResultComponentId property, its Id that points to component where Search results will be shown. Most important thing in my sample application - custom QueryListener processQuery(). I have created it custom, because I want to control query execution and not to execute query when there are pending changes in results table:


However, custom QueryListener declaration is not enough in order to control query execution regarding pending changes. Additionally, you need to set PartialTrigger on results table and point to ADF Query. This is needed in order to auto submit results table and inform Application Module about pending changes before executing query:


Created custom QueryListener is simple - if there are pending changed, query is not invoked, if there is no - standard QueryListener is invoked. Information about pending changes is retrieved from Application Module, using isDirty() function:


On runtime, you will see such screen - Query Criteria area and results table:


DepartmentId List Of Values is shown, you can use it to provide search criteria parameter:


Results table is populated and if user will decide to change for example HireDate column value:


And re-execute query with new query criteria for DepartmentId:


There will be pending changes message shown, since user didnt saved his/her last changes before re-executing query. When user will save pending changes and will query again, it will be successful:

at

74 comments:

  1. ChandraNovember 24, 2008 at 7:27 AM

    Hi Andrejus
    Thanks for all the blogs. Great help. I have a unique requirement.

    I have 2 tables

    Table1:

    Attribute LOV


    Table 2:


    Attribute Values LOV


    Attrbute LOV displays attributes like City, State, Country, Product etc., This is not a finite list. The list can expand.

    Table 2 displays the values in LOVs

    If I chose City in Table 1

    I get the LOV to display City values

    I could add more rows in table 2 and choose more City values

    If I add another row in Table 1

    and choose a different Attribute say Country

    The Table 2 is PPR'ed with LOVs popuplated with Country LOV.

    The SQLs for all these attributes

    are stored in a different table with Attribute Id

    I need to dynamically bind the SQL from this table. How do I get this??

    ReplyDelete
  2. Vikram KohliDecember 5, 2008 at 10:53 AM

    Hi Andrejus,

    I am facing a problem related to result table in search page. Here is the description:-

    http://forums.oracle.com/forums/thread.jspa?messageID=3156298&tstart=0

    Please help to solve this.

    ReplyDelete
  3. VinithaDecember 30, 2008 at 12:38 PM

    Hi Andrejus,
    The blog was really too good is there any other alternative to perform the search using find and execute buttons.
    I am working on JDeveloper studio edition 11.1.1.0.1 , creating an ADF Search form using the steps followed in this link...

    http://download.oracle.com/docs/html/B25947_01/web_search_bc003.htm#sthref1796

    Search form works in 11g TP3 and TP4 but not in this new release .
    can you help me with a solution attaining the same search form with find and execute buttons.

    regards

    vinitha

    ReplyDelete
  4. Andrej BaranovskijDecember 30, 2008 at 12:54 PM

    Hi,

    You can check my previous blog post - http://andrejusb.blogspot.com/2008/09/jdeveloper-11g-crud-in-adf-form.html

    Regards,
    Andrej

    ReplyDelete
  5. AnonymousFebruary 27, 2009 at 8:41 PM

    Hi,
    I want to execute a view criteria in addtion to the view criteria used for search every time the search button is clicked. How can i use query listener for the same?

    ReplyDelete
  6. Andrej BaranovskijFebruary 28, 2009 at 2:49 PM

    Hi,

    You can use setApplyViewCriteriaNames(java.lang.String[] names) method to execute multiple criterias at once.

    Regards,
    Andrej

    ReplyDelete
  7. AnonymousAugust 15, 2009 at 1:13 AM

    Hi Andrejus,

    Thanks for sharing this information, this blog is really useful.

    We have a page which uses the Query Panel in the same way you have defined, however anytime we select a given View Criteria, it automatically executes the ViewObjects executeQuery() method.

    I want to suppress this functionality and want the query panel to fetch data only when the Search button is pressed.

    Do you think it is possible?

    Thanks,
    Makrand

    ReplyDelete
  8. Andrej BaranovskijAugust 15, 2009 at 12:24 PM

    Hi Makrand,

    There is an option to disable automatic query execution, its defined on View Object level. Have you set it?

    Regards,
    Andrejus

    ReplyDelete
  9. Makrand PareAugust 19, 2009 at 2:28 AM

    Hi Andrejus,

    Thanks a lot for pointing the solution. Infact I looked at one of your another BLOGS

    http://andrejusb.blogspot.com/2009/02/improving-performance-in-adf.html

    and unchecked the
    Query Automatically property of the View Criteria for the ViewObject and got it work.

    Thanks,
    Makrand

    ReplyDelete
  10. svSeptember 11, 2009 at 1:39 PM

    I have an job LOV column in the table. I have a department drop down above the table.The lov values should change based on the department value selected in the drop down. I created the view link,but still im getting all the values in the job LOV.

    ReplyDelete
  11. JesseNovember 12, 2009 at 8:41 PM

    Hi Andrejus,

    How do I allow the user to select the operators for each attribute in the query component? In your view criteria, you are declaring the operator (= or startswith), but I would like to leave this up to the user. Is this possible using a view criteria, or do I have to setup custom QueryModel and Query Descriptor classes?

    Regards,
    Jesse

    ReplyDelete
  12. KarenFebruary 3, 2010 at 11:32 PM

    What is the alternative to af:query if you want to search an updatable table? The 11g handbook indicates that this creates r/o tables which I verified.

    Thanks, Karen

    ReplyDelete
  13. Andrej BaranovskijFebruary 4, 2010 at 7:21 AM

    Hi,

    By default yes, but you can just drag and drop editable table as result table and it will work the same.

    Regards,
    Andrejus

    ReplyDelete
  14. AnonymousFebruary 16, 2010 at 6:12 AM

    hi,
    In one of the application I am working on, there is an af:query implemented.af:query is embedded in a popup(af:dialog) , there are 2 saved searches listed in the dropdown. With the default selected saved search SS1 in dropdown, when clicked on Search button, some results displayed.
    Then selected the other savedsearch SS2, where (bindvar)input xxxx is required, on search gave error, input is required. gave a value in xxxx, on search no results. Removed this value in input field xxxx and then selected back the first saved search SS1which was previously defaulted in dropdown on pageload.

    Error thrown complaining about input filed xxxx is required when I have navigated to a different savedsearch SS1, even after going to SS2 & keying some value into xxxx , then coming back to SS1 & doing a search will throw the same error and cant continue with any actions further. User has to close the popup ...

    on toggling the saved search ,how can I avoid this .. please help


    thanks
    Sowjanya

    ReplyDelete
  15. Andrej BaranovskijFebruary 16, 2010 at 8:04 AM

    Hi Sowjanya,

    May be you set as required, Bind Variable itself. You should not do this. You should set as required Query item, but not Bind Variable.

    Regards,
    Andrejus

    ReplyDelete
  16. mexieandcoMarch 23, 2010 at 4:19 PM

    Hi, Andrejus. Your blog entries are great -- keep it up! I'm currently going through the integration and region integration examples.

    I have a yes/no field that I created a "button" binding for. It works fine, even in a table. However, I want to have the checkbox in a search form (af:query) connected to that table, and although it does display as a checkbox, it doesn't function properly as a search criterion. Is there a way to make that work?

    Thanks for any help you can provide.

    Larry

    ReplyDelete
  17. Andrej BaranovskijMarch 23, 2010 at 7:36 PM

    Hi,

    I will take a look into this.

    Regards,
    Andrejus

    ReplyDelete
  18. AnonymousMarch 31, 2010 at 12:16 AM

    Hi,

    I have a bunch of pre-defined system queries and each one of those has different fields, but each one also has the same field. I need to get this field/value from whatever query the user is running. How can we do this? For example, I have 5 saved ViewCriterias and on each one there is a field called ProcessType and I need to get the value for that field (e.g. can be "Process" or "Validate") in my backing bean.

    I can get as far as finding the search field (ProcessType), but I can't get the value from it...

    QueryModel varQueryModel = getQueryHeader().getModel();
    List attributeDescriptors = varQueryModel.getAttributes();
    Iterator itr = attributeDescriptors.iterator();
    while(itr.hasNext()){
    AttributeDescriptor attributeDescriptor = (AttributeDescriptor)itr.next();
    String attributeName = attributeDescriptor.getName();
    if(attributeName.equals("ProcessType")){ get the Value?? }

    Any suggestions? Thanks.

    ReplyDelete
  19. harikiranOctober 8, 2010 at 3:05 PM

    I'm trying to get the values entered in the queryPanel... As mentioned, I can get the handle of the UIXComponent of the field, but not the entered data.. As a work around, I'm iterating over the QueryIterator and picking the field i wanted from first row. (b'coz this fields are results of the query execution. This workaround only works for LOVs/PickLists). But, i'm really looking for a way to add my custom fields (binded to BackBean) into query panel and refer to same for my purpose later in the code. Is it possible?

    Thanks,
    Harikiran.

    ReplyDelete
  20. Ganesh ParthasarathiNovember 5, 2010 at 3:58 PM

    Hi, i'm having 2 issues with af:query.

    1. The auto refresh of search fields.
    Here is what i did...

    1. In a view, few attributes are in view criteria, which is used in af:query.
    2. The attributes used in the view criteria are defined as LOV accessed via view-link from a separate view to display distinct values in the LOV.
    3. The LOV's VO is set to auto refresh.
    4. DB user given the permission.
    5.The attribute is set as Key attribute.
    6. In the GUI, if i click the Reset button of the search panel, then the new records are inserted in the table are visible in the LOV.

    *But i want the LOV of the search field to be updated automatically or while accessing the LOV... *
    Documentation says how to set the auto refresh. But i didn't get any clue , when and how the lov will be updated.


    Issue # 2.

    I'm using VO's readXML to update the VO from an xml file. If i enable the auto refresh , then this readXML of the VO throws error "ORA-29983: Unsupported query for Continuous Query Notification "

    The acutal query in VO is "SELECT DISTINCT XXXX FROM YYYY"

    But the run time query by optimizer is

    "SELECT * FROM (SELECT DISTINCT XXXX FROM YYYY) QRSLT WHERE ( ( (XXXX LIKE :vc_temp_1 ) ) )"


    Could you please help me to solve this. Thanks .

    ReplyDelete
  21. Ganesh ParthasarathiNovember 5, 2010 at 10:56 PM

    Could you help me to call Reset operation of af:query , programaticly. Thanks .

    ReplyDelete
  22. Joe D GDecember 23, 2010 at 2:03 AM

    I have 3 issues with af:query:

    1. Refresh button clears af:query fields but not the table that displays the [last set of] results? This seems odd to me (and my user). We think refresh should clear the page.

    2. In my popup lov triggered from the af:query the column width of the content displayed is not wide enough. I have tried setting display width on: the lov attribute itself, the attribute that is taking the LOV in the VO (which is part of the viewcriteria) and the viwcriteria bind vars. Nothing seems to fix this. I have been told this is change din next release (11.1.1.4) but until then, any ideas?

    3. How do I get access to the ui components and their events in the af:query? My client wants the query to execute when the last query field in af: query is populated (too lazy to press search button or press enter it would seem) and they want the whole form cleared when they click into the first af: query field to start a new query. Refresh seemingly would solve this if it did what wee expected (see #1)

    Excellent posts, Andrejus, nicely put together, thank you.

    ReplyDelete
  23. Andrej BaranovskijDecember 23, 2010 at 1:07 PM

    Hi,

    1) There is no Refresh button, probably you mean Reset. In order results to be cleaned automatically, you need to set Query to be auto-executable.

    2) PS3 is coming very soon, not much to wait

    3) Yes, such functionality would be cool. For now its not available, but I'm sure Oracle will implement it and enhancement request.

    Thanks...

    Andrejus

    ReplyDelete
  24. Joe D GDecember 23, 2010 at 6:08 PM

    1) There is no Refresh button, probably you mean Reset. In order results to be cleaned automatically, you need to set Query to be auto-executable.

    Yes, reset, thank you.

    Not sure where to set query to auto-executable. if it set it on the view criteria itself, then the page auto queries with no search criteria when it opens (all rows returned).
    Reset does clear the search but then the page queries again.

    ReplyDelete
  25. Andrej BaranovskijDecember 23, 2010 at 6:10 PM

    Hi,

    Reset must clear search block as well.

    In order to prevent results population on page load, there can be workarounds. I will post something in near future.

    Andrejus

    ReplyDelete
  26. Joe D GDecember 23, 2010 at 9:56 PM

    exactly, it does clear the search block as well...then an "open search" is executed. If you can post that would help.
    thanks you and happy holidays.

    ReplyDelete
  27. Andrej BaranovskijDecember 27, 2010 at 4:33 PM

    Hi Joe,

    Actually, I already had blog post on this topic - http://andrejusb.blogspot.com/2009/09/programmatical-reset-for-query-results.html

    Hope this will help.

    Andrejus

    ReplyDelete
  28. Joe D GDecember 29, 2010 at 10:03 PM

    perfect, thank you!

    ReplyDelete
  29. Andrej BaranovskijJanuary 1, 2011 at 11:09 AM

    Hi,

    Also there is a way to execute your query, when user is typing search parameter. See recent post from Steve Muench - http://blogs.oracle.com/smuenchadf/2010/12/conditionally_auto-executing_a.html

    Regards,
    Andrejus

    ReplyDelete
  30. Joe D GJanuary 3, 2011 at 6:43 AM

    thanks - Steve's post was prompted by my question! Works great.

    ReplyDelete
  31. Andrej BaranovskijJanuary 3, 2011 at 9:59 AM

    As always - Oracle is great ! :)

    Andrejus

    ReplyDelete
  32. AnonymousMay 9, 2011 at 12:02 PM

    Hi Andrejus,

    Let's say I have 3 VOs with several LoVs defined on each of them, actually the View Accessors defined on each VO are based on the same set of VOs (e.g. each VO has attributes like RegionId, CountryId, etc) ... What I need in the UI is ONLY one search criteria section targeting 3 different tables (a table per VO).
    How could be achieved such a behavior? Do you have any idea?

    Thanks,
    Livakis

    ReplyDelete
  33. Andrej BaranovskijMay 9, 2011 at 1:59 PM

    Hi,

    If you was to use single ViewCriteria, you need to join all required attributes into View Object and then you will be able to define one ViewCriteria for all these attributes.

    Andrejus

    ReplyDelete
  34. AnonymousMay 10, 2011 at 9:39 AM

    Hi Andrejus,

    I guess I was not very clear... so, there are 3 different VOs and I could create 3 different jsp pages with an element 'triggering' an on each of these pages.
    The problem is, I need to have only one page (not 3 pages) with 1 'triggering' 3 elements.
    I hope now I explained it better.

    Thanks,
    Livakis

    ReplyDelete
  35. Andrej BaranovskijMay 10, 2011 at 2:22 PM

    You can't merge ViewCriterias from different VO's into one single ViewCriteria.

    Andrejus

    ReplyDelete
  36. Sumit YadavMay 26, 2011 at 5:13 PM

    Hi Andrejus,
    I have one requirement,wanted your advise on it. It is with respect to Search page built with a view Criteria.
    I enter the user_name in the query panel and hit Search and the result panel shows the results.

    Now If I gave a user_name which does not have any record in the VO, no data is displayed. At this point I want to capture the user_name from the query panel and on the click of a button ("Add" button I have created for Adding Records if not present) want to navigate to a different Add page .

    So how to capture the user_name from the query panel. I am using Jdev 11.1.1.4.0

    Thanks
    Sumit Yadav

    ReplyDelete
  37. LisaJune 29, 2011 at 12:54 PM

    Hi Andrejus

    If you have advice on the following I would most appreciate it.

    1. If using an input text with list of values in a query component (too many items to use a choice list, such as thousands of employees), is there a way to display name rather than code once the selection has been made from the list of values? More friendly and would confirm to the user that the correction selection has been made.

    2. In an application with one search page (query and results tree) and one edit page (navigate to the edit page from clicking a row in the table on the search page), when the user returns to the search page how can I have the query and table still showing what they were showing previously. I would like the contents of the query component to be unchanged and the results table to still be there and the current row to be unchanged.

    thank you for your time
    regards
    Lisa

    ReplyDelete
  38. LisaJuly 1, 2011 at 10:54 AM

    Replying to my own post.

    The second item in my query I have resolved. To get the property TrackQueryPerformed to have an effect, and keep the contents of the query when returning to that screen, I had to change my design so that search and edit fragments were part of the same flow rather than two separate flows. Works as I want it to now.

    ReplyDelete
  39. Andrej BaranovskijJuly 1, 2011 at 2:50 PM

    Great. I'm now on ODTUG conference, so didnt had time to advice :)

    Andrejus

    ReplyDelete
  40. karnniteshJuly 8, 2011 at 4:36 PM

    Hi Andrejus,
    I am using the saved search functionality and using it to save results layout. It works fine.
    However when I navigate to a different task flow from the search ui and cancel hence return on the search ui. The ui is refreshed and the results fetched but the layout is not as per the custom saved search criteria while it is selected in the saved search list.
    Any suggestion to retain the saved layout as associated with the custom saved search critera.
    Thanks,
    Nitesh

    ReplyDelete
  41. AdrianAugust 2, 2011 at 4:11 PM

    is there a way to enable table filtering without connecting to a database a build a datacontrol ? I want to enable it for a table with some sample data ?

    ReplyDelete
  42. Andrej BaranovskijAugust 2, 2011 at 4:35 PM

    You can use static VO probably with dummy data.

    Andrejus

    ReplyDelete
  43. Assem SherifOctober 12, 2011 at 1:19 PM

    Dear Andrejus,

    I tried to change the width of the components in ViewCriteria, but it didn't changed ..
    I tried to change the width from its' attribute in ViewObject, also from bindVariable, but No changes happened to it ..

    Thanks for your help,

    ReplyDelete
  44. Siva SankarDecember 9, 2011 at 1:46 PM

    Hi Andrejus
    Thanks for all the blogs. Great help
    i have some requirement in query panel i created query panel with table in query panel by default one of DataBase field as Timestamps while drag from named query its displaying simple date with popup for that filed i need time stamp which mean Date-time-am/pm. if i use date formatted in VO its giving some AD like formats if i wnat to date asking like in b2b reports section with timezone please explain to achive that ...

    ReplyDelete
  45. AnonymousMarch 12, 2012 at 7:40 AM

    Hello Andrejus, Your help is very much reqd. Pls help.

    In our AF:Query component, We have two LOVS. One dependent of the other one values selected. Any ideas how the data can be passed. It wud be simpler to use listener in Non-AFQuery way to set the Bind Variables but not able to find solution in AF:Query.

    Your help wud be much appreciated. Thanks.
    Sachin

    ReplyDelete
  46. AnonymousMay 2, 2012 at 7:37 AM

    Hi Andrejus,
    I have a field in the database as clientid which is of the type varchar2. This field is exposed as a field in the query panel in the front end.

    The problem is that the client wants the search option "between" (found in the advanced option of the query pane) to work like a number.
    That is if the client searches for a value between 5 and 7 , it should not return values such as 51,52,5232 etc . (which is actually the correct behaviour for varchar fields)

    But the client does not want the field to be changed to number instead of varchar2 (since he wants to enter alphabet characters in the future as well).

    So is there a way to make the search functionality to work like numbers instead of characters for the field clientid.

    J dev version Studio Edition Version 11.1.1.4.0
    Thanks
    Sameera

    ReplyDelete
  47. Yakup YÜCEMay 3, 2012 at 3:09 PM

    Hi Andrejus,
    I created a ADF query and result table from LOV.
    I wonder how can I listen to query and make a popup if query returns 0 rows.

    Thx

    ReplyDelete
  48. Tripuresh PandeyDecember 28, 2012 at 10:34 AM

    Hi Andrejus,

    I have a adf table based on list object from my bean which i show in the UI, is there a way to add query panel based on list object from the bean?

    ReplyDelete
  49. Prakash GMarch 11, 2013 at 3:19 PM

    Hi Andrejus,
    Can you please elaborate why we should not perform search when there are pending changes in the table?
    Should we not perform QBE also then?
    Is it inconvenient or entirely impossible?
    I have a requirement in which we need to perform qbe on a table which contains uncommitted data but we are facing problems if we try to save whilst results are filtered.

    Thanks.

    ReplyDelete
  50. Andrej BaranovskijMarch 11, 2013 at 6:00 PM

    Hi,

    Mainly because transaction will remain dirty, and when next time user press Commit - it may cause unexpected results of data being changed which is not currently on the screen.

    Technically its possible, but it might be risky to make data dirty.

    Andrejus

    ReplyDelete
  51. PrakashMarch 15, 2013 at 11:02 AM

    Thanks for reply. I will post my problem in case you might be knowing something useful about that.

    In our case we need to have QBE enabled on a af:table in which we can insert rows. Suppose I have 3 uncommitted rows and 2 committed rows and now I filter the table using some value then the filter results are correct (and say 1 uncommitted row is being shown) but when i clear the filter the I won't get back all the uncommitted rows in the table. So now I will only have the 2 committed rows and that 1 filtered uncommitted rows back. 2 uncommitted rows are lost. And even if i commit the changes to db only that 1 uncommitted row will be added to the DB. Any idea?

    ReplyDelete
  52. Andrej BaranovskijMarch 17, 2013 at 8:23 AM

    Quite interesting - I will test it as well and let you know. Which JDev version are you using? This might be ADF bug...

    Andrejus

    ReplyDelete
  53. Prakash GApril 1, 2013 at 12:44 PM

    Please do. Appreciate your help.
    I am using an internal build of Jdev v11.1.1.7.0
    Sorry for the late reply.

    ReplyDelete
  54. Prakash GApril 1, 2013 at 1:17 PM

    Another thing-
    You will have to use this command vo.setQueryMode(ViewObject.QUERY_MODE_SCAN_ENTITY_ROWS | ViewObject.QUERY_MODE_SCAN_DATABASE_TABLES) so that QBE starts working for uncommitted rows at all.

    ReplyDelete
  55. Prakash GApril 18, 2013 at 11:09 AM

    Hi,
    Were you able to test this?

    Thanks

    ReplyDelete
  56. Andrej BaranovskijApril 18, 2013 at 11:15 AM

    No time yet - busy in projects. Will do.

    Andrejus

    ReplyDelete
  57. PraksashMay 10, 2013 at 1:55 PM

    Thanks.
    Its an open issue for us. It got me curious. Please look whenever you have time.

    ReplyDelete
  58. UnknownAugust 22, 2013 at 11:45 AM

    Hi,
    What happen if I want to use af:Quickquery with customize named view criteria?

    ReplyDelete
  59. UnknownOctober 16, 2013 at 9:26 AM

    Is there any way of getting the view criteria query that is fired on the click of search button?

    ReplyDelete
  60. Andrej BaranovskijOctober 16, 2013 at 9:39 AM

    Yes, you can get it in VO Impl class.

    Andrejus

    ReplyDelete
  61. AnonymousMarch 23, 2014 at 8:15 AM

    Hi Andrejus,

    I know this is a very old post but i am stuck in a scenario of af:query.

    We have a requirement where we need to display say two LOVs in search criteria corresponding to same attribute. Our database table is something like follow:

    ColumnName -> Lookup_Type Lookup_Value
    Data -> BusinessUnit BU1
    BusinessUnit BU2
    ClientCode CC1
    ClientCode CC2

    So its a simple look up table where we can provide the lookup type and get the values. On top of this table we have created one query based view object "select Lookup_type, Lookup_Value from Lookup_table". Now the requirement is to display each lokup type as drop down with all lookup values in search criteria. Means in search criteria we should have two drop downs , one for businessUnit and one for ClientCode.

    What we tried is, we created LOV on Lookup_type attribute, created two bind variables with default values as BusinessUnit and ClientCode and created one view criteria. In view criteria we added two items,like, lookuptype =:pBusinessUnit and lookupType=:pClientCode. On dropping the view criteria on page, we can see two drop downs. But problem is they both are populated with all the values. BusinessUnit drop down is populated with all the values but BU1 as selected value and ClientCode is populated with all the values but CC1 as selected value. However they should be populated with there corresponding values.

    Please suggest if we can achieve this in single VO or we need to create separate VO for looup type, then create LOV in all the VO and drop them on the page.

    Using JDev11.1.1.6.
    Appreciate your help..
    Thanks

    ReplyDelete
  62. AnonymousDecember 27, 2014 at 6:50 PM

    Hi, we have a requirement where we have to handle ADF query component with custom search query in advance mode.
    Currently we have the query component in basic mode and the query clauses are constructed dynamically based on the entered search criteria values. We have a requirement recently to enable advanced mode for this adf query component. Could you please help in understanding how could we handle custom query in advance mode especially when a view criteria item is added multiple times.

    ReplyDelete
  63. Richard BurkMay 4, 2015 at 9:27 PM

    Andrejus, thanks for all of your blog postings! They have been very handy since I started with ADF programming Dec 2013. There is so much more to learn.

    My question: How do I default a mandatory query date field to the current day's date?

    I have been searching for a solution for a few days without any solutions that have actually worked. I am hoping for a declarative solution versus a programmatic solution. It seems intuitive to me that I should be able to provide SYSDATE as the value, but the only choices of operand provided by the Edit View Criteria window of the query is Literal Value and Bind Variable value. What I'd like to choose for Operand is SQL and value of SYSDATE, but that is not available.

    Any ideas? I will take a programmatic solution, but declarative is preferred.

    Thanks in advance for your time!

    Richard Burk

    ReplyDelete
  64. Andrej BaranovskijMay 5, 2015 at 6:45 AM

    Hi,

    Try to check this post: http://andrejusb.blogspot.com/2013/03/default-dynamic-value-for-adf-query.html

    Regards,
    Andrejus

    ReplyDelete
  65. Richard BurkMay 5, 2015 at 4:46 PM

    Thanks Andrejus, that got me further, but I am still running into the problem of converting apparently a groovy date to a oracle.jbo.domain.Timestamp. I have been trying different data types and different date formats to make it work, but nothing yet. It seems like date conversion is difficult when it comes to basically prepopulating an af:query search field of date/timestamp data type.

    ReplyDelete
  66. Richard BurkMay 5, 2015 at 8:28 PM

    Andrejus, I found a work-around. Even though I find oracle.jbo.domain.Timestamp in the hierarchy search, the xml file still shows java.sql.Timestamp which then causes the type conversion problem. So I updated the xml file from java.sql.Timestamp to oracle.jbo.domain.Timestamp and it worked. Now if I play around with it, I have to keep setting it back as jDeveloper wants to keep changing it on me. But once I set it and leave it alone it is fine.

    Unfortunately using the bind variable gave me a side effect that I wasn't expecting which was that the bind variable then showed up as one of the search parameters. In fact the variable that I wanted to pre-populate while it was populated with the Bind variable's value, the search form was using the value set on the bind variable rather than the value beside the date field that I had prepopulated. So I had to abandon this solution. Fortunately I have found another possible solution using CompOper directly in the xml code. I found it here: http://docs.oracle.com/cd/E25178_01/web.1111/b31974/web_search_bc.htm in section 27.3.3 How to Create Custom Operators or Remove Standard Operators. So far I have yet to get it to work, but I know that I am close. The db date field generates code or not depending upon what I place in the TransientExpression. Now I am testing converting SYSDATE to a timestamp using the TO_TIMESTAMP function to match the code that was generated during the last run when the code wrapped the REQUEST_DATE field with TO_TIMESTAMP and wrapped that in TO_CHAR when I just passed SYSDATE. Hopefully I will find a solution before I leave from work in about 3hrs.

    Thanks again!

    Richard

    ReplyDelete
  67. TokandoJuly 29, 2015 at 12:57 AM

    hi ...
    can i replace the department id like 60 with the matching name like Administration after the search ....
    Thank you

    ReplyDelete
  68. vimalJanuary 18, 2016 at 2:29 PM

    Hi,
    Nice post....
    I have a requirement to add cancel button at bottom for consistency.
    Is it possible to customize view criteria in search form so that we can have search,reset and one more cancel button.

    please let me know.

    ReplyDelete
  69. Atif DarJanuary 22, 2016 at 8:17 PM

    Hi,

    Is it possible to put more than one ids in resultcomponentid? i want to have a single af:query and with those search parameters i want to show result in more than one result tables. is it possible?

    actually we have 4 different views and all of them have three common fields and i want to built a single search for all four views.

    Regards
    Atif

    ReplyDelete
  70. annnnn101April 18, 2016 at 9:19 PM

    Im having problem with this specific situation everytime i click on the search for my rows it say "no data to display" need help

    ReplyDelete
  71. UnknownApril 26, 2016 at 3:20 PM

    Hi Andrejus,

    It's been a wonderful experience with ADF learning from your blog in this short span of 4 months started learning on ADF. I want the latest code for reset button action where it has to empty the inputs textboxes even after the when we take the cursor out of the fields.

    This code is certainly not working when we input some data and come out of the fields and click somewhere on the screen. then the reset functionality is not working.

    My requirement is not just bringing the search form. I am using execute with parameters in operations from the table, not queryable attributes. So, I will not get reset button with execute with parameters.

    if you can please give the solution this will be a great help.

    Best wishes,
    Pavan.

    ReplyDelete
  72. UnknownDecember 21, 2016 at 5:07 PM

    Hi Andrejus,
    I need a way to clear search parameters. Standard Reset not only clears but removes the parameters added in advanced mode and switches to a basic mode.

    In basic query mode, a custom button to clear bind parameters works, but in advanced mode this doesn't work.

    Any way to reset the values of search parameters, in advanced query mode?

    -Vishnu

    ReplyDelete
  73. UnknownFebruary 13, 2017 at 3:52 PM

    hi

    Have a use case where have to do the af:query 'Add Feilds' button does programmatically. Can do that by getting the current af:query view criteria and adding new rows to the VC.
    Like:
    ViewCriteriaRow vcRow = vc.createViewCriteriaRow();
    vcRow.setName("Service");
    ViewCriteriaItem viewObjectVCRowItem = vcRow.ensureCriteriaItem("Service");
    viewObjectVCRowItem.setValue("value");
    vc.addElement(vcRow);

    But is there any way we can get the currentRow(existing row) in the view criteria.So that can add the new fields as criteria item to the row. Is that possible?
    Any pointer on how the Add fields button behaves in the backend.

    Jeethu

    ReplyDelete
  74. SHEVINApril 18, 2019 at 4:17 PM

    If a view criteria has 2 items, say ID and DATE, then is it possible to make ID independent of DATE. ie, if only ID is entered then it should search with only ID but if DATE is entered, it should consider ID if it is not null? I'm using af:query here in my JSFF page.

    ReplyDelete