Forum Stats

  • 3,826,757 Users
  • 2,260,705 Discussions
  • 7,897,072 Comments

Discussions

How I can change :APEX$ROW_STATUS to 'U' of all rows in Interactive Grid on load time?

User_XMEUF
User_XMEUF Member Posts: 8 Green Ribbon

I want to fetch data from One Table by using SQL Query. Now I want to modify some values in loaded rows. Some rows are not modified but I want to store All rows in another table. For this purpose I need :APEX$ROW_STATUS to 'U' for all rows. Please guide me how I can change :APEX$ROW_STATUS to 'U' of all rows without clicking one by one.

Answers

  • Hamza Al-abbasi
    Hamza Al-abbasi Member Posts: 572 Gold Trophy
    edited May 20, 2022 8:31AM

    Try to store your SQL Query to a collection using CREATE_COLLECTION_FROM_QUERY2 with modifying the values you want in the query then, set your IG SQL Query based on the collection. Your IG SQL Query will look like :

    select N001,
           N002,
           C001,
           ....
    
    from apex_collections
    where collection_name = 'COLLECTION_NAME
    

    Finally, if you want the user modifying the IG, create a DML process with type PL/SQL to add changes to the collection using ADD_MEMBER/UPDATE_MEMBER/DELETE_MEMBER then use SQL Query to fetch data from the collection to the database.

  • Arie Geller
    Arie Geller Member Posts: 4,183 Gold Trophy

    Hello,

    The APEX$ROW_STATUS is a built-in option; however, APEX enables you to assign the same functionality to any of the columns you are having, as the screenshot (from the IG Attributes tab) shows:

    If you add a pseudo-column – e.g., MY_STATUS – to your SQL query, populate it with 'U' and assign it as the Allowed Row Operations Column, you should get the functionality you want.


    Regards,

    Arie.

  • User_XMEUF
    User_XMEUF Member Posts: 8 Green Ribbon

    I did as you advised and my procedure to update without error is as below but it is not working

    IF MY_STATUS='U' THEN

       UPDATE MENU_RIGHTS SET ENAB=:ENABLED WHERE USER_ID=:P4_USER_ID AND BR_CODE=:G_BR_CODE AND MENU_ID=:PK;

    END IF;

  • InoL
    InoL Member Posts: 9,881 Gold Crown

    That only indicates that you are allowed to update the row, not that the row is updated.

  • User_XMEUF
    User_XMEUF Member Posts: 8 Green Ribbon
    edited May 29, 2022 3:20PM

    I am new in Apex. I think I am unable to describe my problem because I am still searching the right answer. Here I am trying again to describe the problem. please try to understand;

    I have a table Menu_Rights with columns: User_ID varchar2(100), Menu_ID Number(10), Status Varchar2(1) value in STATUS column A and I (A stands for Active and I Stands for Inactive). I want to change values only in column Status. I have successfully fetched these values in an Interactive Grid.

    Now some rows will be modify and some will not be modified. When I clicked on Save Button the all rows including modified and not modified should be save in another table Named: User_Rights_applied with same columns.

    I am searching the way to complete my task. If there is any other way please guide. I will be very thankful to you for this help. Can you please share any screen short or video link?

  • InoL
    InoL Member Posts: 9,881 Gold Crown

    When I clicked on Save Button the all rows including modified and not modified should be save in another table

    This is a very strange requirement. What business requirement made you come up with this solution?

    What if you have hundreds or even thousands of users and some funny user will click on the Save button button hundred times? You will store thousands of records in User_Rights_applied for what reason?

  • User_XMEUF
    User_XMEUF Member Posts: 8 Green Ribbon

    This is a basic need. I do not want the user to click 100 times or 1000 times so I want the user to only change the lines that need to be changed, all the rest will be saved with one click.

    Come on, if you don't understand, there is a general requirement that I have 100 students in my class out of which 8 are absent. By default all children will be considered present when attendance is intended. Only those who are absent will be marked in the list.

    Or it could be the other way around. Most of the children are absent in the class. The number of children present is only eight. Will I attend one by one?

    What do I have to do for such a need?

  • InoL
    InoL Member Posts: 9,881 Gold Crown

    Only those who are absent will be marked in the list.

    Very clear and easy. But why "all rows including modified and not modified should be save in another table".

    If you include a checkbox in your IG, based on some YES/NO column in your table, updating that checkbox in your IG will flag the record as UPDATED and update the associated absence column.

    So, if you want a class attendance, you first put all 100 student in that class with default ABSENT = NO. Then use the IG to check the checkbox for absent students.