Hi Jessica,

Two options come to mind. I’m assuming you are trying to create a true “Excel” checklist where multiple artist are in one cell (for example Printer: Antonius Zarotus, Author: C. Cornelius Tacitus)?

If you don’t care about maintaining a true grid, then a Crystal subreport will be the easiest solution. If you do care about the grid…here’s what I suggest…let me know if I can help you offline J

 

1)      If you can create custom objects in your TMS database, you can compile multiple artists together (I’ve added role too) and delimit them using a function. Dimitry at Gallery Systems helped me with this. Pasting the SQL code below to create the function. I (or Dimitry) can help you troubleshoot it if you run into problems. Functions are great, don’t be discouraged by what looks like complex SQL! Once your function is created, you call you function into a SQL query (I’ve highlighted the function from within this select statement below, you could create a view that does this, or even from a command in Crystal):

 

SELECT ObjectID, dbo.wam_Concat_Creator (ObjectID, '; ') as CreatorsFunction from Objects

 

2)      If you can’t create a custom function or view in your TMS database you can use a command in a Crystal report. If you’ve never used commands you might need to research a little but commands are well documented all over the interwebs! Command code also pasted below.

 

 

CREATE FUNCTION [dbo].[wam_Concat_Creator] (@ObjectID int, @Delim varchar(32)) 

RETURNS varchar(max) AS 

BEGIN

 

      -- Declare the return variable here

      DECLARE @out varchar(1000)

      DECLARE @DelimLen int

 

      Set @out = ''

      Set @DelimLen = datalength(@Delim)

 

select  @out=@out+ @Delim + R.Role+ ': '+

+ ltrim(rtrim(C.DisplayName)) --+ Char(13)+Char(10)

FROM

constituents c inner join conxrefdetails cxd on c.constituentid = cxd.constituentid inner join conxrefs cx on cxd.conxrefid = cx.conxrefid

and cx.id = @ObjectID and cx.tableid = 108 and cxd.unmasked = 1  and cx.displayed = 1

inner join roles r on cx.roleid = r.roleid and cx.roletypeid = 1

order by cx.displayorder asc

if len(@out)>@DelimLen

            set @out= substring(@out,@DelimLen+1,len(@out)-@DelimLen)

           

      RETURN @out

END

 

GO

 

select distinct ObjectID,

STUFF((Select ', '+ R.Role + ': ' + C.DisplayName

FROM

constituents c inner join conxrefdetails cxd on c.constituentid = cxd.constituentid

inner join conxrefs cx on cxd.conxrefid = cx.conxrefid

inner join roles r on cx.roleid = r.roleid and cx.roletypeid = 1 and cx.tableid = 108 and cxd.unmasked = 1  and cx.displayed = 1

where CX.ID=O.ObjectID

FOR XML PATH('')),1,1,'') from Objects O

 

 

 

 

 

 

TMSUSERS Digest - 11 Aug 2016 to 13 Aug 2016 (#2016-116) Table of contents: Checklist list with more than one artist/maker listed 1.     Checklist list with more than one artist/maker listed o    Checklist list with more than one artist/maker listed (08/13) From: Jessica Wilks <[log in to unmask]>

 

Browse the TMSUSERS online archives.