This discussion is archived
5 Replies Latest reply: May 17, 2010 11:38 AM by CraigB RSS

Save only edited or new records

638062 Newbie
Currently Being Moderated
Hi,
I have a block based on a table and display a few records on the form after retrieving records from the table behind. Now I should be able to edit a record or add a new record to the existing records on the screen. So when i click the save button I only want the rows which are new or have been edited to go back into the db. This is the piece of code I was using for the save button in the on-button-clicked action.
IF alert_button=alert_button1 THEN
GO_BLOCK('my_block');
IF NOT Form_Success THEN RAISE Form_Trigger_Failure;
END IF;
/* ** Commit if anything is changed */
IF :System.record_Status IN ('CHANGED','NEW') THEN Commit_Form;
END IF;
msg_info('Your changes have been saved.');

END IF;

When I do this all the records on the screen gets inserted into the table instead of only the one's I have edited or newly inserted. I only want to insert rows which are new or edited into the table after I click the save button.
  • 1. Re: Save only edited or new records
    609123 Explorer
    Currently Being Moderated
    IF alert_button=alert_button1 THEN
    GO_BLOCK('my_block');
    IF NOT Form_Success THEN RAISE Form_Trigger_Failure;
    END IF;
    /* ** Commit if anything is changed */
    IF :System.record_Status IN ('CHANGED','NEW') THEN Commit_Form;

    msg_info('Your changes have been saved.');

    END IF;


    END IF;
  • 2. Re: Save only edited or new records
    CraigB Guru
    Currently Being Moderated
    How do you populate your block with records? Is your Data Block based on a Table? If your data block is based on the table and you query the base table for the records there is no need to keep track of which records are new or have been updated because Oracle Forms does this for you. All you need to do is call Commit_Form to update or insert records.

    If you populate the block by looping through a cursor, then you* have to keep track of which records are "new" or "updated". This complicates things by putting the burden of tracking new or updated records on you. It is always best to let Oracle Forms do as much of the work as possible, but if you have to handle your data this way, then you might want to add a non-base table item to your data block and use it to keep track of which records need to be inserted and which ones need to be updated. You will have to write code in a trigger to keep track of which records are new or updated. You will need to modify your Save button trigger to just call Commit_Form and move your logic to the On-Insert (OI) trigger. This will allow you to use the Insert_Record() built-in in the OI trigger - which will insert only the current record and allow you to pick and choose which records are inserted.

    Personally, I would look at redesigning your process to allow Oracle Forms to handle as much of the process as possible. This makes your Form easier to maintain and faster.

    Hope this helps,
    Craig B-)

    If a response is helpful or correct, please mark it accordingly.
  • 3. Re: Save only edited or new records
    638062 Newbie
    Currently Being Moderated
    Hi,
    Thanx for the explanation. I have based the block of a table in the data source but then I have procedure which contains a loop that populates the table. I think this cursor loop might be the culprit. But my problem is I don't have to query and populate everything in the table to the block. I have a few search criteria based on which I have to retrieve only a few records and populate only those on the screen. Hence the procedure. So if i need to use commit_form and let form form handle the commit itself how do i handle display only selected records while retrieving from the base table.
  • 4. Re: Save only edited or new records
    InoL Guru
    Currently Being Moderated
    You can:
    1. let the user decide what the search criteria are. This is standard Forms functionality when the user clicks enter query.
    2. programatically create the criteria by setting the default where clause.
  • 5. Re: Save only edited or new records
    CraigB Guru
    Currently Being Moderated
    If the only reason for your procedure is to filter the records, Forms handles this natively when you place the Form in "Enter Query" mode (Forms 6i = F7, Forms 9i or higher = F11). A user can enter their search criteria in each field displayed (to include Oracle Wildcard charaters) and then execute the query using F8 (Forms 6i) or Ctrl+F11 (Forms 9i or higher). This is one of the great features of Forms is that it doesn't require additionaly programming to accomodate searches. Once has queried the records into the data block, Forms keeps track of which records were modified or are new. When you Save (Commit_Form), Forms will update or insert as needed to save the changes the user made.

    As Inol suggested, if you need to gather search criteria fro m your us er a different way, you can use the criteria to change the W here Clause of the data block to display records. In this situation, you would code your "Search" button to modify the w here clause. Here's an example w here a user can search on an employee's first name, last name, employee id or any combination of the three:
    First Name [                  ]    Last Name [                      ] Emp ID [                     ]
    [SEARCH]
    /* Sample When-Button-Pressed trigger for Search button */
    /* Example of searching by Last Name */
    DECLARE
       v_def_where    VARCHAR2(500);
    BEGIN
       IF ( :search_block.first_name IS NOT NULL ) THEN 
          v_def_where := 'first_name = :search_block.first_name';
       END IF;   
       IF ( :search_block.last_name IS NOT NULL ) THEN
          IF (v_def_where IS NOT NULL ) THEN 
             v_def_where := v_def_where || ' and ';
          END IF;
          v_def_w here := v_def_w here || ' last_name = :search_block.last_name';
       END IF;
    
       IF ( :search_block.employee_id IS NOT NULL ) THEN
          IF (v_def_where IS NOT NULL ) THEN 
             v_def_w here := v_def_w here || ' and ';
          END IF;
          v_def_w here := v_def_w here || ' last_name = :search_block.employee_id';
       END IF;
      IF ( v_def_w here IS NOT NULL ) THEN 
          /* Forms 6i */
          S et_B lock_Property('DATA_BLOCK',DEFAULT_W HERE, v_def_w here);
          /* Forms 9i or Higher */
          S et_B lock_Property('DATA_BLOCK',ONETIME_W HERE, v_def_w here);
          Execute_Query;
       ELSE
          Message ('No Search Criteria was entered.');
          Message ('No Search Criteria was entered.');
          RAISE Form_Trigger_Failure;
       END IF;
    
     END;
    NOTE: As you can see I had to EDIT this post numerous times to get it to save. X-( There are spaces embedded in the code sample.

    Hope this helps,
    Craig B-)

    If a response is helpful or correct, please mark it accordingly.

    Edited by: CraigB on May 17, 2010 12:21 PM

    Edited by: CraigB on May 17, 2010 12:22 PM

    Edited by: CraigB on May 17, 2010 12:23 PM

    Edited by: CraigB on May 17, 2010 12:24 PM

    Edited by: CraigB on May 17, 2010 12:24 PM

    Edited by: CraigB on May 17, 2010 12:24 PM

    Edited by: CraigB on May 17, 2010 12:26 PM

    Edited by: CraigB on May 17, 2010 12:28 PM

    Edited by: CraigB on May 17, 2010 12:29 PM

    Edited by: CraigB on May 17, 2010 12:31 PM

    Edited by: CraigB on May 17, 2010 12:33 PM

    Edited by: CraigB on May 17, 2010 12:34 PM

    Edited by: CraigB on May 17, 2010 12:36 PM

    Edited by: CraigB on May 17, 2010 12:37 PM

    Edited by: CraigB on May 17, 2010 12:37 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points