Hi, I am a novice at Jet and programming and I cannot figure out what a previous consultant did to try and get some notes to populate from one data source in my staging database. You'll see from the 2 images below that my Source WMS - PODetail table shows the highlighted records with no POItemNotes, which is correct. Notice the record at the very top has starting with "FPO #05-05-2134", which is a separate record (POHeader_ID). Now the second image is the same table in the StageDatabase, but notice it's filling in the Null values with the previous record's notes on all the line items.
There is some odd programming that takes this field of POITEMNOTES and splits it into 2 separate fields - one for Original PO's and one for Variance PO's and then combines them. I believe the reason it was written this way is because another linked table called POJCCDETAIL then splits a PODETAIL record into separate lines if the record is a Variance PO and an actual Variance record was created (*sidenote* The variance record code and amount is also recorded in the PODetail table in other fields so in essence it's duplicated in POJCCDETAIL, but with a little more detail info; tax group, tax amount, etc.). The 3rd screenshot shows these fields.
There are 2 other odd things taking place.
1. There is a View setup with the following code: CREATE VIEW [dbo].[POItemNotes] AS
SELECT DISTINCT
[POITEMNOTES]
FROM [Sage_CURRENT_JCT_TRANSACTION]
2. You'll notice the Custom Value ISNULL for the combined PO Notes field, basically recombining the 2 custom fields previously created. I don't know what this was done because it doesn't matter if the PO is an original or Variance PO, the POITEMNOTES field is the same for that PODETAIL record. It seems to me that it could have been created without this by referencing back to the PODETAIL_ID.
I do have to connect through 2 additional tables;
The combined POnotes becomes 'POITEMNOTES' from POJCCDETAIL going to Sage_MASTER_JCM_PO_ITEM and joined by line item number fields and PO number.
POITEMNOTES from there then gets added to Sage_Current_JCT_Transaction and is joined by PO # and Job #.
Any help is greatly appreciated.
