OraFAQ Forum: Forms » How to Identify changed records after querying?

Home » Developer & Programmer » Forms » How to Identify changed records after querying? (Oracle Forms 10.1.2.0.2, Oracle 11g DB)

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

How to Identify changed records after querying? [message #651229]

Fri, 13 May 2016 05:25

adfnewbie
Messages: 54
Registered: January 2016

Member

Hi friends,
I have a non-DB block with 10 fields out of which 8 are read-only(only for display) and 2 are LOV's which user will select. Question is, how can I know for which record user has changed the value from LOV if there are, say, 100 records displayed on the block.
I have defined a cursor which fetches these 100 records and I am displaying this data row-by-row in this non-db block. After querying this data, user will change some records randomly by selecting from LOV's and hit some save/copy button. How do I identify which records have been changed and needs to be updated in database?

Any suggestions/help is highly appreciated. Thanks

Report message to a moderator

Re: How to Identify changed records after querying? [message #651231 is a reply to message #651229]

Fri, 13 May 2016 06:00

azamkhan
Messages: 557
Registered: August 2005

Senior Member

If I understood correctly, because you are using a control block you cannot check the system variables for status check.

If that is the issue. I think you should two field for each list item. And on when-list-change event you should populate a value in the field respected field of the list time and you will know for which record user has changed the value from LOV

Report message to a moderator

Re: How to Identify changed records after querying? [message #651232 is a reply to message #651231]

Fri, 13 May 2016 06:05

adfnewbie
Messages: 54
Registered: January 2016

Member

Hi Azam,

I am not using list-item in particular. I have created a normal text item, an LOV with a radio group and assigned this LOV to the text item. So when-list-change will not be available for me to use, is that correct?

Report message to a moderator

Re: How to Identify changed records after querying? [message #651233 is a reply to message #651232]

Fri, 13 May 2016 06:13

azamkhan
Messages: 557
Registered: August 2005

Senior Member

Ok what I understood is that you have a none DB text item on which you have an LOV.

If that is the case. I think you should use the event KEY-LISTVAL

Report message to a moderator

Re: How to Identify changed records after querying? [message #651234 is a reply to message #651233]

Fri, 13 May 2016 06:28

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

The obvious question is why can't you make this a DB block?

Report message to a moderator

Re: How to Identify changed records after querying? [message #651237 is a reply to message #651234]

Fri, 13 May 2016 06:48

adfnewbie
Messages: 54
Registered: January 2016

Member

I didn't think of making it a DB block because almost all the fields are from different tables. I wrote a cursor select with multiple joins and showing the result-set in the fields on the multi-record block in consideration. And the only fields which will hit the database are the two LOV field values which user is changing/selecting.

Report message to a moderator

Re: How to Identify changed records after querying? [message #651267 is a reply to message #651237]

Fri, 13 May 2016 14:19

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

Senior Member
Account Moderator

I'd still try to do it using a database block, especially if those two items belong to the same table. Non-database items can easily be populated using the same SELECT statements in two triggers: WHEN-VALIDATE-ITEM (while inserting/updating values) and POST-QUERY (when querying records from the database).

Otherwise, you could create a VIEW, base a database block on that view, create INSTEAD OF triggers which would take care of proper inserts/updates via the form. Alternatively, you could use Forms' ON-UPDATE and ON-INSERT triggers.

Report message to a moderator

Re: How to Identify changed records after querying? [message #651351 is a reply to message #651267]

Mon, 16 May 2016 04:12

adfnewbie
Messages: 54
Registered: January 2016

Member

Littlefoot, The columns are not from the same table but I will definitely try that. Thanks... And since those 2 columns are LOV's, I want to save some id in the table based on what is selected in LOV. Eg-If I select a city name from the list, I want to store city_id of the city in the database. Is it something to do with the return items? I'm new to this, seeking help...

Report message to a moderator

Re: How to Identify changed records after querying? [message #651432 is a reply to message #651351]

Tue, 17 May 2016 14:25

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

Senior Member
Account Moderator

LoV's query will have to select both CITY_ID and CITY_NAME. A form should have both items, i.e. "targets" for those values. When creating a LoV (do use a Wizard!), you'll be asked to specify return items. You'd then select CITY_ID item for CITY_ID LoV column, and CITY_NAME item for CITY_NAME LoV column.

If you are not sure how to properly do that, run the Forms Builder; navigate to "Help" menu; open Online Help System; Search for "list of values"; read the results you find appropriate.

Report message to a moderator

Re: How to Identify changed records after querying? [message #651462 is a reply to message #651432]

Wed, 18 May 2016 06:17

adfnewbie
Messages: 54
Registered: January 2016

Member

Thanks Littlefoot, I am capturing the values exactly as you said, but I am not finding how to understand for which records value has been changed (or selected from LOV). I am not doing execute_query to populate the data in the DB data block. I am using a cursor for loop and assigning the values to non-db items in that datablock. Now if I populate 10 records, how will I know which records have been changed so that I can update the same in DB. Also, the issue is whatever fields are available/visible on form, none of them are from table (i.e., all those fields are non-db items) on which data block is built

Report message to a moderator

Re: How to Identify changed records after querying? [message #651466 is a reply to message #651462]

Wed, 18 May 2016 07:53

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Are the editable fields from the same table?
If they are is the block based on that table?

Report message to a moderator

Re: How to Identify changed records after querying? [message #651469 is a reply to message #651466]

Wed, 18 May 2016 08:42

adfnewbie
Messages: 54
Registered: January 2016

Member

No, the editable fields are not from the table. Actual case example is, editable fields are LOV's and they provide list of cities, and based on selection, I have to save/update corresponding city_id (derived from another table)

Report message to a moderator

Re: How to Identify changed records after querying? [message #651470 is a reply to message #651469]

Wed, 18 May 2016 09:44

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

I'll rephrase - are the two fields that actually get saved to the DB (city_id and other) on the same table?
Cause if they are, and the block is based on that table, then you don't need any update code, forms will handle it automatically.

Report message to a moderator

Re: How to Identify changed records after querying? [message #651472 is a reply to message #651229]

Wed, 18 May 2016 10:10

adfnewbie
Messages: 54
Registered: January 2016

Member

Yes, the 2 fields which get saved are on the same table but forms will handle updating automatically only when when I query the records from db onto the form right? Forms will not take care of updating automatically in this case because it cannot understand which record to be updated in db, it will only create a new record everytime

Report message to a moderator

Re: How to Identify changed records after querying? [message #651473 is a reply to message #651472]

Wed, 18 May 2016 10:29

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

If it's a DB block then you have to use execute_query to populate it, that's not optional.
You can use selects/cursors for the non-db items but the DB items have to be populated by excute_query, otherwise forms will think the record is a new one and try do an insert.
So use execute_query

Report message to a moderator

Re: How to Identify changed records after querying? [message #651474 is a reply to message #651473]

Wed, 18 May 2016 10:41

adfnewbie
Messages: 54
Registered: January 2016

Member

I understand that but the data which I want to show is not from that table alone. All the fields which are on the form are non-db items, there are no db items shown on the form, so I am using a select with multiple joins to get that data.

Report message to a moderator

Re: How to Identify changed records after querying? [message #651475 is a reply to message #651474]

Wed, 18 May 2016 10:43

adfnewbie
Messages: 54
Registered: January 2016

Member

I just made it a db block so that I might have a chance at using system variables like record_status, block_status, etc

Report message to a moderator

Re: How to Identify changed records after querying? [message #651476 is a reply to message #651475]

Wed, 18 May 2016 10:45

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

That still doesn't explain why you can't use execute query and then populate the non-DB items using selects in the post-query trigger.
Try it.

Report message to a moderator

Re: How to Identify changed records after querying? [message #651478 is a reply to message #651476]

Wed, 18 May 2016 11:04

adfnewbie
Messages: 54
Registered: January 2016

Member

execute_query will pull all the records, or is it possible to filter the result set to be displayed?

Report message to a moderator

Re: How to Identify changed records after querying? [message #651479 is a reply to message #651478]

Wed, 18 May 2016 11:19

adfnewbie
Messages: 54
Registered: January 2016

Member

cursor c1 is select a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r from x,y,z where x.a=y.b and y.c=z.d and x.e='123';

This is my query to pull the data. Let's assume it gives me 10 records. User will always work with these 10 records, he cannot create an eleventh one. This data is only for informative purpose. Looking at this data, user will decide for which record he wants to insert/update by making selections from the 2 lov's provided. Problem is, user will not know if that record exists in system or not, it's for me to handle that if a record corresponding to user's selection is available/present in the table, it should simply update, if not, it should create a new one.

Report message to a moderator

Re: How to Identify changed records after querying? [message #651500 is a reply to message #651479]

Thu, 19 May 2016 03:54

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

I think you're going to have to describe what you're trying to do in more detail with a more meaningful example than a load of letters.
Why 10 records?
How do you know if the record exists in the system or not?

Report message to a moderator

Re: How to Identify changed records after querying? [message #652349 is a reply to message #651500]

Tue, 07 June 2016 11:38

adfnewbie
Messages: 54
Registered: January 2016

Member

I will create a doc and update in a day or two about the whole scenario. Thanks anyway for all the guidance so far

Report message to a moderator

Previous Topic:	Printing reports directly [merged]
Next Topic:	slow calling form 10 g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 04:54:55 CST 2025