I want to use a "Related records" field to retrieve records with matching date values
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.
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.
Configure the following settings for the "Lead Management" app.
-
Add a Date field.
Leave the Default value as it is. -
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)
-
When using a Text field:
- Field name: Date (Text)
- Field code: Date_Text
- Formula: DATE_FORMAT(Date,"MM-dd-YYYY","Etc/GMT")
-
-
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
Configure the following settings in the "Customer Management" app.
-
Place a Date field on the app form.
Leave the Default value as it is. -
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)
-
When using a Text field:
- Field name: Date (Text)
- Field code: Date_Text
- Formula: DATE_FORMAT(Date,"MM-dd-YYYY","Etc/GMT")
-
-
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 -
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.
-
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. -
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.