Home » Developer & Programmer » Forms » how to validate a record in oracle form?
how to validate a record in oracle form? [message #202206] Wed, 08 November 2006 14:03 Go to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi ,

I have a form in which a block displaying 5 rows at a time.

The block display items is like

Routing_set_name,Alloy,Planner_code,Group_name,Percent,Routing_item.

I have to do a validation for any new or existing record with respect to percent column.

No routing_set_name should exceed 100%.

eg. valid data
Routing_set_name,Alloy,Planner_code,Group_name,Percent,Routing_item. 
RS1                    A1      P1          G1         100 RI1 

so if any one trying to enter another RS1(routing_set_name) record form should throw an error as RS1 already has 100%.

How can i do this kind of validation in form?

Thanks in advance

Prashant Pathak

Mod-Upd: So it fits on one page.

[Updated on: Wed, 08 November 2006 18:18] by Moderator

Report message to a moderator

Re: how to validate a record in oracle form? [message #202207 is a reply to message #202206] Wed, 08 November 2006 14:07 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
I have writtern KEY-NEXT-ITEM trigger on percent item calling program unit doing a validation as following but not working.
IF event_name = 'KEY-NEXT-ITEM' THEN
  DECLARE
  l_percent 	NUMBER :=0 ;
    	BEGIN

		first_record;
		LOOP

			IF (:system.Last_record = 'TRUE') THEN
				EXIT;
			ELSE 
			message(:SMCBOM_ROUTING_MODELS.ROUTING_SET_NAME);
				BEGIN
					select nvl(SUM(percent),0)
					into   l_percent
					from   apps.smcbom_routing_models 
					where  routing_set_name = :SMCBOM_ROUTING_MODELS.ROUTING_SET_NAME;
	
					IF l_percent >= 100 THEN
						message('Percent...'||'-'||to_char(l_percent));
					ELSE
						message('Percent...'||'-'||to_char(l_percent));
					END IF;	
				EXCEPTION
				WHEN NO_DATA_FOUND THEN
				l_percent := 0;
				WHEN OTHERS THEN
				l_percent := 0;
				END;
			next_record;
			END IF;
		END LOOP;
	EXCEPTION
	WHEN OTHERS THEN
	l_percent := 0;
	END;
	END IF;

any suggestion....

Thanks in advance

Thanks & Regards
Prashant Pathak

[Updated on: Wed, 08 November 2006 18:21] by Moderator

Report message to a moderator

Re: how to validate a record in oracle form? [message #202234 is a reply to message #202207] Wed, 08 November 2006 18:28 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
If you have only one 'Routing_set_name' displayed at a time then you could use the aggregation facility of Oracle Forms.

To get only one 'Routing_set_name' in a block I suggest using a master-detail relationship and only permit data entry in the detail block. You can then do a When-Validate-Item on the 'Percent' item that adds the current value to the aggregated value and fails if they are greater than 100%.

I am assuming that you are using Forms 6 or higher.

David
Re: how to validate a record in oracle form? [message #202235 is a reply to message #202234] Wed, 08 November 2006 18:46 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi David,

I am using 4.5.

I am sorry i am really new to form so i might not understand what you are saying it will take some time get a grip.....

Is there any way only check for a perticular records.

Thanks in advance
Prashant Pathak
Re: how to validate a record in oracle form? [message #202236 is a reply to message #202234] Wed, 08 November 2006 18:56 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi David,

The scenario is like ...

There will be 5 records in block displayed at time.

So a user wants add/update record using this form he can add/update any line on form.

so on-insert/on-update for a perticular routing_set_name i have to check that any combination not crossed 100.

Let me know if you any suggestion.

Thanks in advance
Prashant Pathak
Re: how to validate a record in oracle form? [message #202255 is a reply to message #202236] Wed, 08 November 2006 20:48 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Unfortunately, the aggregate facilities don't exist in Forms 4.5 so we will have to use another method.

Please reread the Forms documentation. Basically, you should only use the 'ON-' triggers when working with non-Oracle databases. Unfortunately, you can't loop through the records in the 'Pre-' triggers so we have to come up with another method.

Do you have a table that will store the accummulated 'Percent'? I hope you don't as, from a database design point of view, it is highly redundant.

We could 'commit' after each record and then do a 'select' on the database and add the current record's value to it and then do the test. Alternatively, build a 'record_group' with the 'Routing_set_name' and accumulated 'Percent' and use it in the When-Validate-Item trigger on the 'Percent' field.

David
Re: how to validate a record in oracle form? [message #202257 is a reply to message #202255] Wed, 08 November 2006 20:56 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi David,

Thanks a lot for the reply.

Do you have a table that will store the accummulated 'Percent'?
>> NO
I hope you don't as, from a database design point of view, it is highly redundant.
>> Correct

We could 'commit' after each record and then do a 'select' on the database and add the current record's value to it and then do the test.
>> Can you please explain how can get total? i mean...on which trigger i can total.

Alternatively, build a 'record_group' with the 'Routing_set_name' and accumulated 'Percent' and use it in the When-Validate-Item trigger on the 'Percent' field.
>> I will read for record_group how to create that? will give you an update if i found some things.

Thanks a lot

Thanks & Regards
Prashant Pathak
Re: how to validate a record in oracle form? [message #202259 is a reply to message #202257] Wed, 08 November 2006 21:45 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
>> Can you please explain how can get total? i mean...on which trigger i can total.
All the work is done in the When-Validate-Item of the 'Percent' field. You would do something like
begin
   select sum (percent)
     into :ctrl.percent_sum
     from smcbom_routing_models
    where routing_set_name = :smcbom_routing_models.routing_set_name;

   if :ctrl.percent_sum + :smcbom_routing_models.percent > 100 then
      message ('Percent too big.  Max. percent left is '
               || to_char (100 - :ctrl.percent_sum, '990.99') );
      bell;
      raise form_trigger_failure;
   end if;
end;
This is probably the simplest. Just remember to have a 'commit_form' in a 'Post-Record' trigger. You may wish to test if the record status is 'changed' before requesting the 'commit' otherwise you will get the message 'No changes to be saved' if you haven't changed anything.

David
Re: how to validate a record in oracle form? [message #202267 is a reply to message #202259] Wed, 08 November 2006 22:06 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi David,

Thanks a lot for the reply.

Will try this and update you if i am successful.

Thanks again for the great support.


Regards
Prashant pathak
Re: how to validate a record in oracle form? [message #202376 is a reply to message #202259] Thu, 09 November 2006 07:19 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi David,

Thanks a lot.

It worked.......

Thanks again.
Will ask you if i have some more confusing question

Regards
Prashant Pathak
Re: how to validate a record in oracle form? [message #202404 is a reply to message #202376] Thu, 09 November 2006 10:16 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi David,

Can you please help in following?

I want to copy/duplicate some records in same block.
Block is have following column.

Routing Set Name , Alloy , Planner Code , Group , Percent and a copy Button.

When a copy button pressed take the current record's routing set name and duplicate all record in same block.

Eg.

Routing Set Name , Alloy , Planner Code , Group , Percent
R A P G 80
R A1 P G 20

When copy button pressed duplicate above two record in same block but do not insert into base table.

How to do that?

Thanks in advance
Prashant Pathak
Re: how to validate a record in oracle form? [message #202428 is a reply to message #202404] Thu, 09 November 2006 14:18 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no need to code that; Oracle Forms already provide such a functionality.

Open a new, empty record below the one you'd like to duplicate. From the Record menu select "Duplicate Record" and that's it.

For list of shortcut keys press <Ctrl+K>.
Previous Topic: Diff bet Rownum And Rowid (ORA-01445 using ROWID in Forms)
Next Topic: Difference between RUN_PRODUCT,RUN_REPORT AND RUN_REPORT_OBJECT
Goto Forum:
  


Current Time: Fri Dec 09 19:29:18 CST 2016

Total time taken to generate the page: 0.20835 seconds