valuecount

Syntax

number = VALUECOUNT(database,dataset,tag,value)

Arguments

number: integer

tag: field tag

database, dataset, value: text

Meaning

Returns the number of appearances of value in the term or integer indexed tag in dataset in database. In other words: if you'd like to know how often some value already appears in a certain tag in a dataset, you can use the valuecount function (available from adlwin.exe 7.5.17233.3 and Axiell Collections version 1.0.20170821.3 ) which is faster than the workaround using a FACS READ. There's no need for a FACS declaration for database, when you use valuecount.

Example 1

if (&1 = 11 and IN <> '' and &0 = 0) {                  /* 'Object number'
 if (valuecount('collect','archivecatalogue',IN,IN) > 0 or ~        
     valuecount('collect','intern',IN,IN) > 0 or ~
     valuecount('collect','extern',IN,IN) > 0 or ~
     valuecount('collect','archiveaccession',IN,IN) > 0) {
       errorm 'The object number already appears in this dataset. Please enter a different one.', 2
       end
  }
}

Result

Suppose your object number index in non-unique, so you allow object numbers to appear multiple times in the database as long as they appear in different datasets but you would like users to be notified when they enter an object number that already exists in the current dataset and to stop them from saving the record until they do. You could write a before-storage adapl for the object number field (tag IN) with the code above. It first checks (before-storage) if the object number field has been filled in and if this is a new record and then it checks if the value entered in the field already appears in tag IN in any of the stored records in the four datasets of Collect. If so, the warning is displayed and the user won't be allowed to store the record because of severity code 2 of the errorm.
Note that a check for a duplicate value when saving an existing record or after-field when leaving the relevant field in an existing record is more complicated: after all, the current object number already exists in a previously saved version of this record, but you can also not just ask if the valuecount is greater than one, because if the user changed the existing object number to a different existing object number then the greater-than-one check doesn't work after-field (because the edited record hasn't been saved yet), so you'll still have to read the previously saved version of the record to find out if the user entered a new object number or left it as it was, and then still determine in which case a warning should be shown.