Home » Developer & Programmer » Forms » Validation of multirecord block
Validation of multirecord block [message #234289] Mon, 30 April 2007 13:32 Go to next message
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,
Re: Validation of multirecord block [message #234469 is a reply to message #234289] Tue, 01 May 2007 13:07 Go to previous messageGo to next message
mgore
Messages: 4
Registered: April 2007
Junior Member
Any pointers? Any ideas?
Re: Validation of multirecord block [message #234492 is a reply to message #234469] Tue, 01 May 2007 15:30 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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?
Re: Validation of multirecord block [message #234493 is a reply to message #234492] Tue, 01 May 2007 15:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
Re: Validation of multirecord block [message #234498 is a reply to message #234497] Tue, 01 May 2007 16:09 Go to previous messageGo to next message
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 Go to previous message
Littlefoot
Messages: 20901
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.
Previous Topic: saving certain records
Next Topic: order by in tabular form
Goto Forum:
  


Current Time: Sat Dec 10 01:02:48 CST 2016

Total time taken to generate the page: 0.10219 seconds