Crystal Reports中如何显示包含空白字段的记录——解决无发票号销售订单不显示问题

Hey there, let's get to the bottom of why those sales orders without matching invoices are vanishing when you add the invoice number field!

The Root Cause: Join Type is Filtering Out Records #

The most likely issue here is the join type between your sales order table and invoice table. By default, most reporting tools (like Crystal Reports, which your formula syntax suggests you're using) use an Inner Join when linking tables. This means only records that have matching values in both tables get included in your report—so any sales order without a corresponding invoice gets dropped before your null-handling formulas even have a chance to run.

How to Fix It: Switch to a Left Outer Join #

You need to change the join to a Left Outer Join (sometimes called a Left Join) to ensure all sales order records are retained, even if they don't have a matching invoice. Here's how to do it:

• Open your report's Database Expert (usually found under the Database menu)
• Locate the link between your sales order table and vFM_INVOICE_DETAIL
• Right-click the link line and select Edit Join
• In the join options, choose the setting that says something like:

  Include all records from [Your Sales Order Table] and only those records from vFM_INVOICE_DETAIL where the joined fields are equal

• Save the change and refresh your report—your missing sales orders should now appear!

Why Your Previous Attempts Didn't Work #

Let's clarify why the steps you tried didn't fix the problem:

• The "convert null values to default" setting: This only affects how null values are displayed, but Inner Join already filtered out the records with no invoice before they reached the display stage.
• Your isNull formula: Same issue—those records were already excluded by the join, so the formula never ran against them.

Quick Additional Checks #

Just to be thorough, double-check these two things:

• No extra filters: Make sure your report's Selection Formula doesn't have a rule that excludes records where {vFM_INVOICE_DETAIL.InvoiceNumber} is null or empty. For example, avoid conditions like {vFM_INVOICE_DETAIL.InvoiceNumber} <> "".
• Correct join field: Verify that you're linking the tables using the exact sales order number field in both tables—mismatched field types (e.g., text vs. number) or typos can cause unexpected filtering.

内容的提问来源于stack exchange，提问作者Reenans