forms updating multiple records [message #449602] |
Tue, 30 March 2010 15:27 |
jemkeith
Messages: 26 Registered: May 2006
|
Junior Member |
|
|
I have a form with two data blocks, one parent, one child block.
The parent is holds mineral lease info while the child holds the mineral owner info, such as addresses and phone numbers. One owner can be in the owner block multiple times (different owner types). The form only displays one owner at a time.
We have a separate master owner table which holds owner address. (We set it up this way because we get electronic info from mineral companies that we have to load each year).
As you tab through the owner block, it checks the FEIN against the master table and pulls updated address info from the master table. I have a problem in which if an owner is on the lease multiple times, when you tab through the first instance, it pulls in the new address info, but when you go to the next instance, it won't update. If you requery, it seems that the first update actually updated all the owner records on that lease. How can I turn this off?
|
|
|
|
|
Re: forms updating multiple records [message #449686 is a reply to message #449624] |
Wed, 31 March 2010 07:49 |
jemkeith
Messages: 26 Registered: May 2006
|
Junior Member |
|
|
This is the SQL I use to get the new address from our address table.
DECLARE
err_code NUMBER; /* Error Trapping for Code */
err_text VARCHAR2(255) ; /* Error Trapping for Message */
BEGIN
/*make sure that the entity has an FEIN and that it is in CURRADD */
if :intid is not null and check_ssn_curradd(:intid) > 0 then
SELECT name1, name2, address, city, st, zip, exempt
INTO :name1, :name2, :address, :city, :st, :zip, :exempt
FROM curradd
WHERE ssnfin = :intid;
else
null;
end if;
--:end intid is not null
exception
when no_data_found then
null;
when others then
err_code := sqlcode;
err_text := sqlerrm;
message('Error: '||to_char(err_code) ||'-'||err_text) ;
END;
|
|
|
|
|
|
|
Re: forms updating multiple records [message #449738 is a reply to message #449602] |
Wed, 31 March 2010 10:36 |
cookiemonster
Messages: 13938 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The obvious question is why you are doing this in this form at all?
If you've got a new set of address data from somewhere and you want to update existing records with the new data then wouldn't it make more sense to update all the records as a batch process when you get the new data?
Or if you want more control create a form specifically for the job and show the users the new address and then let them press a button or something if they want to update it.
What you've got it the moment is going to cause the users to constantly get messages asking them if they want to save data that they haven't realised they've changed.
|
|
|
Re: forms updating multiple records [message #449740 is a reply to message #449738] |
Wed, 31 March 2010 10:54 |
jemkeith
Messages: 26 Registered: May 2006
|
Junior Member |
|
|
Part of it is that we've always done it this way... Part of it is that I inherited these forms that have been basically unchanged since 1998, and I have been updating them, slowly, as I can. Another part is that I have no DBA or database design training, and I've learned what little I know from books and the web.
We get data in a spreadsheet then I load it to our database, replacing any data that we may have for a company.
The data we get may or may not have a correct address. We have a rule that if our address data hasn't been updated for over a year, we take the new address, otherwise we use our existing address data. That rule is implemented through a different trigger that I forgot to include. This trigger fires when the record is committed. It is below.
If we update the address table, it does have triggers that update the other tables.
DECLARE
CheckDate DATE := to_date('08/19/09', 'MM/DD/YY') ;
/* Date chosen as cutoff */
DateEntered DATE := NULL;
/* Date Variable */
err_code NUMBER;
/* Error Trapping for Code */
err_text VARCHAR2(255) ;
/* Error Trapping for Message */
BEGIN
IF :intid IS NOT NULL THEN
execute_trigger('deo_update') ;
execute_trigger('dtentry_update') ;
IF check_ssn_curradd(:intid) = 0 THEN
--message(:intid|| :name1|| :name2|| :address|| :city|| :st|| :zip|| :deo|| :dtentry);
--temp message for error checks
insert_curradd(:intid, :name1, :name2, :address, :city, :st, :zip, :deo, :dtentry) ;
-- end if check_ssn_curradd(:intid) = 0
ELSIF get_curradd_date_entered(:intid) >= CheckDate OR check_Supdate_curradd(:intid) = 1 THEN
SELECT
name1, name2, address, city,
st, zip, exempt
INTO
:name1, :name2, :address, :city,
:st, :zip, :exempt
FROM
curradd
WHERE
ssnfin = :intid;
ELSIF check_Supdate_curradd(:intid) = 0 THEN
update_curradd(:intid, :name1, :name2, :address, :city, :st, :zip, :deo, :dtentry) ;
END IF; -- end if get_curradd_date_entered(:intid) => CheckDate
END IF; --:end intid is not null
EXCEPTION
WHEN no_data_found THEN
NULL;
WHEN OTHERS THEN
err_code := SQLCODE;
err_text := sqlerrm;
MESSAGE('Error: '||TO_CHAR(err_code) ||'-'||err_text) ;
END; [EDITED by DJM: cut overly long line]
[Updated on: Thu, 01 April 2010 01:50] by Moderator Report message to a moderator
|
|
|
Re: forms updating multiple records [message #449741 is a reply to message #449602] |
Wed, 31 March 2010 11:03 |
cookiemonster
Messages: 13938 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That code doesn't mean a lot to me, lots of context missing here. First off how exactly is that code called, what trigger are you using?
2nd if that code is modifying the data for you why have you get the other code in key-next-item as well?
|
|
|
Re: forms updating multiple records [message #449744 is a reply to message #449741] |
Wed, 31 March 2010 11:37 |
jemkeith
Messages: 26 Registered: May 2006
|
Junior Member |
|
|
The second trigger is called when someone either tabs past the zip code, or when the record is committed.
I don't know why the code is on the key-next-item... I will look and see if I can get rid of it.
|
|
|
Re: forms updating multiple records [message #449751 is a reply to message #449744] |
Wed, 31 March 2010 12:43 |
cookiemonster
Messages: 13938 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
jemkeith wrote on Wed, 31 March 2010 17:37The second trigger is called when someone either tabs past the zip code, or when the record is committed.
The exact trigger please: KEY-COMMIT, POST-FORMS-COMMIT, POST-DATABASE-COMMIT, what?
|
|
|
Re: forms updating multiple records [message #449759 is a reply to message #449751] |
Wed, 31 March 2010 13:26 |
jemkeith
Messages: 26 Registered: May 2006
|
Junior Member |
|
|
I'm sorry it is key-commit.
I finally figured out what the first trigger is for. If someone enters data manually, it pulls the address from the address table, after the zip code field, it runs the other trigger checking to see if the address table should be updated with new info or not.
There was a mistake in the trigger, it should have checked to see if the name1 field was null before it pulled address data. I made a few other adjustments to some other triggers and the problem seemed to go away.
|
|
|