Feature Request - Media label "date uploaded or added"

[AJLinn]

Per our discussion at AWG issues meeting last week, I'd like to add a new label to our media tools. (CT media_lable) "uploaded date" or "added date" - whatever makes most sense in terms of our standard nomenclature. (Only date label currently available is "made date," which is intended to relate when the digital file was made (Documentation: "The date/time at which the media object was created.")

This is similar to what I was hoping to get from issue #1407.

New info for CTmedia_label could be: added date: The date/time at which the media object was uploaded or added to Arctos.

Priority: medium/high (certainly would help annual reporting on the number of media files added each year!)

[dustymc]

@AJLinn has this fallen through the cracks?

[AJLinn]

Thank you @Jegelewicz for moving this discussion along to the Code Table Admins. It still is a feature I'd appreciate being added.

[Jegelewicz]

Suggest we add.

[Jegelewicz]

added date: The date/time at which the media object was uploaded or added to Arctos.

@AJLinn can you clarify this? Are you looking for the date the media was loaded to TACC or the day the relationship to that media was created in Arctos?

[AJLinn]

Because we upload one at a time rather than bulkloading, it's the day the relationship between the catalog record and the media was made. I hadn't thought about those who are bulkloading to TACC directly. When you do it one at a time, the relationship creation and upload to TACC are simultaneous.

[Jegelewicz]

@dustymc can @AJLinn get what she needs from what's in Arctos already?

[dustymc]

yep

Time: 49.081 ms
arctosprod@arctos>> \d media_relations
                                             Table "core.media_relations"
       Column        |            Type             | Collation | Nullable |                  Default                   
---------------------+-----------------------------+-----------+----------+--------------------------------------------
 media_relations_id  | integer                     |           | not null | nextval('sq_media_relations_id'::regclass)
 media_id            | integer                     |           | not null | 
 media_relationship  | character varying(40)       |           | not null | 
 created_by_agent_id | integer                     |           | not null | getagentidfromlogin(SESSION_USER::text)
 created_on_date     | timestamp without time zone |           |          | LOCALTIMESTAMP
....

[Jegelewicz]

make this more visible then close.

[Jegelewicz]

BUT maybe they should all say "assigned by" because created by agent Sam Howes created by Anna Chinn on 2019-06-22 sounds really odd?

[Jegelewicz]

@dustymc where are we on this one?

[Jegelewicz]

Here is another one that will likely never get done. However, it isn't clear what the label should be. @AJLinn are you OK with assigned by as opposed to created by?

[dustymc]

where are we on this one?

I thought it was resolved but then you changed things.

[Jegelewicz]

BUT maybe they should all say "assigned by" because "created by agent Sam Howes created by Anna Chinn on 2019-06-22" sounds really odd?

If my assertion above is senseless, then proceed with the original plan, but this is "just" a label, so changing it shouldn't be too difficult?

[AJLinn]

. @AJLinn are you OK with assigned by as opposed to created by?

"Assigned by" sounds good to me. It technically makes better sense I think. I just need to be able to do a search that somehow gives me a number of media files uploaded for / assigned to my collection each year.

[Jegelewicz]

OK - change approved. @dustymc This should be ready to go.

[dustymc]

approved

I'm so lost. I am just about 100% sure we're tossing around noninterchangeable terminology - IDK what "assigned" means, but it implies something about asserting data, at least in my head. Created relationship means just that with no justifiable implications that it might something else.

media files uploaded for / assigned to my collection each year.

I'm not sure how that's related to what I thought this involved. Do you just need some SQL/a report?

select 
 -- year the relationship was created, other intervals are possible
to_char(created_on_date,'YYYY'),
-- number of relationships, this could be adjusted to be other things
count(*) 
from media_relations
-- filter for relationships to catalog records in a collection
inner join cataloged_item on media_relations.cataloged_item_id=cataloged_item.collection_object_id
inner join collection on cataloged_item.collection_id=collection.collection_id and guid_prefix='UAM:EH' 
group by to_char(created_on_date,'YYYY') order by to_char(created_on_date,'YYYY')
;
 to_char | count 
---------+-------
 2018    |   471
 2019    |  2212
 2020    |   436
 2021    |  1850
 2022    |  1337
         |  9337
(6 rows)

[AJLinn]

Do you just need some SQL/a report?

I'm not sure... I created the issue because I thought it would be useful to know when the media file was uploaded/assigned a relationship to an object in our collection. The date created is when the media file was created (we regularly upload photos of objects that were taken 15-20 years ago or more). This is a statistic I have to provide for our annual report and I couldn't figure out any way to get that information through our standard media search fields. If there's an easy way to get at that information at the collection level rather user level, I'm all ears for an alternative.

[dustymc]

easy way to get at that information

Cool, added it to the cheat sheet.