Advanced search using date ranges/last updates

[Andrés Abad]

Good morning,

We're currently trying to find the date of creation for items in our AtoM database.

Unfortunately, when I use the Date Range option from Advanced Search, it yields no result. 

One field that holds this information is the "Last updates" section within "Administration Area", but I'm unable to use it in advanced search. 

Is there any other way I could sort items based on the date/month they were added to the archive?

Thanks

Andrés

[Dan Gillean]

Hi Andrés, 

So many of the pieces of what you need are there, but they are not together! I think that we could possibly use SQL to help though, if you have access to the command-line interface of your AtoM installation. 

First, some clarifications: 

The Date Range search in the advanced search will search against the internal, ISO 8601-formatted Start and End date fields of an archival description's creation dates. AtoM has 3 date fields in its main archival templates (i.e. ISAD, RAD, DACS)  -the Display date is the free-text field that end users see, where you can enter whatever you want (following your local policies, or your standard) to represent dates, including approximation or uncertainty. The Start and End date fields, on the other hand, are for internal use only -  i.e. for date range searching. These are ONLY shown to public users if they are populated but the Display date is not. The Start and End date fields expect ISO 8601 dates i.e. YYYY-MM-DD, YYYY-MM, or YYYY. You will get the best date range search results if you enter YYYY-MM-DD dates for the widest possible values of your display date - for example, if the only known date of a record is "circa 1905", then I would enter 1905-01-01 as the start date, and 1905-12-31 as the end date, so all of 1905 is covered by that general "circa" Display date. 

In no way do these dates relate to the creation or modification dates of your archival description in AtoM itself. They are meant to be metadata that describe the creation (or accumulation, etc) dates of the record that your description describes. See: 

• https://www.accesstomemory.org/docs/latest/user-manual/access-content/advanced-search/#date-range-search-filter
  

You are right to check the Description Updates module in the Admin menu - but also correct that the search there does not have as many options as the Advanced search. You can read more about using the Description updates module here: 

• https://www.accesstomemory.org/docs/latest/user-manual/administer/search-updates/
  

I am curious what filters or facets are missing from the Description updates that you need? But in any case, let's continue! 

There USED to be a method in Elasticsearch to search by createdAt or updatedAt date ranges - but a) this doesn't necessarily SHOW those dates in the results, and b) I tried in our most recent 2.8 release, and it no longer seems to work (it was not a supported feature, but rather a way of bending Elasticsearch, in any case). 

However, AtoM does keep track of when records are first created, and modified - we can even see the last modified date in the Administration area of a record's edit page: 

However, you need to go into edit mode to see this on a single record, so it's not currently that useful. 

This is where I think we could potentially use SQL to give us what we want. If you have access to the command-line interface of your AtoM installation, we have provided instructions on how to access the MySQL command prompt in our docs here: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#accessing-the-mysql-command-prompt
  

Now, the query I will provide worked in my own local test instance, running 2.8.1. And it is a SELECT, meaning it makes no changes to the data. Nevertheless: accessing the database directly on a production site is ALWAYS a "proceed at your own risk" situation - ideally, even though this is low risk, you should make a backup of your data first! See: 

• https://www.accesstomemory.org/docs/latest/admin-manual/maintenance/common-atom-queries/#backing-up-the-database
  

Here is the query that I came up with - this should output the title and slug of your descriptions, along with the MySQL timestamps for when the record was created and when it was last modified. I have also included a clause to limit the output to 50 records at a time and sort the results by the date of creation column, but you should be able to look at my query and modify that easily enough: 

The query, which you would run after you have accessed the MySQL command prompt: 

SELECT io.title AS title, s.slug AS slug, o.created_at AS created, o.updated_at AS modified FROM information_object_i18n io
  INNER JOIN object o ON o.id=io.id
  INNER JOIN slug s ON io.id=s.object_id
  WHERE io.id <> 1
  ORDER BY o.created_at
  LIMIT 50;

Here is an example of the first 10 rows of my test data output, as an example: 

+-------------------------------------------------+-------------------------------------------------+---------------------+---------------------+
| title                                           | slug                                            | created             | modified            |
+-------------------------------------------------+-------------------------------------------------+---------------------+---------------------+
| Frederick Hagan fonds                           | frederick-hagan-fonds                           | 2011-11-22 20:18:02 | 2011-11-22 20:18:02 |
| Iain Baxter fonds                               | iain-baxter-fonds                               | 2011-11-22 20:18:03 | 2011-11-22 20:18:03 |
| Goldwin Smith fonds                             | goldwin-smith-fonds                             | 2011-11-22 20:18:04 | 2011-11-22 20:18:04 |
| Frances Loring and Florence Wyle fonds          | frances-loring-and-florence-wyle-fonds          | 2011-11-22 20:18:04 | 2011-11-29 18:33:58 |
| Arthur Lismer and Marjorie Lismer Bridges fonds | arthur-lismer-and-marjorie-lismer-bridges-fonds | 2011-11-22 20:18:04 | 2011-11-22 20:18:04 |
| Frederick Varley fonds                          | frederick-varley-fonds                          | 2011-11-22 20:18:05 | 2011-11-29 18:35:51 |
| Tim Zuck fonds                                  | tim-zuck-fonds                                  | 2011-11-22 20:18:05 | 2011-11-22 20:18:05 |
| Gallery Moos fonds                              | gallery-moos-fonds                              | 2011-11-22 20:18:05 | 2011-11-22 20:18:05 |
| Gordon Conn collection                          | gordon-conn-collection                          | 2011-11-22 20:18:06 | 2011-11-22 20:18:06 |
| Frederick S. Challener collection               | frederick-s-challener-collection                | 2011-11-22 20:18:06 | 2011-11-22 20:18:06 |
+-------------------------------------------------+-------------------------------------------------+---------------------+---------------------+
10 rows in set (0.06 sec)

(the created and modified dates on mine are all very close because this is an old copy of our public site's demo data, which I reload fresh between every test)

Let's say you now wanted to skip the first 50 rows and output the next 50 - change the last line to: 

• LIMIT 50, 50; 

If you wanted to order by the date of modification instead, change the ORDER BY line to: 

• ORDER BY o.updated_at
  

Hope this helps! 

Cheers, 

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

he / him

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/fe9db641-604c-407c-bafc-e2038c83bc59n%40googlegroups.com.