talk@lists.collectionspace.org
WE HAVE SUNSET THIS LISTSERV - Join us at collectionspace@lyrasislists.org
View all threadsExhibition Planning - Object List problem
Hello all,
We at OMCA are running into a conundrum with how to track object checklist
information in a modified Exhibition Planning procedure. We want to be able
to list Exhibited Object Information - like an object checklist, with
information on recent condition, mounting, framing, and location within the
exhibition. UC Berkeley has shared the schema for their Exhibition Planning
procedure that includes these fields, and I'm wondering if other museums
have tried to solve a similar issue. Basically, we want to list objects and
the information related to their participation in an exhibition, but also
be able to run reports on that list. As it stands, in UCB's extension, the
Object field is text - so it doesn't link up with the object record in the
database. We've thought about including exhibition information fields in
the object cataloging record itself, but it seems sloppy practice to
perform exhibition planning within the object cataloging record, which we
feel should really be preserved for object-specific info (not
exhibition-specific info).
I've attached a screenshot of the the Exhibited Object Information group,
which I believe is a UCB-produced extension to Exhibition Planning.
Has anyone faced a similar problem, and come up with an elegant solution?
Just wondering! Let me know if you need more information here -
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
[image: Inline image 1]
Al,
Of course you can relate those exhibition records to the objects as well,
although that doesn't tie the particular object in the checklist to its
object record, and relations can't take the additional information either.
An alternative I guess is to make exhibition an authority and create
exhibition checklist type records that relate to a single object.
Susan
On Fri, Sep 25, 2015 at 11:14 AM, Al Bersch abersch@museumca.org wrote:
Hello all,
We at OMCA are running into a conundrum with how to track object
checklist information in a modified Exhibition Planning procedure. We want
to be able to list Exhibited Object Information - like an object checklist,
with information on recent condition, mounting, framing, and location
within the exhibition. UC Berkeley has shared the schema for their
Exhibition Planning procedure that includes these fields, and I'm wondering
if other museums have tried to solve a similar issue. Basically, we want to
list objects and the information related to their participation in an
exhibition, but also be able to run reports on that list. As it stands, in
UCB's extension, the Object field is text - so it doesn't link up with
the object record in the database. We've thought about including exhibition
information fields in the object cataloging record itself, but it seems
sloppy practice to perform exhibition planning within the object cataloging
record, which we feel should really be preserved for object-specific info
(not exhibition-specific info).
I've attached a screenshot of the the Exhibited Object Information group,
which I believe is a UCB-produced extension to Exhibition Planning.
Has anyone faced a similar problem, and come up with an elegant solution?
Just wondering! Let me know if you need more information here -
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
[image: Inline image 1]
Talk mailing list
Talk@lists.collectionspace.org
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
Thanks Susan,
Right - yes, what we're trying to solve is a way to match up that object
checklist info with the actual object (not just the exhibiton procedure).
The idea of using the exhibition authority is one solution to think about.
In that scenario, we could make an "exhibition info" group in the object
record, using an exhibition authorty controlled field and then all the
object checklist fields.
Has anyone in the group done something like this?
thanks again,
Al
On Fri, Sep 25, 2015 at 11:37 AM, Susan STONE sstone@berkeley.edu wrote:
Al,
Of course you can relate those exhibition records to the objects as well,
although that doesn't tie the particular object in the checklist to its
object record, and relations can't take the additional information either.
An alternative I guess is to make exhibition an authority and create
exhibition checklist type records that relate to a single object.
Susan
On Fri, Sep 25, 2015 at 11:14 AM, Al Bersch abersch@museumca.org wrote:
Hello all,
We at OMCA are running into a conundrum with how to track object
checklist information in a modified Exhibition Planning procedure. We want
to be able to list Exhibited Object Information - like an object checklist,
with information on recent condition, mounting, framing, and location
within the exhibition. UC Berkeley has shared the schema for their
Exhibition Planning procedure that includes these fields, and I'm wondering
if other museums have tried to solve a similar issue. Basically, we want to
list objects and the information related to their participation in an
exhibition, but also be able to run reports on that list. As it stands, in
UCB's extension, the Object field is text - so it doesn't link up with
the object record in the database. We've thought about including exhibition
information fields in the object cataloging record itself, but it seems
sloppy practice to perform exhibition planning within the object cataloging
record, which we feel should really be preserved for object-specific info
(not exhibition-specific info).
I've attached a screenshot of the the Exhibited Object Information group,
which I believe is a UCB-produced extension to Exhibition Planning.
Has anyone faced a similar problem, and come up with an elegant solution?
Just wondering! Let me know if you need more information here -
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
[image: Inline image 1]
Talk mailing list
Talk@lists.collectionspace.org
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
--
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
Hi Al,
We've done just that with a series of four Exhibition reports I've
written that I'd be happy to share with you (mostly documented in PAHMA-1282
https://issues.collectionspace.org/browse/PAHMA-1282 and related
subtasks). The reports do depend on two conditions, but so far that's not
been a problem:
- The object number written in the text field exactly match the actual
object number - All objects in an exhibit are in the Exhibited Object Information and
are related to the Exhibition record.
The boilerplate query I based these reports on is:
SELECT ec.exhibitionnumber AS "Exh. number", ec.title AS "Exhibit
title", cc.objectnumber AS "Mus. number", cp.sortableobjectnumber,
eog.exhibitionobjectsection AS "Section",
eog.exhibitionobjectcase AS "Case",
eog.exhibitionobjectseqnum AS "Seq. num.",
eog.exhibitionobjectmount AS "Mount",
ong.objectName AS "Name",
spd.datedisplaydate AS "Prod. date",
scd.datedisplaydate AS "Coll. date",
STRING_AGG(DISTINCT REGEXP_REPLACE(fc.item, '^.)''(.)''$', '\1'),
'; ') AS "Collector(s)",
STRING_AGG(DISTINCT ac.acquisitionreferencenumber, '; ') AS "Acc. No.",
STRING_AGG(DISTINCT REGEXP_REPLACE(aco.item, '^.)''(.)''$', '\1'),
'; ') AS "Donor(s)",
STRING_AGG(DISTINCT REGEXP_REPLACE(apg.assocpeople, '^.)''(.)''$',
'\1'),'; ') AS "Culture",
REGEXP_REPLACE(opp.objectproductionperson, '^.)''(.)''$', '\1') AS "Maker",
REGEXP_REPLACE(oppl.objectproductionplace, '^.)''(.)''$', '\1') AS
"Production place",
REGEXP_REPLACE(fcp.item, '^.)''(.)''$', '\1') AS "Field collection place",
STRING_AGG(DISTINCT(CASE
WHEN dim.value = 0 OR dim.value IS NULL OR dim.measurementunit
IS NULL THEN ''
WHEN mpg.measuredpart IS NULL AND dim.dimension IS NULL THEN
CAST(dim.value AS numeric) || ' ' || dim.measurementunit
WHEN mpg.measuredpart IS NULL THEN dim.dimension || ' ' ||
CAST(dim.value AS numeric) || ' ' || dim.measurementunit
WHEN dim.dimension IS NULL THEN mpg.measuredpart || '— ' ||
CAST(dim.value AS numeric) || ' ' || dim.measurementunit
ELSE mpg.measuredpart || '— ' || dim.dimension || ' ' ||
CAST(dim.value AS numeric) || ' ' || dim.measurementunit
END), '; ') AS "Dimension(s)",
STRING_AGG(DISTINCT REGEXP_REPLACE(mat.material, '^.)''(.)''$', '\1')
||CASE WHEN (mat.materialcomponent IS NOT NULL OR
mat.materialcomponentnote IS NOT NULL) THEN ' (' ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL AND
mat.materialcomponent <>'') THEN mat.materialcomponent ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL AND
mat.materialcomponentnote IS NOT NULL) THEN ', ' ELSE '' END
||CASE WHEN (mat.materialcomponentnote IS NOT NULL AND
mat.materialcomponentnote <>'') THEN mat.materialcomponentnote ELSE ''
END
||CASE WHEN (mat.materialcomponent IS NOT NULL OR
mat.materialcomponentnote IS NOT NULL) THEN ')' ELSE '' END, '; ') AS
"Material(s)",
REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1') AS
"Storage location",
REGEXP_REPLACE(ca.computedcrate, '^.)''(.)''$', '\1') AS "Box",
CASE WHEN (ca.computedcrate IS NOT NULL) THEN
REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$',
'\1') ||'—'||
REGEXP_REPLACE(ca.computedcrate, '^.)''(.)''$', '\1')
ELSE REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1')
END AS "Full location",
com.item AS "Label text",
(SELECT ccg.condition
FROM relations_common rcc
LEFT OUTER JOIN hierarchy hcc ON (hcc.name=rcc.objectcsid)
LEFT OUTER JOIN conditionchecks_common ccc ON (hcc.id=ccc.id)
LEFT OUTER JOIN misc ON (misc.id=ccc.id)
LEFT OUTER JOIN hierarchy hccg ON (hccg.parentid=ccc.id AND
hccg.name='conditionchecks_common:conditionCheckGroupList')
LEFT OUTER JOIN conditioncheckgroup ccg ON (ccg.id=hccg.id)
WHERE h1.name=rcc.subjectcsid AND
rcc.objectdocumenttype='Conditioncheck' AND
misc.lifecyclestate<>'deleted'
ORDER BY ccc.conditioncheckassessmentdate DESC
LIMIT 1) AS "Latest condition",
(SELECT ccc.conditionchecknote
FROM relations_common rcc
LEFT OUTER JOIN hierarchy hcc ON (hcc.name=rcc.objectcsid)
LEFT OUTER JOIN conditionchecks_common ccc ON (hcc.id=ccc.id)
LEFT OUTER JOIN misc ON (misc.id=ccc.id)
WHERE h1.name=rcc.subjectcsid AND
rcc.objectdocumenttype='Conditioncheck' AND
misc.lifecyclestate<>'deleted'
ORDER BY ccc.conditioncheckassessmentdate DESC
LIMIT 1) AS "Latest condition note",
(SELECT 'https://dev.cspace.berkeley.edu/pahma_project/imageserver/blobs/'||mc.blobcsid||'/derivatives/Medium/content'
FROM relations_common rcm
LEFT OUTER JOIN hierarchy hmd ON (hmd.name=rcm.objectcsid)
LEFT OUTER JOIN media_pahma mp ON (hmd.id=mp.id AND
mp.primarydisplay='true')
LEFT OUTER JOIN media_common mc ON (mp.id=mc.id)
LEFT OUTER JOIN misc ON (misc.id=mc.id)
WHERE h1.name=rcm.subjectcsid AND
rcm.objectdocumenttype='Media' AND misc.lifecyclestate<>'deleted'
LIMIT 1) AS "imagefilepath"
FROM collectionobjects_common cc
JOIN collectionobjects_pahma cp ON (cc.id=cp.id)
JOIN collectionobjects_anthropology ca ON (cc.id=ca.id)
JOIN hierarchy h1 ON (cc.id=h1.id)
JOIN relations_common rc ON (h1.name=rc.subjectcsid AND
rc.objectdocumenttype='Exhibition')
JOIN hierarchy h2 ON (rc.objectcsid=h2.name)
LEFT OUTER JOIN exhibitions_common ec ON (h2.id=ec.id)
LEFT OUTER JOIN hierarchy hn ON (cc.id=hn.parentid AND
hn.name='collectionobjects_common:objectNameList' AND (hn.pos=0 OR
hn.pos IS NULL))
LEFT OUTER JOIN objectnamegroup ong ON (ong.id=hn.id)
LEFT OUTER JOIN hierarchy hpd ON (hpd.parentid=cc.id AND
hpd.primarytype='structuredDateGroup' AND
hpd.name='collectionobjects_common:objectProductionDateGroupList' AND
(hpd.pos=0 or hpd.pos IS NULL))
LEFT OUTER JOIN hierarchy hcd ON (hcd.parentid=cc.id AND
hcd.primarytype='structuredDateGroup' AND
hcd.name='collectionobjects_pahma:pahmaFieldCollectionDateGroupList'
AND (hcd.pos=0 or hcd.pos IS NULL))
LEFT OUTER JOIN structureddategroup spd ON (spd.id=hpd.id)
LEFT OUTER JOIN structureddategroup scd ON (scd.id=hcd.id)
LEFT OUTER JOIN collectionobjects_common_fieldcollectors fc ON (fc.id=cc.id)
LEFT OUTER JOIN relations_common rca ON (h1.name=rca.subjectcsid AND
rca.objectdocumenttype='Acquisition')
LEFT OUTER JOIN hierarchy hac ON (hac.name=rca.objectcsid AND
(hac.pos=0 OR hac.pos IS NULL))
LEFT OUTER JOIN acquisitions_common ac ON (hac.id=ac.id)
LEFT OUTER JOIN acquisitions_common_owners aco ON (ac.id=aco.id)
LEFT OUTER JOIN hierarchy hc ON (hc.parentid=cc.id AND
hc.primarytype='assocPeopleGroup')
LEFT OUTER JOIN assocpeoplegroup apg ON (apg.id=hc.id)
LEFT OUTER JOIN hierarchy hmk ON (cc.id=hmk.parentid AND
hmk.name='collectionobjects_common:objectProductionPersonGroupList'
AND (hmk.pos=0 OR hmk.pos IS NULL))
LEFT OUTER JOIN objectproductionpersongroup opp ON (opp.id=hmk.id)
LEFT OUTER JOIN collectionobjects_pahma_pahmafieldcollectionplacelist
fcp ON (fcp.id=cc.id AND (fcp.pos=0 OR fcp.pos IS NULL))
LEFT OUTER JOIN places_common pc ON
(pc.shortidentifier=REGEXP_REPLACE(fcp.item,
'^.item:name((.))''.*', '\1'))
LEFT OUTER JOIN hierarchy hdm ON (cc.id=hdm.parentid AND
hdm.primarytype='measuredPartGroup')
LEFT OUTER JOIN measuredpartgroup mpg ON (mpg.id=hdm.id)
LEFT OUTER JOIN hierarchy hdm2 ON (mpg.id=hdm2.parentid AND
hdm2.primarytype='dimensionSubGroup')
LEFT OUTER JOIN dimensionsubgroup dim ON (dim.id=hdm2.id AND
dim.measurementunit <> 'pixels' AND dim.measurementunit <> 'bits')
LEFT OUTER JOIN hierarchy hm ON (hm.parentid=cc.id AND
hm.primarytype='materialGroup')
LEFT OUTER JOIN materialgroup mat ON (mat.id=hm.id)
LEFT OUTER JOIN collectionobjects_common_comments com ON (com.id=cc.id
AND (com.pos=0 OR com.pos IS NULL))
LEFT OUTER JOIN hierarchy hpp ON (hpp.parentid=cc.id AND
hpp.primarytype='objectProductionPlaceGroup')
LEFT OUTER JOIN objectproductionplacegroup oppl ON (hpp.id=oppl.id)
LEFT OUTER JOIN exhibitionobjectgroup eog ON
(eog.exhibitionobjectnumber=cc.objectnumber)
JOIN hierarchy h3 ON (h3.id=eog.id AND h3.parentid=ec.id)
WHERE h2.name = 'bd0c5831-06fa-458f-b686'
GROUP BY ec.exhibitionnumber, ec.title,
eog.exhibitionobjectsection,
eog.exhibitionobjectcase,
eog.exhibitionobjectseqnum,
eog.exhibitionobjectmount,
cc.objectnumber, ong.objectname,
opp.objectproductionperson, oppl.objectproductionplace,
spd.datedisplaydate, scd.datedisplaydate,
fcp.item, cc.computedcurrentlocation, ca.computedcrate, com.item,
cp.sortableobjectnumber, h1.name
ORDER BY eog.exhibitionobjectsection, eog.exhibitionobjectcase,
eog.exhibitionobjectseqnum, cp.sortableobjectnumber
Michael
On Fri, Sep 25, 2015 at 11:14 AM, Al Bersch abersch@museumca.org wrote:
Hello all,
We at OMCA are running into a conundrum with how to track object
checklist information in a modified Exhibition Planning procedure. We want
to be able to list Exhibited Object Information - like an object checklist,
with information on recent condition, mounting, framing, and location
within the exhibition. UC Berkeley has shared the schema for their
Exhibition Planning procedure that includes these fields, and I'm wondering
if other museums have tried to solve a similar issue. Basically, we want to
list objects and the information related to their participation in an
exhibition, but also be able to run reports on that list. As it stands, in
UCB's extension, the Object field is text - so it doesn't link up with
the object record in the database. We've thought about including exhibition
information fields in the object cataloging record itself, but it seems
sloppy practice to perform exhibition planning within the object cataloging
record, which we feel should really be preserved for object-specific info
(not exhibition-specific info).
I've attached a screenshot of the the Exhibited Object Information group,
which I believe is a UCB-produced extension to Exhibition Planning.
Has anyone faced a similar problem, and come up with an elegant solution?
Just wondering! Let me know if you need more information here -
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
[image: Inline image 1]
Talk mailing list
Talk@lists.collectionspace.org
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
--
Michael Black, Ph.D.
Head of Research and Information
Phoebe A. Hearst Museum of Anthropology
University of California, Berkeley
mtblack@berkeley.edu
Visit our DOCTOR Campaign https://crowdfund.berkeley.edu/project/805
today and join us in moving history!
Campaign runs until October 14, 2015
Give | Share | Like https://crowdfund.berkeley.edu/project/805
Hi Michael,
Thanks so much for this. Fantastic. While it's not totally ideal to rely on
people to exactly record the numbers, this would fulfill the need I
described, and we can probably handle the extra attention to detail. Great.
thanks again for all your help -
Al
On Fri, Sep 25, 2015 at 11:44 AM, Michael Black mtblack@berkeley.edu
wrote:
Hi Al,
We've done just that with a series of four Exhibition reports I've
written that I'd be happy to share with you (mostly documented in
PAHMA-1282 https://issues.collectionspace.org/browse/PAHMA-1282 and
related subtasks). The reports do depend on two conditions, but so far
that's not been a problem:
- The object number written in the text field exactly match the actual
object number - All objects in an exhibit are in the Exhibited Object Information and
are related to the Exhibition record.
The boilerplate query I based these reports on is:
SELECT ec.exhibitionnumber AS "Exh. number", ec.title AS "Exhibit title", cc.objectnumber AS "Mus. number", cp.sortableobjectnumber,
eog.exhibitionobjectsection AS "Section",
eog.exhibitionobjectcase AS "Case",
eog.exhibitionobjectseqnum AS "Seq. num.",
eog.exhibitionobjectmount AS "Mount",
ong.objectName AS "Name",
spd.datedisplaydate AS "Prod. date",
scd.datedisplaydate AS "Coll. date",
STRING_AGG(DISTINCT REGEXP_REPLACE(fc.item, '^.)''(.)''$', '\1'), '; ') AS "Collector(s)",
STRING_AGG(DISTINCT ac.acquisitionreferencenumber, '; ') AS "Acc. No.",
STRING_AGG(DISTINCT REGEXP_REPLACE(aco.item, '^.)''(.)''$', '\1'), '; ') AS "Donor(s)",
STRING_AGG(DISTINCT REGEXP_REPLACE(apg.assocpeople, '^.)''(.)''$', '\1'),'; ') AS "Culture",
REGEXP_REPLACE(opp.objectproductionperson, '^.)''(.)''$', '\1') AS "Maker",
REGEXP_REPLACE(oppl.objectproductionplace, '^.)''(.)''$', '\1') AS "Production place",
REGEXP_REPLACE(fcp.item, '^.)''(.)''$', '\1') AS "Field collection place",
STRING_AGG(DISTINCT(CASE
WHEN dim.value = 0 OR dim.value IS NULL OR dim.measurementunit IS NULL THEN ''
WHEN mpg.measuredpart IS NULL AND dim.dimension IS NULL THEN CAST(dim.value AS numeric) || ' ' || dim.measurementunit
WHEN mpg.measuredpart IS NULL THEN dim.dimension || ' ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
WHEN dim.dimension IS NULL THEN mpg.measuredpart || '— ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
ELSE mpg.measuredpart || '— ' || dim.dimension || ' ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
END), '; ') AS "Dimension(s)",
STRING_AGG(DISTINCT REGEXP_REPLACE(mat.material, '^.)''(.)''$', '\1')
||CASE WHEN (mat.materialcomponent IS NOT NULL OR mat.materialcomponentnote IS NOT NULL) THEN ' (' ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL AND mat.materialcomponent <>'') THEN mat.materialcomponent ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL AND mat.materialcomponentnote IS NOT NULL) THEN ', ' ELSE '' END
||CASE WHEN (mat.materialcomponentnote IS NOT NULL AND mat.materialcomponentnote <>'') THEN mat.materialcomponentnote ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL OR mat.materialcomponentnote IS NOT NULL) THEN ')' ELSE '' END, '; ') AS "Material(s)",
REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1') AS "Storage location",
REGEXP_REPLACE(ca.computedcrate, '^.)''(.)''$', '\1') AS "Box",
CASE WHEN (ca.computedcrate IS NOT NULL) THEN
REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1') ||'—'||
REGEXP_REPLACE(ca.computedcrate, '^.)''(.)''$', '\1')
ELSE REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1')
END AS "Full location",
com.item AS "Label text",
(SELECT ccg.condition
FROM relations_common rcc
LEFT OUTER JOIN hierarchy hcc ON (hcc.name=rcc.objectcsid)
LEFT OUTER JOIN conditionchecks_common ccc ON (hcc.id=ccc.id)
LEFT OUTER JOIN misc ON (misc.id=ccc.id)
LEFT OUTER JOIN hierarchy hccg ON (hccg.parentid=ccc.id AND hccg.name='conditionchecks_common:conditionCheckGroupList')
LEFT OUTER JOIN conditioncheckgroup ccg ON (ccg.id=hccg.id)
WHERE h1.name=rcc.subjectcsid AND rcc.objectdocumenttype='Conditioncheck' AND misc.lifecyclestate<>'deleted'
ORDER BY ccc.conditioncheckassessmentdate DESC
LIMIT 1) AS "Latest condition",
(SELECT ccc.conditionchecknote
FROM relations_common rcc
LEFT OUTER JOIN hierarchy hcc ON (hcc.name=rcc.objectcsid)
LEFT OUTER JOIN conditionchecks_common ccc ON (hcc.id=ccc.id)
LEFT OUTER JOIN misc ON (misc.id=ccc.id)
WHERE h1.name=rcc.subjectcsid AND rcc.objectdocumenttype='Conditioncheck' AND misc.lifecyclestate<>'deleted'
ORDER BY ccc.conditioncheckassessmentdate DESC
LIMIT 1) AS "Latest condition note",
(SELECT 'https://dev.cspace.berkeley.edu/pahma_project/imageserver/blobs/'||mc.blobcsid||'/derivatives/Medium/content http://dev.cspace.berkeley.edu/pahma_project/imageserver/blobs/'%7C%7Cmc.blobcsid%7C%7C'/derivatives/Medium/content'
FROM relations_common rcm
LEFT OUTER JOIN hierarchy hmd ON (hmd.name=rcm.objectcsid)
LEFT OUTER JOIN media_pahma mp ON (hmd.id=mp.id AND mp.primarydisplay='true')
LEFT OUTER JOIN media_common mc ON (mp.id=mc.id)
LEFT OUTER JOIN misc ON (misc.id=mc.id)
WHERE h1.name=rcm.subjectcsid AND rcm.objectdocumenttype='Media' AND misc.lifecyclestate<>'deleted'
LIMIT 1) AS "imagefilepath"
FROM collectionobjects_common cc
JOIN collectionobjects_pahma cp ON (cc.id=cp.id)
JOIN collectionobjects_anthropology ca ON (cc.id=ca.id)
JOIN hierarchy h1 ON (cc.id=h1.id)
JOIN relations_common rc ON (h1.name=rc.subjectcsid AND rc.objectdocumenttype='Exhibition')
JOIN hierarchy h2 ON (rc.objectcsid=h2.name)
LEFT OUTER JOIN exhibitions_common ec ON (h2.id=ec.id)
LEFT OUTER JOIN hierarchy hn ON (cc.id=hn.parentid AND hn.name='collectionobjects_common:objectNameList' AND (hn.pos=0 OR hn.pos IS NULL))
LEFT OUTER JOIN objectnamegroup ong ON (ong.id=hn.id)
LEFT OUTER JOIN hierarchy hpd ON (hpd.parentid=cc.id AND hpd.primarytype='structuredDateGroup' AND hpd.name='collectionobjects_common:objectProductionDateGroupList' AND (hpd.pos=0 or hpd.pos IS NULL))
LEFT OUTER JOIN hierarchy hcd ON (hcd.parentid=cc.id AND hcd.primarytype='structuredDateGroup' AND hcd.name='collectionobjects_pahma:pahmaFieldCollectionDateGroupList' AND (hcd.pos=0 or hcd.pos IS NULL))
LEFT OUTER JOIN structureddategroup spd ON (spd.id=hpd.id)
LEFT OUTER JOIN structureddategroup scd ON (scd.id=hcd.id)
LEFT OUTER JOIN collectionobjects_common_fieldcollectors fc ON (fc.id=cc.id)
LEFT OUTER JOIN relations_common rca ON (h1.name=rca.subjectcsid AND rca.objectdocumenttype='Acquisition')
LEFT OUTER JOIN hierarchy hac ON (hac.name=rca.objectcsid AND (hac.pos=0 OR hac.pos IS NULL))
LEFT OUTER JOIN acquisitions_common ac ON (hac.id=ac.id)
LEFT OUTER JOIN acquisitions_common_owners aco ON (ac.id=aco.id)
LEFT OUTER JOIN hierarchy hc ON (hc.parentid=cc.id AND hc.primarytype='assocPeopleGroup')
LEFT OUTER JOIN assocpeoplegroup apg ON (apg.id=hc.id)
LEFT OUTER JOIN hierarchy hmk ON (cc.id=hmk.parentid AND hmk.name='collectionobjects_common:objectProductionPersonGroupList' AND (hmk.pos=0 OR hmk.pos IS NULL))
LEFT OUTER JOIN objectproductionpersongroup opp ON (opp.id=hmk.id)
LEFT OUTER JOIN collectionobjects_pahma_pahmafieldcollectionplacelist fcp ON (fcp.id=cc.id AND (fcp.pos=0 OR fcp.pos IS NULL))
LEFT OUTER JOIN places_common pc ON (pc.shortidentifier=REGEXP_REPLACE(fcp.item, '^.item:name((.))''.*', '\1'))
LEFT OUTER JOIN hierarchy hdm ON (cc.id=hdm.parentid AND hdm.primarytype='measuredPartGroup')
LEFT OUTER JOIN measuredpartgroup mpg ON (mpg.id=hdm.id)
LEFT OUTER JOIN hierarchy hdm2 ON (mpg.id=hdm2.parentid AND hdm2.primarytype='dimensionSubGroup')
LEFT OUTER JOIN dimensionsubgroup dim ON (dim.id=hdm2.id AND dim.measurementunit <> 'pixels' AND dim.measurementunit <> 'bits')
LEFT OUTER JOIN hierarchy hm ON (hm.parentid=cc.id AND hm.primarytype='materialGroup')
LEFT OUTER JOIN materialgroup mat ON (mat.id=hm.id)
LEFT OUTER JOIN collectionobjects_common_comments com ON (com.id=cc.id AND (com.pos=0 OR com.pos IS NULL))
LEFT OUTER JOIN hierarchy hpp ON (hpp.parentid=cc.id AND hpp.primarytype='objectProductionPlaceGroup')
LEFT OUTER JOIN objectproductionplacegroup oppl ON (hpp.id=oppl.id)
LEFT OUTER JOIN exhibitionobjectgroup eog ON (eog.exhibitionobjectnumber=cc.objectnumber)
JOIN hierarchy h3 ON (h3.id=eog.id AND h3.parentid=ec.id)
WHERE h2.name = 'bd0c5831-06fa-458f-b686'
GROUP BY ec.exhibitionnumber, ec.title,
eog.exhibitionobjectsection,
eog.exhibitionobjectcase,
eog.exhibitionobjectseqnum,
eog.exhibitionobjectmount,
cc.objectnumber, ong.objectname,
opp.objectproductionperson, oppl.objectproductionplace, spd.datedisplaydate, scd.datedisplaydate,
fcp.item, cc.computedcurrentlocation, ca.computedcrate, com.item, cp.sortableobjectnumber, h1.name
ORDER BY eog.exhibitionobjectsection, eog.exhibitionobjectcase, eog.exhibitionobjectseqnum, cp.sortableobjectnumber
Michael
On Fri, Sep 25, 2015 at 11:14 AM, Al Bersch abersch@museumca.org wrote:
Hello all,
We at OMCA are running into a conundrum with how to track object
checklist information in a modified Exhibition Planning procedure. We want
to be able to list Exhibited Object Information - like an object checklist,
with information on recent condition, mounting, framing, and location
within the exhibition. UC Berkeley has shared the schema for their
Exhibition Planning procedure that includes these fields, and I'm wondering
if other museums have tried to solve a similar issue. Basically, we want to
list objects and the information related to their participation in an
exhibition, but also be able to run reports on that list. As it stands, in
UCB's extension, the Object field is text - so it doesn't link up with
the object record in the database. We've thought about including exhibition
information fields in the object cataloging record itself, but it seems
sloppy practice to perform exhibition planning within the object cataloging
record, which we feel should really be preserved for object-specific info
(not exhibition-specific info).
I've attached a screenshot of the the Exhibited Object Information group,
which I believe is a UCB-produced extension to Exhibition Planning.
Has anyone faced a similar problem, and come up with an elegant solution?
Just wondering! Let me know if you need more information here -
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
[image: Inline image 1]
Talk mailing list
Talk@lists.collectionspace.org
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
--
Michael Black, Ph.D.
Head of Research and Information
Phoebe A. Hearst Museum of Anthropology
University of California, Berkeley
mtblack@berkeley.edu
Visit our DOCTOR Campaign https://crowdfund.berkeley.edu/project/805
today and join us in moving history!
Campaign runs until October 14, 2015
Give | Share | Like https://crowdfund.berkeley.edu/project/805
--
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
While it's not totally ideal to rely on people to exactly record the
numbers ...
I don't know if this might help, but in the Hierarchy section at the bottom
of object records, there are a couple of autocomplete fields that constrain
the values that can be entered to the actual object (museum) numbers of
already-created object records.
To see these in action, on demo.collectionspace.org, log into the core
tenant, create a new Cataloging record, and enter 'IN2' into either the
'Broader object' or 'Object component' fields in the Hierarchy section.
What I don't know is whether you could redefine the Object field, in that
Exhibited Object Information repeatable group, to also be an autocomplete
field of that type. But if that's feasible, that might be one way to go ...
Another way might be to create a report that identifies any Object fields
in that group whose numbers don't match up with a valid object number in
Cataloging, run that periodically, and do any cleanup needed.
On Fri, Sep 25, 2015 at 11:54 AM, Al Bersch abersch@museumca.org wrote:
Hi Michael,
Thanks so much for this. Fantastic. While it's not totally ideal to rely
on people to exactly record the numbers, this would fulfill the need I
described, and we can probably handle the extra attention to detail. Great.
thanks again for all your help -
Al
On Fri, Sep 25, 2015 at 11:44 AM, Michael Black mtblack@berkeley.edu
wrote:
Hi Al,
We've done just that with a series of four Exhibition reports I've
written that I'd be happy to share with you (mostly documented in
PAHMA-1282 https://issues.collectionspace.org/browse/PAHMA-1282 and
related subtasks). The reports do depend on two conditions, but so far
that's not been a problem:
- The object number written in the text field exactly match the actual
object number - All objects in an exhibit are in the Exhibited Object Information
and are related to the Exhibition record.
The boilerplate query I based these reports on is:
SELECT ec.exhibitionnumber AS "Exh. number", ec.title AS "Exhibit title", cc.objectnumber AS "Mus. number", cp.sortableobjectnumber,
eog.exhibitionobjectsection AS "Section",
eog.exhibitionobjectcase AS "Case",
eog.exhibitionobjectseqnum AS "Seq. num.",
eog.exhibitionobjectmount AS "Mount",
ong.objectName AS "Name",
spd.datedisplaydate AS "Prod. date",
scd.datedisplaydate AS "Coll. date",
STRING_AGG(DISTINCT REGEXP_REPLACE(fc.item, '^.)''(.)''$', '\1'), '; ') AS "Collector(s)",
STRING_AGG(DISTINCT ac.acquisitionreferencenumber, '; ') AS "Acc. No.",
STRING_AGG(DISTINCT REGEXP_REPLACE(aco.item, '^.)''(.)''$', '\1'), '; ') AS "Donor(s)",
STRING_AGG(DISTINCT REGEXP_REPLACE(apg.assocpeople, '^.)''(.)''$', '\1'),'; ') AS "Culture",
REGEXP_REPLACE(opp.objectproductionperson, '^.)''(.)''$', '\1') AS "Maker",
REGEXP_REPLACE(oppl.objectproductionplace, '^.)''(.)''$', '\1') AS "Production place",
REGEXP_REPLACE(fcp.item, '^.)''(.)''$', '\1') AS "Field collection place",
STRING_AGG(DISTINCT(CASE
WHEN dim.value = 0 OR dim.value IS NULL OR dim.measurementunit IS NULL THEN ''
WHEN mpg.measuredpart IS NULL AND dim.dimension IS NULL THEN CAST(dim.value AS numeric) || ' ' || dim.measurementunit
WHEN mpg.measuredpart IS NULL THEN dim.dimension || ' ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
WHEN dim.dimension IS NULL THEN mpg.measuredpart || '— ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
ELSE mpg.measuredpart || '— ' || dim.dimension || ' ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
END), '; ') AS "Dimension(s)",
STRING_AGG(DISTINCT REGEXP_REPLACE(mat.material, '^.)''(.)''$', '\1')
||CASE WHEN (mat.materialcomponent IS NOT NULL OR mat.materialcomponentnote IS NOT NULL) THEN ' (' ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL AND mat.materialcomponent <>'') THEN mat.materialcomponent ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL AND mat.materialcomponentnote IS NOT NULL) THEN ', ' ELSE '' END
||CASE WHEN (mat.materialcomponentnote IS NOT NULL AND mat.materialcomponentnote <>'') THEN mat.materialcomponentnote ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL OR mat.materialcomponentnote IS NOT NULL) THEN ')' ELSE '' END, '; ') AS "Material(s)",
REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1') AS "Storage location",
REGEXP_REPLACE(ca.computedcrate, '^.)''(.)''$', '\1') AS "Box",
CASE WHEN (ca.computedcrate IS NOT NULL) THEN
REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1') ||'—'||
REGEXP_REPLACE(ca.computedcrate, '^.)''(.)''$', '\1')
ELSE REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1')
END AS "Full location",
com.item AS "Label text",
(SELECT ccg.condition
FROM relations_common rcc
LEFT OUTER JOIN hierarchy hcc ON (hcc.name=rcc.objectcsid)
LEFT OUTER JOIN conditionchecks_common ccc ON (hcc.id=ccc.id)
LEFT OUTER JOIN misc ON (misc.id=ccc.id)
LEFT OUTER JOIN hierarchy hccg ON (hccg.parentid=ccc.id AND hccg.name='conditionchecks_common:conditionCheckGroupList')
LEFT OUTER JOIN conditioncheckgroup ccg ON (ccg.id=hccg.id)
WHERE h1.name=rcc.subjectcsid AND rcc.objectdocumenttype='Conditioncheck' AND misc.lifecyclestate<>'deleted'
ORDER BY ccc.conditioncheckassessmentdate DESC
LIMIT 1) AS "Latest condition",
(SELECT ccc.conditionchecknote
FROM relations_common rcc
LEFT OUTER JOIN hierarchy hcc ON (hcc.name=rcc.objectcsid)
LEFT OUTER JOIN conditionchecks_common ccc ON (hcc.id=ccc.id)
LEFT OUTER JOIN misc ON (misc.id=ccc.id)
WHERE h1.name=rcc.subjectcsid AND rcc.objectdocumenttype='Conditioncheck' AND misc.lifecyclestate<>'deleted'
ORDER BY ccc.conditioncheckassessmentdate DESC
LIMIT 1) AS "Latest condition note",
(SELECT 'https://dev.cspace.berkeley.edu/pahma_project/imageserver/blobs/'||mc.blobcsid||'/derivatives/Medium/content http://dev.cspace.berkeley.edu/pahma_project/imageserver/blobs/'%7C%7Cmc.blobcsid%7C%7C'/derivatives/Medium/content'
FROM relations_common rcm
LEFT OUTER JOIN hierarchy hmd ON (hmd.name=rcm.objectcsid)
LEFT OUTER JOIN media_pahma mp ON (hmd.id=mp.id AND mp.primarydisplay='true')
LEFT OUTER JOIN media_common mc ON (mp.id=mc.id)
LEFT OUTER JOIN misc ON (misc.id=mc.id)
WHERE h1.name=rcm.subjectcsid AND rcm.objectdocumenttype='Media' AND misc.lifecyclestate<>'deleted'
LIMIT 1) AS "imagefilepath"
FROM collectionobjects_common cc
JOIN collectionobjects_pahma cp ON (cc.id=cp.id)
JOIN collectionobjects_anthropology ca ON (cc.id=ca.id)
JOIN hierarchy h1 ON (cc.id=h1.id)
JOIN relations_common rc ON (h1.name=rc.subjectcsid AND rc.objectdocumenttype='Exhibition')
JOIN hierarchy h2 ON (rc.objectcsid=h2.name)
LEFT OUTER JOIN exhibitions_common ec ON (h2.id=ec.id)
LEFT OUTER JOIN hierarchy hn ON (cc.id=hn.parentid AND hn.name='collectionobjects_common:objectNameList' AND (hn.pos=0 OR hn.pos IS NULL))
LEFT OUTER JOIN objectnamegroup ong ON (ong.id=hn.id)
LEFT OUTER JOIN hierarchy hpd ON (hpd.parentid=cc.id AND hpd.primarytype='structuredDateGroup' AND hpd.name='collectionobjects_common:objectProductionDateGroupList' AND (hpd.pos=0 or hpd.pos IS NULL))
LEFT OUTER JOIN hierarchy hcd ON (hcd.parentid=cc.id AND hcd.primarytype='structuredDateGroup' AND hcd.name='collectionobjects_pahma:pahmaFieldCollectionDateGroupList' AND (hcd.pos=0 or hcd.pos IS NULL))
LEFT OUTER JOIN structureddategroup spd ON (spd.id=hpd.id)
LEFT OUTER JOIN structureddategroup scd ON (scd.id=hcd.id)
LEFT OUTER JOIN collectionobjects_common_fieldcollectors fc ON (fc.id=cc.id)
LEFT OUTER JOIN relations_common rca ON (h1.name=rca.subjectcsid AND rca.objectdocumenttype='Acquisition')
LEFT OUTER JOIN hierarchy hac ON (hac.name=rca.objectcsid AND (hac.pos=0 OR hac.pos IS NULL))
LEFT OUTER JOIN acquisitions_common ac ON (hac.id=ac.id)
LEFT OUTER JOIN acquisitions_common_owners aco ON (ac.id=aco.id)
LEFT OUTER JOIN hierarchy hc ON (hc.parentid=cc.id AND hc.primarytype='assocPeopleGroup')
LEFT OUTER JOIN assocpeoplegroup apg ON (apg.id=hc.id)
LEFT OUTER JOIN hierarchy hmk ON (cc.id=hmk.parentid AND hmk.name='collectionobjects_common:objectProductionPersonGroupList' AND (hmk.pos=0 OR hmk.pos IS NULL))
LEFT OUTER JOIN objectproductionpersongroup opp ON (opp.id=hmk.id)
LEFT OUTER JOIN collectionobjects_pahma_pahmafieldcollectionplacelist fcp ON (fcp.id=cc.id AND (fcp.pos=0 OR fcp.pos IS NULL))
LEFT OUTER JOIN places_common pc ON (pc.shortidentifier=REGEXP_REPLACE(fcp.item, '^.item:name((.))''.*', '\1'))
LEFT OUTER JOIN hierarchy hdm ON (cc.id=hdm.parentid AND hdm.primarytype='measuredPartGroup')
LEFT OUTER JOIN measuredpartgroup mpg ON (mpg.id=hdm.id)
LEFT OUTER JOIN hierarchy hdm2 ON (mpg.id=hdm2.parentid AND hdm2.primarytype='dimensionSubGroup')
LEFT OUTER JOIN dimensionsubgroup dim ON (dim.id=hdm2.id AND dim.measurementunit <> 'pixels' AND dim.measurementunit <> 'bits')
LEFT OUTER JOIN hierarchy hm ON (hm.parentid=cc.id AND hm.primarytype='materialGroup')
LEFT OUTER JOIN materialgroup mat ON (mat.id=hm.id)
LEFT OUTER JOIN collectionobjects_common_comments com ON (com.id=cc.id AND (com.pos=0 OR com.pos IS NULL))
LEFT OUTER JOIN hierarchy hpp ON (hpp.parentid=cc.id AND hpp.primarytype='objectProductionPlaceGroup')
LEFT OUTER JOIN objectproductionplacegroup oppl ON (hpp.id=oppl.id)
LEFT OUTER JOIN exhibitionobjectgroup eog ON (eog.exhibitionobjectnumber=cc.objectnumber)
JOIN hierarchy h3 ON (h3.id=eog.id AND h3.parentid=ec.id)
WHERE h2.name = 'bd0c5831-06fa-458f-b686'
GROUP BY ec.exhibitionnumber, ec.title,
eog.exhibitionobjectsection,
eog.exhibitionobjectcase,
eog.exhibitionobjectseqnum,
eog.exhibitionobjectmount,
cc.objectnumber, ong.objectname,
opp.objectproductionperson, oppl.objectproductionplace, spd.datedisplaydate, scd.datedisplaydate,
fcp.item, cc.computedcurrentlocation, ca.computedcrate, com.item, cp.sortableobjectnumber, h1.name
ORDER BY eog.exhibitionobjectsection, eog.exhibitionobjectcase, eog.exhibitionobjectseqnum, cp.sortableobjectnumber
Michael
On Fri, Sep 25, 2015 at 11:14 AM, Al Bersch abersch@museumca.org wrote:
Hello all,
We at OMCA are running into a conundrum with how to track object
checklist information in a modified Exhibition Planning procedure. We want
to be able to list Exhibited Object Information - like an object checklist,
with information on recent condition, mounting, framing, and location
within the exhibition. UC Berkeley has shared the schema for their
Exhibition Planning procedure that includes these fields, and I'm wondering
if other museums have tried to solve a similar issue. Basically, we want to
list objects and the information related to their participation in an
exhibition, but also be able to run reports on that list. As it stands, in
UCB's extension, the Object field is text - so it doesn't link up with
the object record in the database. We've thought about including exhibition
information fields in the object cataloging record itself, but it seems
sloppy practice to perform exhibition planning within the object cataloging
record, which we feel should really be preserved for object-specific info
(not exhibition-specific info).
I've attached a screenshot of the the Exhibited Object Information
group, which I believe is a UCB-produced extension to Exhibition Planning.
Has anyone faced a similar problem, and come up with an elegant
solution? Just wondering! Let me know if you need more information here -
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
[image: Inline image 1]
Talk mailing list
Talk@lists.collectionspace.org
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
--
Michael Black, Ph.D.
Head of Research and Information
Phoebe A. Hearst Museum of Anthropology
University of California, Berkeley
mtblack@berkeley.edu
Visit our DOCTOR Campaign https://crowdfund.berkeley.edu/project/805
today and join us in moving history!
Campaign runs until October 14, 2015
Give | Share | Like https://crowdfund.berkeley.edu/project/805
--
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
Talk mailing list
Talk@lists.collectionspace.org
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
Hi Al,
The Exhbited Object Information section, among others, was apparently
supposed to go into the core system, and I forgot them when contributing
the exhibition procedure. On the v4.2 branch, all of those fields now
exist, so you can pull them in if you like. I'm working on getting them
into master. These were the pull requests to v4.2:
https://github.com/collectionspace/application/pull/150
https://github.com/collectionspace/ui/pull/270
I hope to look at ways of making this work better at some point. The
initial idea is exactly what Aron proposed, which is to make the object
number fields autocomplete. Another option would be to make the object
number fields read-only, and have rows in the table generated automatically
from (and kept in sync with) the related cataloging records.
Ray
On Fri, Sep 25, 2015 at 12:13 PM, Aron Roberts aron@socrates.berkeley.edu
wrote:
While it's not totally ideal to rely on people to exactly record the
numbers ...
I don't know if this might help, but in the Hierarchy section at the
bottom of object records, there are a couple of autocomplete fields that
constrain the values that can be entered to the actual object (museum)
numbers of already-created object records.
To see these in action, on demo.collectionspace.org, log into the core
tenant, create a new Cataloging record, and enter 'IN2' into either the
'Broader object' or 'Object component' fields in the Hierarchy section.
What I don't know is whether you could redefine the Object field, in that
Exhibited Object Information repeatable group, to also be an autocomplete
field of that type. But if that's feasible, that might be one way to go ...
Another way might be to create a report that identifies any Object fields
in that group whose numbers don't match up with a valid object number in
Cataloging, run that periodically, and do any cleanup needed.
On Fri, Sep 25, 2015 at 11:54 AM, Al Bersch abersch@museumca.org wrote:
Hi Michael,
Thanks so much for this. Fantastic. While it's not totally ideal to rely
on people to exactly record the numbers, this would fulfill the need I
described, and we can probably handle the extra attention to detail. Great.
thanks again for all your help -
Al
On Fri, Sep 25, 2015 at 11:44 AM, Michael Black mtblack@berkeley.edu
wrote:
Hi Al,
We've done just that with a series of four Exhibition reports I've
written that I'd be happy to share with you (mostly documented in
PAHMA-1282 https://issues.collectionspace.org/browse/PAHMA-1282 and
related subtasks). The reports do depend on two conditions, but so far
that's not been a problem:
- The object number written in the text field exactly match the
actual object number - All objects in an exhibit are in the Exhibited Object Information
and are related to the Exhibition record.
The boilerplate query I based these reports on is:
SELECT ec.exhibitionnumber AS "Exh. number", ec.title AS "Exhibit title", cc.objectnumber AS "Mus. number", cp.sortableobjectnumber,
eog.exhibitionobjectsection AS "Section",
eog.exhibitionobjectcase AS "Case",
eog.exhibitionobjectseqnum AS "Seq. num.",
eog.exhibitionobjectmount AS "Mount",
ong.objectName AS "Name",
spd.datedisplaydate AS "Prod. date",
scd.datedisplaydate AS "Coll. date",
STRING_AGG(DISTINCT REGEXP_REPLACE(fc.item, '^.)''(.)''$', '\1'), '; ') AS "Collector(s)",
STRING_AGG(DISTINCT ac.acquisitionreferencenumber, '; ') AS "Acc. No.",
STRING_AGG(DISTINCT REGEXP_REPLACE(aco.item, '^.)''(.)''$', '\1'), '; ') AS "Donor(s)",
STRING_AGG(DISTINCT REGEXP_REPLACE(apg.assocpeople, '^.)''(.)''$', '\1'),'; ') AS "Culture",
REGEXP_REPLACE(opp.objectproductionperson, '^.)''(.)''$', '\1') AS "Maker",
REGEXP_REPLACE(oppl.objectproductionplace, '^.)''(.)''$', '\1') AS "Production place",
REGEXP_REPLACE(fcp.item, '^.)''(.)''$', '\1') AS "Field collection place",
STRING_AGG(DISTINCT(CASE
WHEN dim.value = 0 OR dim.value IS NULL OR dim.measurementunit IS NULL THEN ''
WHEN mpg.measuredpart IS NULL AND dim.dimension IS NULL THEN CAST(dim.value AS numeric) || ' ' || dim.measurementunit
WHEN mpg.measuredpart IS NULL THEN dim.dimension || ' ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
WHEN dim.dimension IS NULL THEN mpg.measuredpart || '— ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
ELSE mpg.measuredpart || '— ' || dim.dimension || ' ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
END), '; ') AS "Dimension(s)",
STRING_AGG(DISTINCT REGEXP_REPLACE(mat.material, '^.)''(.)''$', '\1')
||CASE WHEN (mat.materialcomponent IS NOT NULL OR mat.materialcomponentnote IS NOT NULL) THEN ' (' ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL AND mat.materialcomponent <>'') THEN mat.materialcomponent ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL AND mat.materialcomponentnote IS NOT NULL) THEN ', ' ELSE '' END
||CASE WHEN (mat.materialcomponentnote IS NOT NULL AND mat.materialcomponentnote <>'') THEN mat.materialcomponentnote ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL OR mat.materialcomponentnote IS NOT NULL) THEN ')' ELSE '' END, '; ') AS "Material(s)",
REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1') AS "Storage location",
REGEXP_REPLACE(ca.computedcrate, '^.)''(.)''$', '\1') AS "Box",
CASE WHEN (ca.computedcrate IS NOT NULL) THEN
REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1') ||'—'||
REGEXP_REPLACE(ca.computedcrate, '^.)''(.)''$', '\1')
ELSE REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1')
END AS "Full location",
com.item AS "Label text",
(SELECT ccg.condition
FROM relations_common rcc
LEFT OUTER JOIN hierarchy hcc ON (hcc.name=rcc.objectcsid)
LEFT OUTER JOIN conditionchecks_common ccc ON (hcc.id=ccc.id)
LEFT OUTER JOIN misc ON (misc.id=ccc.id)
LEFT OUTER JOIN hierarchy hccg ON (hccg.parentid=ccc.id AND hccg.name='conditionchecks_common:conditionCheckGroupList')
LEFT OUTER JOIN conditioncheckgroup ccg ON (ccg.id=hccg.id)
WHERE h1.name=rcc.subjectcsid AND rcc.objectdocumenttype='Conditioncheck' AND misc.lifecyclestate<>'deleted'
ORDER BY ccc.conditioncheckassessmentdate DESC
LIMIT 1) AS "Latest condition",
(SELECT ccc.conditionchecknote
FROM relations_common rcc
LEFT OUTER JOIN hierarchy hcc ON (hcc.name=rcc.objectcsid)
LEFT OUTER JOIN conditionchecks_common ccc ON (hcc.id=ccc.id)
LEFT OUTER JOIN misc ON (misc.id=ccc.id)
WHERE h1.name=rcc.subjectcsid AND rcc.objectdocumenttype='Conditioncheck' AND misc.lifecyclestate<>'deleted'
ORDER BY ccc.conditioncheckassessmentdate DESC
LIMIT 1) AS "Latest condition note",
(SELECT 'https://dev.cspace.berkeley.edu/pahma_project/imageserver/blobs/'||mc.blobcsid||'/derivatives/Medium/content http://dev.cspace.berkeley.edu/pahma_project/imageserver/blobs/'%7C%7Cmc.blobcsid%7C%7C'/derivatives/Medium/content'
FROM relations_common rcm
LEFT OUTER JOIN hierarchy hmd ON (hmd.name=rcm.objectcsid)
LEFT OUTER JOIN media_pahma mp ON (hmd.id=mp.id AND mp.primarydisplay='true')
LEFT OUTER JOIN media_common mc ON (mp.id=mc.id)
LEFT OUTER JOIN misc ON (misc.id=mc.id)
WHERE h1.name=rcm.subjectcsid AND rcm.objectdocumenttype='Media' AND misc.lifecyclestate<>'deleted'
LIMIT 1) AS "imagefilepath"
FROM collectionobjects_common cc
JOIN collectionobjects_pahma cp ON (cc.id=cp.id)
JOIN collectionobjects_anthropology ca ON (cc.id=ca.id)
JOIN hierarchy h1 ON (cc.id=h1.id)
JOIN relations_common rc ON (h1.name=rc.subjectcsid AND rc.objectdocumenttype='Exhibition')
JOIN hierarchy h2 ON (rc.objectcsid=h2.name)
LEFT OUTER JOIN exhibitions_common ec ON (h2.id=ec.id)
LEFT OUTER JOIN hierarchy hn ON (cc.id=hn.parentid AND hn.name='collectionobjects_common:objectNameList' AND (hn.pos=0 OR hn.pos IS NULL))
LEFT OUTER JOIN objectnamegroup ong ON (ong.id=hn.id)
LEFT OUTER JOIN hierarchy hpd ON (hpd.parentid=cc.id AND hpd.primarytype='structuredDateGroup' AND hpd.name='collectionobjects_common:objectProductionDateGroupList' AND (hpd.pos=0 or hpd.pos IS NULL))
LEFT OUTER JOIN hierarchy hcd ON (hcd.parentid=cc.id AND hcd.primarytype='structuredDateGroup' AND hcd.name='collectionobjects_pahma:pahmaFieldCollectionDateGroupList' AND (hcd.pos=0 or hcd.pos IS NULL))
LEFT OUTER JOIN structureddategroup spd ON (spd.id=hpd.id)
LEFT OUTER JOIN structureddategroup scd ON (scd.id=hcd.id)
LEFT OUTER JOIN collectionobjects_common_fieldcollectors fc ON (fc.id=cc.id)
LEFT OUTER JOIN relations_common rca ON (h1.name=rca.subjectcsid AND rca.objectdocumenttype='Acquisition')
LEFT OUTER JOIN hierarchy hac ON (hac.name=rca.objectcsid AND (hac.pos=0 OR hac.pos IS NULL))
LEFT OUTER JOIN acquisitions_common ac ON (hac.id=ac.id)
LEFT OUTER JOIN acquisitions_common_owners aco ON (ac.id=aco.id)
LEFT OUTER JOIN hierarchy hc ON (hc.parentid=cc.id AND hc.primarytype='assocPeopleGroup')
LEFT OUTER JOIN assocpeoplegroup apg ON (apg.id=hc.id)
LEFT OUTER JOIN hierarchy hmk ON (cc.id=hmk.parentid AND hmk.name='collectionobjects_common:objectProductionPersonGroupList' AND (hmk.pos=0 OR hmk.pos IS NULL))
LEFT OUTER JOIN objectproductionpersongroup opp ON (opp.id=hmk.id)
LEFT OUTER JOIN collectionobjects_pahma_pahmafieldcollectionplacelist fcp ON (fcp.id=cc.id AND (fcp.pos=0 OR fcp.pos IS NULL))
LEFT OUTER JOIN places_common pc ON (pc.shortidentifier=REGEXP_REPLACE(fcp.item, '^.item:name((.))''.*', '\1'))
LEFT OUTER JOIN hierarchy hdm ON (cc.id=hdm.parentid AND hdm.primarytype='measuredPartGroup')
LEFT OUTER JOIN measuredpartgroup mpg ON (mpg.id=hdm.id)
LEFT OUTER JOIN hierarchy hdm2 ON (mpg.id=hdm2.parentid AND hdm2.primarytype='dimensionSubGroup')
LEFT OUTER JOIN dimensionsubgroup dim ON (dim.id=hdm2.id AND dim.measurementunit <> 'pixels' AND dim.measurementunit <> 'bits')
LEFT OUTER JOIN hierarchy hm ON (hm.parentid=cc.id AND hm.primarytype='materialGroup')
LEFT OUTER JOIN materialgroup mat ON (mat.id=hm.id)
LEFT OUTER JOIN collectionobjects_common_comments com ON (com.id=cc.id AND (com.pos=0 OR com.pos IS NULL))
LEFT OUTER JOIN hierarchy hpp ON (hpp.parentid=cc.id AND hpp.primarytype='objectProductionPlaceGroup')
LEFT OUTER JOIN objectproductionplacegroup oppl ON (hpp.id=oppl.id)
LEFT OUTER JOIN exhibitionobjectgroup eog ON (eog.exhibitionobjectnumber=cc.objectnumber)
JOIN hierarchy h3 ON (h3.id=eog.id AND h3.parentid=ec.id)
WHERE h2.name = 'bd0c5831-06fa-458f-b686'
GROUP BY ec.exhibitionnumber, ec.title,
eog.exhibitionobjectsection,
eog.exhibitionobjectcase,
eog.exhibitionobjectseqnum,
eog.exhibitionobjectmount,
cc.objectnumber, ong.objectname,
opp.objectproductionperson, oppl.objectproductionplace, spd.datedisplaydate, scd.datedisplaydate,
fcp.item, cc.computedcurrentlocation, ca.computedcrate, com.item, cp.sortableobjectnumber, h1.name
ORDER BY eog.exhibitionobjectsection, eog.exhibitionobjectcase, eog.exhibitionobjectseqnum, cp.sortableobjectnumber
Michael
On Fri, Sep 25, 2015 at 11:14 AM, Al Bersch abersch@museumca.org
wrote:
Hello all,
We at OMCA are running into a conundrum with how to track object
checklist information in a modified Exhibition Planning procedure. We want
to be able to list Exhibited Object Information - like an object checklist,
with information on recent condition, mounting, framing, and location
within the exhibition. UC Berkeley has shared the schema for their
Exhibition Planning procedure that includes these fields, and I'm wondering
if other museums have tried to solve a similar issue. Basically, we want to
list objects and the information related to their participation in an
exhibition, but also be able to run reports on that list. As it stands, in
UCB's extension, the Object field is text - so it doesn't link up with
the object record in the database. We've thought about including exhibition
information fields in the object cataloging record itself, but it seems
sloppy practice to perform exhibition planning within the object cataloging
record, which we feel should really be preserved for object-specific info
(not exhibition-specific info).
I've attached a screenshot of the the Exhibited Object Information
group, which I believe is a UCB-produced extension to Exhibition Planning.
Has anyone faced a similar problem, and come up with an elegant
solution? Just wondering! Let me know if you need more information here -
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
[image: Inline image 1]
Talk mailing list
Talk@lists.collectionspace.org
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
--
Michael Black, Ph.D.
Head of Research and Information
Phoebe A. Hearst Museum of Anthropology
University of California, Berkeley
mtblack@berkeley.edu
Visit our DOCTOR Campaign https://crowdfund.berkeley.edu/project/805
today and join us in moving history!
Campaign runs until October 14, 2015
Give | Share | Like https://crowdfund.berkeley.edu/project/805
--
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
Talk mailing list
Talk@lists.collectionspace.org
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
Thanks so much Ray and Aron. Wow - yes, the ideal situation would be to
make the object number fields autocomplete - however, having the rows in
the table autogenearte automatically with the related catalgoing records
would also work very well. It seems worth looking into - and Ray if you are
thinking abou tlooking at this section down the line, perhaps there's some
way OMCA could contribute to that work. For now, it's good to know the
Exhibited Object Information section are now included! Thanks for the
links.
thanks again -
Al
On Fri, Sep 25, 2015 at 12:35 PM, Ray Lee rhlee@berkeley.edu wrote:
Hi Al,
The Exhbited Object Information section, among others, was apparently
supposed to go into the core system, and I forgot them when contributing
the exhibition procedure. On the v4.2 branch, all of those fields now
exist, so you can pull them in if you like. I'm working on getting them
into master. These were the pull requests to v4.2:
https://github.com/collectionspace/application/pull/150
https://github.com/collectionspace/ui/pull/270
I hope to look at ways of making this work better at some point. The
initial idea is exactly what Aron proposed, which is to make the object
number fields autocomplete. Another option would be to make the object
number fields read-only, and have rows in the table generated automatically
from (and kept in sync with) the related cataloging records.
Ray
On Fri, Sep 25, 2015 at 12:13 PM, Aron Roberts <aron@socrates.berkeley.edu
wrote:
While it's not totally ideal to rely on people to exactly record the
numbers ...
I don't know if this might help, but in the Hierarchy section at the
bottom of object records, there are a couple of autocomplete fields that
constrain the values that can be entered to the actual object (museum)
numbers of already-created object records.
To see these in action, on demo.collectionspace.org, log into the core
tenant, create a new Cataloging record, and enter 'IN2' into either the
'Broader object' or 'Object component' fields in the Hierarchy section.
What I don't know is whether you could redefine the Object field, in that
Exhibited Object Information repeatable group, to also be an autocomplete
field of that type. But if that's feasible, that might be one way to go ...
Another way might be to create a report that identifies any Object fields
in that group whose numbers don't match up with a valid object number in
Cataloging, run that periodically, and do any cleanup needed.
On Fri, Sep 25, 2015 at 11:54 AM, Al Bersch abersch@museumca.org wrote:
Hi Michael,
Thanks so much for this. Fantastic. While it's not totally ideal to rely
on people to exactly record the numbers, this would fulfill the need I
described, and we can probably handle the extra attention to detail. Great.
thanks again for all your help -
Al
On Fri, Sep 25, 2015 at 11:44 AM, Michael Black mtblack@berkeley.edu
wrote:
Hi Al,
We've done just that with a series of four Exhibition reports I've
written that I'd be happy to share with you (mostly documented in
PAHMA-1282 https://issues.collectionspace.org/browse/PAHMA-1282 and
related subtasks). The reports do depend on two conditions, but so far
that's not been a problem:
- The object number written in the text field exactly match the
actual object number - All objects in an exhibit are in the Exhibited Object Information
and are related to the Exhibition record.
The boilerplate query I based these reports on is:
SELECT ec.exhibitionnumber AS "Exh. number", ec.title AS "Exhibit title", cc.objectnumber AS "Mus. number", cp.sortableobjectnumber,
eog.exhibitionobjectsection AS "Section",
eog.exhibitionobjectcase AS "Case",
eog.exhibitionobjectseqnum AS "Seq. num.",
eog.exhibitionobjectmount AS "Mount",
ong.objectName AS "Name",
spd.datedisplaydate AS "Prod. date",
scd.datedisplaydate AS "Coll. date",
STRING_AGG(DISTINCT REGEXP_REPLACE(fc.item, '^.)''(.)''$', '\1'), '; ') AS "Collector(s)",
STRING_AGG(DISTINCT ac.acquisitionreferencenumber, '; ') AS "Acc. No.",
STRING_AGG(DISTINCT REGEXP_REPLACE(aco.item, '^.)''(.)''$', '\1'), '; ') AS "Donor(s)",
STRING_AGG(DISTINCT REGEXP_REPLACE(apg.assocpeople, '^.)''(.)''$', '\1'),'; ') AS "Culture",
REGEXP_REPLACE(opp.objectproductionperson, '^.)''(.)''$', '\1') AS "Maker",
REGEXP_REPLACE(oppl.objectproductionplace, '^.)''(.)''$', '\1') AS "Production place",
REGEXP_REPLACE(fcp.item, '^.)''(.)''$', '\1') AS "Field collection place",
STRING_AGG(DISTINCT(CASE
WHEN dim.value = 0 OR dim.value IS NULL OR dim.measurementunit IS NULL THEN ''
WHEN mpg.measuredpart IS NULL AND dim.dimension IS NULL THEN CAST(dim.value AS numeric) || ' ' || dim.measurementunit
WHEN mpg.measuredpart IS NULL THEN dim.dimension || ' ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
WHEN dim.dimension IS NULL THEN mpg.measuredpart || '— ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
ELSE mpg.measuredpart || '— ' || dim.dimension || ' ' || CAST(dim.value AS numeric) || ' ' || dim.measurementunit
END), '; ') AS "Dimension(s)",
STRING_AGG(DISTINCT REGEXP_REPLACE(mat.material, '^.)''(.)''$', '\1')
||CASE WHEN (mat.materialcomponent IS NOT NULL OR mat.materialcomponentnote IS NOT NULL) THEN ' (' ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL AND mat.materialcomponent <>'') THEN mat.materialcomponent ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL AND mat.materialcomponentnote IS NOT NULL) THEN ', ' ELSE '' END
||CASE WHEN (mat.materialcomponentnote IS NOT NULL AND mat.materialcomponentnote <>'') THEN mat.materialcomponentnote ELSE '' END
||CASE WHEN (mat.materialcomponent IS NOT NULL OR mat.materialcomponentnote IS NOT NULL) THEN ')' ELSE '' END, '; ') AS "Material(s)",
REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1') AS "Storage location",
REGEXP_REPLACE(ca.computedcrate, '^.)''(.)''$', '\1') AS "Box",
CASE WHEN (ca.computedcrate IS NOT NULL) THEN
REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1') ||'—'||
REGEXP_REPLACE(ca.computedcrate, '^.)''(.)''$', '\1')
ELSE REGEXP_REPLACE(cc.computedcurrentlocation, '^.)''(.)''$', '\1')
END AS "Full location",
com.item AS "Label text",
(SELECT ccg.condition
FROM relations_common rcc
LEFT OUTER JOIN hierarchy hcc ON (hcc.name=rcc.objectcsid)
LEFT OUTER JOIN conditionchecks_common ccc ON (hcc.id=ccc.id)
LEFT OUTER JOIN misc ON (misc.id=ccc.id)
LEFT OUTER JOIN hierarchy hccg ON (hccg.parentid=ccc.id AND hccg.name='conditionchecks_common:conditionCheckGroupList')
LEFT OUTER JOIN conditioncheckgroup ccg ON (ccg.id=hccg.id)
WHERE h1.name=rcc.subjectcsid AND rcc.objectdocumenttype='Conditioncheck' AND misc.lifecyclestate<>'deleted'
ORDER BY ccc.conditioncheckassessmentdate DESC
LIMIT 1) AS "Latest condition",
(SELECT ccc.conditionchecknote
FROM relations_common rcc
LEFT OUTER JOIN hierarchy hcc ON (hcc.name=rcc.objectcsid)
LEFT OUTER JOIN conditionchecks_common ccc ON (hcc.id=ccc.id)
LEFT OUTER JOIN misc ON (misc.id=ccc.id)
WHERE h1.name=rcc.subjectcsid AND rcc.objectdocumenttype='Conditioncheck' AND misc.lifecyclestate<>'deleted'
ORDER BY ccc.conditioncheckassessmentdate DESC
LIMIT 1) AS "Latest condition note",
(SELECT 'https://dev.cspace.berkeley.edu/pahma_project/imageserver/blobs/'||mc.blobcsid||'/derivatives/Medium/content http://dev.cspace.berkeley.edu/pahma_project/imageserver/blobs/'%7C%7Cmc.blobcsid%7C%7C'/derivatives/Medium/content'
FROM relations_common rcm
LEFT OUTER JOIN hierarchy hmd ON (hmd.name=rcm.objectcsid)
LEFT OUTER JOIN media_pahma mp ON (hmd.id=mp.id AND mp.primarydisplay='true')
LEFT OUTER JOIN media_common mc ON (mp.id=mc.id)
LEFT OUTER JOIN misc ON (misc.id=mc.id)
WHERE h1.name=rcm.subjectcsid AND rcm.objectdocumenttype='Media' AND misc.lifecyclestate<>'deleted'
LIMIT 1) AS "imagefilepath"
FROM collectionobjects_common cc
JOIN collectionobjects_pahma cp ON (cc.id=cp.id)
JOIN collectionobjects_anthropology ca ON (cc.id=ca.id)
JOIN hierarchy h1 ON (cc.id=h1.id)
JOIN relations_common rc ON (h1.name=rc.subjectcsid AND rc.objectdocumenttype='Exhibition')
JOIN hierarchy h2 ON (rc.objectcsid=h2.name)
LEFT OUTER JOIN exhibitions_common ec ON (h2.id=ec.id)
LEFT OUTER JOIN hierarchy hn ON (cc.id=hn.parentid AND hn.name='collectionobjects_common:objectNameList' AND (hn.pos=0 OR hn.pos IS NULL))
LEFT OUTER JOIN objectnamegroup ong ON (ong.id=hn.id)
LEFT OUTER JOIN hierarchy hpd ON (hpd.parentid=cc.id AND hpd.primarytype='structuredDateGroup' AND hpd.name='collectionobjects_common:objectProductionDateGroupList' AND (hpd.pos=0 or hpd.pos IS NULL))
LEFT OUTER JOIN hierarchy hcd ON (hcd.parentid=cc.id AND hcd.primarytype='structuredDateGroup' AND hcd.name='collectionobjects_pahma:pahmaFieldCollectionDateGroupList' AND (hcd.pos=0 or hcd.pos IS NULL))
LEFT OUTER JOIN structureddategroup spd ON (spd.id=hpd.id)
LEFT OUTER JOIN structureddategroup scd ON (scd.id=hcd.id)
LEFT OUTER JOIN collectionobjects_common_fieldcollectors fc ON (fc.id=cc.id)
LEFT OUTER JOIN relations_common rca ON (h1.name=rca.subjectcsid AND rca.objectdocumenttype='Acquisition')
LEFT OUTER JOIN hierarchy hac ON (hac.name=rca.objectcsid AND (hac.pos=0 OR hac.pos IS NULL))
LEFT OUTER JOIN acquisitions_common ac ON (hac.id=ac.id)
LEFT OUTER JOIN acquisitions_common_owners aco ON (ac.id=aco.id)
LEFT OUTER JOIN hierarchy hc ON (hc.parentid=cc.id AND hc.primarytype='assocPeopleGroup')
LEFT OUTER JOIN assocpeoplegroup apg ON (apg.id=hc.id)
LEFT OUTER JOIN hierarchy hmk ON (cc.id=hmk.parentid AND hmk.name='collectionobjects_common:objectProductionPersonGroupList' AND (hmk.pos=0 OR hmk.pos IS NULL))
LEFT OUTER JOIN objectproductionpersongroup opp ON (opp.id=hmk.id)
LEFT OUTER JOIN collectionobjects_pahma_pahmafieldcollectionplacelist fcp ON (fcp.id=cc.id AND (fcp.pos=0 OR fcp.pos IS NULL))
LEFT OUTER JOIN places_common pc ON (pc.shortidentifier=REGEXP_REPLACE(fcp.item, '^.item:name((.))''.*', '\1'))
LEFT OUTER JOIN hierarchy hdm ON (cc.id=hdm.parentid AND hdm.primarytype='measuredPartGroup')
LEFT OUTER JOIN measuredpartgroup mpg ON (mpg.id=hdm.id)
LEFT OUTER JOIN hierarchy hdm2 ON (mpg.id=hdm2.parentid AND hdm2.primarytype='dimensionSubGroup')
LEFT OUTER JOIN dimensionsubgroup dim ON (dim.id=hdm2.id AND dim.measurementunit <> 'pixels' AND dim.measurementunit <> 'bits')
LEFT OUTER JOIN hierarchy hm ON (hm.parentid=cc.id AND hm.primarytype='materialGroup')
LEFT OUTER JOIN materialgroup mat ON (mat.id=hm.id)
LEFT OUTER JOIN collectionobjects_common_comments com ON (com.id=cc.id AND (com.pos=0 OR com.pos IS NULL))
LEFT OUTER JOIN hierarchy hpp ON (hpp.parentid=cc.id AND hpp.primarytype='objectProductionPlaceGroup')
LEFT OUTER JOIN objectproductionplacegroup oppl ON (hpp.id=oppl.id)
LEFT OUTER JOIN exhibitionobjectgroup eog ON (eog.exhibitionobjectnumber=cc.objectnumber)
JOIN hierarchy h3 ON (h3.id=eog.id AND h3.parentid=ec.id)
WHERE h2.name = 'bd0c5831-06fa-458f-b686'
GROUP BY ec.exhibitionnumber, ec.title,
eog.exhibitionobjectsection,
eog.exhibitionobjectcase,
eog.exhibitionobjectseqnum,
eog.exhibitionobjectmount,
cc.objectnumber, ong.objectname,
opp.objectproductionperson, oppl.objectproductionplace, spd.datedisplaydate, scd.datedisplaydate,
fcp.item, cc.computedcurrentlocation, ca.computedcrate, com.item, cp.sortableobjectnumber, h1.name
ORDER BY eog.exhibitionobjectsection, eog.exhibitionobjectcase, eog.exhibitionobjectseqnum, cp.sortableobjectnumber
Michael
On Fri, Sep 25, 2015 at 11:14 AM, Al Bersch abersch@museumca.org
wrote:
Hello all,
We at OMCA are running into a conundrum with how to track object
checklist information in a modified Exhibition Planning procedure. We want
to be able to list Exhibited Object Information - like an object checklist,
with information on recent condition, mounting, framing, and location
within the exhibition. UC Berkeley has shared the schema for their
Exhibition Planning procedure that includes these fields, and I'm wondering
if other museums have tried to solve a similar issue. Basically, we want to
list objects and the information related to their participation in an
exhibition, but also be able to run reports on that list. As it stands, in
UCB's extension, the Object field is text - so it doesn't link up
with the object record in the database. We've thought about including
exhibition information fields in the object cataloging record itself, but
it seems sloppy practice to perform exhibition planning within the object
cataloging record, which we feel should really be preserved for
object-specific info (not exhibition-specific info).
I've attached a screenshot of the the Exhibited Object Information
group, which I believe is a UCB-produced extension to Exhibition Planning.
Has anyone faced a similar problem, and come up with an elegant
solution? Just wondering! Let me know if you need more information here -
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
[image: Inline image 1]
Talk mailing list
Talk@lists.collectionspace.org
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
--
Michael Black, Ph.D.
Head of Research and Information
Phoebe A. Hearst Museum of Anthropology
University of California, Berkeley
mtblack@berkeley.edu
Visit our DOCTOR Campaign
https://crowdfund.berkeley.edu/project/805 today and join us in
moving history!
Campaign runs until October 14, 2015
Give | Share | Like https://crowdfund.berkeley.edu/project/805
--
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468
Talk mailing list
Talk@lists.collectionspace.org
http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org
--
Al Bersch
Collections Systems Manager
Oakland Museum of California
1000 Oak Street, Oakland, CA 94607
abersch@museumca.org
510-318-8468