If you’re looking at this post, I’m sure you’ve realized that when you use the filter of Internal ID in a Saved Search that it’s extremely difficult. Unless your environment looks like my temporary account with only 8 transactions listed… You’ll more than likely know the pains of the horrendously long load times and then the struggle to find the exact transaction you’re looking for. Obviously this is used on a situational basis and won’t make sense to use in some cases. However, when needed… it’s very very handy!

In contrast, when you’re working in a live environment, you can have literally hundreds of thousands of records to individually search for, if they were not created directly one after the other… What a pain!

The beauty of the method I’m going to demonstrate is that it can be done using a list from a spreadsheet. Let’s be honest, nine times out of ten you’ll be working from a spreadsheet regardless! It’s just the nature of the beast.

When would I use something like this?

Let’s say you have an integration or a CSV import that you just ran, and your accounting team realized that there are errors with a random listing out of the transactions brought in. Often times they’ll conveniently send you a spreadsheet with a list of records. I would use this method to quickly find only those orders to investigate further. In some cases, you may need to delete them all together! You can do that here too! I’ll put up another guide on how to quickly delete from a saved search soon.

On to the good stuff…

How to use a CSV/Spreadsheet to set specific internal ID’s on a Saved Search

For this example, I’m going to use a very short list. Please note that how we’re going to set this up may require split batches due to field character limits. To save myself hassle, I keep a template saved and ready to go then just update the ID’s. Here’s the list I want to search on specifically.

We’re going to setup a basic CASE/WHEN statement, that we can throw into the saved search in a few minutes.

=CONCATENATE("WHEN ", A2 , " THEN 1 ")

This is the formula that we’re going to set up in cell B2 next to the first ID that we want to use. Basically we’re going to use the spreadsheet to build our statement. Here’s what it should look like in action.

Click + hold + drag the small box in the bottom right corner of B2 down to the last ID that you want to use this with, or double click it to auto-fill. Now that our spreadsheet is done let’s move to the Saved Search. Again, I’d recommend saving this as a template so you can just paste over the ID’s for faster future use.

Saved Search Setup

Start with your basic transaction search. For this example, I’m going to use an Invoice search. You can use criteria additional to these listed, but this is the basic setup to retrieve only the invoices that have the same Internal ID listed in the Spreadsheet we setup. Yours will obviously be different, that shouldn’t need to be said.

• Type is Invoice
• Main Line is true
• Formula (Numeric) – We’ll set this up now.

At this point, your search should look like this:

Next, click the “Set Description” icon (circled in the image above). Here’s where we’re going to insert part of the statement we setup in the spreadsheet.

The entire statement should look something like this:

CASE {internalid} WHEN 13754067 THEN 1 WHEN 13754068 THEN 1 WHEN 13754069 THEN 1 WHEN 13754070 THEN 1 WHEN 13754072 THEN 1 ELSE 0 END

Basically what it’s saying is “Only retrieve invoices when the internal id is 13754067, 13754068, 13754069, 13754070, or 13754072. Otherwise ignore them!”

Lastly, switch to the results tab and choose what you’d like to see. For my example I’m only going to use these results for confidentiality purposes.

• Date
• Document Number
• Amount

Save and Run the search and here are my results. You should have something similar.

This may seem like it takes too long to setup… but once you have your templates saved and the first search under your belt, it will be much quicker. As I said previously, I use this in rare situations. It’s a great trick to have in your arsenal.

As always, please let me know if this was useful, confusing or otherwise. I’m always open to new ideas and feedback. If you have another method, I’d love to hear what you do!