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

User login

Frontpage Sponsor


Google search

How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
200 - 500 GB
500 - 800 GB
800 - 1200 GB
1200 - 1500 GB
1500 - 2000 GB
> 2000 GB
Total votes: 34

Baanboard at LinkedIn

Reference Content

Thread Tools Display Modes
Old 10th January 2017, 18:05
VishalMistry's Avatar
VishalMistry VishalMistry is offline
Join Date: Dec 2004
Location: India, Gujarat
Posts: 608
VishalMistry has a little shameless behaviour in the past
Baan: Baan IV, ERPLn - DB: SQL Server 2000 / 2008 - OS: Windows Server 2003 / 2008
Wink Relation between tfacp200 & tdpur046
Baan: Baan IVc4
C/S: Both

Hello everyone,

This is related to Baan IV.

we have a report requirement with following details:

Supplier Tran.type Doc.No Invoice amt Order Order amt
abc FIP 20090001 1000 100001 500
abc FIP 20090001 1000 100002 500

here, invoice FIP 2009000 is paid against two purchase orders (records from tdpur046). Is there any way to join these two tables (obviously based on ttyp and ninv) in such a way that if a matching record is not found in tdpur046, the fields from tdpur046 will be blank but the values from tfacp200 will be printed. I could not find any way to use "refers to" as there is no native relationship between these two tables. One way is I can use is as below:
tfacp200.ttyp = tdpur046.ttyp and tfacp200.ninv = tdpur046.invn



Is there any way out to overcome this problem ?

Reply With Quote
Old 12th January 2017, 17:09
mark_h's Avatar
mark_h mark_h is offline
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,146
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
I would just use a sub query inside the query on tfacp200. So query tfacp200 on your ranges. Then inside the select do query tdpur046 - in select empty 0 out the fields or clean up the fields from the previous find. There might be better ways, but this is easy for me to follow.

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
Sponsored Links
Old 3rd February 2017, 02:51
shah_bs's Avatar
shah_bs shah_bs is offline
Join Date: Jan 2002
Location: Lewisville, Texas
Posts: 390
shah_bs is on a distinguished road
Baan: BAAN IVc3 with A&D2.2b - DB: ORACLE 9 - OS: HPUX
As Mark said, you would start with tfacp200 as your 'driving' table and using the Document Type and Number as key, fetch the tdpur046 records based on that table's secondary key.

But the issue is that these set of records sometime (very rarely) break in strange ways. SO, you can have a tfacp200 record which satisfies let us say three purchase order receipts, but only two purchase order invoice records (tdpur046) are created. So it is not merely a matter of whether the tdpur046 record exists or not, but whether the SUM of amounts of tdpur046 match the amount of the tfacp200 record. Of course, when selecting records from tfacp200, you have to pick the records where the second document type and number are blank and zero, as the records in which the second document number, etc. is filled in represent those tfacp200 records related to say payments or adjustments and such.

For us at least, this was quite an expensive report to run unless properly constrained - so we had additional constraints like say analyze only one vendor at a time (since usually, the issue would have been raised by a particular vendor.) Once a year, we would run the complete report to 'repair' anything remaining.

Last edited by shah_bs : 13th February 2017 at 03:02.
Reply With Quote

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
JV Transaction in tfacp200 johnmark Tools Development 4 20th March 2013 07:06
Pick up of default Bank relation while processing payments fionad Finance, Invoicing and Integration 2 29th August 2012 18:15
Pegging Relation quelle Manufacturing & Supply Chain 5 23rd December 2009 05:06
Restoring tdpur046 records manish_patel Finance, Invoicing and Integration 9 25th April 2007 04:50
Finace - Logistic co. relation concept in LN satinderg Finance, Invoicing and Integration 2 2nd January 2006 10:57

All times are GMT +2. The time now is 21:58.

©2001-2018 - -