Sage 100

Having a problem trying to modify the WO form (v.4.5). I need to suppress a section where I've stored the component barcode. This needs to happen when the Component Item's Category 1 field is blank (ie not equal to ""Y""). However, each time I try to add CI_Item to the datafile listing, I get an error indicating that: ""More than one datasource or a stored procedure has been used in this report. Please make sure that no SQL Expression is added and no server-side group-by is performed."" And then the WO fails to print completely. All I wand to do is use the Category field to suppress a section. What the bloody heck am I doing wrong??!!

Deb - when you pull it up in Crystal, do a Verify Database or refresh before attempting to do anything else, so you get logged into the correct database. THEN add CI_Item and it should be OK.

Not really an error @DebThomas it's just a new connection in Crystal. Usually, if you just verify the database first and then go and then add the CI_item table you'll be connected to the same connection instance and you won't get that message.

@BethBowers - Great Minds!

Yay! So that worked, however, adding CI_Item to the form and putting in the suppression condition now causes the WO to hang and indicate it's not responding. What now?

Here's the suppression formula: {@Mixed}<>""M"" or {WO_28CRWTraveler.LineType}<>""1"" or {CI_Item.Category1} <> ""Y

If you take {CI_Item.Category1} <> ""Y"" back out does it still run?

@DebThomas I had to turn off graphical reporting in WO at one manuf. client for the same reason you describe - WO Travelers hung each time the task was launched. Thanks to @BrettZimmerman for the heads up, once we turned off graphical reporting in WO - no more problems.

I've had WO Traveler printing issues off and on for years usually it has to do with all of the various working files that are involved(Extended Descs, etc.) If you re-initialize all of the it generally fixes the problem. That is for a graphical form that has otherwise always worked in the past.

@BrettLyder it does work if I delete the reference to CI or just comment it. I changed the link from equal join to a left outer too. But I can't seem to find a way to get the section to suppress.

You're running into nulls. Use a formula to get the value of Category1 (If it's null set it to Y or N) and then use that formula in your suppression logic.

Might you be willing to share an example?

Sure, formula My_Category1 something to the effect of: if isnull({CI_Item.Category1}) then """" else {CI_Item.Category1} Then just use {@My_Category1} in place of {CI_Item.Category1} in your suppression formula.

I'm heading out but i'll keep an eye on the thread, I have to use this type of logic semi-regularly but i'll admit I have to re-invent it everytime.

Now getting a Crystal print error indicating that the report failed to retrieve data from the database. Sigh...any ideas?

Here's what it looks like now: {@Mixed}<>""M"" or {WO_28CRWTraveler.LineType}<>""1"" or {@Category 1 suppress} <> ""Y

Hmmm. Just having that formula In there shouldn't give you a message like that. If you preview in crystal(I know you won't get any data) but do you get an error?

Your report is using two DSNs. Remove the CI_Item table. Verify the database. Then add CI_Item from Current Connections.

That was her original issue. I thought we had that handled.

Is the CI_Item table joined with a left-outer join?

Yes @MikeBrown it is.

Deb. I know I don't have your data but can you email me a copy of the report? Brett@neo3.com

It sometimes depends on the order of the formula. Like @BrettLyder , even though I don't have your data, it might be helpful to see how your links are, etc.

File sent @BrettLyder This is the last piece to making this thing work.

@ThereseLogeais can I send it to you too? Believe it or not crystal reports on my laptop is opening my windows on my second monitor(which I don't have access to here.) I can't believe it. Apparently there are registry entries that remember where I open those windows last.

Nevermind, I found an old VGA cable and have a second monitor. The problem is that the SQL query fails as soon as we try to use a field from the CI_Item table. I'm getting an error in the CR Designer when I preview the report If I take the field from CI_Item out of the report it previews fine. blank because of the working tables but fine.

Please send it to me as well @BrettLyder .

OH my bad, it's been a long day. WO_21CRWExtendedDesc is already in there as a left outer join. We can't have more than one left outer join on the old school files. @DebThomas maybe we can change the extended description to print in a sureport and then use a left outer for CI_Item.

Just a thought - do you have you default nulls checkboxes on in the report options?

@MoiraGoggin the convert database nulls is checked but the problem she is having now is that the SQL is throwing an error because there are multiple files using left outer joins. I think she has to use left outer to join the CI_Item to the component Item number from WO_28

@BrettLyder I see - only suggested the default nulls since earlier in the thread she has a suppression formula and I've had issues with nulls in WO before...

Oh. I know, it was a good thought. Earlier we thought that was the issue now I think we know what's up it's just a matter of coming up with the cleanest solution.

Problem is that there are a couple of extended descriptions - item description and step description...which on is the link problem?

Why not a subreport for the CI_Item data instead of a LOJ????

@ThereseLogeais, Deb's not actually printing data from CI_Item. She's just using it to see if the component item category 1 field is a ""Y"".

@DebThomas the extended descriptions in Details B and G are the ones from WO_21 that need moved to a subreport.

I am actually using the category field to suppress data when the field is empty. Those are the ones. So.@BrettLyder i think those are the step descriptions... I'll give that a try.

OK, don't forget to remove WO_21 from the main report.