Over the years, I noticed that even experienced consultants sometimes have trouble figuring out how to properly link database tables for reporting purposes. It doesn’t matter what reporting tool is being used, it could be Crystal Reports, Sage Intelligence, or any product specific tool that lets you pick the tables to report on and specify the relationships between them.
This article assumes that the reader has basic understanding of database tables, relationships and reporting tools. It only covers a relatively unusual scenario that I noticed cased some confusion in people.
In this example, we’ll be using Sage 300 tables for BOM header and BOM components, the respective table names are ICBOMH and ICBOMD. They are linked by the ITEMNO and BOMNO fields, as shown on the figure below.
So far, this is straight-forward. Now, let’s say we want to include the item category for both the BOM’s master item and for each component. The item category is a field in the ICITEM table. It can be linked to ICBOMH table using the ITEMNO field. ICBOMD also has ICITEM field, but it is used to link to the ICBOMH table. The component item number is actually in the COMPONENT field, and that’s what needs to be linked to ICITEM table.
We have to tables (ICBOMH and ICBOMD) that both need to be linked to ICITEM table. Here is where I noticed people get confused. They try to do something like this:
At the first glance, it seems to make sense. After all, we have three tables that we want to report on, and all three are included in the report and the relationships seem to be configured correctly, but this type of circular reference does not work. To understand why, consider how this translates to SQL select statement.
Notice that when linking to ICITEM table, it wants to make sure that the matching item number is equal to both the BOM master item AND the BOM component item, but this will never be true. Surely the master item and component item will be different.
The correct way to link these tables is to add the ICITEM table twice. Here is how it looks.
And the equivalent SQL select statement is:
Notice that the second time the ICITEM table was added, it was assigned an alias of ICITEM_1, and that alias is used in the rest of the statement. Now, if you want to get the category of the BOM master item, you would get it from the ICITEM table. If you want the category of the component item, you would get it from the ICITEM_1 table.
About Optimize Business Consulting
We specialize in building custom software to enhance your business, such as automatic integrations, customizations, complicated reporting, completely new business processes. Please review our blog for examples of past projects. We also have a number of products, that are aimed on improving productivity. Please contact us for a no obligation discussion about how we can help your business.
Leave a Reply