Crystal rpt to show all invoiced serial numbers. Need to nix credits

[uff555]

I recently signed up for the sage university course on crystal reports. I figured I may be able to help my family business automate a few reports.

I am having a hard time navigating the table and fields. It seems like the documentation is lacking or I am missing something.

I am creating my first report to show all the invoiced serial numbers. I was able to find the OESELI table with the SERIALNUM entity. I have however not been able to locate a field that will tell me weather or not the item is in fact invoiced and not debit, credit, or adjusted.

 

[ettienne]

Do you use serialized inventory?
Look at the AOM to get the db layout here:

http://support.accpac.com/techsupport/aom/advantage.asp

 

[uff555]

We input the serial numbers by hand when creating invoices.

 

[Jay Converse]

OEINVH->OEINVD->OESELI. It's simple.

 

[ettienne]

Yeah, it will only be in OESELI if it was invoiced, debit/credit/returns will not end up there.

 

[uff555]

I appreciate all the responses. I am however still scratching my head. We are just getting used to entering serial #s. We made a mistakes entering the serial #s on a few invoices. We decided to credit the invoices and make a new invoice.

When I create a report and include only the OESELI table and display all fields, I see serial numbers of items that were at one time invoiced but later credited.

 

[Jay Converse]

Yes, that's correct.

 

[ettienne]

The credit does not take them out of OESELI. This method of serial tracking is really simple, rather dumb actually, it's just a comment field that tracks something - in this case it just happens to be a serial number. There is no intelligence behind tracking these serial numbers, if someone makes a mistake then it is there forever.

 

[uff555]

Ahh that makes sense. So, is it possible to eliminate the serial numbers that were later credited?

 

[Jay Converse]

Not easily. Try a subreport with a left join from OEINVD to OECRDD, then use conditional formatting to not display the OESELI record if an OECRDD record exists.

 

[uff555]

I don't see any keys that will match and OEINVD record with an OECRDD record.

 

[uff555]

I also do not see a way to create conditions on sub reports.

 

[Jay Converse]

Keep studying, young one. This is IT 101.

 

[ettienne]

Credit/debit serial numbers are in OESLEC... so use that to exclude the credited/returned serials from your report.

 

[uff555]

I uncovered the OESELC table as well and made this connection. However I don't see how to make crystal exclude anything appearing in OESLEC.

 

[uff555]

I ended up creating the following view:

CREATE VIEW "InvNumOfCredited" AS SELECT DISTINCT ("T1" ."INVNUMBER" )FROM "OECRDH" "T1"

And this sort of works. In some cases, there are invoice numbers that were never actually credited. Is my database messed up? am I just missing something really obvious