Home » Developer & Programmer » Forms » How to query two data blocks simultaneously? (Forms 10g Windows)
icon5.gif  How to query two data blocks simultaneously? [message #379862] Thu, 08 January 2009 03:15 Go to next message
amit_yadav
Messages: 11
Registered: January 2009
Location: India
Junior Member
Dear all,

Need your inputs on the following problem statement...

Existing Functionality:
We have two data blocks A (Parent) and B (Child) in a relationship. Both data blocks refer to different tables.
Data block A is the query data block and for a single record in block A we can have multiple records in block B. Multiple records in block B are shown one at a time after executing a query, this is implemented by using a list item C (belongs to a third block).

For example
Let us say block B can have 5 different types of entries for each record in block A. In this case the list item C will have 5 values corresponding to these entries. Once the query is executed, block B will show the default entry for the queried record in block A. To see other entries for the record user can select the required value in list item C and corresponding entry in block B will be fetched.

Requirement:
Our requirement now is to enable querying upon both data blocks A and B simultaneously. To implement this I replaced data blocks A and B with a single data block X which is based on the join of the two tables referred by the Data blocks A and B earlier. As the fields should allow insert, update, delete operations we did not use a view here.

Issue:
Now the problem is, each record in block A for which block B had multiple entries earlier is now getting listed multiple times because of the join.

I have tried but could not find any solution for this. I would appreciate if any of you can suggest how we can query both blocks simultaneously without affecting the existing functionality.

Thanks,
Amit
Re: How to query two data blocks simultaneously? [message #379866 is a reply to message #379862] Thu, 08 January 2009 03:23 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Remove list item C and let Forms fetch all detail records in the B block for their master record in block A. This is, after all, Forms' default behaviour.

If you still need list item C, use it "on request", not "by default".
Re: How to query two data blocks simultaneously? [message #379883 is a reply to message #379866] Thu, 08 January 2009 04:24 Go to previous messageGo to next message
amit_yadav
Messages: 11
Registered: January 2009
Location: India
Junior Member
Thanks Littlefoot.

Block B here (as well as block A) is in Form Layout (not Tabular). Due to this design constraint List item C is used to view various detail records for a particular master record. Now we can not make block B as tabular and list all detail records at once as this is an already existing application and we do not have much liberty to change the look and feel.

Thanks,
Amit
Re: How to query two data blocks simultaneously? [message #380033 is a reply to message #379883] Thu, 08 January 2009 21:19 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Put a 'distinct' in your view. If you still have duplicate records, then reduce the number of items in the 'select' until you just have the 'key' fields.

You do not need to have a block defined to handle list C, just use a 'record_group'.

David
Re: How to query two data blocks simultaneously? [message #380273 is a reply to message #380033] Sat, 10 January 2009 00:07 Go to previous message
amit_yadav
Messages: 11
Registered: January 2009
Location: India
Junior Member
Thanks David.

As I mentioned earlier we are not using a View for the block X. We are actually using a join of two tables.
Earlier we had block A as a separate block to hold the key values. But since the requirement is to enable querying on non-key values as well, we had to replace block A and B with a single block X (key + non-key values). This was done because as I understand one can query (enter data in fields and fetch) single block at a time.

I was able to resolve the problem by going back to blocks A & B; adding items similar to the ones in block B to block A as non-database; showing them in place of block B fields when user is entering a query; modifying the query on execute (within the code) based on the data entered; hiding the non-database fields and showing block A & B with results.
I hope I am able to explain the solution.

Regards,
Amit

[Updated on: Sat, 10 January 2009 00:08]

Report message to a moderator

Previous Topic: Please Please I need your help
Next Topic: Hide Blanks in a List Item - Tricky Situation!
Goto Forum:
  


Current Time: Fri Dec 02 16:20:07 CST 2016

Total time taken to generate the page: 0.24221 seconds