I want to use a "Related records" field to retrieve records with matching date values

Article Number:040217

A Date field cannot be specified for Fetch criteria in the settings of a Related records field.

To retrieve records that have a specific date value and display them in a Related records field, you need to use a Calculated or Text field for the Fetch criteria setting instead.
Reference: Fields that can be specified for the "Fetch criteria" setting in Related records

To display records with the same date in a Related records field, follow the steps below.

Example of configuring the settings

The following example shows how to configure the settings for a "Lead Management" app from which data will be retrieved, and a "Customer Management" app in which a Related records field will be placed.

In a "Calculated" or "Text" field, set a formula that refers to a value in a date field

Configure the following settings for the "Lead Management" app.

  1. Add a Date field.
    Leave the Default value as it is.

  2. Add a Calculated or Text field to get the Date field value.

    • When using a Calculated field:

      • Field name Date (Calculated)
      • Field code: Date_Calculated
      • Formula: Date
      • Display format: Date(Example: 2012-08-06)
        (/k/img-en/relatedrecords_date01.png)Screenshot: Example of settings for a "Calculated" field
    • When using a Text field:

      • Field name: Date (Text)
      • Field code: Date_Text
      • Formula: DATE_FORMAT(Date,"MM-dd-YYYY","Etc/GMT")
        Screenshot: Example of settings for a "Text" field
  3. If there are existing records in the app, update them as needed.
    You can also bulk update records by performing the Export to file and Import from file actions.
    Exporting data to a file
    Importing record data from a file into an app

In a "Related records" field, specify the "Calculated" or "Text" field as the "Fetch criteria"

Configure the following settings in the "Customer Management" app.

  1. Place a Date field on the app form.
    Leave the Default value as it is.

  2. Add a Calculated or Text field to get the Date field value.

    • When using a Calculated field:

      • Field name Date (Calculated)
      • Field code: Date_Calculated
      • Formula: Date
      • Display format: Date(Example: 2012-08-06)
        (/k/img-en/relatedrecords_date01.png)Screenshot: Example of settings for a "Calculated" field
    • When using a Text field:

      • Field name: Date (Text)
      • Field code: Date_Text
      • Formula: DATE_FORMAT(Date,"MM-dd-YYYY","Etc/GMT")
        Screenshot: Example of settings for a "Text" field
  3. If there are existing records in the "Customer Management" app, update them as needed.
    You can also bulk update records by using the Export to File and Import from File features.
    Exporting data to a file
    Importing record data from a file into an app

  4. Add a Related records field.
    Specify the Calculated or Text field that you configured in Step 2 as the app from which data will be retrieved.

    • Field name: Related records
    • Field code: Related_records
    • Datasource app: Lead Management
    • Fetch criteria: "Date (Calculated)" = "Date (Calculated)" or "Date (Text)" = "Date (Text)"
    • Datasource app fields to display: Specify fields as necessary
      Reference: Configuring a "Related records" field

You have now completed the settings.

Example of using the configured settings

  1. In the "Lead Management" app, add a new record with a date.
    The same value will be automatically displayed in the "Date (Calculated)" or "Date (Text)" field.

  2. In the "Customer Management" app, add a new record with the same date as in step 1.

When you open the Record details screen to view the record added in step 2, the Related records field will retrieve and list records with matching dates from the "Lead Management" app.