POB soft lock column

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #4679
    kieranwilson
    Member

    Hi,

    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?

    #4680
    Stefan Reichelt
    Participant

    Hi,

    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.

    Br,

    Stefan

    #4681
    kieranwilson
    Member

    Hi Stefan,

    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.

Viewing 3 posts - 1 through 3 (of 3 total)
  • You must be logged in to reply to this topic.