Troubleshooting a missing record

Thursday 17 December 2015 @ 10:03 am

I had two different cases in the past week where a customer wanted to know why a specific record was missing from a report. I used the same process in both cases and thought I would share it. So say a report should include Order 273 and it doesn’t.

The preliminary step: missing or hidden?

Before we start we want to make sure that the ‘missing’ record is really missing and not just hiding in the report.  Go into the select expert and add a new rule that says [ {Order ID} = 273 ] and then refresh the report. When it is done look at the record count in the status bar at the bottom of the screen. Do NOT look at the report totals or anything else for this step. If the number in the status bar is not zero than the record is not missing, it is hidden. It might be suppressed or it might have been grouped in an unexpected place, or it might be filtered out using group selection, but it is already in the report. If the record count is zero then you have a missing record and can start the process below.

Selection Criteria or Inner Join:

There are really only two things that can prevent a record from being included in a report, an Inner Join or the Selection Criteria. So if Order 273 is not in the report then there is either an inner join to a table with no matching records, or Order 273 does not qualify on one of the report’s criteria. So we need to back these things out until the record shows up. Go into the selection formula and cut out or comment out the entire formula. Then add the test rule that I mentioned above:

{OrderID} = 273

Refresh the report with that criteria and see if the record count in the status bar shows more than zero records. If it does then the problem is in the selection formula. Leave the new rule in place and add one of the original rules at a time back into the selection formula.  See which one makes the record count drop to zero. If you find a suspect rule, put back all of the other rules and see if Order 273 still shows up. This way you know for sure that only one rule was the problem.

If you have removed all of the rules in the selection formula (except for the test rule) and the record count is still zero, there is one more place to check. Criteria can sometimes hide in the group expert if you use specified order. If all of the groups use ascending (A) or descending(D) order you can skip to the “Joins” step below. But if any groups show “S” for “Specified Order” go into the Options button for that group and check the “Others” tab.  An others setting of “discard” could eliminate Order 273. Change this to say “Leave in their own groups”, refresh and check the record count.

If after all that the record count is still zero then the problem has to be an inner join. Go into the Database Expert (Links tab) and hit the “auto-arrange” button. If the table used in our test criteria is not on the far left, then reverse any joins  that come out of the left side of that table.  To reverse a join, right click on the line, select reverse join, and then hit “Auto-Arrange” again. Repeat this process until the table that contains the test criteria field is on the far left. Then take all the join lines and change them to Left Outer Joins. Now the record should show up. To figure out which join is the problem you can start changing the Left Outer joins back to inner joins, one at a time, and refresh each time. Start with the joins that come out of the far left table. Then change the joins that go from those tables to other tables, and so on. When the record count goes back to zero, that will be the table with unmatched records.

And, if you need help applying these steps, you can always give me call to schedule a session.

Leave a Reply

Recrystallize Pro