Customers with no invoices disappears when linking OCRD, OINV, INV1 tables

[Former Member]

Hello everyone,

I am trying to create a report that shows me the customers in a certain city with their total invoices in 2012, 2013, 2014, and 2015.

When I run the OCRD report to get these customers, there are 41 records but when I create the formulas for 2012--2015, some of the customers disappear due to the fact that they don't have any transactions throughout these years. The end result is 29 customers.

I, simply, would like to see all of those customers whether they have any invoices or not.

Here is my formula  to calculate invoice totals per year

If year({OINV.DocDate}) = 20XX Then
     {INV1.LineTotal}

I have also grouped these records by OINV.CardName to only see the summary per year.

How can I link these tables to see all of those 41 customers?

Thank you.

[Former Member]

Hi Korhan, altering the given basic SQL query might help you:

SELECT * FROM OINV T1

INNER JOIN INV1 T2 ON T1."DocEntry" = T2."DocEntry"

INNER JOIN OCRD T3 ON T1."CardCode" = T3."CardCode"

[Former Member]

Hi,

I am doing this on CR2008 and I don't know how to modify the SQL query on CR.

[Former Member]

Hi, if you would like to have all customers from OCRD with and without invoices from OINV and INV1 then you should be using RIGHT OUTER JOIN with OCRD (all customer) like:

SQL:

SELECT * FROM OINV T1

INNER JOIN INV1 T2 ON T1."DocEntry" = T2."DocEntry"

RIGHT OUTER JOIN OCRD T3 ON T1."CardCode" = T3."CardCode"

Database Expert - Links (GUI):

[Former Member]

This doesn't work either. Also, IMHO, it makes more sense to do LEFT OUTER JOIN with OCRD being the main data set.

[abhilash_kumar]

Hi Korhan,

You need a Left Join from whatever table holds the Customer Name/ID to the Table that holds the Invoices.

-Abhilash

[Former Member]

Abhilash,

I have tried this already and it didn't work.