Free cookie consent management tool by TermsFeed

a!relatedRecordData() Function

On This Page

References a one-to-many relationship defined on a record type and allows for additional filtering, sorting, and limiting of the related record set.

Related functions:

  • a!queryRecordType(): Use a!relatedRecordData() in the relatedRecordData parameter to filter, sort, or limit one-to-many related records in a query.
  • a!queryRecordByIdentifier(): Use a!relatedRecordData() in the relatedRecordData parameter to filter, sort, or limit one-to-many related records when querying a single record.
  • a!recordData(): Use a!relatedRecordData() in the relatedRecordData parameter to filter, sort, or limit one-to-many related records in a read-only grid.
  • a!sortInfo(): Pass to the sort parameter of a!relatedRecordData() to control the order of returned related records.
  • a!queryFilter(): Pass to the filters parameter of a!relatedRecordData() to narrow the related record set.

Parameters

a!relatedRecordData( relationship, limit, sort, filters )

Keyword Type Description

relationship

Record Type Relationship

A reference to a one-to-many record type relationship, configured using the recordType! domain. For example, recordType!Department.relationships.employees.

limit

Number (Integer)

Number of related records to return. If you're using this function in a!queryRecordType() or in a records-powered component, the maximum number is 100. If you're using this function in a!queryRecordByIdentifier(), the maximum number is 250.

sort

List of SortInfo

Array of Sort Info configurations created with a!sortInfo(). When defining the field to sort on, reference a record field or related record field starting from the related record type. For example, sort on the field recordType!Employee.fields.status when relationship is recordType!Department.relationships.employees.

filters

Any Type

A single logical expression or a list of query filters, which are applied together with an AND operation, can be provided to apply additional filters to the record set. When defining fields to filter on, reference record fields or related record fields starting from the related record type. For example, filter on the field recordType!Employee.fields.status when relationship is recordType!Department.relationships.employees. Queries also inherit the default filters defined on the referenced record type.

Returns

List of RelatedRecordData

Using the function

The following sections cover important details about the a!relatedRecordData() function.

Supported record types

The a!relatedRecordData() function is only supported for record types that have a one-to-many relationship with another record type.

Where to use a!relatedRecordData

Use a!relatedRecordData() to filter, sort, or limit the related record data returned from a one-to-many relationship.

The a!relatedRecordData() function can be used in the relatedRecordData parameter of the following functions:

The Examples section demonstrates sample configurations for each use case.

Aggregation limitation

The a!relatedRecordData() function cannot be used when performing an aggregation in a!queryRecordType() or in a records-powered chart.

Default and maximum limits

The maximum number of related records returned for each base record varies depending on the calling function. If a!relatedRecordData() does not specify a limit, a default number of related records is returned.

Function Maximum related records returned Default related records returned
a!queryRecordType() 100 10
a!recordData() 100 10
a!queryRecordByIdentifier() 250 100

For example, when querying multiple customers and their related support cases using a!queryRecordType(), the query returns up to 10 related support cases for each customer if no limit is applied.

How to reference fields in filters and sorts

When applying a filter or sort to a!relatedRecordData(), the field reference must start from the related record type specified in the relationship parameter.

For example, the Department record type has a one-to-many relationship with the Employee record type. To return only employees in each department that have a status of "Active", the filter field starts from recordType!Employee:

1
2
3
4
5
6
7
8
9
10
11
12
13

   relatedRecordData: {
   /*Only return related Employees that have a status of active*/
    a!relatedRecordData(
!    relationship: recordType!Department.relationships.employee,
     filters: {
        a!queryFilter(
!         field: recordType!Employee.fields.status,
          operator: "=",
          value: "Active"
          )
        }
      )
    }

Using smart search in a!relatedRecordData

If the related record type has smart search enabled, you can use the following smart search operators in the filters parameter of a!relatedRecordData():

  • "semantic search": Performs an AI-powered search based on the intent and meaning of the query.
  • "lexical search": Performs a full-text search based on exact text matches.

Smart search in a!relatedRecordData() is supported only when the function is used in a!queryRecordType() or a!recordData() in a read-only grid. Smart search cannot be applied to a!relatedRecordData() when the function is used in a!queryRecordByIdentifier().

For example, you can use a!queryRecordType() to query customers with active support cases, and filter their related support cases to only those semantically similar to "outages". The query returns customers with active cases and the related cases about "network problems" or "connection failures". See the Return related customer cases semantically similar to a search term example for a full expression.

To apply smart search in a!relatedRecordData(), reference a field from the related record type specified in the relationship parameter. The field in the a!queryFilter() expression must belong directly to that related record type. Smart search cannot filter on fields from a nested relationship (a relationship from the related record type).

For example, the Customer record type has a one-to-many relationship to the Case record type, and the Case record type has a one-to-many relationship to the Comments record type. To apply smart search on related cases, reference a field from the Case record type:

Supported smart search filter:

1
2
3
4
5
6
7
8
9
10

a!relatedRecordData(
!  relationship: recordType!Customer.relationships.case,
/* Apply smart search on a field from the related record */
/* type referenced in the relationship parameter         */
  filters: a!queryFilter(
!    field: recordType!Case.fields.description, 
     operator: "semantic search",
     value: "outage"
  )
)

Smart search cannot apply to a field from the Comments record type:

NOT supported smart search filter:

1
2
3
4
5
6
7
8
9

a!relatedRecordData(
!  relationship: recordType!Customer.relationships.case,
  /* You cannot apply smart search on a nested relationship */
  filters: a!queryFilter(
!    field: recordType!Case.relationships.Comment.fields.description, 
     operator: "semantic search",
     value: "outage"
  )
)

See the Return related customer cases semantically similar to a search term example for a full expression.

Sort behavior for smart search results

The a!relatedRecordData() function does not automatically sort smart search results by similarity score. You cannot sort by any searchResults properties in a!relatedRecordData(). Instead, the related records are sorted by default by the primary key field in ascending order.

To keep results in a logical order, consider sorting smart search results by a created on date or title.

Examples

The following examples demonstrate how to use a!relatedRecordData() in the a!queryRecordType() function. The examples use the Customer record type, which has a one-to-many relationship with the Case record type, to return information about customers and their related support cases.

Record type object references are specific to each environment. If you copy and paste these examples into your interface, they will not evaluate. Use them as a reference only.

See the a!recordData() function for an example using a!relatedRecordData() in a read-only grid.

Return the latest customer support case

The following expression returns customer data with only the most recent support case for each customer. The a!relatedRecordData() function sorts cases by creation date in descending order and limits the result to 1.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

a!queryRecordType(
  recordType: recordType!Customer,
  fields: {
    /*Fields from the Customer record type*/
    recordType!Customer.fields.name,
    recordType!Customer.fields.phoneNumber,
    
    /*Related fields from the Case record type*/
    recordType!Customer.relationships.cases.fields.title 
  },
  relatedRecordData: {
    /*Only return the latest support case for a customer*/
    a!relatedRecordData(
      relationship: recordType!Customer.relationships.cases,
      sort: {
        a!sortInfo(
          field: recordType!Case.fields.createdOn,
          ascending: false
        )
      },
      limit: 1
    )
  },
  pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 500
  )
)

Sample output from the query above:

Returns the latest customer support case for each customer

Filter customer cases by a status of "critical"

The following expression returns customer data with only support cases that have a priority of "Critical". The a!relatedRecordData() function applies a filter on the related Case record type's priority label.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

a!queryRecordType(
  recordType: recordType!Customer,
  fields: {
    /*Fields from the Customer record type*/
    recordType!Customer.fields.name,
    recordType!Customer.fields.phoneNumber,
    
    /*Related fields from the Case record type*/
    recordType!Customer.relationships.cases.fields.title 
  },
  relatedRecordData: {
    /*Only return related cases that have a priority of critical*/
    a!relatedRecordData(
      relationship: recordType!Customer.relationships.cases,
      filters: {
        a!queryFilter(
          field: recordType!Case.relationships.priority.fields.label,
          operator: "=",
          value: "Critical"
        )
      }
    )
  },
  pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 500
  )
)

Sample output from the query above:

Returns only cases that have a status of critical

Tip:  Since there is no limit specified in a!relatedRecordData(), the query returns a maximum of 100 support cases for each customer.

Return the latest critical case and latest case comment

The following expression uses two a!relatedRecordData() configurations to return customer data with the most recent critical case and its latest comment. The first a!relatedRecordData() filters cases by priority and limits to 1. The second a!relatedRecordData() limits comments to 1, sorted by creation date.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

a!queryRecordType(
  recordType: recordType!Customer,
  fields: {
    /*Fields from the Customer record type*/
    recordType!Customer.fields.name,
    recordType!Customer.fields.customerSince,

    /*Related fields from the Case record type*/
    recordType!Customer.relationships.cases.fields.title,
    recordType!Customer.relationships.cases.fields.status,

    /*Related fields from the Comments record type*/
    recordType!Customer.relationships.cases.relationships.comments.fields.commentText
  },
  relatedRecordData: {
    /*Only return the latest case with a priority of critical*/
    a!relatedRecordData(
      relationship: recordType!Customer.relationships.cases,
      filters: {
        a!queryFilter(
          field: recordType!Case.relationships.priority.fields.label,
          operator: "=",
          value: "Critical"
        )
      },
      sort: {
        a!sortInfo(
          field: recordType!Case.fields.createdOn,
          ascending: false
        )
      },
      limit: 1
    ),
    /*Only return the latest comment associated with each critical case*/
    a!relatedRecordData(
      relationship: recordType!Customer.relationships.cases.relationships.comments,
      sort: {
        a!sortInfo(
          field: recordType!Comments.fields.createdOn,
          ascending: false
        )
      },
      limit: 1
    )
  },
  pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 500
  )
)

Sample output from the query above:

Returns only cases that have a status of critical and the latest related case comment

The following expression uses smart search in a!relatedRecordData() to return active customers and their related cases that are semantically similar to "outage". Cases about "network problems" or "connection failures" are returned because their meaning is similar to "outage".

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

a!queryRecordType(
  recordType: recordType!RE Customer,
  fields: {
    /*Fields from the Customer record type*/
    recordType!RE Customer.fields.displayName,
    recordType!RE Customer.fields.isActive,

    /*Related fields from the Case record type*/
    recordType!RE Customer.relationships.case.fields.title,
    recordType!RE Customer.relationships.case.searchResults.title
  },
  filters: a!queryFilter(
    field: recordType!RE Customer.fields.isActive,
    operator: "=",
    value: true
  ),
  /* For each active customer, return case semantically similar to "outage" */
  relatedRecordData: a!relatedRecordData(
    relationship: recordType!RE Customer.relationships.case,
    filters: a!queryFilter(
      field: recordType!RE Case.fields.title,
      operator: "semantic search",
      value: "outage"
    )
  ),
  pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 5)
).data

Sample output from the query above:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

data (List of Customer)
  Customer
    displayName: "Acme Corp"
    isActive: true
    case:
      Case
        title: "Network connectivity failure affecting east region"
        searchResults.title
          similarityScore: 0.9129736
      Case
        title: "Intermittent service disruption on production servers"
        searchResults.title
          similarityScore: 0.8745219
      Case
        title: "Complete system downtime after scheduled maintenance"
        searchResults.title
          similarityScore: 0.8412853
  Customer
    displayName: "TechNova Inc"
    isActive: true
    case:
      Case
        title: "Repeated connection drops during peak hours"
        searchResults.title
          similarityScore: 0.8836471
      Case
        title: "Data center power loss causing service interruption"
        searchResults.title
          similarityScore: 0.8503692
  Customer
    displayName: "GlobalEdge Solutions"
    isActive: true
    case:
      Case
        title: "Unplanned downtime on client-facing application"
        searchResults.title
          similarityScore: 0.9218354

The following expression uses smart search to return cases and their related comments that are semantically similar to the search term "print".

In the following expression:

  • The filters parameter of a!queryRecordType() applies a semantic search to the description field on the Case record type, returning only cases with a similarity score of 0.3 or greater.
  • The relatedRecordData parameter of a!queryRecordType() applies a semantic search on the related Comment record type's description field, returning only comments with a similarity score of 0.3 or greater.
  • Cases are sorted by similarity score in descending order, while related comments are sorted by creation date since sorting by similarity score is not supported in a!relatedRecordData().
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

a!localVariables(
  local!searchTerm: "print",
  a!queryRecordType(
    fetchTotalCount: true,
    recordType: recordType!Case,
    fields: {
      recordType!Case.fields.id,
      recordType!Case.fields.title,
      recordType!Case.fields.description,
      recordType!Case.relationships.comments.fields.description,
      /* Returns the similarity score for searches on */
      /* the Case record type and the related Comment record type*/
      recordType!Case.searchResults.description.similarityScore,
      recordType!Case.relationships.comments.searchResults.description.similarityScore
    },
    /* Return cases semantically similar to the search term */
    /* that have a similarity score of 0.3 or greater */
    filters: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        a!queryFilter(
          field: recordType!Case.fields.description,
          operator: "semantic search",
          value: local!searchTerm
        ),
        a!queryFilter(
          field: recordType!Case.searchResults.description.similarityScore,
          operator: ">=",
          value: 0.3
        )
      }
    ),
    /* For each returned case, only return comments */
    /* semantically similar to the search term that */
    /* have a similarity score of 0.3 or greater*/
    relatedRecordData: a!relatedRecordData(
      relationship: recordType!Case.relationships.comments,
      filters: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          a!queryFilter(
            field: recordType!Comment.fields.description,
            operator: "semantic search",
            value: local!searchTerm
          ),
          a!queryFilter(
            field: recordType!Comment.searchResults.description.similarityScore,
            operator: ">=",
            value: 0.3
          )
        }
      ),
      /* Sort comments by created on date */
      /* Note: Sorting by similarity score is not */
      /* supported in the a!relatedRecordData() function */
      sort: {
        a!sortInfo(
          field: recordType!Comment.fields.createdOn,
          ascending: false
        )
      }
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 10,
      /* Sort cases by similarity score */
      sort: a!sortInfo(
        field: recordType!Case.searchResults.allSearchFields.similarityScore,
        ascending: false
      )
    )
  ).data
)

Sample output from the query above:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

data (List of Case)
  Case
    id: 24
    title: "Printer queue stuck"
    description: "The printer queue for the main office printer seems stuck. Print jobs from multiple users are just sitting in the queue not printing. Restarting the print spooler service didn't resolve the issue."
    comments: (none)
    searchResults.description
      similarityScore: 0.3812435
  Case
    id: 14
    title: "Printer not responding"
    description: "Office printer is not responding to print commands from multiple computers."
    comments:
      Comment
        id: 34
        description: "Remote employee unable to connect to VPN. Verified credentials and provided troubleshooting for network setup."
        searchResults.description
          similarityScore: 0.3539561
      Comment
        id: 35
        description: "Overheating issue causing random shutdowns on marketing workstation. Cleaned fans and reapplied thermal paste."
        searchResults.description
          similarityScore: 0.3407176
      Comment
        id: 38
        description: "Server running low on available RAM. Worked to identify and stop unnecessary services to free up memory."
        searchResults.description
          similarityScore: 0.3347864
    searchResults.description
      similarityScore: 0.3770905
  Case
    id: 62
    title: "Printer queue crashing"
    description: "The main print server handling print queues for the office printers has been crashing multiple times a day over the past week. Open print jobs are being lost each time it crashes."
    comments: (none)
    searchResults.description
      similarityScore: 0.3770905
  Case
    id: 109
    title: "Network printer offline issues"
    description: "Users have reported that one of the main network printers used for high-volume printing seems to frequently go into an offline/disconnected state where print jobs queue indefinitely before printing."
    comments: (none)
    searchResults.description
      similarityScore: 0.3770905
  Case
    id: 46
    title: "Server outage impact"
    description: "A number of our internal business systems and applications were impacted by the recent unplanned outage at one of our data centers last night. We need to assess the full scope of impact on different apps and databases."
    comments: (none)
    searchResults.description
      similarityScore: 0.3754545
  Case
    id: 36
    title: "Certificate expired"
    description: "I'm getting security errors when trying to access a vendor site due to an expired SSL certificate. Accessing the site through the browser as well as APIs seems to be impacted. The errors started earlier today."
    comments: (none)
    searchResults.description
      similarityScore: 0.3738327
  Case
    id: 38
    title: "API endpoint errors"
    description: "External partners are reporting errors when trying to access and consume data from some of our public-facing API endpoints. Some data seems to be missing or malformed in the responses they receive."
    comments: (none)
    searchResults.description
      similarityScore: 0.3690502
  Case
    id: 16
    title: "Network outage"
    description: "Major internet service provider experienced a widespread network outage."
    comments: (none)
    searchResults.description
      similarityScore: 0.3651572
  Case
    id: 100
    title: "Website performance issues"
    description: "Our main public-facing website has become extremely slow and unresponsive during peak traffic hours. This poor performance is likely driving customers away and negatively impacting sales/conversions."
    comments: (none)
    searchResults.description
      similarityScore: 0.3651572

Feature compatibility

The table below lists this function's compatibility with various features in Appian.
Feature Compatibility Note
Portals Compatible
Offline Interfaces Compatible
Sync-Time Custom Record Fields Incompatible
Real-Time Custom Record Fields Incompatible

Custom record fields that evaluate in real time must be configured using one or more Custom Field functions.
Process Reports Incompatible

Cannot be used to configure a process report.
Process Events Partially compatible

If autoscale is enabled, can be used to configure a supported process event node (such as a start event or timer event).
Process Autoscaling Compatible

Can be used in autoscaled processes.
Event Consumer Filter Conditions Incompatible

Cannot be used in filter conditions.
Event Consumer Event Handlers Incompatible

Cannot be used in event handler expressions.

Feedback