You can use a Select Statement to find data in any field, including those unavailable in the Search Fields section of Advanced Search. To help clean up your data, you can search for records where data is missing.

How to search with a Select Statement

1.Open Advanced Search by clicking Advanced in the toolbar.

2.Select the file you want to search. For example, Object.

3.Choose Select Statement as the search method.

4.Click into the Select Statement field and enter your Select Statement. See format, examples, and components below.

5.Click Next, and then click Finish to see your results.

Select Statement format

Select Statements have up to 5 parts.

Example: SELECT OBJECT WITH PROD_PRI_PERSON = ”Jane Smith”

Part 1: File to select

Example: SELECT OBJECT

You don’t need to enter this part. It’s automatically entered by the system when you press Enter after typing the other parts of the statement.

Part 2: Keyword

Example: WITH

Part 3: Internal fieldname

Example: PROD_PRI_PERSON

To find the internal fieldname, click into the field, and then press Alt+F1 or go to Help > Database Information. The internal field name is listed at the bottom of the Database Information window. Ignore the FMC number in brackets

 

Part 4: Comparison operator

Example: =

Part 5: Information you are searching for

Example: “Jane Smith”

Select Statement examples

Searching for information

•Find objects made by a certain artist or maker
SELECT OBJECT WITH PROD_PRI_PERSON = ”Jane Smith”
This statement will find Object records where the Primary Production Person field contains Jane Smith.

•Find objects classified at a certain time
SELECT OBJECT WITH CLASSIFICATION_DATE FROM "01 Jun 2015" TO "30 Jun 2015"
This statement will find Object records where the Classification Date is between 1 and 30 June 2015.

Searching for dates

•Find objects with a current location date
SELECT OBJECT WITH CURR_LOC_DATE = "6/12/21"
This statement will find Object records with a Current Location Date of 6/12/21.

•Find objects with an earlier current location date
SELECT OBJECT WITH CURR_LOC_DATE < "6/12/21"
This statement will find Object records with a Current Location Date earlier than 6/12/21.

•Find objects with any current location date other than that specified
SELECT OBJECT WITH CURR_LOC_DATE <> "6/12/21"
This statement will find Object records with any Current Location Date other than 6/12/21.

Searching for a presence or absence of data

•Find objects with data in a field
SELECT OBJECT WITH DEPARTMENT
This statement will find objects that contain data in the Department field.

•Find objects without data in a field
SELECT OBJECT WITHOUT DEPARTMENT
This statement will find objects that don’t contain data in the Department field.

Searching with case sensitivity

•Find data with any case (sentence case, lower case, and all caps)
SELECT PERSON WITH LAST_NAME = "Smith"
This statement will find Person records with Smith, SMITH or smith in the Last Name field.

•Find data using a specific case (for example, sentence case)
SELECT PERSON WITH CASESENS LAST_NAME = "Smith"
This statement will find Person records with Smith in the Last Name field. SMITH and smith will not be included.

Select Statement components

Keywords

 

Comparison operators