Sage 100

Ran into an odd issue with a Crystal Reports selection formula the other day and need the why explained by someone much smarter than I LOL The selection formula is: QuantityOnHand <> 0 AND ItemCode <> ""FS"" (pulling from IM_ItemWhse) What happens is; items with 0 QoH are excluded but so are ALL items that start with FS. Not just the one item FS, but ALL items with FS as the first two letters!? Why is this happening? What should the correct formula be?

You could try .... AND trim(ItemCode)<> ""FS

Maybe try QuantityOnHand <> 0 AND NOT(ItemCode = ""FS""). Your original formula seems like it should be viable to me.

and Not({ItemCode} Like ""FS"")

Your current formula should be giving you any IM_ItemWhse record where the item code is not FS AND not zero. SO... You are getting all items other than FS and QoH when it is not zero. You have nothing in your formula that is telling it you want evaluate just the first two characters (alan has addressed that for you). Kathryn's formula should give you the same as your current results. What I need to know is what do you truly want to happen.

I got this to work, but was using BO - {IM_ItemWarehouse.ItemCode}=(upTo_ ""BO"" ) or {IM_ItemWarehouse.ItemCode}=(upFrom_""BO"") and {IM_ItemWarehouse.QuantityOnHand} <> 0.00

@LarryBradford The report should not have any lines that have 0 quantity on hand and the item FS (a single item used for Fuel Surcharge) should be excluded from the report. All other items that start with FS... (FSBM3313, FSBM3314, etc...) should be included. FWIW - this was a Report Master and the logic was exactly as written and it worked fine - the good ole' days - sigh...

@KellySappington what does (UpTo_""BO"") & (UpFrom_""BO"") do?

You are correct. That should not happen. I don't mind taking a quick look at it for you. 703-913-3500 I can remote to you if you like assuming you have access to the current system.

Is there any suppression logic at work?

@LarryBradford was able to get this to work: not ({IM_ItemWarehouse.ItemCode} in ""FS"" to ""FS@"") and {IM_ItemWarehouse.QuantityOnHand} <> 0.00. No Idea why, but it is a work around. I am guessing this does the same thing as @KellySappington solution. Thanks to you both. @PhilMcIntosh - no suppression logic, only this selection.

@GregStiles I was using demo data and have items starting with Board. I created an item BO. Up to gives me all the items up to not including BO and the other formula gives me all the items up from BO not including. Need that _ after each though

So is this a known issue Kelly that you previously encountered?

Actually I was just trying to solve @GregStiles problem and created a report to try and recreate the issue, which I was able to. Just kept looking in the formula editor to find a way around it. It took a bit.

Hate the fact that this one could be duplicated. WOW. It should not work this way.

@LarryBradford the formula was excluding FS- items, the @ must come later in the sort order than a hyphen. I am going to try @KellySappington formula tonight as well just to test. What a weird issue?

Did you try the QtyOnHand <> 0 and trim(ItemCode)<>""FS""? Just wondering.

Had to do this way to accomodate the zeros and multi warehouses {IM_ItemWarehouse.ItemCode}=(upTo_ ""BO"") and {IM_ItemWarehouse.QuantityOnHand}<>0 and {IM_ItemWarehouse.WarehouseCode}=""000"" or {IM_ItemWarehouse.ItemCode}= (upFrom_""BO"") and {IM_ItemWarehouse.QuantityOnHand}<>0 and {IM_ItemWarehouse.WarehouseCode}=""000

(QuantityOnHand <> 0) AND (left({CI_Item.ItemCode},2)<>""FS"")

And the Occums Razor Solution award goes to @ThereseLogeais Thanks, The simplest solution and the only one that actually worked. @KellySappington your solution was including items with zero QtyOnHand and they have too many warehouses to apply your second formula. @DougHiggs your formula excludes all items that have FS as the first two characters, the formula would also have to test for len(itemcode) = 2 to only exclude the one itemFS, @LarryBradford thanks for your help today.

OK. I have to ask @ThereseLogeais ... trim removes leading and following blanks. Why does this solve our problem?

Because he only wants to eliminate item FS. If you trim the item code and it is only FS, it will be excluded. If you trim item FS123, it will not be FS so will be included.

If you don't trim ""FS123"" is it not still ""FS123"" (maybe ""FS123 "" or "" FS123"") but overall certainly not ""FS"". I am not questioning. I just don't remember things that I don't understand and I am assuming my base knowledge is wrong on this one.

I'm thinking if you don't trim FS123 it is ""FS123 "" so if it is looking for FS, it may still see the leading FS. The Trim function simply eliminates any extra spacing so you are left with the actual item code.

That's funny. I added more spaces but it 'trimmed' it to what you had @LarryBradford!

Is trim trailing spaces checked in the ODBC? Does that setting change the behavior?

But I would thing ""FS123"" <> ""FS"" and ""FS123 "" <> ""FS

I think it's a bug and trim works around the bug - logic does not apply here. Also, it has something to do with the AND statement, because each exclude formula work on their own, it's only when combined that it stops working.

FYI - @PhilMcIntosh it is broken when using an OLAP data source (SQL Mirror) as well as ODBC.

It could be an order of operations (precedence) issue. Have you tried using parenthesis to isolate the formulas?

@DougHiggs yep, doesn't matter.

Weird. I wonder what would show if you did a Database - Show SQL Query with the original formula. It might be something odd with the constructed Providex ODBC statement.

@LarryBradford and I did check that and it looked completely normal.

Then I'd suspect something like a NULL value in a field used in a formula (CR tossing the rows out), co-incidentally for the strangely omitted items.

@KevinMoyes , I don't think so. We tried a query of the table with no other parameters. It is bizarre and duplicable.

Definitely not related to CR... it is the ODBC return set.

@KevinMoyes it will only fail if you add the QtyOnHand <> 0 to the select. Each by themselves work fine.

My test with just the item code restriction seemed to fail consistently.