‘Where Used’ Search on Resource (Group)

“Where used” search is an important functionality in every ERP system. For instance, it is a well known feature for Bills of Materials. I often do a bottom-up search for a certain raw material in the BOMs, because I want to know in which end products this material is used.

For Routings, such a where-used feature is also useful. If I have new machines in my shop, and I have routings to update, I want to know which routings are using this machine resource. Or if I do a lot of outsourcing and I am changing my contractor, I want to know which routings contain the resource (group) that represents this contractor.

The standard system has no menu choice for “where-used” for routings.

But a query can be created for this. It is not easy, but this article will show how.

Joins Joins Joins

Start with the menu choice “All routings,” and click the Advanced filter.

Put the cursor on Routes and go the Joins tab and click Add table join.

Change the slider “show table names” to “Yes.” This makes it easier to follow the joining party, because you will see the real table names. Search for the table name “Route.”

Highlight the table “route” and click “Select.”

This is the first join! Your cursor will be automatically on the newly joined table. Repeat this table joining following the table list below.

After joining the Resource groups table, return to range. Add a new row for this Resource groups table and put in the resource group you want to do the where-used search for.

Click Modify – Save As to save this amazing query.

The joins will look like below.

Scroll Alert!

The space to display the tree of joined tables is limited and a scroll bar appears in our mega-join table list. This can be easily overlooked, and you would think your join did not work because you don’t see your joined table.

Scroll down then you can open the next branch in the tree. In the example above you see “Resource requirements” without a white carrot, so you would think there is no lower level table. But after scrolling you will see there are two more tables. You will have to open/scroll a few times. In our particular example, we will have to scroll two times. The tables in the top by then have disappeared, which is why I needed two screen shots to show the entire joining tree. (I will enter a request in the ideas database to give us more space here.)

Where-used search for other resource requirements

If you don’t search using resource group, you would choose a different table to join from the Resource Requirements forward. The table list for the join options for this table looks like this:

You will recognize all available resource types here.

Conclusion

I have done quite some table joining in my life but never more than 2 or 3. I was under the wrong impression that the more joins, the greater the chance it would not work. But this is a misconception. You have to choose the right path, and then the number of joins does not seem to matter. Search time is not very long. You will need help from a developer to find this perfect path through the tables.

Once you have a good join path, the example above can be used in other situations. A great example is the list of late production orders. “Delayed production orders” is a standard menu choice, but what we really need is delayed production orders per resource group.

Many thanks to Jonathan Havard for his invaluable assistance in getting this where-used join defined.

What questions do you have about your ERP solution? Sikich experts are ready to help.