Agent Report - Additional Data Request

[Jegelewicz]

from @DellaCHall

I need to know which of our agents have biographies, and which do not. I'm thinking I need a spreadsheet that contains all of the agents connected to UAM:Art records, their roles, and their associated metadata, so I can sort it. Is that possible?

The list of names can be retrieved using write SQL and

select distinct agent.preferred_agent_name
from cataloged_item, collection, agent, collector, agent_name
where agent.agent_id = collector.agent_id
and collector.collection_object_id = cataloged_item.collection_object_id
and collection.collection_id = cataloged_item.collection_id
and collection.guid_prefix = 'UAM:Art'
and agent.agent_id = agent_name.agent_id
and agent_name.agent_name_type='preferred'

but getting the rest of the information is beyond me. @dustymc this seems like a nice report that any collection might use?

[dustymc]

SQL

Or

and #6645 might include...

roles, and their associated metadata,

..and/or I can help with SQL, but I need to know specifics.

[Jegelewicz]

and/or I can help with SQL, but I need to know specifics.

For all agents associated with UAM:Art

preferred_name | remark | curatorial_remark | first_name | middle_name | last_name | aka | status | relationship | address | unique agent_role

@DellaCHall does that seem reasonable? The akas, statuses, relationships, addresses, and roles would each be a concatenation of how ever many of them exist. If you need more refined details, please say so.

@dustymc does that make sense and seem possible?

[dustymc]

I still don't know what 'unique agent_role' means.

'associated' == collector?

Otherwise it's possible as long as you don't mind long concatenations when there are 472 first names and nothing when there's whatever you haven't columnified.

IDK if that makes sense or not....

[Jegelewicz]

'associated' == collector?

yes - the collector roles I think, but there may also be a need for transaction roles. I'll leave that to @DellaCHall

[DellaCHall]

What @Jegelewicz said seems to be what I need. However, I have no idea what SQL is. Is that something I should know how to do? Someplace in the handbook for me to learn it?

Specifically, for this instance, I need to know if the agent is a creator, and I need to see the remarks.

[Jegelewicz]

I have no idea what SQL is. Is that something I should know how to do? Someplace in the handbook for me to learn it?

@DellaCHall SQL is a language that can be used to query Arctos. There is a Write SQL tool that makes that a bit easier that also includes a link to a cheat sheet where some previously used SQL can be found and re-used. You don't need to know how to write SQL, that is where the community can help. For this particular request, I think @dustymc will be required to get what you want!

[dustymc]

Is Agent/Collection Report unsuitable for some reason?

[Jegelewicz]

Is Agent/Collection Report unsuitable for some reason?

Not sure - I just ran it, let me see how the data looks.

[Jegelewicz]

@DellaCHall Dusty is talking about this:

I ran it for UAM:Art, and once I get the results, I'll put them here.

[Jegelewicz]

@DellaCHall here is the file - let us know if it doesn't provide what you need!

temp_agent_down_CABCBDADDAAEBADB.csv

[DellaCHall]

Thanks! I didn't know that existed - I was looking in the Reports section.

Unfortunately, it does not appear to have what I need in it. It does not contain remarks at all, nor whether or not the agent is a creator. It has COLLECTOR_SUMMARY, but the data in that field does not give the role, only the decade, number of instances, and the state, like so: "UAM:Art - 1980 - Alaska (6)".

[Jegelewicz]

@dustymc difficult to add remarks (both public and curatorial) and change the COLLECTOR SUMMARY to summarize by collector role?