Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

Help with Joins

Report design issues and solutions.
Post Reply
philallen
User
User
Posts: 1
Joined: 04 Dec 2019, 09:23
Sage Version: Other

Help with Joins

Post by philallen » 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:
Explorer.JPG



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.
OTD Report.report
Sage Version Number 26.1.99.0
Report Designer Version 4.0.164.0
Login to access the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4616
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Help with Joins

Post by brucedenney » 05 Dec 2019, 11:40

The join between the GDN_ITEM Table and the SOP_ITEM table is not the Order Number, there are lots of records in the SOP_ITEM table with the Order Number on them and if you join on that field, you will get Every SOP_ITEM record with that order number for each item in the GDN_ITEM table, with that order number. The number of recrds will be the number of items on the order multiplied by the number of GDN_ITEM record for that order which is lots and lots of records and they will not all relate to the item.

What you want is a link from the GDN_ITEM to the SOP_ITEM.

In the past the only way was a compound key of both the ORDER_NUMBER and the ORDER_ITEM (number) in the GDN_ITEM table to both the ORDER_NUMBER and the ITEM_NUMBER in the SOP_ITEM table. And this does work, sort of, well not is some cases. You see the thing you might not know is that the item number does not remain the same, if you sort the order, delete a line or insert a line, then the Item numbers on other items change the item number determines the order in which the lines appear on your layouts and reports. It is pretty rare for people to do something that changes the order after an item has been shipped, but it does happen so this might look like a solution but it is not.

This issue also applied to Invoice Items and Purchase Order Items as well although in different contexts

So a few years ago Sage added a new variable to fix this issue to the SOP/POP and INV Items tables to get around this and it is the field called ITEMID, it is a unique incrementing number identifying the item among all the items in the table so the 423rd item entered will be numbered 423 regardless of what order number or item number it has.

The GDN_ITEM table has a copy of the ITEMID from the SOP_ITEM in it, but, for reasons that are unknown to anyone outside of sage, it is not called ITEMID instead it is called UNIQUE_ID.

So if you make a join between GDN_ITEM.UNIQUE_ID and SOP_ITEM.ITEMID you can access information out of the SOP_ITEM table for that GDN_ITEM record.

I am not sure what happens historically, if you go back a long time you may find that the UNIQUE_ID may be blank for GDN_ITEM records created before the field was introduced, but for current records you are fine.

<aside>Compound joins can't be done by dragging and dropping between tables, you have to drag and drop the first of the compound fields and then type the name of the 2nd compound field after it separated by a comma in the From Field and To Field box</aside>

<another aside>There have been several places in Sage where unique keys did not exist, and whilst some have been fixed, some have not and there are still cases where things can not be linked such as delivery addresses on orders/invoices. There are also values that are not exposed. On delivery addresses there are VAT tax codes, VAT numbers and VAT country codes that are copied to the Invoice and used for Vat Returns/EC Sales Lists but are not all are visible through the ODBC drivers, so for example if your want to find the VAT country code for an invoice that has it set from a delivery address, there is no way to find which delivery address relates to the invoice and the field is not exposed on the invoice so you unable to do it. This issue also extends even further into the developer tools, where fields we know exist from ODBC and the User Interface are not exposed and can not be set. In future we would like to see these fixed, however, the ones that have been fixed had issues for a considerable periods prior to getting fixed.</another aside>
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4616
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Help with Joins

Post by brucedenney » 05 Dec 2019, 11:42

<yet another aside!> There are 2 dues dates, one on the order and one on each item, so make sure you use the correct one</yet another aside!>
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Post Reply

Who is online

Users browsing this forum: No registered users and 11 guests