Finding Accessions Related Physical Object

[Tom Misilo]

Hello,

We are trying to send some data for our physical objects to our high density storage system. The problem I am having is finding the accession records that have physical objects.

I started with the slug from the physical object record

mysql> select * from slug where slug = 'box-1-a83412035813';
+-----------+--------------------+--------+---------------+
| object_id | slug               | id     | serial_number |
+-----------+--------------------+--------+---------------+
|    129271 | box-1-a83412035813 | 150841 |             0 |
+-----------+--------------------+--------+---------------+
1 row in set (0.00 sec)

Used that to find the physical object record

mysql> select * from physical_object_i18n where name like '%A83412154332%';
+---------------------+-------------+----------+--------+---------+
| name                | description | location | id     | culture |
+---------------------+-------------+----------+--------+---------+
| Box 1, A83412154332 | NULL        | Annex    | 129271 | en      |
+---------------------+-------------+----------+--------+---------+
1 row in set (0.03 sec)

and can find the same record in the object table

mysql> select * from object where id = '129271';
+---------------------+---------------------+---------------------+--------+---------------+
| class_name          | created_at          | updated_at          | id     | serial_number |
+---------------------+---------------------+---------------------+--------+---------------+
| QubitPhysicalObject | 2022-01-31 10:57:11 | 2022-08-31 15:01:50 | 129271 |             0 |
+---------------------+---------------------+---------------------+--------+---------------+
1 row in set (0.00 sec)

However, I can't find a relation that gets me to the accession record

mysql> select * from accession_i18n WHERE title LIKE 'Papers'\G;
*************************** 1. row ***************************
               appraisal: 18
        archival_history: NULL
    location_information: Pre-fire 2018: 1 Box, 1.25 Cubic Feet; Stack G:R5/S19/Sf5
physical_characteristics: 3 notebks
        processing_notes: 18
   received_extent_units: NULL
       scope_and_content: Reprints of publications
   source_of_acquisition: data
                   title: Papers
                      id: 3553
                 culture: en
1 row in set (0.00 sec)

I'm sure I am missing a simple relation somewhere, but I just can't seem to find it.

Thanks!

Tom

[Dan Gillean]

Hi Tom, 

Before I try to help you work out the SQL involved, if you're using version 2.6 then there might be a much easier way to get the information you need. 

In 2.6.0 and later, AtoM includes a global physical storage report with a few customization options. It will by default generate a CSV that includes all physical container relations to both accessions and descriptions. You can choose to just export relations to accessions or just descriptions if preferred as well. It will also show you those containers that aren't currently linked to anything (for example, legacy remnants of deleted descriptions, etc) - though there's also a couple command-line tasks that can help with that as well: 

• Global storage report: https://www.accesstomemory.org/docs/latest/user-manual/reports-printing/create-physical-storage-report/#global-storage-report
• Physical storage CLI tasks: https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/cli-tools/#cli-normalize-physical-object

So, hopefully a CSV that lists the related accession number and accession title, as well as the name, type, and location of its related container(s), will give you what you need. Be sure to read the TIP at the end of the report documentation section, to understand why a container might appear more than once in a report. 

Cheers, 

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

he / him

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/a8757ef1-5a5d-4827-beee-8f8c508f59d1n%40googlegroups.com.

[Tom Misilo]

Hi Dan,

Fortunately, I was able to figure out the relation, in the relation table between the object_id and subject _id that got me the relationship I needed.  I just hadn't gotten a chance to reply back.  Sorry about that.

I didn't look at the CSV reports, since I was trying to create a singular report of data comparing between AtoM and our High Density Storage system.

Thanks again!

Tom

You received this message because you are subscribed to a topic in the Google Groups "AtoM Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ica-atom-users/67O-dgiM2iM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CAC1FhZL07g7db0tnmXLevT74V1MYTbHJNgt9TW_VgVTZBcCp1Q%40mail.gmail.com.

[Dan Gillean]

Hi Tom, 

Glad to hear you figured out what you need - sorry the report wasn't as helpful as I'd hoped. In case it helps in the future, you can find entity relationship diagrams of AtoM's database schema on the wiki, here: 

• https://wiki.accesstomemory.org/Development/ERDs
  

I'll try to generate a new one once we release 2.7. 

Cheers, 

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

he / him

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CACZhg%3DFua3zsmKYhBShg%2Bzo447zyDAypugwrqVTzz_H%3DEEhBVQ%40mail.gmail.com.