| saving data from multiple datablocks into new table? [message #472308] |
Thu, 19 August 2010 16:18  |
jimbob123
Messages: 6
Registered: August 2010
|
Junior Member |
|
|
Hi guys, I have a problem I just cant get my head around and wondering if anyone could help.
I have a form with 2 datablocks, datablock 1 is the datablock in which I want to write data too that table within the database.
datablock 1(which i want to insert data too) is:
In forms layout this simply consist of a display box at the top of the screen which is populated by a LOV from another table within the database. The lov value selected will then be placed in the appropriate field of the table. For example the LOV contains a list of every single car make there is. i.e. Renault, Ford, Vauxhall etc
datablock 2 (in which i just want to read data from, and write the data to the table used in datablock1) is:
Below this is my second datablock, which on the trigger 'on-new-form-instance' reads data from an already existing table and displays a list of every single make of car ie, focus, clio, megane, astra etc (tabular layout displaying 12 records at a time). Beside these car models will be a checkbox which is a non database item, originally unticked.
What I want to do is the user selects a car type from the top LOV say Renault for example, then they will go down the list of car models checking each and every model of car Renault makes, i.e clio, megane, etc when these models are all checked, and the user clicks save, it will insert into my table (which is the top datablock) the car which was selected From the LOV at the top, plus each and every car model which has a checkbox checked. So for example, say the user selects Renault from the top LOV and checks models clio, megane, laguna. There should be 3 entries in the table consisting of:
Renault, clio
Renault, Megane
Renault, Laguna
Below is a quick diagram to give a better understanding.
Now I have got it to work partially, in the fact that if I load the screen, select my car type and then multiple models that it will add 1 car type and model to the database but not the others which I have checked. I think I need it to go down each row and check if the value is ticked or something like that? using row id or something? Im not sure. Perhaps i am even approaching this using the wrong method i dont know.
Here is the code I have so far:
Just so you know car_model is the name of my table used as my second datablock, contains all car models as u would expect.
BEGIN
if :model_chkbox = 'Y'THEN
v_model_id := :car_model.id;
INSERT INTO NOTE_ACCESS_DEFAULT (car_type, model_id)
VALUES (:car_type, v_model_id);
end if;
EXCEPTION
WHEN OTHERS THEN
MESSAGE('SQLERRM: ' || SQLERRM);
END;
Another option was to do a loop and go through each record and see if it is checked, not sure how I would implement this but it does seem like a very inefficient way to do such a task, perhaps it would be something to do with when a checkbox is ticked stamp it or something. I really am unsure.
-
Attachment: work.JPG
(Size: 32.30KB, Downloaded 1699 times)
|
|
|
|
| Re: saving data from multiple datablocks into new table? [message #472375 is a reply to message #472308] |
Fri, 20 August 2010 04:28  |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I really don't see any point in basing the top block on the table. You're going to have it set to only ever display a single record when you want to insert multiple. So you can't use it to modify the data. You're not using it to query data. Might as well make it a non-database block.
There are two ways of doing this
The simple one to code:
Create a button on the screen (attach it to the first block) with a WHEN-BUTTON-PRESSED trigger that looks something like this:
go_block(<2nd block>);
first_record;
IF :model_chkbox = 'Y' THEN
INSERT INTO NOTE_ACCESS_DEFAULT (car_type, model_id)
VALUES (:car_type, :car_model.id);
END IF;
LOOP
next_record;
IF :model_chkbox = 'Y' THEN
INSERT INTO NOTE_ACCESS_DEFAULT (car_type, model_id)
VALUES (:car_type, :car_model.id);
END IF;
EXIT WHEN :system.last_record='TRUE';
END LOOP;
standard.commit;
Probably you'll also want a delete if they uncheck an item.
You'll notice the commit at the end.
Reason for that is that when you press the save button in forms oracle only does anything if a database item in one of the blocks has been changed. This code doesn't do that so as far as forms is concerned nothing needs saving.
Which ties into the problem with this approach. If the user makes some changes and then exits the screen it will not ask them if they want to save changes. Also clicking on the save button will do nothing.
The 2nd approach is more fiddly to code but avoids these issues:
Base the block on procedures.
In this case you'd base the block on a procedure that returns the list of car models, along with a dummy column that you would base the check box on. You would then also have an update procedure that would insert/delete records from note_access_default based on the current value of the checkbox.
The big advantage here is that forms will now treat the check box as a database item (it's not based on a table, but it is based on the result set of the query procedure). Consequently if you check or uncheck an item forms will realise there are changes to save and clicking on the save button will work as normal.
I haven't done a block on a procedure in years so I'm a bit hazy on the details but form builder help covers it all in detail so have a read.
Couple of miscellaneous points:
1) rowid is no use to you here.
2) always prefix datablock items with the datablock name in code.
3) If you're thinking of using key-commit at all don't - If you get the "Do you want to save changes?" message and click yes it doesn't fire key-commit. Which to my mind makes that trigger a waste of time.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
