- 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 POB G6 2.0 mobile interfaces for analysts and customers provide great new capabilities and features. The mobile interfaces have a new look and feel and they allow utilization of most areas of POB from a mobile platform. On January 19th, Kevin Goertzen will show and demonstrate the new mobile interfaces in POB G6 2.0 […]
The PUG board has decided to have next year’s Scandinavian conference in a virtual setting. So, mark your calendar for April 20th, 2021, where PUG will host a one-day virtual conference. The decision was made based on the feedback the PUG board got when they asked how participants would feel about attending a physical conference, […]