Help with Joins
Posted: 04 Dec 2019, 15:29
Hi,
I have created a report using the Wizard that lists "Despatched Items" in a given time period. This time period is specified by GDN_ITEM.DATE criteria when you run the report. (usually Calendar Month).
What i want to do is add the data field SOP_ITEM.DUE_DATE.
We are using this field as the acknowledge date to the customer (ie. when the line on the Sales Order is due to ship). We need to use this field as a lot of our Sales Order's have multiple lines shipping on different dates.
I then want to compare this to the GDN_ITEM.DATE to see if we despatched the line on time / late.
This is then exported to excel and "On time Delivery" charts etc are produced from this.
The current report structure is:
The problems arise when i try and add the data field SOP_ITEM.DUE_DATE
My current Joins are:
[SALES_ORDER]
INNER JOIN [GDN_ITEM] ON SALES_ORDER.ORDER_NUMBER = GDN_ITEM.ORDER_NUMBER
INNER JOIN [STOCK] ON GDN_ITEM.STOCK_CODE = STOCK.STOCK_CODE
INNER JOIN [SALES_LEDGER] ON GDN_ITEM.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF
STATIC JOIN [COMPANY]
I have added the SOP_ITEM database into the joins editor and attempted to join SOP_ITEM.ORDER_NUMBER with GDN_ITEM.ORDER_NUMBER and/or SALES_ORDER.ORDER_NUMBER, however this causes issues.
The issues i get are that line on the report are duplicated multiple times. The number of times the item is duplicated seems to correlate to the number of lines on the Sales Order. 5 lines on the Sales Order = 5 instances of each line on the report.
I think i need a "multi conditional" Join but my knowledge of joining databases is limited to what i have found on the internet and trial and error. So far i havent been able to find a multi conditional join that give the correct results.
Could you please advise if its possible to do this and how it could be achieved.
Sage Version Number 26.1.99.0
Report Designer Version 4.0.164.0
I have created a report using the Wizard that lists "Despatched Items" in a given time period. This time period is specified by GDN_ITEM.DATE criteria when you run the report. (usually Calendar Month).
What i want to do is add the data field SOP_ITEM.DUE_DATE.
We are using this field as the acknowledge date to the customer (ie. when the line on the Sales Order is due to ship). We need to use this field as a lot of our Sales Order's have multiple lines shipping on different dates.
I then want to compare this to the GDN_ITEM.DATE to see if we despatched the line on time / late.
This is then exported to excel and "On time Delivery" charts etc are produced from this.
The current report structure is:
The problems arise when i try and add the data field SOP_ITEM.DUE_DATE
My current Joins are:
[SALES_ORDER]
INNER JOIN [GDN_ITEM] ON SALES_ORDER.ORDER_NUMBER = GDN_ITEM.ORDER_NUMBER
INNER JOIN [STOCK] ON GDN_ITEM.STOCK_CODE = STOCK.STOCK_CODE
INNER JOIN [SALES_LEDGER] ON GDN_ITEM.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF
STATIC JOIN [COMPANY]
I have added the SOP_ITEM database into the joins editor and attempted to join SOP_ITEM.ORDER_NUMBER with GDN_ITEM.ORDER_NUMBER and/or SALES_ORDER.ORDER_NUMBER, however this causes issues.
The issues i get are that line on the report are duplicated multiple times. The number of times the item is duplicated seems to correlate to the number of lines on the Sales Order. 5 lines on the Sales Order = 5 instances of each line on the report.
I think i need a "multi conditional" Join but my knowledge of joining databases is limited to what i have found on the internet and trial and error. So far i havent been able to find a multi conditional join that give the correct results.
Could you please advise if its possible to do this and how it could be achieved.
Sage Version Number 26.1.99.0
Report Designer Version 4.0.164.0