Select Expert not showing all records.

[Former Member]

<p>Alright, forgive me if I&#39;m asking a relatively noobish question, my experience with Crystal Reports is fairly limited, although I&#39;m no stranger to SQL and queries and the like.</p><p>I&#39;m having a very frustrating problem - I&#39;m trying to generate a report that&#39;s querying a handful of random Access tables. In theory, this report should be able to pull out all the necessary information I need to generate it using only one Select criteria, which is the SalesOrderID in the Master Sales Order table.</p><p>If I look at this table in the Microsoft SQL Enterprise Manager, there&#39;s one row for every Sales Order ID ranging all the way up to about 36000. For some reason, when I tell Crystal to try and find the Sales Order ID that I&#39;m looking for, it&#39;s not even listed. If I change the formula to query for SPECIFICALLY the Sales Order ID that I&#39;m trying to use, the whole report just comes up blank.</p><p>To give another idea, I made another report not too long ago to generate a newly designed Purchase Order, and luckily all this information is a bit more centralized, so if I told it to find just one single Purchase Order ID, it successfully built the entire PO using just that one number. But even THEN, it couldn&#39;t find some PO IDs, even though they&#39;re right there in the database. If I looked at the table querying all rows, it&#39;ll show PO10000, 10001, 10002, etc, all in order. If I ask Crystal Reports to look at the table with the Select Expert, it&#39;ll show me something like PO10000, PO10023, PO10047, just randomly skipping large blocks. Any idea why this is happening? Any particular piece of documentation or a tutorial you can point me in the direction of that might explain this? Thanks much. <br /></p>

[Former Member · Accepted solution]

Can you paste the record Selection Formula and some sample records and do you have join on Sales Order and Purchase Order

Thanks

Rahul

[Former Member · Accepted solution]

<p>Alright, if I&#39;m looking at the Master Sales Order Table returning all rows in the SQL Enterprise Manager, it will show me this: </p><p><a href="http://www.whatthefuckiswrongwithpeople.com/misc/sql.jpg" target="_blank">Click Here</a> <br /></p><p>(I&#39;m sorry about the NSFW domain name - That&#39;s just my personal website which I use mostly for hosting. But this message board is turning one of those words automatically into "Filtered Word"... I&#39;ll give a hint what&#39;s supposed to take it&#39;s place - It starts with an "F" and has four letters.) </p><p>Here&#39;s what Crystal Reports will show me if I try to select that record.</p><p><a href="http://www.whatthefuckiswrongwithpeople.com/misc/crystal.jpg">Click Here</a> <br /> </p><p>So yeah. Here&#39;s the SQL Query being performed by Crystal. (I haven&#39;t finished building the document yet, I&#39;ve only gotten the header done.)</p><p><br />SELECT SOS.SOS_PackingSlipNbr, SOM.SOM_CustomerPOID, SOM.SOM_SalesOrderID, SOS.SOS_ShipDate, Customer.CUS_BillName, Customer.CUS_BillAddress, Customer.CUS_BillCity, Customer.CUS_BillState, Customer.CUS_BillPostalCode, SOM.SOM_SalesOrderDate, SOS.SOS_ShipMethod, SOS.SOS_ExtBOLNbr, SOM.SOM_FOB, SOM.SOM_TerritoryCodes FROM ((RAINIER.dbo.SOS SOS INNER JOIN RAINIER.dbo.SOD SOD ON ((SOS.SOS_SalesOrderID=SOD.SOD_SalesOrderID) AND (SOS.SOS_SOLineNbr=SOD.SOD_SOLineNbr)) AND (SOS.SOS_RequiredDate=SOD.SOD_RequiredDate)) INNER JOIN RAINIER.dbo.SOI SOI ON (SOD.SOD_SalesOrderID=SOI.SOI_SalesOrderID) AND (SOD.SOD_SOLineNbr=SOI.SOI_SOLineNbr)) INNER JOIN (RAINIER.dbo.Customer Customer INNER JOIN RAINIER.dbo.SOM SOM ON Customer.CUS_CustomerID=SOM.SOM_CustomerID) ON SOI.SOI_SalesOrderID=SOM.SOM_SalesOrderID</p><p>The Selection Formula itself is nothing more than:</p><p>{SOM.SOM_SalesOrderID} = "36331"</p><p>Just gives me a blank report. <br /></p><p>&nbsp;</p><p>-Ryan</p>

[Former Member · Accepted solution]

Hiya,

Here's three thoughts...

1) Crystal won't always show everything in the 'browse field data' type boxes - it's limited to the first 1000 (or some value, I don't remember exactly) rows that it finds.

2) I notice that you've got a string field for the ID - could it be that there is a space or two after the number that you see? Could you try changing your select expert formula to:Â

trim({SOM.SOM_SalesOrderID}) = "36331"

or

tonumber({SOM.SOM_SalesOrderID},0) = 36331

 and see how that goes?

 3 )Also check your table linking - if you change all your links to left outer joins, and your value suddenly appears, you're heading in the right direction!

 Good luck...

[Former Member · Accepted solution]

<p>oops, that should be:</p><p><em>tonumber({SOM.SOM_SalesOrderID}) = 36331</em></p><p>(got my tonumber and totext functions mixed up!).</p>

[Former Member · Accepted solution]

I had a feeling Crystal was just cutting out random numbers to sort of cut down on the incredibly large list of numbers it WOULD have shown me. (I'd still prefer the complete list, but I won't be that picky.)

Good news is... That "tonumber" formula worked - What the hell was I doing wrong? Could you be a pal and...tell me what the difference is between using "Formula Is" and the above formula in the Select Expert...and saying, Value "is equal to" "36331"?

(EDIT: Weirdest thing just happened - I used the Select Expert again, setting it to "is equal to" "36331" and that ALSO worked... But it wasn't working BEFORE... >< I need some aspirin.)Â

-Ryan

[Former Member · Accepted solution]

Heya,

Glad to hear its resolved itself! I couldn't really tell why one way works and not the other, other than to say "Welcome to the wierd world of Crystal!". I've been pounding CR every day for 6 years and I still find oddities, but I love it!

I suppose when working with numbers, treat them as numbers - as I said, when it's held as a string, there might be spaces or other hidden data (line returns etc) that you won't necessarily see in Enterprise Manager - so convert them to a number and go from there.

[Former Member · Accepted solution]

Hi Jaime,

Thanks so much for the above suggestion; it saved me hours of work. I was trying to filter a string field and it had numbers in it . The select expert was not working and I couldn't figure out why. When I used the tonumber function, it worked like a charm.

Again thanks for taking the time to anser questions.

cheers

Angelle

aadlay@simivalley.org

[Former Member]

Hi,

I'm a noob too, but when I have run into stuff like your describing I have found it is helpful to break things down some. So try creating a new report, with only the table that is supposed to have the information, and no other table/database links.  Then see if you can get information out of that table.

 Also if you go under feild explorer, then database feilds, you can right click on the field and browse data. If you can't find the correct number in the browse area and you have no other links or filters, then you know for sure that crystal isn't seeing that data for whatever reason.

Good luck!

Rody

[Former Member]

<p>I thought I had posted this, but I forgot that I had just typed it up while at work and never got around to finishing/posting it - I tried exactly what you&#39;re talking about. I started up an entirely new, blank document and tried to put JUST the SOM.SOM_SalesOrderID field into the report with absolutely nothing else linked. Ran the Select Expert, and it&#39;s showing me the exact same data.</p><p>At this point, I&#39;m not sure whether to blame Crystal Reports for not pulling the information out correctly (even though it&#39;s all clearly listed there if queried by SQL Enterprise Manager), or if I should blame my ERP System for having some kind of funky issue since it&#39;s an Access Database mixed with a bunch of sloppy .NET. Oh, computer, why do you do this to me? Destroy</p><p>Anyway, if anyone else has come across this issue before, I could use some advice. If I figure anything out, I&#39;ll put it up in case anyone else stumbles across this same issue and I just happen to find some kind of solution. Shrug We&#39;ll see. </p><p>-Ryan</p>