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.
SQL Function to Concatenate All Creators
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
Crystal Command to Concatenate All Creators
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
Kate Blanch
Systems Manager, Data & Digital Resources
410.547.9000 x.266 | [log in to unmask]
The Walters Art Museum
600 North Charles Street, Baltimore, MD 21201-5185
From: The Museum System (TMS) Users [mailto:[log in to unmask]]
On Behalf Of TMSUSERS automatic digest system
Sent: Saturday, August 13, 2016 5:00 PM
To: [log in to unmask]
Subject: TMSUSERS Digest - 11 Aug 2016 to 13 Aug 2016 (#2016-116)
|
|
Browse the
TMSUSERS online archives. |