CLOSE
CLOSE
https://www.sikich.com

‘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.

manufacturing routing where-used search

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

manufacturing routing where-used search

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.”

manufacturing routing where-used search

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.

Routing Routetable
Route Route
Operation relation RouteOpr
Operation relation activity WrkCtrRouteOprActivity
Activity WrkCtrActivity
Resource requirement set WrkCtrActivityRequirementset
Resource requirements WrkCtrActivityRequirement
Resource group requirement WrkCtrActivityResourceGroupRequirement
Resource groups WrkCtrResourceGroup

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.

manufacturing routing where-used search

Click Modify – Save As to save this amazing query.

The joins will look like below.

manufacturing routing where-used search

manufacturing routing where-used search

Scroll Alert!

manufacturing routing where-used search

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:

manufacturing routing where-used search

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.

This publication contains general information only and Sikich is not, by means of this publication, rendering accounting, business, financial, investment, legal, tax, or any other professional advice or services. This publication is not a substitute for such professional advice or services, nor should you use it as a basis for any decision, action or omission that may affect you or your business. Before making any decision, taking any action or omitting an action that may affect you or your business, you should consult a qualified professional advisor. In addition, this publication may contain certain content generated by an artificial intelligence (AI) language model. You acknowledge that Sikich shall not be responsible for any loss sustained by you or any person who relies on this publication.

About the Author