How to create a query with a large criteria list

Epicor ERP 10
,
1

I am trying to create a query with a list of criteria. It’s a large list and when I try to use the “IN” Operation with a constant list I find I have to manually add criteria one item at a time. I cannot even copy and paste into the list.

Do you have any recommendations for a better way to do this?

2

The only thing I have found is to create the BAQ and then run it in BAQ Maintenance. You can copy and paste a list in the criteria. Not great because you cannot publish it, but at least you don’t have to manually type them all in.

1 Like
3

I have used a UD table as well, but that’s kinda funky.

4

Paste insert?

5

Unfortunately, for version 10.2.300 I think there is no good way. @jkane I never tried that before. Would’ve been nice. I know at some point they fixed this and made it like any grid and you can paste into it. But not at 10.2.300.

Another thing you might be able to do is to create a Boolean calculated field. Then you could use an Excel list and format the rows so that they say the value surrounded in single quotes with a comma at the end (make sure to remove the last comma). Then you can essentially tell the calculated field to check the box when it’s in your list. You can use this as a subquery then reference it to only bring back rows where that column is true.

Then copy/paste that selection of cells into your calculated field window and it should keep the formatting with a line break between each value…
So it would look like this:

case
  when Table.Column in (
  'value1',
  'value2',
  'value3'
  )
  then 1
  else 0
end
2 Likes
6

Actually for the criteria - it’s a paste update to the list when using the IN function.

Another way to have a long list is to use User Codes.
I have a query where the user can select in an updatable BAQ, the transaction types. This updates the table to then show part transactions associated with the transaction types.

7

@LarsonSolutions , this was added in later versions, so I don’t think @CStahl 's version (10.2.300 if the profile is correct) has that feature.

2 Likes
8

Hi Charles, maybe if you describe the business process you’re trying to solve. For example, I have a list of parts, suppliers, customers, etc.

Normally if the criteria list gets too long, that indicates to me that I should probably use an attribute on the record like a Data Tag or a User Defined Field and then use that attribute to select records. Attributes reduce the amount of maintenance on the query as well since they can be set in a maintenance command or BPM.

1 Like