Link to home
Start Free TrialLog in
Avatar of wasabi3689
wasabi3689Flag for United States of America

asked on

how to convert this idea into a list trigger ? urgent needed!!

I create a block named "media_request_info". There is one item/field called "status" which is a list with 3 elements - requested, in_progress and canceled.

I create a when-list-changed trigger for the status list.
Under this trigger, I want the following ideas to be converted into code
1. when status is in_progress, canceled, user cannot change the list values to something else. If they change, there will be a message popped up to warn them.
2. when status is "requested", if user wants to change it to "canceled", this is allowed. There will be a message to tell them if it's sure to make this change. If click "Yes", it will go to call a procedure to update the table. Attached is my code, but I still cannot convert above ideas to the code completely. The code still doesn't work well and still can allow users to change status from in_progress to something else.

I am using orcle 8i and form version is 11.0.35
IF (:media_request_info.status = 'IN_PROGRESS') THEN
       MESSAGE('The media request is in progress status and you cannot change the status.');
        set_item_property ('status', ENABLED, PROPERTY_FALSE);
        RAISE FORM_TRIGGER_FAILURE; 
	--pause;
 
  ELSIF (:media_request_info.status = 'CANCELED') THEN
       MESSAGE('The request has been canceled and you cannot change the status.');
       set_item_property ('status', ENABLED, PROPERTY_FALSE);
 
  ELSIF (:media_request_info.status = 'REQUESTED') THEN
---I don't know how to do if user want to change to canceled, this is allowed.  but not --in_progress
       MESSAGE('The media has been requested and you can only change to canceled.');
      media_request_status_changed; 
   
 END IF;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of MarkusId
MarkusId
Flag of Austria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Well, I think it''s called PRE-TEXT-ITEM, not PRE-FIELD - I hope it works for list-items. Anyway, you have to log the list-value before changing of the value somewhere.
Avatar of wasabi3689

ASKER

for both PRE-TEXT-ITEM trigger and Post-TEXT-ITEM, are they block media_request_info triggers or item "status" trigger?
It doesn't work!!  I either put the PRE-TEXT-ITEM trigger and Post-TEXT-ITEM triggers under media_request_info block or item status

I don't see the message popped up and I can still change from in_progress to requested.....
I think that should be post-change trigger. It works some, but

when the status is "canceled" status, I still can change to "requested". For example,

After I open the form, the status is " requested ", then I change to " canceled", it works. But, after I save it, then open the same item, I change the "canceled to requested, I don't see any message popped up and allow me to save. This should not allow.

How to fix it?
I know why the cancel status doesn't work. I fixed it. It's typing error for item value. Now there is only one thing bother me.

When the item is in canceled status, I click on the list to change to requested or in_progress, it pops up the message, this is correct. Then after I  click on other items, it pop up a message to ask me if I want to save the change. I don't need this message if there is no change. The message is "Do you want to save changes you have made" Yes, NO, Cancel

I only need this message when there is a real change. Also, I prefer to save item by action->save from the toolbar and I want the message shown in the tool bar, not pop up.

How to fix this?
Avatar of Ora_Techie
Ora_Techie

use:
set_item_propert(<ItemName>, item_is_valid, TRUE)
before/after that message.
what is used for

set_item_propert(<ItemName>, item_is_valid, TRUE)
when you assing a value to an item, form marks the item (and hence record) as 'CHANGED'. By setting ITEM_IS_VALID to TRUE for that item, you tell forms to not to mark the item as 'CHANGED'.
 
read the forms documentation for more info.
Before I remove some items, everything seems working. After I remove inventory_item_id on the media_request_info block, the list status can change whatever I want but with the following message

frm-40815:variable GLOBAL.status does not exist

If I add back the item - inventory_item_id to the block, everything seems working. I don't understand why. I don't need inventory_item_id on the block.

Here is my when-list-changed trigger code

Under block media-request_info, I have the following triggers
on-update
key-commit
pre-text-item
on-lock
post-change

Before I remove inventory_item_id, everything seems working. But, after I remove it, it comes up that error message and the status list doesn't following what I want.
Why?
How to fix it?


IF (:global.status = 'IN_PROGRESS') THEN
       :media_request_info.status := :global.status;
       MESSAGE('The media request is in progress status and you cannot allow to change the status.');
        set_item_property ('media_request_info.status', ENABLED, PROPERTY_FALSE);
        RAISE FORM_TRIGGER_FAILURE; 
 
  ELSIF (:global.status = 'CANCELED') THEN
       :media_request_info.status := :global.status;
       MESSAGE('The request has been canceled and you cannot allow to change the status.');
       set_item_property ('media_request_info.status', ENABLED, PROPERTY_FALSE);
       RAISE FORM_TRIGGER_FAILURE; 
 
  ELSIF (:global.status = 'REQUESTED') THEN
   IF :media_request_info.status = 'IN_PROGRESS' THEN
      :media_request_info.status := :global.status;
       MESSAGE('The media has been requested and you can only allow to change to canceled status.');
    ELSE
      --media_request_status_changed; 
	MESSAGE('The media has been requested to cancel.');
    END IF;
   
 END IF;

Open in new window

Have you defined any triggers for item  inventory_item_id ? Can you please post the name & code of the triggers?
I have the following code in when-new-reocrd-instance

declare
   v_dummy NUMBER;
   CURSOR c_check_properties ( p_licensed_products_id NUMBER ) IS
      SELECT 1
      FROM XX_PRODUCT_PROPERTIES
        WHERE licensed_products_id = p_licensed_products_id
        AND rownum = 1;

CURSOR c_check_media_info IS
    SELECT Count(1)
        FROM apps.xx_media_request_info_v xmi
        WHERE xmi.INVENTORY_ITEM_ID=:licensed_products.inventory_item_id
        AND rownum = 1;


begin

    set_item_property ('LICENSES.PRODUCT_INFO', ENABLED, PROPERTY_TRUE);
    set_item_property ('LICENSES.PRODUCT_INFO', visual_attribute, 'BUTTON');    

     set_item_property('LICENSES.MEDIA_INFO',ENABLED,PROPERTY_FALSE);
     set_item_property('LICENSES.MEDIA_INFO',visual_attribute,'DISABLED_TEXT');
 
     open c_check_media_info;
     fetch c_check_media_info into v_dummy;
     close c_check_media_info;  
 
--if v_dummy is not null and :media_request_info.inventory_item_id is not null then
  if (v_dummy > 0 and :media_request_info.inventory_item_id is not null and (:media_request_info.status = 'IN_PROGRESS'or :media_request_info.status = 'REQUESTED')) then
        set_item_property('LICENSES.MEDIA_INFO',ENABLED,PROPERTY_TRUE);
        set_item_property('LICENSES.MEDIA_INFO',visual_attribute,'BUTTON');
        set_item_property ('LICENSES.MEDIA_INFO', visual_attribute, 'blue_prompt');
   
   elsif (v_dummy > 0 and :media_request_info.inventory_item_id is not null and :media_request_info.status = 'CANCELED' ) then
        set_item_property('LICENSES.MEDIA_INFO',ENABLED,PROPERTY_TRUE);
        set_item_property('LICENSES.MEDIA_INFO',visual_attribute,'BUTTON');
        set_item_property ('LICENSES.MEDIA_INFO', visual_attribute, 'red_prompt');  
    else
        --set_item_property('LICENSES.MEDIA_INFO',ENABLED,PROPERTY_FALSE);
        --set_item_property('LICENSES.MEDIA_INFO',visual_attribute,'DISABLED_TEXT');
        set_item_property('LICENSES.MEDIA_INFO',ENABLED,PROPERTY_TRUE);
        set_item_property('LICENSES.MEDIA_INFO',visual_attribute,'BUTTON');
        set_item_property ('LICENSES.MEDIA_INFO', visual_attribute, 'gray_prompt');  
    end if;
......