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

Oracle Database

Avatar of undefined
Last Comment
wasabi3689

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
MarkusId

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
MarkusId

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.
ASKER
wasabi3689

for both PRE-TEXT-ITEM trigger and Post-TEXT-ITEM, are they block media_request_info triggers or item "status" trigger?
ASKER
wasabi3689

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.....
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
wasabi3689

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?
ASKER
wasabi3689

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?
Ora_Techie

use:
set_item_propert(<ItemName>, item_is_valid, TRUE)
before/after that message.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
wasabi3689

what is used for

set_item_propert(<ItemName>, item_is_valid, TRUE)
Ora_Techie

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.
ASKER
wasabi3689

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ora_Techie

Have you defined any triggers for item  inventory_item_id ? Can you please post the name & code of the triggers?
ASKER
wasabi3689

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;
......