Recently a customer asked me if he could specify or manage his own help labels. My first reaction was to give him access to my development environment and explain how he could set the help texts for each label. After some more thinking I think anyone would see that this is not really the best solution, what if he accidently deletes items or other components?
Time to work out a custom solution. I started by searching how the help texts for items are saved on the flow user. I found out that if you write a help text, a record is inserted in the wwv_flow_step_item_help table. This record refers to a record in the wwv_flow_step_item table, which is the actual item.
With this information I could make a view and work with an ‘instead of’ trigger to update/insert the help texts. Let’s start with the view, I need all items that are not hidden and order them by page and region and display sequence.
CREATE OR REPLACE FORCE VIEW “V_APEX_ITEM_HELP_TEXT” (“FLOW_ITEM_ID”, “FLOW_ID”, “ITEM_NAME”, “ITEM_LABEL”, “PAGE_ID”, “PAGE_NR”, “REGION_NAME”, “ITEM_HELP_ID”, “ITEM_HELP_TEXT”) AS
SELECT item.id flow_item_id,
FROM flows_030000.WWV_FLOW_STEP_ITEMS item,
WHERE item.flow_id = v(‘APP_ID’)
AND item.item_plug_id = region.id
AND item.id = item_help.flow_item_id(+)
AND display_as NOT IN (‘HIDDEN’)
ORDER BY flow_step_id, plug_display_sequence, item_sequence;
We now need the trigger that will update or insert the help text for an item:
create or replace TRIGGER V_APEX_ITEM_HELP_TEXT_BIUD
INSERT OR DELETE OR UPDATE
REFERENCING OLD AS OLD NEW AS NEW
IF inserting OR deleting THEN
raise_application_error(‘-20010′,’delete are insert not allowed’);
ELSIF updating THEN
— insert help text
IF :new.item_help_id IS NULL THEN
INSERT INTO flows_030000.wwv_flow_step_item_help ( flow_id,
— update help text
SET help_text = :new.item_help_text
WHERE id = :new.item_help_id;
The application in question has more than 100 pages so I will give my customer the option to first select the page and then fill in the help items. I started by making a page and create a new item (P9500_PAGE_ID) with type select list with submit, in the list of values source I have the next query:
SELECT page_id || ‘ – ‘ || page_title display_value,
ORDER by page_id
The query will list all my pages in my select list. Now I made an updateable report based on my V_APEX_ITEM_HELP_TEXT view.
All done, the instead of trigger will perform the necessary action to save the help texts.
Although this solution has a lot of pro’s and contra’s, this proves again how flexible we can work with ApEx.