Multiplied details due to tables links?

[borozu]

Hi,

Some details printouts are doubled. Roughly I know why. As I have two table fields connected by left outer join:

V_BI_ACKNWLDGMNT.ORDER_NO --> V_ORDER_LN_TEXT.ORDER_NUM

V_BI_ACKNWLDGMNT.RECORD_NO --> V_ORDER_LN_TEXT.ORDER_LINE

And trouble is that in V_ORDER_LN_TEXT table my record apprears twice

but just with different ORDER_TYPE. But CR still prints it twice:

I've tried to suppress it using ORDER_TYPE but CR always prints it twice anyway.

I need to print TEXT from V_ORDER_LN_TEXT only when ORDER_TYPE = 9999. Any way to do that?

[DellSC]

To add to Jamie's answer, because of the OR, you need to use parentheses to get the filter to process correctly. Something like this:

(isnull({TEXT}) and {ORDER_TYPE} = 2) or
(not isnull({TEXT}) and {ORDER_TYPE} = 9999)

-Dell

[borozu]

Thank you for all the suggestions guys. I'm afraid when using the suggested code

(isnull({TEXT}) and {ORDER_TYPE} = 2) or
(not isnull({TEXT}) and {ORDER_TYPE} = 9999)

it prints JUST the lines which have the extra comment so 006 and 007 when I need all of them of course so 001-007.

[DellSC]

Try this:

(isnull({TEXT}) and {ORDER_TYPE} <> 9999) or
(not isnull({TEXT}) and {ORDER_TYPE} = 9999)

They other thing you could do would be to use a formula for the text - something like this:

If {ORDER TYPE} = 9999 then 
  {TEXT}
else
  ""

-Dell

[JWiseman]

Kris, it could be that your {text} field is actually blank in some cases, i.e. "", as opposed to being a NULL value. This could be the actual data or that in the Report Options that you are converting null data values to a default non-null.

(isnull({TEXT}) and {ORDER_TYPE} = 2) or
(trim({TEXT}) = "" and {ORDER_TYPE} = 2) or
(not isnull({TEXT}) and {ORDER_TYPE} = 9999) or
(trim({TEXT}) <> "" and {ORDER_TYPE} = 9999)

[JWiseman]

OK thanks Kris, that's very helpful. There's several ways to deal with this...

• Bring back everything that you see in that last screenshot, create a formula based on the condition, and then suppress the unwanted Details records. or
• Bring back everything that you see in that last screenshot, create a Group on the item and then a Group Summary, Maximum, on the Order Type, then use a Group Selection Formula. or
• Use a SQL Expression field to bring back the field from the V_Order_Line_Text table instead of using that table in the Database Expert. or
• Create a Command Object to base your report off of, instead of using tables, and in that Command object use a subquery to bring back the TEXT field based on a conditional mapping. or
• Look at creating a Record Selection formula like

isnull({TEXT}) and {ORDER_TYPE} = 2 or
not isnull({TEXT}) and {ORDER_TYPE} = 9999

My worry about the above Record Selection Formula is that you might be missing out on some data that you need on the report...i.e. you might have additional conditions based on the order type and text etc.

[JWiseman]

Hi Kris, in your Report > Selection Formulas > Record use one of the following, depending on what you're trying to accomplish with filtering out the orders.

{ORDER_TYPE} = 9999

or

isnull({ORDER_TYPE}) or {ORDER_TYPE} = 9999

[borozu]

Hi Jamie,

That's a nice hint about Record Selection formulas - I might use it in a future.

In this particular case I'm not sure will be able to use it as after applying the record filter I can see only two records - 006 & 007. It's not so easy to use it as I need a lot of data from ther tables and filter cuts them out.

For ex. ITEM, QTY, PART NUMBER from details A are coming from main V_BI_ACKNWLDGMNT table and only TEXT in details B should come from V_ORDER_LN_TEXT table but only when ORDER_TYPE = 9999

[JWiseman]

Did you try the 2nd option above? isnull({ORDER_TYPE}) or {ORDER_TYPE} = 9999

[borozu]

Yes. It prints weird results, like just line 001 and 002.

I have 7 line items in this case. As said before most of items data comes from main table V_BI_ACKNWLDGMNT. Rest of the data is optional sometmes lines do have it sometimes don't (V_ORDER_LN_TEXT.TEXT is extra note to the mail line).

Like on this screenshot: ORDER_NUM=13 does have optional TEXT only for some ORDER_LINES. Does not have anything for line 0020

[JWiseman]

Please let us know out of the screenshot exactly what records, using Order_Num should be returned n the report Details and which ones should not.

If the goal is not to filter anything, but just have a conditional display, then that is different than filtering. If you want to bring back all of the Order Numbers like in the screenshot and just have a conditional display for Text, then that's done by adding a Formula.

if {ORDER_TYPE} = 9999 then {TEXT} else ""

[borozu]

Ok,

That's what I got right now:

All of the "details A" data comes from V_BI_ACKNWLDGMNT table - that's the master.

IF there's extra note for one of the lines (001, 002 etc) it's being stored in V_ORDER_LN_TEXT table under TEXT column with ORDER_TYPE=9999. Unfortunately in some cases also line with ORDER_TYPE=2 is being generated

All I need is to get this extra note (so when ORDER_TYPE=9999).