Crystal Reports Null Fields disappear on filter

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Null Fields disappear on filter

Author	Message
customreport Newbie Joined: 26 Jan 2011 Location: United States Online Status: Offline Posts: 28	Topic: Null Fields disappear on filter Posted: 26 Jan 2011 at 6:09am
	I am automating an open sales order report and have it almost done except when I go to filter out a record in one of the fields the blank records disappear. When I first added the field to the report the report only showed sales orders with a record in the field, the blank records disappeared. I changed the join type to left outer and this seemed to fix the problem. Now when I filter out one of the records the blank records disappear again. What am I doing wrong? I tried the report in Access and didn't have the problem.
	customreport
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 26 Jan 2011 at 7:40am
	likely your select statement is turning an outer join into an inner one. You may be able to avoid it with a simply adding in "isnull(field) or" to your select statment but can't really tell without knowing the joins, table fields and the select statement.
	IP Logged

customreport Newbie Joined: 26 Jan 2011 Location: United States Online Status: Offline Posts: 28	Posted: 26 Jan 2011 at 9:50am
	thanks, I tried adding the isnull or (field) formula earlier and when I did that the null fields appeared, but I had a{v_txn_sales_order_line.is_invoiced} field set to false and now it shows all sales orders whether invoiced or not. When I go to set the filter back to false an error comes up that says: "Composite expression. Please use formula editor to do editing." Below are my formulas not {v_txn_sales_order_line.is_invoiced} and not ({v_lst_item.name} like ["Warranty - Service", "Subtotal", "REPAINT", "Remake-Warranty", "RE-MAKE-Shop error", "RE-MAKE-Shipping error", "RE-MAKE-Sales Error", "RE-MAKE-Freight Charges", "RE-MAKE-Engineer", "Packaging/Crating", "NOTES", "INSF", "Freight--Third Party", "Freight--Prepaid", "Freight--FA", "Freight--Collect", "CUSTSER", "CR-Shop Error", "CR-Sales Error", "CR-Paint Problem", "CR-Miscellaneous Clean UP", "CR-Freight Claim-Unpaid Portion", "CR-Freight Charges-Shipping", "CR-Freight Charges - Late Ship", "CR-Engineering Error", "CR-Discounts Taken", "CR-Bad Debt", "CANCEL", "988888"]) and isnull ({v_cf_customer.field}) or (not ({v_cf_customer.field} like "SHIPPED"))
	customreport
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 26 Jan 2011 at 10:00am
	maybe this (changes in red)...? not ({v_txn_sales_order_line.is_invoiced}) and not ({v_lst_item.name} like ["Warranty - Service", "Subtotal", "REPAINT", "Remake-Warranty", "RE-MAKE-Shop error", "RE-MAKE-Shipping error", "RE-MAKE-Sales Error", "RE-MAKE-Freight Charges", "RE-MAKE-Engineer", "Packaging/Crating", "NOTES", "INSF", "Freight--Third Party", "Freight--Prepaid", "Freight--FA", "Freight--Collect", "CUSTSER", "CR-Shop Error", "CR-Sales Error", "CR-Paint Problem", "CR-Miscellaneous Clean UP", "CR-Freight Claim-Unpaid Portion", "CR-Freight Charges-Shipping", "CR-Freight Charges - Late Ship", "CR-Engineering Error", "CR-Discounts Taken", "CR-Bad Debt", "CANCEL", "988888"]) and (isnull ({v_cf_customer.field}) or (not({v_cf_customer.field} like "SHIPPED"))) Edited by DBlank - 26 Jan 2011 at 10:00am
	IP Logged

customreport Newbie Joined: 26 Jan 2011 Location: United States Online Status: Offline Posts: 28	Posted: 26 Jan 2011 at 10:21am
	Thank you! That worked!
	customreport
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum