Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    8

    Thumbs up Unanswered: checkbox - WHEN CHECKED

    I am trying to implement a checkbox in oracle forms 6i.I got 2 screens.1st screen is a search screen where I got my checkbox along with other controls and 2nd screen displays the result set for the search.

    The checkbox when checked should retrive all active records (for a database field, STATUS=NULL).When unchecked it should retrive
    all records active as well as deleted (STATUS=NULL & STATUS=D).

    I set the value of checked as '1' and unchecked as '0' in screen 1.
    The on event WHEN_CHECKBOX_CHANGED , I ty to set the values
    as required above.

    I pass this value to the PRE_QUERY event of 2nd screen.

    Still For checkbox checked I am getting all records.When unchecked
    I am getting the same result set .

    If anyone came across this condition or can help me to resolve this
    I will appreciate it.

    Thanks

    -Faizal
    Last edited by faizalkp; 11-18-02 at 20:50.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: checkbox - WHEN CHECKED

    It's hard to guess what your problem is from the information you have given. You say that the checkbox values are '1' for checked, '0' for unchecked. How do you translate that to the query conditions, which are:
    active: status IS NULL
    active/deleted: status IS NULL OR status = 'D'
    ?

    I can think of two ways you could do this:

    1) Change the checkbox values to:
    checked = '?' /* A value that is NOT a valid status code */
    unchecked = 'D'

    Then in the WHERE clause property for the query block put:
    (status IS NULL OR status = :blk1.checkbox_value)

    2) In PRE-QUERY trigger say:

    IF :blk1.checkbox_value = '1' THEN
    SET_BLOCK_PROPERTY( 'qry_blk', DEFAULT_WHERE, 'status IS NULL');
    ELSE
    SET_BLOCK_PROPERTY( 'qry_blk', DEFAULT_WHERE, 'status IS NULL OR status = ''D''');
    END IF;

  3. #3
    Join Date
    Nov 2002
    Posts
    8

    Checkbox-Checked

    Andrew

    Thanks for the help !

    I am transalating the query condition in the PRE_QUERY event of blk2.

    My WHERE clause looks like this.(blk1-->search,blk2-->customer)

    :customer.cus_customer_no := :search.cus_customer_no;
    :customer.cus_name := :search.cus_name;
    :customer.cus_city := :search.cus_city;
    :customer.cus_state := :search.cus_state;
    :customer.cus_zip := :search.cus_zip;
    :customer.cus_co := :search.cus_co;
    :customer.cus_div := :search.cus_div;
    :customer.cus_status := :search.cus_status;


    So if I put the last condition as,

    IF :search.cus_status = '1' THEN
    SET_BLOCK_PROPERTY( 'customer.cus_status', DEFAULT_WHERE, 'cus_status IS NULL');
    ELSE
    SET_BLOCK_PROPERTY( 'customer.cus_status', DEFAULT_WHERE, 'cus_status IS NULL OR cus_status = ''D''');
    END IF;


    how can I append it to other WHERE clauses.Sorry for this basic question, but I am pretty new to this Forms stuff.

    Thanks

    -Faizal

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Checkbox-Checked

    Originally posted by faizalkp
    Andrew

    Thanks for the help !

    I am transalating the query condition in the PRE_QUERY event of blk2.

    My WHERE clause looks like this.(blk1-->search,blk2-->customer)

    :customer.cus_customer_no := :search.cus_customer_no;
    :customer.cus_name := :search.cus_name;
    :customer.cus_city := :search.cus_city;
    :customer.cus_state := :search.cus_state;
    :customer.cus_zip := :search.cus_zip;
    :customer.cus_co := :search.cus_co;
    :customer.cus_div := :search.cus_div;
    :customer.cus_status := :search.cus_status;


    So if I put the last condition as,

    IF :search.cus_status = '1' THEN
    SET_BLOCK_PROPERTY( 'customer.cus_status', DEFAULT_WHERE, 'cus_status IS NULL');
    ELSE
    SET_BLOCK_PROPERTY( 'customer.cus_status', DEFAULT_WHERE, 'cus_status IS NULL OR cus_status = ''D''');
    END IF;


    how can I append it to other WHERE clauses.Sorry for this basic question, but I am pretty new to this Forms stuff.

    Thanks

    -Faizal
    Well, your WHERE clause can't look like that, or it will simply fail! It would have to have "AND" between the conditions, not ";". I'm therefore not clear whether you actually have a WHERE clause, or whether you mean that the items have "Copy Value From Item" properties to link them to the control block.
    If you have "Copy Value From Item" then there is no problem, the WHERE clause will be applied in addition.
    If you have a (valid) where clause, then setting DEFAULT_WHERE will override what you have. You would have to include the entire WHERE clause in your new DEFAULT_WHERE.
    Also note that if you include all the conditions in your WHERE clause as you have shown them, then the query will return no records unless the user enters a value for each and every search item.
    Finally, note that the first parameter to SET_BLOCK_PROPERTY should be 'customer', not 'customer.cus_status'.

  5. #5
    Join Date
    Nov 2002
    Posts
    8

    Checkbox-Checked

    Tony

    When I put the DEFAULT WHERE , it is over riding the other block.

    :customer.cus_customer_no := :search.cus_customer_no;
    :customer.cus_name := :search.cus_name;
    :customer.cus_city := :search.cus_city;
    :customer.cus_state := :search.cus_state;
    :customer.cus_zip := :search.cus_zip;
    :customer.cus_co := :search.cus_co;
    :customer.cus_div := :search.cus_div;

    So none of the other condition is getting passed except the checkbox flag.

    Thanks

    Faizal

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Checkbox-Checked

    Originally posted by faizalkp
    Tony

    When I put the DEFAULT WHERE , it is over riding the other block.

    :customer.cus_customer_no := :search.cus_customer_no;
    :customer.cus_name := :search.cus_name;
    :customer.cus_city := :search.cus_city;
    :customer.cus_state := :search.cus_state;
    :customer.cus_zip := :search.cus_zip;
    :customer.cus_co := :search.cus_co;
    :customer.cus_div := :search.cus_div;

    So none of the other condition is getting passed except the checkbox flag.

    Thanks

    Faizal
    I don't really understand what you are doing. If your PRE-QUERY trigger on customer looks like this:

    BEGIN
    :customer.cus_customer_no := :search.cus_customer_no;
    :customer.cus_name := :search.cus_name;
    :customer.cus_city := :search.cus_city;
    :customer.cus_state := :search.cus_state;
    :customer.cus_zip := :search.cus_zip;
    :customer.cus_co := :search.cus_co;
    :customer.cus_div := :search.cus_div;
    IF :search.cus_status = '1' THEN
    SET_BLOCK_PROPERTY( 'customer', DEFAULT_WHERE, 'cus_status IS NULL');
    ELSE
    SET_BLOCK_PROPERTY( 'customer', DEFAULT_WHERE, 'cus_status IS NULL OR cus_status = ''D''');
    END IF;
    END;

    ... then it should be OK. The conditions based on the item assignements will be appended to the DEFAULT_WHERE at run time.

    If it is not working, then show me:

    1) the WHERE CLAUSE property of block CUSTOMER (i.e. that typed into the property page in Form Builder)

    2) the full text of the PRE-QUERY trigger

  7. #7
    Join Date
    Nov 2002
    Posts
    8

    Checkbox-Checked

    Tony

    That problem got resolved

    In the else condition ELSE

    SET_BLOCK_PROPERTY( 'customer', DEFAULT_WHERE, '(cus_status IS NULL OR cus_status = ''D'')');

    I was not including the braces it was retreving wrong number of rows earlier becasue of the OR query.When I included the braces it resolved
    the problem.

    Thanks for your valuable advice again

    -F

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •