External Email - Exercise Caution
Thank you, Jeffrey! I also contacted GS Support and was given an option that helped. When I did my advanced query search (see screenshot), I added another line, object value = is current = yes. That gave better results. (Fyi the purpose of the advanced query was to examine the values of artworks acquired in the 1990s.) The problems to the value query were from records that had multiple values – if one of the values was under 5000, it was included in the search results.
It’s hard to describe this clearly, I’m even confusing myself! Mary Douglas
[Graphical user interface, application Description automatically generated]
-----Original Message-----
From: The Museum System (TMS) Users <[log in to unmask]> On Behalf Of TMSUSERS automatic digest system
Sent: Friday, January 13, 2023 8:00 PM
To: [log in to unmask]
Subject: TMSUSERS Digest - 10 Jan 2023 to 13 Jan 2023 (#2023-5)
There are 2 messages totaling 1458 lines in this issue.
Topics of the day:
1. Object Value for Advanced Query (2)
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.
----------------------------------------------------------------------
Date: Fri, 13 Jan 2023 16:42:15 +0000
From: Mary Douglas <[log in to unmask]<mailto:[log in to unmask]>>
Subject: Object Value for Advanced Query
External Email - Exercise Caution
Hello,
I am doing advanced query and want to search for the latest object value. But, the results are pulling from not the latest or current value but from the first value that was entered into the object record. I went to configuration to look for a "latest value" but that term isn't there to add. How to fix this? I'm doing a query for artworks that have a current (latest) value under $5000 and with an object number of less than 2000.1
Thank you for your help. Mary
Mary Douglas
Collections Manager
Kamm Teapot Foundation
1950 Flintstone Drive
Statesville NC 28677
704-876-1180 office
704-881-4664 cell
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.
------------------------------
Date: Fri, 13 Jan 2023 17:18:02 +0000
From: "Smith, Jeffrey" <[log in to unmask]<mailto:[log in to unmask]>>
Subject: Re: Object Value for Advanced Query
Mary,
That data doesn't really exist in TMS. The only control is the "Current" check box by which you could manually select the most recent value, and then maintain that procedure, which could be tricky, or easily inaccurate.
So the best way to do this is to create a SQL view that pulls only the most recent value. Such as:
SELECT O.ObjectID, MAX(OI.ValueISODate) AS ValueDate, MAX(OI.Value) AS Value FROM Objects O LEFT OUTER JOIN ObjInsurance OI ON O.ObjectID = OI.ObjectID
Then you could use that view in a Crystal Report.
Or, you could create a Crystal Report using the ObjInsurance table, group by ObjectID (or other object field), and then sort by the value date in descending order. You could then even format that field to suppress all but the first record, and you'd have your most recent value in a report.
But to my knowledge, there is no way to search TMS for a value and know it's the most recent.
Hope that helps!
Jeffrey Smith
Registrar for Collections Information
[log in to unmask]<mailto:[log in to unmask]>
O: 202.633.0348
C: 202-340-4896
[signature_691063453]<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fasia.si.edu%2F&data=05%7C01%7CTMSUSERS%40SI-LISTSERV.SI.EDU%7Cb20e16623b094f04d3e908daf7d9b551%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C638094812006309831%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=bT0xwnGZWVeY0jvWxHRcRIbLWHmh%2FA7rqS3bSwpXh2E%3D&reserved=0>
From: The Museum System (TMS) Users <[log in to unmask]<mailto:[log in to unmask]>> On Behalf Of Mary Douglas
Sent: Friday, January 13, 2023 11:42 AM
To: ListServ tmsusers <[log in to unmask]<mailto:[log in to unmask]>>
Subject: Object Value for Advanced Query
External Email - Exercise Caution
Hello,
I am doing advanced query and want to search for the latest object value. But, the results are pulling from not the latest or current value but from the first value that was entered into the object record. I went to configuration to look for a "latest value" but that term isn't there to add. How to fix this? I'm doing a query for artworks that have a current (latest) value under $5000 and with an object number of less than 2000.1
Thank you for your help. Mary
Mary Douglas
Collections Manager
Kamm Teapot Foundation
1950 Flintstone Drive
Statesville NC 28677
704-876-1180 office
704-881-4664 cell
To unsubscribe, send an email to [log in to unmask]<mailto:[log in to unmask]<mailto:[log in to unmask]:[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.
------------------------------
End of TMSUSERS Digest - 10 Jan 2023 to 13 Jan 2023 (#2023-5)
*************************************************************
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.
|
