Kenny it look like AP_TransactionPaymentHistory (the old API file) is used as an intermediary go between the 2 tables. The CheckHistoryHeaderSeqNo in there is how the AP_CheckHistoryHeader record is uniquely identified. Here are the specifics if interested:
Look at that AP_TransactionPaymentHistory table in File Layouts, you'll see a KBANKREC key there. It reads AP_TransactionPaymentHistory via KBANKREC key (not KPrimary) and passes in from BR_Transaction the BankCode, DocumentType, CheckNo (into BRCheckNo), BRACHCheckSeqNo, CheckDate, and EntryNo fields.
Once it lands on the AP_TransactionPaymentHistory record, next it updates the ClearedDate and ClearedBank in there. Then from that very same AP_TransactionPaymentHistory record, it takes the BankCode, CheckNo, and CheckHistoryHeaderSeqNo and reads the related record out of AP_CheckHistoryHeader (using regular KPrimary key).
Finally in AP_CheckHistoryHeader it updates the ClearedDate and ClearedBank.
------------------------------
Alnoor Cassim
Accounting Systems, Inc. (ASI)
Email:
alnoor@asifocus.comOrange County, CA
------------------------------
Original Message:
Sent: 05-11-2020 18:19
From: Kenny Daniel
Subject: Link BR Transactions to AP Vendor
Jeff, unfortunately it is not. If you compare the same check number in both files you will see that in HistoryHeader they are mostly 000000 whereas in BR it could be 000001 or anything else for that matter. Maybe I'm wrong but that's what i'm seeing.
------------------------------
Kenny Daniel
Technoclarity, Inc.
------------------------------
Original Message:
Sent: 05-11-2020 18:05
From: Jeff Schwenk
Subject: Link BR Transactions to AP Vendor
Kenny,
The entry number field is your sequence number
------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
Original Message:
Sent: 05-11-2020 17:35
From: Kenny Daniel
Subject: Link BR Transactions to AP Vendor
Thank you @Alnoor Cassim. Very clever but you already knew that :) This will work for what i'm trying to do. Any idea how Sage is linking BR_Transaction to AP_CheckHistoryHeader? There is no CheckSeq in BR_Transaction. If you have the same check number twice or do a Reversal how do they update the right CheckSeq with the ClearedDate? Not asking you to spend more time on this because your solution will work. I'm just curious if you already know.
------------------------------
Kenny Daniel
Technoclarity, Inc.
Original Message:
Sent: 05-11-2020 16:00
From: Alnoor Cassim
Subject: Link BR Transactions to AP Vendor
Using Jeff's note about the Reference No field it is possible with Crystal via subreport. In the subreport itself adjust the linking in the Record Selection to grab only the 1st 2 characters from ReferenceNo for the APDivisionNo and the next 7 for the VendorNo. Here is an example of adding Vendor Name based on this subreport link parsing (v2019 pointed to ABC demo data).
------------------------------
Alnoor Cassim
Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA
Original Message:
Sent: 05-11-2020 15:33
From: Jeff Schwenk
Subject: Link BR Transactions to AP Vendor
The reference No field in the br_transaction table appears to be the div_vendor number combined. Anyway to create a link with this in Crystal? If Excel or Access were in place you could do it fairly easy. Not sure using straight Crystal. Call all Crystal Gurus.....
------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
Original Message:
Sent: 05-11-2020 10:29
From: Kenny Daniel
Subject: Link BR Transactions to AP Vendor
Thanks @Doug Higgs. I tried that but even that is iffy. There is no checkseqno or anything that links APCheckHistoryHeader to the BR_Transaction. I don't know how Sage does it so that when a check is Cleared in BR they Clear it in The AP History Header File. I think that might be why they asked for this report. They are saying that in certain occasions the checks cleared in BR are not being Cleared in AP. Might be when there are Reversals or a check is used more than once. Bug?
---------------------------------
Kenny Daniel
Technoclarity, Inc.
Original Message:
Sent: 05-11-2020
From: Doug Higgs
Subject: RE: Link BR Transactions to AP Vendor
Edited my last post. Join BR_Transaction to AP_CheckHistoryHeader and then join AP_CheckHistoryHeader to AP_Vendor.
------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary