Designing expressions querying data is an example of reusability by the book. I am sure you already heard that you should follow the Don’t-Repeat-Yourself (DRY) principle. In most applications, you will probably need to query data from the same source in very similar ways multiple times. The most used are:

• Query by the primary key
• Query by a foreign key
• Query using a substring search

To reduce the amount of repeated code, you implement an expression that has a few parameters to define the data source and control the behaviour and call it a day.

Cool, job done!

To DRY or not to DRY

Well, not so fast! I want to add an aspect to this which comes from a big misconception of what Don’t-Repeat-Yourself actually means. Most people think that DRY is about reducing the amount of similar blocks of code. The real meaning is a bit more abstract, and we need to discuss cause and effect. While the effect of DRY is the reduction of lines-of-code, the cause is located on a higher level of abstraction.

An experienced software engineer follows the DRY principle to implement a certain aspect or logic exactly once. And he distinguishes between the definition and implementation of this aspect or logic, and its application.

Let’s get back to Appian and our query data job.

You implemented the following universal expression:

rule!ABC_GetRecordByFilters(
  recordType,
  filters,
  pagingInfo,
  selection,
  aggregation,
  returnData
)

Now, I want to ask two questions:

• Which aspect or logic does this implement or use?
• Is this a significant improvement?

This queries data from some source and allows all sorts of control to make it specific to my use case. And what about the improvement? What does this make different from a!queryRecordType()? Correct, nothing! You even violated the DRY principle, as you need to pass the same set of parameters at all spots in your application which need to query data in a similar way.

Let’s change our perspective! Which problem do you want to solve? Repeated blocks of code, or repeated implementation of concepts? When designing query expressions, I try to formulate the goals of my logic differently. Some examples:

• Get a specific vehicle by its license plate number.
• Get patients living in a specific city.
• Get recipes created in a certain date range.
• Get a specific article by its primary key.

This is what we have to apply the DRY principle on! And this is also the level of abstracted building blocks we need to discuss on in our teams. DRY prevents everybody from having to know the exact name of the license plate field in the vehicle record or the name of the primary key field of an article.

Implement a separate query expression for each defined aspect and give it a descriptive name:

• rule!ABC_Q_GetVehicleByLicensePlateNumber
• rule!ABC_Q_GetPatientsByCity
• rule!ABC_Q_GetRecipesInDateRange
• rule!ABC_Q_GetArticleById

Make use of singular and plural, and try to add all required parameters to the expression’s name. Think about what you want that expression to do in case a mandatory value is not passed. And make all your expressions return values in the same way. I recommend returning a single record item in case of a singular name, and a datasubset, as returned by a!queryRecordType(), in case of a plural name. Add a pagingInfo rule input to any expression with a plural name to allow paging control.

Let’s have a look at the two patterns I strictly follow.

Single Item Query

The concept here is to return a single item of a specific record type. Most of the time, I use the primary key field to query. This specific way of implementation makes sure that the returned data is always of my desired record type.

I added an alternative approach in the comments. This allows me to create an error that is shown as a red box in interfaces and will pause a process by exception. Use this in case you decide that passing a null value must stop any further processing. Think of a money transfer with a null value for the target account.

if(
  a!isNullOrEmpty(ri!id),
  'recordType!Example Record'(),
  /* error("Primary key must not be null!") */
  index(
    a!queryRecordType(
      recordType: 'recordType!Example Record',
      filters: a!queryFilter(
        field: 'recordType!Example Record.fields.id',
        operator: "=",
        value: ri!id
      ),
      pagingInfo: a!pagingInfo(1,1)
    ).data, 1, 'recordType!Example Record'()
  )
)

Multiple Items Query

Whenever a query expression returns multiple items, I return the data structure created by a!queryRecordType(). It contains the data plus some useful metadata. Again, I validate all mandatory parameters first, making sure that the returned data type is consistent. Adding an optional paging info, allows me to control the amount of returned items.

When querying data, always keep memory usage in mind. Retrieving too much data triggers the Appian memory watch dog and leads to overall bad performance. So, I want two dimensions of validation. First, I want to restrict the number of items that can be queried at a time. This is already covered by Appian which caps at 5000 items when querying records. Second, I require passing the list of fields requested when the number of items is above a certain threshold.

if(
  or(
    a!isNullOrEmpty(ri!exampleInput),
    /* Add more checks as required */
  ),
  /* Put below map into a separate expression for convenience */
  a!map(
    success: false,
    startIndex: 1,
    batchSize: 0,
    totalCount: 0,
    sort: null,
    data: {},
    identifiers: {},
    errorCode: "Missing input values"
  ),
  /* Alternative: error("Example input must not be null!") */
  a!queryRecordType(
    recordType: 'recordType!Example Record',
    fetchTotalCount: true, /* Enable total count to get the full set of meta data */
    filters: a!queryLogicalExpression(
      ignoreFiltersWithEmptyValues: true, /* This enables optional parameters. Make sure to check for mandatory parameters above. */
      operator: "AND",
      filters: {
        a!queryFilter(
          field: 'recordType!Example Record.fields.Example Field',
          operator: "includes",
          value: ri!exampleInput
        ),
        /* Add more filters as required */
      }
    ),
    fields: ri!fields, /* Only required when retrieving many items */
    pagingInfo: if(
      isnull(ri!pagingInfo),
      a!pagingInfo(1, 100), /* Adjust the default batch size to your needs, while keeping memory usage in mind. Can also be put in a constant. */
      if(
        and(
          ri!pagingInfo.batchSize > 100,
          a!isNullOrEmpty(ri!fields)
        ),
        /* Error in case of a high batch size and ommited field list */
        error("A batch size above 100 requires listing the retrieved fields!"),
        ri!pagingInfo
      )
    )
  )
)

Summary

Following the above patterns create safe, easy to maintain and debug, use case specific query expressions. Adapt them to your needs, project type and team structure. For any query that is extremely specific like in reports, I typically keep that query inside the parent component.

Apply the DRY principle on a conceptual level and accept similar blocks of code.

And let me know your opinion in the comments below.