Crystal Reports 7.0 Professional<br>Report Type: Custom / SQL-ODBC<br>Data Source: Lawson Procurement Application<br>Database Type: Oracle<br>Table Count= 5<br><br>Problem: inconsistent data population in single table field<br><br>The report is extracting data from Purchase Order related tables and the specific field that is inconsistently populated is an "POLINE.DESCRIPTION" field. <br><br>There are four data "ITEM" type classifications:<br>a) "INVENTORY" or "I"<br>b) "NON-STOCK" or "N"<br>c) "SPECIAL" or "X"<br>d) "SERVICE" or "S"<br><br>The inconsistency occurs when the "ITEM" type is one of the following types:<br>a) "INVENTORY" or "I"<br>b) "NON-STOCK" or "N"<br><br>The same item may be displayed on one report line and not on the very next report line.<br><br>Is this a Oracle Database issue or what?<br><br>Thanks <br>Alan L. Moore<br>Mayo Foundation<br><A HREF="mailto:moore.alan@mayo.edu">moore.alan@mayo.edu</A><br><br><br><br><br><br>
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Blank Fields- Multiple Item Type Data
- Thread starter heyalmo
- Start date
Is your SQL consistent in the data set it returns to Crystal? Try pasting the sql into the Crystal Query Designer, and checking out the data returned. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>
RJFost
Programmer
- Apr 11, 2002
- 180
- 0
- 0
As our facility's Crystal guru & reluctant Lawson analyst, I think you're running into a couple of separate issues here:
1. Under File -> Report Options in Crystal, make sure that "Select distinct records" is enabled/checked. Otherwise- depending on what tables you've used in the report & how you've joined/related them- you'll wind up with more records (primarily dupes) than you ever thought possible.
2. Remember that (in our case, anyway) EVERY field in EVERY table in the Lawson schema (1423 of them, to be precise) has a NOT_NULL constraint on it. So, if it's a numeric field, it'll default to Ø; char/varchar fields will default to " " (space/ASC(32))...and date fields, for reasons known only to God, default to 01 January 1700 (01/01/00).
If you've not downloaded the freeware version of TOAD ( & used it to look at your data from a table/field level, I HIGHLY recommend it. You can get a startlingly different perspective on many, many issues & problems in Lawson looking at your data from the back-end, instead of the front.
Lastly, as a closing thought, the item description in the POLINE table can be spread across 2 fields- DESCRIPTION & DESCRIPTION2. I've found that building a formula in Crystal that looks like UCase(Trim(POLINE.DESCRIPTION) + " " + Trim(POLINE.DESCRIPTION2)) & using that in the report can often make molehills out of the (all too many) Lawson mountains.
1. Under File -> Report Options in Crystal, make sure that "Select distinct records" is enabled/checked. Otherwise- depending on what tables you've used in the report & how you've joined/related them- you'll wind up with more records (primarily dupes) than you ever thought possible.
2. Remember that (in our case, anyway) EVERY field in EVERY table in the Lawson schema (1423 of them, to be precise) has a NOT_NULL constraint on it. So, if it's a numeric field, it'll default to Ø; char/varchar fields will default to " " (space/ASC(32))...and date fields, for reasons known only to God, default to 01 January 1700 (01/01/00).
If you've not downloaded the freeware version of TOAD ( & used it to look at your data from a table/field level, I HIGHLY recommend it. You can get a startlingly different perspective on many, many issues & problems in Lawson looking at your data from the back-end, instead of the front.
Lastly, as a closing thought, the item description in the POLINE table can be spread across 2 fields- DESCRIPTION & DESCRIPTION2. I've found that building a formula in Crystal that looks like UCase(Trim(POLINE.DESCRIPTION) + " " + Trim(POLINE.DESCRIPTION2)) & using that in the report can often make molehills out of the (all too many) Lawson mountains.
Frank49001
MIS
RJSoft, Perhaps I can ask you some questions about Lawson when using Cr, what other problems have you noticed, similar to this one, for example.
Thanks,
Frank
Thanks,
Frank
RJFost
Programmer
- Apr 11, 2002
- 180
- 0
- 0
If you'd like, reply with your e-mail address & I'll draw up a "Top 10" list of Lawson/CR/Oracle issues that I've run into over the last 3 years.
We're an RCO site (which has a definite impact on what you can & can't do in the database). When I started working with Lawson, we were on v7.3.4; we're now running v8.0.3.
We're using the following Lawson modules: AC, AM, AP, CA, CB, FB, GL, IC, MA, PO, RQ, WH (essentially everything but AR, HR & PR).
We're an RCO site (which has a definite impact on what you can & can't do in the database). When I started working with Lawson, we were on v7.3.4; we're now running v8.0.3.
We're using the following Lawson modules: AC, AM, AP, CA, CB, FB, GL, IC, MA, PO, RQ, WH (essentially everything but AR, HR & PR).
Frank49001
MIS
Sure my email is
Freiss47@Yahoo.com
Thanks again,
Frank
Freiss47@Yahoo.com
Thanks again,
Frank
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question