OraFAQ Forum: Forms » Validation of multirecord block

Home » Developer & Programmer » Forms » Validation of multirecord block

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Validation of multirecord block [message #234289]

Mon, 30 April 2007 13:32

mgore
Messages: 4
Registered: April 2007

Junior Member

Need help in developing multi-record validation logic.

I have master detail kind of form. Based on one of the values on master block I am opening another block for entry. This block will have values like

subject start Date end date
history 01/30/2007 03/31/2007
history 04/15/2007
Geography 02/20/2005

in short there are validation which should check for overlapping dates. There should be validation for duplicate records as well. so based on value of subject I need to validate records with same subject line. I am facing difficulty building logic. any pointers?

Right now I am storing values in temp table for that subject and using that temp table for all validations. Is it good path to follow on?

Can anyone suggest any better / easier approach?

Thx,

Report message to a moderator

Re: Validation of multirecord block [message #234469 is a reply to message #234289]

Tue, 01 May 2007 13:07

mgore
Messages: 4
Registered: April 2007

Junior Member

Any pointers? Any ideas?

Report message to a moderator

Re: Validation of multirecord block [message #234492 is a reply to message #234469]

Tue, 01 May 2007 15:30

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Duplicates are easy - create unique index (or variations - primary / unique key) on those columns. It will - on database level - prevent entering duplicates into a table.

Overlapping periods: check that there's no record whose start_time starts before any other end_time for the same subject.

You have mentioned "temporary table"; but, where did you take those records from? Why don't you perform validation in the "original" table? What prevents you from doing so?

Report message to a moderator

Re: Validation of multirecord block [message #234493 is a reply to message #234492]

Tue, 01 May 2007 15:37

mgore
Messages: 4
Registered: April 2007

Junior Member

Thanks littlefoot.

There is unique index on that table with all the three columns so dups are taken care off.

As I enter records on form I need to validate for overlapping dates. As and when record is entered on the form I am putting that in record group (array table). Facing issues as when record is cleared or deleted its gettign hard to keep track of that array record group. Also before saving if I change values in those columns my array is going out of sync.

[Updated on: Tue, 01 May 2007 15:49]

Report message to a moderator

Re: Validation of multirecord block [message #234497 is a reply to message #234493]

Tue, 01 May 2007 16:00

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How about using a global temporary table instead of an array? Base your form on it, apply all validations on it and - once you decide it (perhaps using a "Save changes now" button?) - move records from the GTT into a "real" table.

Report message to a moderator

Re: Validation of multirecord block [message #234498 is a reply to message #234497]

Tue, 01 May 2007 16:09

mgore
Messages: 4
Registered: April 2007

Junior Member

How do I create global temp table? Did not mean to ask the syntax. But wondering how to create it inside form session. Will CREATE GLOBAL TEMPORARY TABLE command work?

[Updated on: Tue, 01 May 2007 16:14]

Report message to a moderator

Re: Validation of multirecord block [message #234500 is a reply to message #234498]

Tue, 01 May 2007 16:23

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Don't create it on-the-fly - create it using (any) SQL*Plus session (using CREATE GLOBAL TEMPORARY TABLE statement). It should remain created all the time; do not drop / recreate it. If you are used to temporary tables from another RDBMS's (MS SQL Server, perhaps?), Oracle's GTT's are NOT those you used to use there. If you are unfamiliar with GTT's, check the documentation.

Even if more than one user uses it, every one of them will see only its own data, so - don't worry about it.

Report message to a moderator

Previous Topic:	saving certain records
Next Topic:	order by in tabular form

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 13 01:09:56 CST 2025