External Email - Exercise Caution
Hi Mary,
You can write a report for this, either as a tms report, an alert, or an automated email from the database.
We can add this one to the cons tally for TMS. Anyone interested in a better and cheaper collection management system? send me a message!

Jonathan




On Wed, Dec 8, 2021 at 5:53 PM David Lowe <[log in to unmask]<mailto:[log in to unmask]>> wrote:
External Email - Exercise Caution
If you want to drill down a little further and, say, find the photographer (or printmaker, sculptor, etc.) with the most records (just change the Role to whatever you use):

Select ConXrefDetails.ConstituentID, Constituents.DisplayName, Count(ConXrefDetails.ConstituentID) as Count from ConXrefDetails
inner join Constituents on (ConXrefDetails.ConstituentID = Constituents.ConstituentID)
inner join ConXrefs on (ConXrefDetails.ConXrefID = ConXrefs.ConXrefID)
where ConXrefDetails.RoleTypeID = 1
and ConXrefDetails.Unmasked=1
and ConXrefs.RoleID in (select RoleID from Roles where Role like 'Photographer')
Group by ConXrefDetails.ConstituentID, Constituents.DisplayName
Order by Count Desc

David Lowe | The New York Public Library
Specialist II, Photography Collection


On Wed, Dec 8, 2021 at 5:31 PM David Lowe <[log in to unmask]<mailto:[log in to unmask]>> wrote:
Below is a quick and dirty attempt, which seems to give me a pretty accurate ranking (though the numbers don't always match exactly when I query from the User Interface vs. the SQL query). Perhaps you (or an IT friend) can run this:

Select ConXrefDetails.ConstituentID, Constituents.DisplayName, Count(ConXrefDetails.ConstituentID) as Count from ConXrefDetails
inner join Constituents on (ConXrefDetails.ConstituentID = Constituents.ConstituentID)
where ConXrefDetails.RoleTypeID = 1
and ConXrefDetails.Unmasked=0
Group by ConXrefDetails.ConstituentID, Constituents.DisplayName
Order by Count Desc

Hope this works for you.
Aside to other SQL users: feel free to critique this query! And, what does the Masked/Unmasked do?

Best,
d

David Lowe | The New York Public Library
Specialist II, Photography Collection


On Wed, Dec 8, 2021 at 4:41 PM Chad Petrovay <[log in to unmask]<mailto:[log in to unmask]>> wrote:
External Email - Exercise Caution
TMS search functionality does not have any aggregate functions that allow you to perform the search you described.

You will only be able to do this sort of search using SQL.


On Wed, Dec 8, 2021 at 4:32 PM Mary Douglas <[log in to unmask]<mailto:[log in to unmask]>> wrote:
Hello,
I want to know which artists have the most artworks in our collection. How to do that query in TMS? How to connect object related constituent with number of object records?
Thank you for your suggestions and help.
Mary Douglas

To unsubscribe, send an email to [log in to unmask]<mailto:[log in to unmask]> with the following commands in the body of the email:

     signoff TMSUSERS

     //  eoj


You will receive a confirmation that your subscription has been removed.
To unsubscribe, send an email to [log in to unmask]<mailto:[log in to unmask]> with the following commands in the body of the email:

signoff TMSUSERS

// eoj

You will receive a confirmation that your subscription has been removed.

To unsubscribe, send an email to [log in to unmask]<mailto:[log in to unmask]> with the following commands in the body of the email:

signoff TMSUSERS

// eoj

You will receive a confirmation that your subscription has been removed.

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

     signoff TMSUSERS

     //  eoj


You will receive a confirmation that your subscription has been removed.