Recently my friend and excellent Sage 300CRE consultant Carol Holley reached out to ask why a Subcontract she had written wasn't always retrieving data. She explained that it often works but not always. Occasionally printing just a blank page.
After thinking for a moment it reminded me of an experience I had more than 20 years earlier. When first writing custom reports for Sage clients while an employee at Sage I had a situation where I too had written a custom Subcontract form. The client would occasionally call to say it wasn't working on a particular Subcontract. Through trial and error, we came to figure out that a new Vendor was setup and later a Commitment (Subcontract) had been setup for that new Vendor. That new Vendor had a Custom Field (A user definable field) and by pure accident the customer had forgotten to fill in the custom field with an answer. As many of you may know, Sage Custom Fields don't really exist, even though they've been setup, until a value has been placed in that custom field.
My early answer to this client was "Don't forget to fill in the custom field". Only after it happened a few times and perhaps my Crystal skills increased did I come to realize the real lesson of this situation. What's that lesson you ask? Table Linking Matters! I had joined the Vendor Table and the Vendor Custom Field table with an Inner Join. An Inner Join only works when data is found on both tables. Unless that Custom Field Tables is so important that the Subcontract should not print when no answer exists, otherwise, the link should have been a Left Outer Join between those two tables. That way the Subcontract would print even though the custom field may be blank.
Links not only tie two tables together based on a common field, they also act as a filter to return data only when both tables contain linked fields. Linking is a powerful filtering feature, even if you did not intend it to be so as my example above illustrates. Only use Inner Joins when you know both tables will always contained the linked fields or your intention was to use that link as a filtering agent.
This example is relevant for Sage, Vista, and Spectrum databases. And for that matter all databases. Make linking a conscious decision instead of just letting Crystal Reports set it as an Inner join by default.