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]
O: 202.633.0348
C: 202-340-4896

[signature_691063453]<https://asia.si.edu/>

From: The Museum System (TMS) Users <[log in to unmask]> On Behalf Of Mary Douglas
Sent: Friday, January 13, 2023 11:42 AM
To: ListServ tmsusers <[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.

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.