How to look for item id with a given status in a given date range?

[TomaszKucharczy]

Hi,   I need to look through a queue using item key to search for item id corresponding to that item key in a given date range (note that the item might not be present in the queue at all) without using collections / loops etc. Currently, I use the Work Queues business object's action "is item in queue", which includes status filters (for pending / deferred / completed / terminated), but it always starts the search from the oldest item in the queue, whereas I need to look through items from the last couple days or so only. Is there maybe a way to use an SQL query of some sort within the process / object studio to do what I need? A simple "select where" would solve the issue here. How one would go about doing that? Thanks!

[John__Carter]

Hi Tomasz - there's no functionality for 'find items with key between 100 and 200' I'm afraid. The Item Key field could be anything, so it would be difficult to define what 'within range' would mean. You might try the Key Filter input to Get Next Item, although with that you have to specify the exact key, eg 100, 101, 102, 103 etc,. So not ideal either, I must admit. Try the Get Report Data action as well, it might be suitable for a date range search.

[TomaszKucharczy]

I think you misunderstood - I do know what the key item is *exactly*. I just want to find if it exists within a queue for a given date range (with a given status - i.e. completed, pending or exception). The ""is item in queue"" actions seems perfect for this, it just lacks the date filter.  Get Report Data outputs a collection, so that's a no no for us. Get next item is out of the question as well, because we don't want to do that at this point in the process :) Is it possible to specify custom SQL queries from within Blue Prism, somehow? Extend the original action this way?

[John__Carter]

No it's not recommended to do some custom SQL. If you know the key then you can get the item ID, and from that you could use Get Item Data to find information about the item.

[TomaszKucharczy]

Yes, but if the item key has been duplicated in the past the lookup will always return the same (the first, i.e. oldest) item ID. Is not recommended or not possible? 🙂

[John__Carter]

Get Pending/Completed/Exception Items with Key Filter input. Loop through collection calling Get Item data for each ID. Tag the item you want. Get Next Item with Tag Filter and Key Filter input. SQL is certainly possible, but I don't recommend you try.

[RonAng]

May I ask why SQL is not recommended?

[TomaszKucharczy]

@John_Carter But then we end up with the same solution I mentioned in the original post - one that requires looping through collections, i.e. is super slow for very big queues, with tendencies to crash. 

[John__Carter]

Tomasz - How many items are you likely to have with the same key - not that many I would have thought? If you have so many that getting them out as a collection would slow down your process then I'd say you need to look again at the process design and choice of key. In my experience the key is usually either unique or else duplicated only once or twice. Rang - SQL is not recommended because it breaks the app server security model and risks damaging the DB and affecting the whole system.