Report Display (1 Viewer)

Hi Guys, I am fairly new to Cyrstal Reports and have come across a small problem that escapes me how to display the needed data. My report is built to reflect missing invoices and has only 5 fields with the first being Table.Invoice. This is a string with 10 characters with the first 4 being leading zeros. I setup the parameter field to limit my searches to only 50 invoices. This part works great. Here is my problem. I need the report to reflect all 50 invoices in the display from the search input. If at any time a range is selected and there are missing invoices within the database the invoice number still needs to be displayed with no data in the other 4 fields. I have about given up on this. Can anyone shed some input on this?
Kind regards,
Mike

It appears from what you are saying. There are multiple tables tied to the Table.Invoice. If that is so, then you should be able to do a left outer join to the other table(s). That would allow CR to pull in the 'missing' data.

Thanks for replying. I'm sorry I guess I did not make myself plain in this. It is not that the fields having missing data it is that it does not exist. My parameter field requests the range of invoices that the report will include. I use the first of 50 invoices in which is the first of an invoice book that the salesmen use. (Example: 0000125000 and the last is 0000125049) The report is generated and displays as this.

Invoice Emp No. Emp Name Total Hours Purchase No.
0000125000 27 Tom 14 51489
0000125001 34 Larry 6 51501
0000125002 27 Tom 9 51496
0000125004 18 Dick 11 51487
0000125005 18 Dick 5 51491
(Sting Value)

I need for the report to list all possible invoice numbers in this range of 50. In the example above note that 0000125003 is not listed in the report because it does not exist in the database. I want this to be included and all missing invoices in the 50 range to print. These are actually the missing invoices. There will be no data in the other fields making it obvious for some reason that it was not turned in to be processed. I am in hopes that when a missing invoice is found and the second field will be NULL I can have the text printed in this area "MISSING".
Hope this helps clearify what I am trying to do.
Kind regards,
Mike

There might be a way. You basically would need to generated the Invoice numbers (from the the beginning) number using a variable, then (the part I am not sure about) match that to data you have. If I have some time, I will see if I can re-create what you are trying to do.

I tried some possibilities and because CR basically cannot report on non-existant data, I could not come up with a solution.

Hi

You can generate some data using a query in Access that will show missing invoices and then report on it from Crystal.

I created a sequence of invoice numbers in a table 'InvoiceNumbers' in the same format as you describe. I then created a database of dummy invoice data in a table 'Invoices' (with some missing). Both tables contain only ID and Description fields.

I created a query that shows all invoice numbers and leaves the missing invoices with a blank description:
SELECT * FROM InvoiceNumbers LEFT JOIN Invoices ON Invoices.ID=InvoiceNumbers.ID

You can also show missing invoices only using:
SELECT InvoiceNumbers.ID, "Missing" AS Description
FROM InvoiceNumbers
WHERE InvoiceNumbers.ID Not In (SELECT ID FROM Invoices)

You should be able to report on these in Crystal and substitute whatever you like for the blank fields.

I hope this is close to what you were looking for. Let me know if you want a copy of the test database I knocked up.

Chris

Hi Chris,
Thanks for replying to my post. I have been away for awhile due to illness in the family. The idea you have outlined sounds exactly what I am needing to do. My database is SQL and not Access. Do you thing this could be accomplished using the SQL in the same fashion? I look forward to your comments.
Thanks,
Mike Stokes

Hi Mike
I hope things are improving for your family.

Yes, you can achieve the same results in SQL. You can create a table of reference invoice numbers as described above, use a SQL query to populate it and then create a view using the SQL above. Crystal should be able to report on the view as if it were a table.

Let me know if you run into any difficulties.
Chris