Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
Is it good to change the Technology per business or changing Business per Technology?
Change Technology per Business
64%
Change Business according to Technology
7%
It Depends
29%
Never change Business
0%
Total votes: 14

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 22nd September 2021, 12:07
Arlina Arlina is offline
Member
 
Join Date: Feb 2019
Posts: 49
Arlina is on a distinguished road
Baan: Infor LN - DB: MSSql - OS: Windows
Comparing Records in different Table with same structure
Baan: Infor ERP 10.7
C/S: None/Unknown

Hello,

I want to compare a customized table(let's say txnbi045) with a standard table(let's say tcmcs045) and insert/update the records in the customized table which differ from the standard table.
I referred to http://www.baanboard.com/baanboard/s...ad.php?t=69115 and did the coding as:

Code:
function extern read.main.table()
{
	long txnbi045_id
	txnbi045_id = db.bind("ttxnbi045")
	db.columns.to.record(ttxnbi045)
	db.store.record(ttxnbi045)
	if records.unequal() then
		db.retry.point()
		select	txnbi045.*
		from	txnbi045
		where 	txnbi045._index1 = {:tcmcs045.creg}
		as set with 1 rows
		selectdo	
			txnbi045.dsca = tcmcs045.dsca
			db.update(ttxnbi045, db.retry)
			commit.transaction()
		selectempty
			txnbi045.creg = tcmcs045.creg
			txnbi045.dsca = tcmcs045.dsca
			db.insert(ttxnbi045, db.retry)
			commit.transaction()
		endselect
	endif	
	
}

function domain tcbool records.unequal()
{

	rcd.ttxnbi045 = rcd.ttcmcs045
	db.record.to.columns(ttxnbi045)
	if db.record.changed(ttxnbi045) then |Not going in this loop
		return(true)
	endif
	return(false)	

}
But it is not giving the desired output. Rather the control is not going into db.record.changed(ttxnbi045) loop even though there are few additional records in table tcmcs045!

What am I doing wrong?
Reply With Quote
  #2  
Old 22nd September 2021, 15:58
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,631
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Is there more code where you read tcmcs045? Are the two tables exactly the same or what? The code I see looks okay compared to the other thread.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #3  
Old 23rd September 2021, 05:47
Arlina Arlina is offline
Member
 
Join Date: Feb 2019
Posts: 49
Arlina is on a distinguished road
Baan: Infor LN - DB: MSSql - OS: Windows
Quote:
Originally Posted by mark_h View Post
Is there more code where you read tcmcs045? Are the two tables exactly the same or what? The code I see looks okay compared to the other thread.
Thanks, mark_h!

Yes, this is the only code. Do I need to read tcmcs045 separately?
The two tables are identical in terms of the number of fields, field names, and index.
Reply With Quote
Sponsored Links
  #4  
Old 24th September 2021, 13:12
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,631
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Yes - if you look at the original like first post - he essentially reads thru the table one record at a times and then checks the other table. So I would expect there to be a read of tcmcs045 with your code inside the selectdo statement.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #5  
Old 27th September 2021, 05:52
Arlina Arlina is offline
Member
 
Join Date: Feb 2019
Posts: 49
Arlina is on a distinguished road
Baan: Infor LN - DB: MSSql - OS: Windows
Quote:
Originally Posted by mark_h View Post
Yes - if you look at the original like first post - he essentially reads thru the table one record at a times and then checks the other table. So I would expect there to be a read of tcmcs045 with your code inside the selectdo statement.
Thanks, mark_h for the response.

Let me try that.
Thanks & Regards,
Arlina

Last edited by Arlina : 27th September 2021 at 06:40. Reason: Got pointer
Reply With Quote
  #6  
Old 27th September 2021, 10:20
Arlina Arlina is offline
Member
 
Join Date: Feb 2019
Posts: 49
Arlina is on a distinguished road
Baan: Infor LN - DB: MSSql - OS: Windows
As per the suggestion given by mark_h, I made changes to the code.

Code:
function extern read.main.table()
{
	long txnbi045_id,tcmcs045_id 
	db.retry.point()
	tcmcs045_id = db.bind("ttcmcs045")
	select	tcmcs045.*
	from	tcmcs045
	selectdo
		txnbi045_id = db.bind("ttxnbi045")
		db.columns.to.record(ttxnbi045)
		db.store.record(ttxnbi045)
		if records.unequal() then
			select	txnbi045.*
			from  	txnbi045 for update
			where 	txnbi045._index1 = {:tcmcs045.creg}
			as set with 1 rows
			selectdo	
				txnbi045.dsca = tcmcs045.dsca
				db.update(ttxnbi045, db.retry)
				commit.transaction()
			selectempty
				txnbi045.creg = tcmcs045.creg
				txnbi045.dsca = tcmcs045.dsca
				db.insert(ttxnbi045, db.retry)
				commit.transaction()
			endselect
		endif
	endselect
}

function domain tcbool records.unequal()
{

	rcd.ttxnbi045 = rcd.ttcmcs045
	db.record.to.columns(ttxnbi045)
	if db.record.changed(ttxnbi045) then
		return(true)
	endif
	return(false)	

}
Table txnbi045 is getting updated now. But again, the code doesn't give which all records are actually updated/inserted, rather it just gives the indication that the table is changed, and needs to go through each record of tcmcs045 for the modification of txnbi045, am I right?
Reply With Quote
  #7  
Old 27th September 2021, 13:14
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,631
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
That is the way it looks. I keep thinking you would have a buffer with a tcmcs045 record and a buffer with the ttxnbi045 record in it. When you run thru the code and hit this line rcd.ttxnbi045 = rcd.ttcmcs045 - it will always be different and possible have a change. So to me I would get a record in tcmcs045, look for a match in ttxnbi045 - does not find one do an insert. If it find one, then it would do records.unequal subroutine. If the records are unequal - then update the description or what ever fields might be different.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #8  
Old 29th September 2021, 13:12
Arlina Arlina is offline
Member
 
Join Date: Feb 2019
Posts: 49
Arlina is on a distinguished road
Baan: Infor LN - DB: MSSql - OS: Windows
Smile

Thanks, mark_h for all the triggers!
This is what finally worked for me.
Code:
function extern read.main.table()
{
	long txnbi045_id,tcmcs045_id 
	db.retry.point()
	tcmcs045_id = db.bind("ttcmcs045")
	select	tcmcs045.*
	from	tcmcs045
	selectdo
		txnbi045_id = db.bind("ttxnbi045")
			select	txnbi045.*
			from	txnbi045 for update
			where 	txnbi045._index1 = {:tcmcs045.creg}
			as set with 1 rows
			selectdo
				
				if records.unequal() then
					txnbi045.dsca = tcmcs045.dsca
					db.update(ttxnbi045, db.retry)
					commit.transaction()
				endif
			selectempty
				txnbi045.creg = tcmcs045.creg
				txnbi045.dsca = tcmcs045.dsca
				db.insert(ttxnbi045, db.retry)
				commit.transaction()
			endselect
		
	endselect

	
}

function domain tcbool records.unequal()
{
	if rcd.ttxnbi045 <> rcd.ttcmcs045 then
		return(true)
	endif
	return(false)	

}
Reply With Quote
  #9  
Old 29th September 2021, 13:40
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,631
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Glad you got it working and thanks for posting the solution.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Use of "background" keyword in Baan kruyoupatel Tools Development 3 24th September 2020 16:43
Zoom Menu Return Multi Value layswan Tools Development 3 24th July 2012 09:42
zoom to correct business partner jojovalenton Tools Development 5 14th October 2009 10:26
Tables used for MRP and PRP. BurghMan Tools Development 1 10th February 2005 14:08
copy records c_siek Tools Development 9 18th June 2004 15:43


All times are GMT +2. The time now is 10:34.


©2001-2021 - Baanboard.com - Baanforums.com