Home » Developer & Programmer » Forms » how to select multiple records? (oracle 10g)
how to select multiple records? [message #544619] Thu, 23 February 2012 01:40 Go to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
Hello, I'd want to select multiple records in my multi-record block. I'd also want to do that with checkboxes. When the user clicks on a particular checkbox, that should be selected and whatever record the user wants to check it should be added to selected records. Any idea would be appreciated.
Re: how to select multiple records? [message #544629 is a reply to message #544619] Thu, 23 February 2012 02:23 Go to previous messageGo to next message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's a multirecord block. Every record contains a checkbox. You check some of these checkboxes - so, you actually "select" them.

That's what you said, and I agree with what you said. But - what now? What is the question? I suspect that "it should be added to selected records" means something (but I can't figure out what).
Re: how to select multiple records? [message #544632 is a reply to message #544629] Thu, 23 February 2012 02:28 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
sorry, the selected_records should be added to a text item.
Re: how to select multiple records? [message #544634 is a reply to message #544632] Thu, 23 February 2012 02:37 Go to previous messageGo to next message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't understand what you are saying.

For example, a record based on Scott's EMP table contains 8 items (character, number, date datatypes). Then, you create a checkbox. You select it, and - as a result - you want to add such a record to a text item?!? I can't imagine that, sorry.
Re: how to select multiple records? [message #544635 is a reply to message #544634] Thu, 23 February 2012 02:46 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
this is what i've been doing so far.

http://i44.tinypic.com/208uy42.jpg
Re: how to select multiple records? [message #544637 is a reply to message #544635] Thu, 23 February 2012 02:48 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
my code so far in when-checkbox-changed trigger is:

DECLARE
l_record varchar2(3);
BEGIN

if :subjects.subj_code = 'Y' then
l_record := :system.cursor_record;
first_record;
loop
if :system.cursor_record <> l_record then
:subjects.subj_code := 'N';
end if;

if :system.last_record = 'TRUE' then
exit;
end if;
next_record;
end loop;

go_record(:system.cursor_record);
end if;

END;


but i am not able to display it in my text item. FYI, my text item is in a multi-line.
Re: how to select multiple records? [message #544640 is a reply to message #544637] Thu, 23 February 2012 02:53 Go to previous messageGo to next message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I can't see the image (there's probably nothing wrong with it, I'm just not allowed to access that site). Please, attach the image to your next message - use "Reply" button in the bottom right corner of the previous message - NOT the "Quick reply" option!
Re: how to select multiple records? [message #544642 is a reply to message #544640] Thu, 23 February 2012 03:03 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
this one.
./fa/9885/0/



CM: made image directly visible. There's a "insert image into message body" link. Use it next time.
  • Attachment: lm7.JPG
    (Size: 34.66KB, Downloaded 1403 times)

[Updated on: Thu, 23 February 2012 03:08] by Moderator

Report message to a moderator

Re: how to select multiple records? [message #544643 is a reply to message #544642] Thu, 23 February 2012 03:09 Go to previous messageGo to next message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right. It seems that you'll need to loop through the first block's records and CONCATENATE values (one by one) you find in checked records. I'd suggest you to do upon a button being pressed ("display selected records" - if that's a button) instead of immediate processing (i.e. when the checkbox is checked) because you might have a problem removing values when user de-selects previously selected record.

Just being curious - what will you do with the result?

[Updated on: Thu, 23 February 2012 03:09]

Report message to a moderator

Re: how to select multiple records? [message #544644 is a reply to message #544643] Thu, 23 February 2012 03:16 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
what i want for results is that when user is done checking subjects, it should be displayed in the text item and then i'm going to put another push button for APPROVE. when the user presses APPROVE button, it should be displayed on another block for enrolment.
Re: how to select multiple records? [message #544645 is a reply to message #544644] Thu, 23 February 2012 03:19 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
I'm doing a simple subject advisement decision support system, that APPROVE button will determine if the student is only a new one(freshman), he/she is not allowed to enrol subjects that are for 2nd year.
Re: how to select multiple records? [message #544647 is a reply to message #544645] Thu, 23 February 2012 03:22 Go to previous messageGo to next message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
By FAR, it would be simpler if you alter the table and add a checkbox column (for example, CB_CHECK). Include it - as a database column - into the upper block in your form. Remove "selected rows" item.

Once the end user selects records he wants, he should COMMIT changes.

Then, pushing the APPROVE button, you'd open another form (block?) which would simply QUERY records from a table which have the CB_CHECK column checked (so that block would have the WHERE clause set) and ... well, approve these records.

That's all.
Re: how to select multiple records? [message #544651 is a reply to message #544647] Thu, 23 February 2012 03:31 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
what would be the data type for my cb_check column?
Re: how to select multiple records? [message #544653 is a reply to message #544651] Thu, 23 February 2012 03:32 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
for the mean time, i used varchar2(5) for Yes [y] and No[n]
Re: how to select multiple records? [message #544654 is a reply to message #544653] Thu, 23 February 2012 03:37 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
Varchar2(5)? For a checkbox? Varchar2(1) should be sufficient. Why would you need to store anything other than Y/N?
Re: how to select multiple records? [message #544659 is a reply to message #544654] Thu, 23 February 2012 03:44 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
ok i modified it to 1. when I run my form again, only 1 record appeared. I don't know what's wrong.
Re: how to select multiple records? [message #544661 is a reply to message #544659] Thu, 23 February 2012 03:48 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do most of the records have the new column set to null?
Re: how to select multiple records? [message #544663 is a reply to message #544661] Thu, 23 February 2012 03:52 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
yup, they are null. Now i put values into it.
Re: how to select multiple records? [message #544665 is a reply to message #544663] Thu, 23 February 2012 03:57 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
what if the user hasn't chosen a subject yet. I want to display my records with my checkbox unchecked first.
Re: how to select multiple records? [message #544666 is a reply to message #544663] Thu, 23 February 2012 03:59 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you have checkboxes or list items or radio buttons and the value in the DB column doesn't match any of the values the form item recognizes it won't display the record.
You fix this by setting the mapping of other values property, or updating the DB data so that it does match a recognized value.
I always prefer the second option.
Re: how to select multiple records? [message #544667 is a reply to message #544665] Thu, 23 February 2012 04:01 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
oh i'm sorry i think i already i got it. I have set all of them to 'n', so during runtime, my checkboxes are all unchecked
Re: how to select multiple records? [message #544670 is a reply to message #544666] Thu, 23 February 2012 04:18 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
how can i disable a row, if the student for example is a freshman, he is not allowed to take 2nd year subjects. how do i do that?
Re: how to select multiple records? [message #544671 is a reply to message #544670] Thu, 23 February 2012 04:22 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
Disable some/all of the items using set_item_property?

Depends what you mean by disable a row.
Re: how to select multiple records? [message #544672 is a reply to message #544671] Thu, 23 February 2012 04:30 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
in a tabular form, i just want to disable a particular row or changed its background color to green or something indicating that student is not allowed to take that subject
Re: how to select multiple records? [message #544673 is a reply to message #544672] Thu, 23 February 2012 04:34 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
So look at the options set_item_instance_property gives you.
Re: how to select multiple records? [message #544676 is a reply to message #544673] Thu, 23 February 2012 04:55 Go to previous messageGo to next message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you display records that do not "belong" to that student? You know who is a "freshman" - display only these subjects that belong to him. You'd do that by setting the data block's ONETIME_WHERE property (using the SET_BLOCK_PROPERTY built-in). As students change, ONETIME_WHERE property displays only records that belong to that student's "level".
Re: how to select multiple records? [message #544677 is a reply to message #544676] Thu, 23 February 2012 04:57 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
Sorry, but the requirement is that, you have to display all subjects from 1st year to 4th year. During runtime, subjects that aren't for 1st year are disabled. So, ONETIME_WHERE property is not the solution.
Re: how to select multiple records? [message #544678 is a reply to message #544677] Thu, 23 February 2012 05:01 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
I mean rows for 1st year subjects are only allowed to check. And other rows(2nd-4th year subjects) are disabled and with backgroundcolor Green.
Re: how to select multiple records? [message #544679 is a reply to message #544677] Thu, 23 February 2012 05:02 Go to previous messageGo to next message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But that's kind of stupid ... for example: in these 4 years, there are 15 subjects per year which makes 60 subjects.

Now, there's a student on the 2nd year. Why would you make someone scroll through 60 records and "disable" 45 of them, if you could easily display only 15 "selectable" subjects?

Anyway: if you don't like that, go back to what Cookiemonster has said. I guess that you should disable only the checkbox item for certain records (i.e. not ALL record items).
Re: how to select multiple records? [message #544680 is a reply to message #544673] Thu, 23 February 2012 05:03 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Thu, 23 February 2012 10:34
So look at the options set_item_instance_property gives you.

Re: how to select multiple records? [message #544681 is a reply to message #544680] Thu, 23 February 2012 05:07 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
i tried SET_ITEM_INSTANCE_PROPERTY('subjects.cb_check', ENABLED, PROPERTY_FALSE); but it didn't disable the row

[Updated on: Thu, 23 February 2012 05:08]

Report message to a moderator

Re: how to select multiple records? [message #544682 is a reply to message #544681] Thu, 23 February 2012 05:10 Go to previous messageGo to next message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Think! Don't just blindly type!

Why would set_ITEM_instance_property modify the whole RECORD?
Re: how to select multiple records? [message #544683 is a reply to message #544681] Thu, 23 February 2012 05:10 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
Of course it didn't, becuase that property can't be set at item instance.
Look it up in form builder help and see what you can set.
Re: how to select multiple records? [message #544684 is a reply to message #544681] Thu, 23 February 2012 05:12 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
ADDITIONAL INFO:
Sorry i didn't tell you that subjects that don't have prerequisites can be checked or are enabled even though it's in 2nd year subjects. Sometimes, students want to take advanced subjects that have no prerequisites so these subjects can be enrolled by that student.
Re: how to select multiple records? [message #544685 is a reply to message #544684] Thu, 23 February 2012 05:14 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
And that makes what difference to us?
Read the help topic.
Re: how to select multiple records? [message #544687 is a reply to message #544685] Thu, 23 February 2012 05:17 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
I want to display subjects(ENABLED), even though it's in 2nd year that have no prerequisites. i will try to do that.
Re: how to select multiple records? [message #544690 is a reply to message #544687] Thu, 23 February 2012 05:40 Go to previous messageGo to next message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
hi, i'm now able to change the background color of the 1st item. This is the code so far i used. taken from online help.

DECLARE
	
	cur_itm VARCHAR2(80); 
	cur_block VARCHAR2(80) := :System.Cursor_Block; 

BEGIN
Go_Block('SubjectS');
Execute_Query;

	cur_itm := Get_Block_Property( cur_block, FIRST_ITEM ); 
	IF :studinfo.yearstatus = '2nd Year' THEN
	
	WHILE ( cur_itm IS NOT NULL ) 
	LOOP 
		cur_itm := cur_block||'.'||cur_itm; 
		Set_Item_Instance_Property( cur_itm, CURRENT_RECORD, VISUAL_ATTRIBUTE,'VA_SELECTED_RECORD'); 
		cur_itm := Get_Item_Property( cur_itm, NEXTITEM ); 
	END LOOP; 

	END IF;
	
END;



but it didn't change automatically if the student is 2nd year.
Re: how to select multiple records? [message #544691 is a reply to message #544690] Thu, 23 February 2012 05:57 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
Does the above code loop through all the records in the block?
Re: how to select multiple records? [message #544692 is a reply to message #544691] Thu, 23 February 2012 05:58 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should use the post-query trigger for this anyway. No loops required.
Re: how to select multiple records? [message #544700 is a reply to message #544691] Thu, 23 February 2012 06:36 Go to previous messageGo to previous message
baliberde
Messages: 201
Registered: January 2012
Location: outer space
Senior Member
yup it looped through all records.

..so am i to omit these lines

WHILE ( cur_itm IS NOT NULL ) 
	LOOP 


END LOOP; 


[Updated on: Thu, 23 February 2012 06:37]

Report message to a moderator

Previous Topic: logon logout screen?
Next Topic: D2KWUTIL in Windows 7
Goto Forum:
  


Current Time: Sat Oct 25 23:31:36 CDT 2014

Total time taken to generate the page: 0.09866 seconds