Bulk BAQ search

Kinetic ERP
1

I’m curious if there are any ways to bulk search in the table criteria of a BAQ.
In short, I want to pull/calculate all usage history for a list of 300 parts.
Yes, I know I can add 300 part numbers in the table criteria area, one by one, but is there a quicker way?

Thanks again,
-Mike

2

The quick and dirty way is to use IN with a list. If you copy your list out of an excel column or even just notepad (as long as each part# is on its own line) you can paste-insert. Nothing wrong with that for ad hoc peeking around.

The set-based efficient and manageable way is to pick some parameter that users can set somewhere like Part Entry, then WHERE that field equals the value you’re looking for.

4 Likes
3

I’ve done something like this where the list changes. Define a parameter of @list and then paste in a tilde-delimited list.

(case when ‘~’ + @list + ‘~’ like ‘%~’ + Part.PartNum + ‘~%’ then Part.PartNum else ‘’ end)

If you don’t want to filter if the list is empty:

(case when ‘~’ + @list + ‘~’ like ‘%~’ + Part.PartNum + ‘~%’ OR @list = ‘’ then Part.PartNum else ‘’ end)

Or replace @list with a hard-coded list in the criteria if it’s not going to change.

Joe

4

I prefer to use the User Codes tables for this type of thing.
Create a User Code type that is appropriate for your BAQ (maybe 2025 Usage Review or your BAQ ID.) and enable Auto Sequence
Then Paste insert the parts into the Description field (it’s required and long enough).
Then in the BAQ include the User Code table and set it’s criteria so the type is the User Code type you created and join PartNum to the UserCode Description.
You can also filter on the Enable field of the User Code.
It’s nice ot be able to add/remove UD codes as needed.
I like this because often times it’s part of some project of evaluating usage or something for the top 100 sold parts or something, so the UD Code List can be used in other BAQs regarding the project and even BAQ Searches so users can view all the parts that are included.

2 Likes
5

A person smarter than myself worked out that you can add JSON in User codes and parse in a BAQ. so similar idea @Rick_Bird had, but just one ud code required.

Now who was that smarter person :thinking: there are a lot on here who are…

1 Like