Filter by Related Objects

A Nintex Apps model pulls in data from one primary data object located in one external system. Most filters work on the fields that are directly pulled from the object. But it's also possible to filter on data from parent or child objects included in the model.

Parent and child objects

In many databases, objects/entities can have relationships, sometimes referred to as master-detail relationships (in Salesforce, for example) or parent-child relationships (in Nintex Apps ). For example, if using the Salesforce connection:

  • A Case object is associated with the Accounts object because any Case would stem from an active account. Cases is the child of the parent object, Accounts.
  • Similarly, the Account object is associated with the Contacts and Opportunities objects: the company surfaced as an opportunity; contacts were logged and deals negotiated; upon signing a deal, the lead converted to an account. So all three objects are linked by common data. Accounts is the parent object of Contacts and Opportunities.

Filtering on a related parent object from a child object can generally be performed using automatic filters. However, filtering on a related child object from a parent calls for a slightly different strategy.

The following is a step-by-step example of creating a filter on one object that uses a filtering field from a related child object. The result is a filter that narrows the list of accounts by the associated location of the account's contacts (the state listed in the contact's mailing or billing address).

In this situation, the account object is the parent; it can exist without any associated child objects, or it can have one (or many) children. The contacts object, on the other hand, is the child of that parent: in a business environment, there is no reason to keep track of contacts unless they are somehow linked to accounts.

Assumptions

Note: 

The example below assumes a Nintex Apps page with:

  • a functioning model on an object that collects data about accounts ( here, the Salesforce Account object )

    • This object has a relationship with an object that stores data about the contacts for those accounts (for example, the Salesforce Contacts object)

  • a Table component on that model with fields for:

    • Account Name
    • Account Description
    • and a few other fields

If using a different connection, the field names indicated may be different.

  1. Under the model name in the Elements pane, click Fields.

  2. In Fields, click the Child Relationship tab.

    • Check Contacts.

  3. In the model's Fields list, click the Contacts field. This opens a list of fields associated with the Contacts child object.

    • Select Name and MailingState/Province.

  4. Drag-and-drop the Contacts field into the Table component.

Important:  Child fields cannot be added using the Add fields dropdown on the component itself.

  1. Click the newly added Contacts field, and configure in the Properties pane:

    • Template: Click the field picker to add the Name and Mailing State fields. The template will read: {{Name}}, {{MailingState}}.
Note: 

To increase readability, separate the field names with punctuation.

  • Delimiter: Use a semicolon.
  1. Click Save.

Now, create a condition to limit the data displayed in the accounts model to those records that actually have contacts.

Because filtering by related child objects can be tricky, this example uses a subquery condition on the primary object (Account) and a sub-condition on the related child object (Contact) to filter the data.

  1. Under the model name in the Elements pane, click Conditions.

  2. Click add Add condition to add a condition that will limit returned records to those that have contacts:

    • Field: Use the field picker to select the Id field for the parent object.

    • Operator: =

    • Value:

      • Content: Result of a subquery.
      • Join object: Contact
      • Join field: AccountId.

    • State:

      • Condition state: Dynamic (Default off).
      • Condition name: Give the condition a helpful name, like AccountId.

Now, filter even more with a sub-condition that limits the account data displayed to those records that actually have contacts with a mailing state indicated.

  1. Click on the condition just created, then click add Add a subcondition.

  2. Click the sub-condition, and make the following edits:

    • Field: Use the field picker to select Mailing State from the child object.

    • Operator: =

    • Value: Leave blank.

    • State:

      • Condition state: Dynamic (Default off).
      • Condition name: Give the condition a helpful name, like MailingState.

Click add Add Add Model to create a model on the related child object—the Contacts object. This model will be used to aggregate the list of states where contacts live.

Then configure that model:

  1. Model behavior: Aggregate

  2. Max # of records (limit): Make blank

  3. Under the model name in the Elements pane, click Groupings, and edit:

    • Grouping method: Simple
    • Grouping field: Select the same geographical string field used in the sub-condition created above (i.e. MailingState).

  4. Click Save.

Time to create the filter.

  1. On the Table component, click Add feature > Table filter.

Note:  You can also add a filter by dragging and dropping a Filter Set component into the page and adding a filter to it.

  1. Click the Filter button, and configure:

    • Filter type: Select option
    • Filter method: Server
    • Pick options and conditions: Manually.
    • Filter label: Contact State
    • Model condition to affect: Choose the sub-condition created above.

The easiest way to add a "none selected" label to a filter is by checking Create "None Selected" option in the Filter Properties, then filling in the "None Selected" option text with a short label.

However, this doesn't always work with conditions and sub-conditions. , add a filter source:

  1. On the filter, click the Sources tab. Click add Add option source, then configure:

    • Source type: Manual.

  2. On the new source, click add Add option and edit:

    • What condition will this option affect?: Affect other conditions
    • Label: The label to go on the filter, for example, "Pick a state."
Add an effect to the filter source

  1. On the filter source, click Add effect and edit:

    • Action: De-activate
    • Model condition: Choose the condition created on the account object, AccountID.

  2. Click Save.

Because this filter uses a sub-condition built on the related child object, the options for the filter must come from that object's model.

  1. On the filter, click the Sources tab. Click add Add option source, then configure:

    • Source type: Rows in a model.
    • Merge source: Contacts (the model on the related child object)
    • Option label template: Use the state field: {{mailingState}}.
    • Which conditions will this source's options affect?: Default condition and others.
    • Value to inject into the default condition: Use the state field: {{mailingState}}.
Add an effect to the filter source

  1. On the filter source, click Add effect and edit:

    • Action: Activate
    • Model condition: Choose the condition created on the account object, AccountID.

  2. Click Save, then click Preview.

The result

The Table component lists all accounts on page load. However, when the filter is activated, the filters narrows the Table's data to show only the accounts that have contacts ( the condition on the parent model ) and from that list, further narrows to those accounts that have at least one contact with a mailing address in the selected state ( the sub-condition on the parent model ).

Troubleshooting

  1. In the primary Model, click Fields.

  2. Click the child field.

  3. In the property pane, click the Properties tab.

    • Max # of records to display: This property defaults to 10, which means that Nintex Apps only displays 10 options. However, if there are many associated records, it may be useful to increase the number, or remove the limit entirely by making it blank.