Sort Data, at runtime

[Iceman]

Hi

I got a datablock on a form with number of records displayed = 10

Some of the items are database items some are non-database items.

The user can choose how to sort the data at runtime with the following code:

SET_BLOCK_PROPERTY('XXP_QUOTATION_LINES', ORDER_BY, 'LINE_ID');
GO_BLOCK('XXP_QUOTATION_LINES');
EXECUTE_QUERY;

but the problem with this method is that it does not work with non-database items.

Does anybody have a solution for this?

Thanks in advance

Elie,

[Mark C. Stock]

"Iceman" <econstantine@fattal.com.lb> wrote in message
news:92c03dc2.0409070627.7c5a5eef@posting.google.com...
| Hi
|
| I got a datablock on a form with number of records displayed = 10
|
| Some of the items are database items some are non-database items.
|
| The user can choose how to sort the data at runtime with the following
code:
|
| SET_BLOCK_PROPERTY('XXP_QUOTATION_LINES', ORDER_BY, 'LINE_ID');
| GO_BLOCK('XXP_QUOTATION_LINES');
| EXECUTE_QUERY;
|
| but the problem with this method is that it does not work with
non-database items.
|
| Does anybody have a solution for this?
|
| Thanks in advance
|
| Elie,

how are the non database items populated? i would assume with a post-query
or some sort of when-validate trigger?

if you base the block on a view which includes the non-database items, then
you can specify the sort (which is done in the database) on any of the items

if the block needs to be updatable, it still may be updatable depending on
the view (but would require some properties tweaked on the reference items)
or you may have to use instead of triggers on the view or on-update, etc.,
triggers on the block

++ mcs

[Daniel Morgan]

Iceman wrote:
Hi I got a datablock on a form with number of records displayed = 10 Some of the items are database items some are non-database items. The user can choose how to sort the data at runtime with the following code: SET_BLOCK_PROPERTY('XXP_QUOTATION_LINES', ORDER_BY, 'LINE_ID'); GO_BLOCK('XXP_QUOTATION_LINES'); EXECUTE_QUERY; but the problem with this method is that it does not work with non-database items. Does anybody have a solution for this? Thanks in advance Elie,

What keeps them in sync now? What creates a relationship between a
non-database item and a database item?

From my experience you have a non-issue ... have you tested your block
to see if a problem exists?

And if it does ... little help is possible without version info.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Iceman]

Daniel,

in the POST-QUERY trigger for this datablock I'm running this code

BEGIN
SELECT distinct description, segment1 INTO :ITEM_DESCRIPTION,
:INVENTORY_ITEM_CODE
FROM apps.mtl_system_items
WHERE organization_id = :xxp_quotation_header.organization_id
AND inventory_item_id = :INVENTORY_ITEM_ID;
EXCEPTION WHEN OTHERS THEN NULL;
END;

SET_RECORD_PROPERTY(:SYSTEM.TRIGGER_RECORD, :SYSTEM.TRIGGER_BLOCK,
STATUS, QUERY_STATUS);

This is what keeps them sync.

[Daniel Morgan]

Iceman wrote:
Daniel, in the POST-QUERY trigger for this datablock I'm running this code BEGIN SELECT distinct description, segment1 INTO :ITEM_DESCRIPTION, :INVENTORY_ITEM_CODE FROM apps.mtl_system_items WHERE organization_id = :xxp_quotation_header.organization_id AND inventory_item_id = :INVENTORY_ITEM_ID; EXCEPTION WHEN OTHERS THEN NULL; END; SET_RECORD_PROPERTY(:SYSTEM.TRIGGER_RECORD, :SYSTEM.TRIGGER_BLOCK, STATUS, QUERY_STATUS); This is what keeps them sync.

Why the query? Why not use calculated/formula items and let me be part
of the block and dynamic?

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Mark C. Stock]

"Daniel Morgan" <damorgan@x.washington.edu> wrote in message
news:1094774804.54106@yasure...
| Iceman wrote:
|
| > Daniel,
| >
| > in the POST-QUERY trigger for this datablock I'm running this code
| >
| > BEGIN
| > SELECT distinct description, segment1 INTO :ITEM_DESCRIPTION,
| > :INVENTORY_ITEM_CODE
| > FROM apps.mtl_system_items
| > WHERE organization_id = :xxp_quotation_header.organization_id
| > AND inventory_item_id = :INVENTORY_ITEM_ID;
| > EXCEPTION WHEN OTHERS THEN NULL;
| > END;
| >
| > SET_RECORD_PROPERTY(:SYSTEM.TRIGGER_RECORD, :SYSTEM.TRIGGER_BLOCK,
| > STATUS, QUERY_STATUS);
| >
| > This is what keeps them sync.
|
| Why the query? Why not use calculated/formula items and let me be part
| of the block and dynamic?
|
| --
| Daniel A. Morgan
| University of Washington
| damorgan@x.washington.edu
| (replace 'x' with 'u' to respond)
|

the formula item uses a PL/SQL formula to return a value -- so it would need
to call a client-side function

however, you can't rely on raising FORM_TRIGGER_FAILURE the function used by
a formula item to validate the source item -- if the source item changes to
an invalid value and raises an exception in the formula item's function, the
formula item's value remains unchanged, and the bad value remains in the
source item

additionally, retrieving the reference value in a formula item would still
not allow sorting on the value -- again, sorts are ONLY done in the
database, not in the client tool

so, typically you must use POST-QUERY + WHEN-VALIDATE-ITEM for populated
reference values for changeable source items

but to be able to sort on reference values, include the reference values in
the block's Query Data Source. note that if the source items are changeable,
you may still need another non-database item to display the reference value
(populated by a WHEN-VALIDATE-ITEM) -- this non-database item would
initially be populated by a POST-QUERY trigger with the reference value
retrieved by the query. also, keep in mind that you may need an INSTEAD OF
trigger on the view, or ON-INSERT, etc. triggers on the block


that being said, i realized another alternative while testing this:

you could write code to generate sub-queries for the order by clause. the
target syntax would be something like:

SELECT *
FROM emp
ORDER BY (select ename from emp e2 where empno = emp.mgr)

this does not require special handling of the form DML (just base the block
on the table and use normal triggers for reference items). however, the
performance implications should be investigated vs using a view, and the
subquery needs to be written carefully -- note the tables alias in this
example to differentiate the subquery table from the block's table (which
will not have an alias in the generated query)

++ mcs