- You must be logged in to reply to this topic.
I am tying to create a new column in POB that will allow users to see who is soft locking or locking a case.
I’ve written the below and tested it, the issue is POB throws an error message when two users soft lock the same case.
(SELECT x.user_id FROM Pob_Soft_Lock x WHERE x.root_pk=case_pk)
Any ideas how I can get this to work correctly?
I’m not sure how deep you’re into SQL, but you’ve obviously already discovered the tricky part: If there is more than one result, your query will return more than one line – and that doesn’t fit into one single cell. However, SQL has some nice functions to merge such results. As a first step, you should transfer the logic into something like a Stored Procedure, Function, or View. I’ll show you the View option:
CREATE VIEW [dbo].[Custom_View_CaseSoftLocks] AS SELECT case_pk ,users = STUFF(( SELECT ', ' + sl.user_id FROM Pob_Soft_Lock sl with(nolock) WHERE sl.root_pk = cas.case_pk FOR XML PATH('') ), 1, 1, '') FROM Case_Table cas
Hint: Test the Select part with “where cas.id = ‘<any case ID>'” in your SQL Management Studio before you proceed.
And now you can query that in your column definition:
(select top 1 sl.users from dbo.Custom_View_CaseSoftLocks sl where sl.case_pk=case_pk)
The “top 1” is not necessary, but I usually take it as an additional security to make really sure to have only one result.
I’m really just a beginner when it comes to SQL. I can do the basics but apart from that i’m lost!
I’ve just tested this and it works as expected.
Thanks for your help again, much appreciated.
The Wendia North American team is excited to announce a new virtual POB Administrator training taking place in November. The training includes remote instruction sessions and daily assigned labs with a mentor available if needed. The first week of training will be the week of November 16th, and the second week will be the week […]
Join our October webinar by Wendia North America senior POB consultant Payton Regan if you would like to learn more about building and utilizing Explorers in POB. Participating in the one-hour webinar that will be held on Tuesday, October 20th, at 16:30 CET / 8:30 am MST, is as always free of charge. See more […]
Join Wendia’s next webinar to learn more about using, maintaining and setting up knowledge. On September 15th, Stephen Goertzen, Wendia North America Senior Consulting Engineer, will host a one-hour webinar on Knowledge Management, where he will approach Knowledge from three different perspectives: Knowledge management for end-users/customers Knowledge management for users Knowledge management in general – setup […]