Home » Developer & Programmer » Forms » How to stop inserting new record in tabular form based on specific condition (Forms 6i)
How to stop inserting new record in tabular form based on specific condition [message #387670] Fri, 20 February 2009 04:19 Go to next message
sindikalac
Messages: 52
Registered: November 2008
Location: Europe
Member
Hi

I have problem like this.

I have a data block, tabular form based on that data block with two fields:

ID and DATE. It's like a list of dates in a form.

I would like to stop inserting new record before the DATE field of the previous record is written.

For instance, I have situation like this:

ID DATE
1 10.10.2009
2 11.10.2009
3

Now when I want to insert a new record:

ID 4 with new DATE,

I would like to stop it, because in record 3 you must first insert the date, then you can insert record 4.

I hope I you understand my English.
Re: How to stop inserting new record in tabular form based on specific condition [message #387682 is a reply to message #387670] Fri, 20 February 2009 05:10 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Set DATE item's "Required" property to "Yes".
Re: How to stop inserting new record in tabular form based on specific condition [message #387708 is a reply to message #387682] Fri, 20 February 2009 06:12 Go to previous messageGo to next message
sindikalac
Messages: 52
Registered: November 2008
Location: Europe
Member
I forgot to say that I can't use Required because, I cannot insert date in the same time when I am inserting ID. When I get information that a job is finished I insert date for that ID (ID is for job, date is when it's finished), after that I can insert new record with new ID, but I have to wait for the information about the date. New record (job) can be inserted just when the previous job is finished meaning that the date of previous record is inserted.

[Updated on: Fri, 20 February 2009 06:16]

Report message to a moderator

Re: How to stop inserting new record in tabular form based on specific condition [message #387719 is a reply to message #387708] Fri, 20 February 2009 06:51 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is that "job ID"? Is it an Oracle job (scheduled by DBMS_JOB or DBMS_SCHEDULER, for example)? If so, why do YOU (i.e. operator) have to insert anything into a table? Job (stored procedure) is capable of inserting its ID and date (time) when it has done what it is supposed to do.

On the other hand, if this job means something "we have created a job ID = 1 which means that today someone began to dig a hole and it will take 2-3 days" so you'll enter date once the man digs the hole (which depends on weather conditions, whether there are rocks there, etc.). If so, do you have to enter ID and date separately? Why not entering both values once the job is done?

Anyway: in the WHEN-CREATE-RECORD you may count number of records where DATE column is empty; if count is greater than 0 (i.e. there is at least one job whose DATE column is NULL), raise an exception. Something like
declare
  l_cnt number;
begin
  select count(*) into l_cnt
    from this_table
    where date_column is null;

  if l_cnt > 0 then
     message('Show your literary skills here');
     raise form_trigger_failure;
  end if;
end;
Re: How to stop inserting new record in tabular form based on specific condition [message #387725 is a reply to message #387670] Fri, 20 February 2009 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You will have to ensure that new records are posted and / or committed as you enter them for that to work.
Re: How to stop inserting new record in tabular form based on specific condition [message #387733 is a reply to message #387725] Fri, 20 February 2009 07:30 Go to previous messageGo to next message
sindikalac
Messages: 52
Registered: November 2008
Location: Europe
Member
To answer the question:

That job means what you said "we have created a job ID = 1 which means that today someone began to dig a hole and it will take 2-3 days" so you'll enter date once the man digs the hole (which depends on weather conditions, whether there are rocks there, etc.). So, I have to enter ID and date separately.

I am now going to try what you suggested in WHEN-CREATE-RECORD, and I'll be back.
Re: How to stop inserting new record in tabular form based on specific condition [message #387751 is a reply to message #387733] Fri, 20 February 2009 08:22 Go to previous message
sindikalac
Messages: 52
Registered: November 2008
Location: Europe
Member
It generally works fine, but is there any other trigger which I could use because there is more to the story.

I move to the tabular form from main tabular form clicking a button after I position to the name of a person. Then I click a button and open a tabular form with the jobs (related to that person) i.e. I have two data blocks, the second one is the one we talked about. In the first block I have names of the people. When I create trigger and place it in the second data block, and when I start a form, trigger fires every time before I can even click a button to move the "job" form.

My problem is now, how to pass a name of a person I chose with the mouse to the second tabular form where I created WHEN-CREATE-RECORD.

For instance

On the first tabular form I have list of names and buttons for each name:

Name
Joe Button
Ivan Button
Ericson Button
etc.

I chose for instance Ivan and click Button and I move to the second data block (i.e. tabular form with the jobs).

In order to do select from your code I need to pass name to a trigger. I tried with global variables like this:

I put select statement in WHEN-BUTTON-PRESSED under button:

select count(*) into :global.l_cnt
from tables
where ... and field=:name;


I created WHEN-CREATE-RECORD in the second data block with the rest of your code adding just Erase('global.l_cnt');

and I declared global varibale in PRE-FORM (form level):

:global.l_cnt:=0

Problem with this is it seems to work, but when I move from one canvas to another few times, it generates strange messages.
Previous Topic: how to create global pl/sql table in form
Next Topic: How to write to Flat files in Oracle
Goto Forum:
  


Current Time: Sat Dec 10 20:17:28 CST 2016

Total time taken to generate the page: 0.16861 seconds