Record Numbers Reduce with Intro of New Field - Don't want this to happen!!

[DeviousDi]

HI,

I am trying create just a basic datasheet with invoice amounts, amounts paid, amounts outstanding etc. I've got the info how I want it, but the moment I introduce my query code field, the number of records drop, and only show those with a query field. How do I get them to show all the records still? I've tried an 'If then else', didn't work.

The report is for a 'dashboard' type report. Any help would be appreciated.

Thanks

Di

 

[pmax9999]

As a starting point, you will need to make sure that the table from where the new field is being added is joined to the others via a Left Outer Join.

I you are including that field in the Record Selection, it will over-ride the Outer Join unless you write it I such a way as to deal with Nulls. If you need to include the field in the Record Selection, post the details back and we should be able to assist.

Cheers
Pete

 

[DeviousDi]

Hi,

This is the SQL query to return the records:

SELECT "debtor_freefields"."ff4", "debtor_freefields"."ff3", "invoices"."amount_open", "debtor_freefields"."ff2", "standard_queries"."code", "invoices"."invoice_number", "debtors"."debtor_number", "debtors"."debtor_name", "invoices"."amount_invoice", "invoices"."amount_paid", "invoices"."invoice_status", "invoices"."date_due", "invoices"."invoice_date"
FROM "OnGuard"."dbo"."debtor_freefields" "debtor_freefields" INNER JOIN ("OnGuard"."dbo"."invoices" "invoices" INNER JOIN (("OnGuard"."dbo"."queries" "queries" INNER JOIN "OnGuard"."dbo"."standard_queries" "standard_queries" ON "queries"."standard_query_id"="standard_queries"."id") LEFT OUTER JOIN "OnGuard"."dbo"."debtors" "debtors" ON "queries"."debtor_id"="debtors"."id") ON "invoices"."debtor_id"="debtors"."id") ON "debtor_freefields"."debtor_id"="debtors"."id"
WHERE "standard_queries"."code"<>N'S01' AND "debtor_freefields"."ff3"<>N'T700'
ORDER BY "debtor_freefields"."ff4"


Thanks

Di

 

[pmax9999]

What is the table/column that, when added to the report, reduces the number of rows returned?

Also, is the SQL provided what Crystal has generated, or is this custom code you have written yourself?


Pete

 

[DeviousDi]

HI,

Crystal has generate the code.

The field causing the problem is called standard_queries.code

Thanks

 

[pmax9999]

Based on the SQL code generated, I am guessing that your record selection formula has a line like this:

{standard_queries.code} <> 'S01'

Try replacing that line with the following:

(
	Isnull({standard_queries.code}) or
	{standard_queries.code} <> 'S01'
)

Hope this helps.

Cheers
Pete

 

[DeviousDi]

HI,

Have tried both of the above, removing all the filters on the standard.queries_Code, it still reduces the records to only those that have a query code against them. I need them to show all the records, including those that don't have a query code against them.

Surely there is a way of getting these to show? What am I missing here?! Arghhhhhh!

Di

 

[IanWaterman]

Its because you are joining that table with an INNER join

INNER JOIN (("OnGuard"."dbo"."queries" "queries" INNER JOIN "OnGuard"."dbo"."standard_queries" "standard_queries" ON "queries"."standard_query_id"="standard_queries"."id")

Change to a left outer and use Pete's filter

Ian

 

[DeviousDi]

HI,

Have changed to outer joins, removed all the filters but one, just to make it easier to look at, and I still am not getting all the records.

This is the SQL Code:

SELECT "debtor_freefields"."ff4", "debtors"."id", "invoices"."amount_open", "invoices"."amount_invoice", "invoices"."amount_paid", "standard_queries"."code"
FROM "OnGuard"."dbo"."standard_queries" "standard_queries" LEFT OUTER JOIN ((("OnGuard"."dbo"."debtors" "debtors" LEFT OUTER JOIN "OnGuard"."dbo"."invoices" "invoices" ON "debtors"."id"="invoices"."debtor_id") LEFT OUTER JOIN "OnGuard"."dbo"."debtor_freefields" "debtor_freefields" ON "debtors"."id"="debtor_freefields"."debtor_id") LEFT OUTER JOIN "OnGuard"."dbo"."queries" "queries" ON "debtors"."id"="queries"."debtor_id") ON "standard_queries"."id"="queries"."standard_query_id"
WHERE "debtor_freefields"."ff4"=N'd44'
ORDER BY "debtor_freefields"."ff4"

I'm lost, and seriously losing the will to live!