Extract data from a row in a table

You can use the USER_SYM_SUBROUTINE subroutine with the EXTRACT.AMV argument in a complex User Symbolic to extract specific rows and data from tables.

Subroutine name

USER_SYM_SUBROUTINE

Subroutine argument

EXTRACT.AMV

Field type

Tables

Examples of use

You could take only the first row of data from a table.

Another example is to take only data with a certain authority field. For example, you could report on the Other ID table and only see the rows with the Other ID Type term Old Accession Number.

Argument syntax and parameters

The subroutine is a piece of code that uses ‘arguments’. An argument is a set of options that tells the subroutine what to do. The argument parameters are commonly separated by an exclamation mark.

The EXTRACT.AMV subroutine argument structure is:

EXTRACT.AMV!arg2!arg3!arg4!arg5!arg6!arg7

What the argument parameters mean

Argument parameter Required or optional? Explanation
arg2 Optional

The internal field name of the field to be checked and the System ID of the value to check for, delimited by a semi-colon. If arg2 is not specified, all rows are returned.

For example, OTHER_ID_TYPE;ETH (where ETH is the System ID of the Other ID Type term Ethnology No).

To check for multiple values, such as multiple Other ID Type terms, use a comma to delimit the System IDs. For example, OTHER_ID_TYPE;ETH,ANT (where ANT is the System ID of the Other ID Type term Anthropology No).

OR

#NULL to extract all rows with data entered in the check field. That is, where the check field is not null.

#n (where 'n' is the row number) to extract a particular row. For example, #1 to extract the first row of data.

#LAST to extract the last row of the table.

arg3 Required

The internal field names of fields to be displayed, delimited by ';'.

For example, OTHER_ID;OTHER_ID_TYPE;OTHER_ID_NOTES.
arg4 Optional Maximum number of rows to be returned. If this is not specified, then all rows are returned
arg5 Optional Conversion. If set to 1, then no conversion. Otherwise, enter an aligned set of conversion overrides, delimited by ';'. If this part of the argument is not specified, convert as per dictionary (that is, use the default conversion).
arg6 Optional

Sort by. Specify the internal field names of the fields you want to sort by. If this is not specified, the default sorting will be used. The sorting options are specified in this order, separated by a semi-colon ;

  • You can include a conversion to sort on the internal value of the field instead of the output. Specify this by using a 1 to sort on internal value.

  • You can sort in either ascending or descending direction. Specify this with A for ascending or D for descending.

  • You can have left, right, or numeric justified sorting. Specify this with L for left, N or R for numeric or right justified.

The following example would specify sorting on the Update User and Update Date fields, with a conversion to sort on the internal value of the Date field, it will be sorting in ascending direction, and will be numerically justified:

UPDATE_USER&UPDATE_DATE;1;A;N
arg7 Optional

Sub delimiter override. If this is not specified, default delimiters are Value Marks, if #n or #Last was specified in arg2, otherwise the delimiter is a comma and a space ", ".

If you want to override this, you can use %VM% for Value marks, %SVM% for sub-value marks, or %TM% for text marks.

Example of subroutine argument

This argument will extract rows where the Other ID Type is Ethnology No and will display the Other ID and Other ID Notes fields where ETH is the System ID of the Other ID Type term Ethnology No.

EXTRACT.AMV!OTHER_ID_TYPE;ETH!OTHER_ID;OTHER_ID_NOTES

This argument will return the Update History table, sorted by user and sorted in ascending order by date.

EXTRACT.AMV!!UPDATE_USER;UPDATE_DATE!!!UPDATE_USER&UPDATE_DATE;1;A;N