09-24-2020 04:06 PM
Hello everyone,
I'm curious if anyone has experimented with the Manage Select Lists and successfully built a query in them that would allow you to group items then let a user select from that group.
Here is my scenario - I have a list of scan queues that I'd like to group together into smaller subgroups for those applicable areas. Currently since my select statement for my select list is:
select queuename,
queuenum
from hsi.scanqueue
ORDER BY 1
It only provides the user with a list of scan queues to choose from. However, I'd ideally like to only provide a handful of choices based on the grouping we've decided.
Has anyone done that/had success with that/or have ideas how I could accomplish what I'd like to do otherwise?
Thanks for your help!
09-25-2020 07:46 AM
Hi Beth,
Hopefully I am understanding what you're asking about - this is a list to select from for a parameter on a custom SQL data provider, right?
You can add to the "where" clause to limit the select list however you like.
Are you looking to only allow users to select from a list of scan queues they have access to via user groups? I'm doing something similar using document types with this as the SQL for the select list, this would need to be adjusted for scan queues and is written for Oracle db so might need adjustment there as well.
select trim(D.ITEMTYPENAME), D.ITEMTYPENUMfrom DOCTYPE Dwhere D.ITEMTYPENUM in (select ITEMTYPENUM from USERGROUPCONFIG where USERGROUPNUM in (select USERGROUPNUM from USERXUSERGROUP where USERNUM = ##OB_CURRENTUSER_ID))order by 1;
This only lists document types that the logged in user has access to.
For scan queues, you'd need to use USERGSCANQUEUE instead of USERGROUPCONFIG.
09-25-2020 01:34 PM
That should be pretty simple then. Assuming all the scan queues have names like "HIM - ...", "Scheduling - ..." (adjust as needed), your main data provider SQL would be along the lines of:
select *
from SCANQUEUE
where QUEUENAME like ''|| @{pGroupName} || '%'
;
and your SQL select list on the parameter configuration would be:
select distinct substr(QUEUENAME, 1, instr(QUEUENAME, ' -')), substr(QUEUENAME, 1, instr(QUEUENAME, ' -'))
from SCANQUEUE
order by 1
;
That will get a substring of the scan queue name up until the first " -" in the name (again, adjust as needed to get the right segment of the name).
09-25-2020 02:16 PM
Thank you so much for all of this great information Larissa, I really appreciate you taking the time to give me the full details, screenshots, and query info so I can be successful in this!
09-25-2020 10:12 AM
You could also try using a case statement in your query.
SELECT scanqueue.queuename,
scanqueue.queuenum
FROM Optima3.hsi.scanqueue scanqueue
WHERE CASE queuenum -- you could also use queuename
WHEN 1 THEN 'Group Name'
WHEN 2 THEN 'Group Name'
WHEN 3 THEN 'Group Name'
ELSE 'Misc'
END =
'Group Name'
ORDER BY scanqueue.queuename ASC
This has the disadvantage of needing to be maintained if you want to add a new scan queue to the group.
09-25-2020 10:28 AM
Thanks Laura, yes I was worried about it becoming an administrative challenge due to the maintenance needed. Thankfully we aren't adding in ones super often but it's always better when we don't have to update constantly.
I'll look into if a case statement would work in the query. Thanks for your advice!
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.