Help with a missing invoice report

[byteofram]

I am working on a report that lists all invoices for my company, which in itself is easy. The problem comes when an invoice that hasn't showed up on the financial side is missing. I want to list the ones that didn't show up besides going through the pages and pages of numbers looking to see which ones aren't there. For instance:

The report prints:

8812
8813
8814
8817

I want it to list at the end the 8815 and 8816 for me. Is there a way to do this?

Thanks in advance!

 

[IdoMillet]

One option is to create a section (e.g, detail section A)
that is suppressed when
------------------------
{Invoice_N} - Previous({Invoice_N}) = 1
-------------------------
It becomes visible only when you have a gap and you can show in it information indicating the gap.

You can also use SQL subselects or join the Invoice Table to itself with a condition saying the Invoice_N on the left is equalt to the number plus one on the right. Make it a Left Outer Join and add a WHERE condition restricting the result set to only those with Nulls on the right.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[byteofram]

Ido,
I tried using the syntax you provided, but Crystal comes back and says it expects "a number, currency amount, date, time, date-time required here" and points at the beginning of the syntax. Am I missing something that I need to do? I would try to do the other suggestion, but I need more help on how to implement that.

 

[IdoMillet]

Make sure your Invoice_N is converted to a number if it's not a number already.

hth,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Another means might be to have a formula akin to the following:

@MissingInvoices
whileprintingrecords;
stringvar TheMissingInvoices;
If {Invoice_N} - Previous({Invoice_N}) > 1 then
for x = previous({Invoice_N})+1 to {Invoice_N}-1 do
(
TheMissingInvoices := TheMissingInvoices+totext(x)+chr(13)
);

TheMissingInvoices

I didn't test it, but that's the general theory.

This has a 254 character limitation per group of missing invoices in CR versions 8.5 or less, so hopefully you won't have that problem.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[IdoMillet]

Cool solution by synapsevampire )

Just rememebr that his solution also requires converting your invoice number to a numerid data type (it it's indeed a string) as implied by your follow-up question.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[byteofram]

The invoice number is a string because it has a -02 at the end of each one signifying 2002. How do I trim off the -02 and make it a number type? My Crystal help book isn't much help on that area.

 

[IdoMillet]

Try:
---------------------------------------
CDbl(LEFT({inv_n}, Length({inv_n})-3))
---------------------------------------

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

If the length is ALWAYS 4, then:

val(left({Invoice_N},4))

If it varies, and the dash - delineates the invoice number from the rest, use:

if instr({Invoice_N},"-&quot > 0 then

val(left({Invoice_N},instr({Invoice_N},"-&quot-1))

else
0

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com