JHeadstart – Querying a Master for its Details – for example: search Departments with at least one Clerk

Lucas Jellema
0 0
Read Time:1 Minute, 44 Second

JHeadstart can generate a number of powerful search facilities, such as the Quick Search widget and Advanced Search area. It supports several different serach operators such as contains, like, smaller and greater than, between, begins with, ends with etc. The Search Items can have associated select-lists , radio-groups and lists of values. All ViewObject attributes that are queryable can be selected as Search Fields in the generated application.

In one of my applications, the need arose to be able to search records that had details conforming to some search criterium. For example: search Departments that had at least one CLERK child record. Or in my case, Companies that had someone called something like ‘Brow’ working for them. Although it cannot be generated – as far as I can tell – we can come very close and only a very small manual step is required to implement this functionaliy. In this article I will explain how it can be done.

First of all, add the following conditiion to the View Object you want to query one:

and ( nvl(:1,'null') = 'null' or exists ( select 1 from emp e where e.deptno = dept.deptno and lower(ename) like  lower('%'||:2 ||'%'))

Secondly, add two Query Bind Parameters to Group definition in the Application Structure File:

${param.EmployeeName},${param.EmployeeName}

Make sure that the Group has an Advanced Search area, by setting the Group property Advanced Search to either samePage or separatePage.

Finally, after generating the application, we need to apply this post-generation step in the Advanced Search area:

add a messageTextInput element like this:

<messageTextInput id="EmployeeName" name="EmployeeName" text="${param.EmployeeName}" promptAndAccessKey="&amp;Employee Name"

also add the new search item to the list of items to ignore when it comes to the warning about changed fields:

<script text="addToIgnoreChangedFields(['FindDepartments1Deptno','FindDepartments1Dname','FindDepartments1Loc','EmployeeName',]);"/>

 

Now run the application.

 

 

 

In this example, I have searched for all Departments that contain an Employee whose name contains the string ARK. Department 10 is returned, because it contains Mr. CLARK.

 

 

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %
Next Post

Improve your Web Forms deployment using the Java JDAPI library

Did you (or one of your customers) ever encounter ORA-00942 (table or view does not exist) while working in a (Web) form? No, then you can skip this post.   Related posts: Eating your own dogfood – use of Oracle Development tools within the Oracle Applications development group Extend the […]
%d bloggers like this: