This discussion is archived
7 Replies Latest reply: Nov 19, 2012 11:48 PM by user346369 RSS

When-Validate-Item and Go_Block/Execute Query dilemma

Roxyrollers Newbie
Currently Being Moderated
Hey there folks -- Probably not a surprise question on this forum. I did try to search for this and came up with a few posts but did not find any solutions. Hopefully, I was also doing the correct way to search on this forum. If this is a post for the nth time on this issue, I apologize.

So, I have 2 blocks:
1. Control Block that contains the STUDENT_ID field (Database Item = 'N')
2. Database block that contains the STUDENT_ID field (Database Item = 'Y' and many other fields)

My goal is do an Execute_Query if User enters the Student ID and either presses TAB or places the MOUSE in the database block and display the record if it exists. As we know, a Go_Block is not allowed in When-Validate-Item. So, I went one step further to "simulate" this. The problem is that I am still firing the Execute-Query when the record has already been retrieved. Here is what I have so far. So, the Key-Next-Item trigger works fine if User enters a Student ID and presses tab. It also works fine if User clicks on the STUDENT_BK as the When-New-Block-Instance trigger fires which in turn calls the Key-Next-Item. The problem occurs when after record has been retrieved and User clicks on control.student_id and then comes and clicks on the STUDENT_BK, the KEY-NEXT-ITEM trigger fires again.

Suggestions or pointers would be more than welcome.

Thanks!

CONTROL.student_id.Key-Next-Item
  if :control.student_id is not null and :SYSTEM.BLOCK_STATUS != 'CHANGED' then
    next_block;
    clear_block(NO_COMMIT);
    execute_query;
  else
    show_alert('Please enter Student ID');
  end if;
STUDENT_BK.When-New-Block-Instance
go_item('control.student_id');
do_key('next_item');
  • 1. Re: When-Validate-Item and Go_Block/Execute Query dilemma
    MLBrown Journeyer
    Currently Being Moderated
    I'm removing my previous thought - that didn't work.

    Edited by: MLBrown on Nov 19, 2012 2:46 PM
  • 2. Re: When-Validate-Item and Go_Block/Execute Query dilemma
    Roxyrollers Newbie
    Currently Being Moderated
    I did realize that my example was not descriptive and sometimes hard to figure out as there is no such data involved here.

    Your approach is certainly in the right direction. The whole idea is to not fire the Key-Next-Item trigger every time user clicks on the Student block and so additional logic would be needed. As for this statement:
    and get_item_property('STUDENT_BK.ITEM_NAME',previousitem) != 'CONTROL.STUDENT_ID'  then
    What should be my STUDENT_BK.item_name? I have 30 fields in the STUDENT_BK. Should it be SYSTEM.CURSOR_ITEM? Also, the Key-Next-Item trigger is for the field CONTROL.student_id

    Edited by: Roxyrollers on Nov 19, 2012 2:51 PM
  • 3. Re: When-Validate-Item and Go_Block/Execute Query dilemma
    MLBrown Journeyer
    Currently Being Moderated
    Yeah... I wrote before I tried it out. The GET_ITEM_PROPERTY('ITEM', previousitem) didn't work the way I thought it did. After I posted that, I tried it out and it was a complete failure.

    I guess what I am thinking now, is in your STUDENT_BK block, do you only want to EXECUTE_QUERY if the :STUDENT_ID does not match the :CONTROL.STUDENT_ID? If that is true, then all you would have to do is to change your KEY-NEXT logic to include the
    if STUDENT_BK.STUDENT_ID != :CONTROL.STUDENT_ID then
      -- original logic
      go_item('control.student_id');
      do_key('next_item');
    end if;
    The more I think about it, this should work for you. You said that you had two requirements: Entered a student id in the control block and tabbed or entered the database block. If you add the check of the student id then that should take care of your second requirement. If they enter the database block and the id's don't match then it will execute the query, else they already have the record displayed for them so you don't need to do anything.

    Edited by: MLBrown on Nov 19, 2012 3:08 PM
  • 4. Re: When-Validate-Item and Go_Block/Execute Query dilemma
    Roxyrollers Newbie
    Currently Being Moderated
    Hi MLB -- I tried this logic and I had thought about it earlier but the condition will never get satisfied in When-New-Block-Instance. This is because in the Properties for STUDENT_BK.STUDENT_ID, I have done a Copy Value From Item from CONTROL.STUDENT_ID to effectively fetch the correct record based on STUDENT_ID
    if STUDENT_BK.STUDENT_ID != :CONTROL.STUDENT_ID then
    What I have now done is something like this in the When-New-Block-Instance.
    if :system.block_status != 'CHANGED' then
      go_item('control.student_id');
      do_key('next_item');
    end if;
    The Key-Next-Item has been simplified as follows:
    if :control.study_id is not null THEN
      next_block;
      clear_block(NO_COMMIT);
      execute_query;
    else
      show_alert_e('Please enter Study ID');
    end if;
    I am going to test this out a bit and see what happens.
  • 5. Re: When-Validate-Item and Go_Block/Execute Query dilemma
    MLBrown Journeyer
    Currently Being Moderated
    I'm thinking that you take the "Copy Value From Item" out and add a where clause to your STUDENT_BK of "student_id = :CONTROL.STUDENT_ID"

    Then when you do the
    if STUDENT_BK.STUDENT_ID != :CONTROL.STUDENT_ID then
    it logic should work to meet your second requirement of the block querying when the the user enters the STUDENT_BK database block. If the IDs match, the record has already been queried, if they don't then your query logic will fire and with the where clause added to your STUDENT_BK block it should bring in the record you want.
  • 6. Re: When-Validate-Item and Go_Block/Execute Query dilemma
    Roxyrollers Newbie
    Currently Being Moderated
    Thanks MLB! It worked.

    I made the following changes:

    1. Removed Copy Value From Item
    2. Where Clause added to STUDENT_BK: student_id = :CONTROL.STUDENT_ID
    3. When-New-Block-Instance
    if nvl(STUDENT_BK.STUDENT_ID,99999998) != nvl(:CONTROL.STUDENT_ID,999999999) then
    4. Added Pre-Insert Trigger
    :STUDENT_BK.STUDENT_ID := :CONTROL.STUDENT_ID;
  • 7. Re: When-Validate-Item and Go_Block/Execute Query dilemma
    user346369 Expert
    Currently Being Moderated
    The above solution will work if you only have two blocks. But what if you add a third block to the form? When user navigates from Control.Student_ID to the other block, the Student_Bk isn't populated.

    Here is another possibility:

    In Control.Student_ID, use a When-validate-item trigger. In that trigger, do a SQL select to see if the student_ID exists. If it does not, issue and error message (...unless you want to allow the user to enter a new student to the table. Where I work, only one or two screens are allowed to enter a new student.)

    If the student id DOES exist, then start a timer (no_repeat, 20 milliseconds). In the When-timer-expires trigger, Go_Block('Student_BK'); and then Execute_Query.

    The when-validate-item trigger can also be used to prevent the user from changing the Student_ID if he has made changes to the Student_Bk, but not committed them.

Legend

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