Archiving Statements

Owned by Mr. Schmittberger
Last updated: Aug 27, 2024

To archive DMS objects yuuvis® RAD, you have to execute the archiving operation (via yuuvis® RAD management-studio) which takes an eSQL statement as input to select the objects to archive. This page gives you examples of the most commonly used statements.

Selecting Objects by Date/Time Criteria


Important

  • The sysitemcreationdate field selects the creation date of the entire object (item), whereas syscreationdate selects the creation date of the active version.
  • The datetime identifier takes input of the form yyyy-MM-dd hh:mm:ss. The only required part is yyyy but all left out parts will be autocompleted with 00.
  • Object type names and element (field) names are accessed by their technical names.
StatementDescription
select * from dms:sysobject where sysitemcreationdate <= datetime'2018-01-23 13:14:15'This will archive all objects (of any document type) that were created before or exactly on January 23, 2018, 13:14:15.

select * from dms:sysobject where syscreationdate < datetime'2018-07'

This will archive all objects (of any document type) that were created and last modified before July 1, 2018, 00:00:00.
select * from dms:document d where d.customfield1 = 'acknowledged' and d.customfield2 < datetime'2018-02-01'This will archive all objects of the type document where customfield1 has the value acknowledged and customfield2 contains a date that is January 31, 2018, 23:59:59.999 or earlier.
select * from dms:sysobject where sysitemcreationdate <= dateadd(day, -7, currentdatetime())This will archive all objects (of any document type) that were created a week ago from now or earlier.
The dateadd function can add or subtract a specified amount of time from the provided date: dateadd(<unit>, <amount>, <date>)
The unit can be "second", "minute", "hour", "day", "week", "month", "year"
The date can be taken from the index data, specified manually or currentdate() (start of current day)/currentdatetime() (now))


Selecting Objects by Creator/Modifier Criteria

StatementDescription
select d from dms:sysobject d join ecm:syscreationtraceowner2user c2u join org:sysuser u
where u.sysname = 'user4711'

This will archive all objects (of any document type) of which user4711 was the creator of the currently active version.
This means either the object is in version 1 and user4711 was the creator, or the version is in version 2 or higher and user4711 was the last modifier.

select * from dms:sysobject (all versions) d join ecm:syscreationtraceowner2user c2u join org:sysuser u
where u.sysname = 'user4711'		This will archive all objects (of any document type) of which user4711 was the creator or the modifier of any version.
select * from dms:sysobject (all versions) d join ecm:syscreationtraceowner2user c2u join org:sysuser u
where d.sysversionnumber = 1 AND u.sysname = 'user4711'		This will archive all objects (of any document type) of which user4711 was the the creator of the first version.
This means user4711 created the (first version of the) item.


Selecting Objects by Index Data/ID

StatementDescription
select * from dms:document where sysitemid = id'ED01B5E5996648088D0A474C37962C93'

This will archive the DMS object of type document with the the item ID ED01B5E5996648088D0A474C37962C93.

select * from dms:document where longnumber = 123456789LThis will archive all DMS objects of type document where the field longnumber has the value 123456789.
select * from dms:document where string1 = 'ipsum'This will archive all DMS objects of type document where the field string1 has the value ipsum.
select * from dms:document where catalogfield = 'english'This will archive all DMS objects of type document where the field catalogfield has a catalog entry with data value english.
select * from dms:document where orderstable containsrowwithcondition(orderstablerecord.order = 'order4711')

This will archive all DMS objects of type document where the table orderstable has a row where the column order has the value order4711.
This also applies to multi-tagging fields which are technically a table with one column. In this case, any element has to have the value order4711. ← Valid for versions <= 8.16 LTS

select * from dms:document where ordersset containsrowwithcondition(order = 'order4711')

select * from dms:document where ordersset containsrowwithcondition(order IN ('order4711','value0815'))

Valid for versions >= 9.0

This will archive all DMS objects of type document where the field of type SET[string] (multi-tagging) with the technical name ordersset contains an element (chip) with the technical element name  order that has the value order4711 (first example) or the values order4711 or value0815 (second example).