Welcome to the POB User Group Online Community! › Forums › PUG International › POB soft lock column › Reply To: POB soft lock column
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