Fluitend door het leven

APEX Show And Create History
I've made an application that logs the analysis of a problem; components can be assigned to solve the problem at hand. The components are not part of another system, so they'll have to be typed in manually each time an analysis is done.

Of course, this is annoying for the user, because he/she has to type the same component code and description over and over.

To solve this user issue I've combined two features of APEX 4.0: autocomplete and a dynamic action. (with pseudo-sql, which should work ;-))

The autocomplete:
autosearch

When a value is selected and the user leaves the field, a dynamic action kicks in:

dynamic_action

This dynamic action consists of the following:
dynamic_action_details and dynamic_action_query

This combination results in a pseudo-history where typing a componentcode gives a potential candidate without disabling the possibility of adding a new one.

The same goes for the componentdescription which is looked for upon leaving the componentcode-field. It gives the latest entry of the description, but doesn't prevent the user adding/changing a componentdescription.

If you want to get the latest description for a componentcode, this is found in the lookup_table using an analytic function (examples):

select *
from
(
select
lu_id,
componentcode,
componentoms ,
row_number() over (partition by componentcode order by lu_id desc) volgnr
from lookup_table
where componentcode is not null
)
where volgnr=1
order by 2,1

Thanks to Denny for the analytic function.
Menno    @ Work    ^^