Advanced Reference Groups

Introduction

In their most simple implementation, Reference Groups are used for referencing other records within an instance. One or more columns on an eform are used to reference columns on another eform, so that the data in these fields are matched to a row in the database. Reference Groups are primarily used for sourcing dynamic lists of data from other forms – usually they are used on fields that have drop-down controls attached to them, so that data can be selected from a list.

In this article, the following terms are used:

  • The Referenced EForm is one from which data is referenced.
  • A Local EForm is one that references data on the Referenced EForm. This is, in most cases the current EForm you are working on.

Usage

Reference Groups are created and maintained in the ‘Data’ tab of the EForm Designer for the Local EForm. Note that no design changes need to be made to the Referenced EForm – the way that data is pulled from the Referenced is configured on the Local EForm.

To create a Reference Group, open up the Local EForm in the EForm Designer and click the Data tab at the top. This tab contains two boxes, one for the Local EForm’s data structure (Column configuration) and one for Reference Groups. In the Reference Group box, click Add to create a new Reference Group.

In the new window that appears, give your new Reference Group a name (mandatory) and select which EForm will be the Referenced by selecting it in the Reference eForm dropdown. Create column mappings by clicking the blue ‘add’ link, and filling the Local Column and Referenced Column fields.

  • The Local Column is the column on the Local EForm that you would like to populate data into.
  • The Referenced Column is the column on the Referenced EForm that contains that data you would like to receive.

You can have as many mappings as you like for a group, but remember that one reference group will map to one row on the database. If you don’t want this constraint, you will need to use separate Reference Groups.

Save the EForm or open up a Preview of it to see if the right data is coming into your fields.

Options

Reference Groups can be customized to behave differently.

  • Data can be filtered using a Conditional Filter Expression. This is useful for filtering out results that you don’t need
  • Mappings can have a ‘Display Column’ set which masks the data from the Referenced Column as the data in the Display Column.
    • For example, you could have a reference group that references a RecordID, but set the Display Column to that record’s Description or some other field.
    • Dropdown controls will show the Display Column in the list of options, so be sure to select a Display Column that has unique data or it will be confusing to fill.
  • Mappings can be set to Filter By (default on), which means that data in other mappings will be filtered by the value of that mapping’s local column.
    • Mappings that are not filtering (Filter By set to off) will not have any effect on the options for other columns.
    • Using non-filtering mappings will allow you to see the whole list of options on that field’s drop-down no matter what other fields in the Reference Group have been filled.
  • The Auto-fill behaviour option determines how the Local EForm’s controls will respond to changes in the group’s Local Columns. In many cases it is desirable for other dropdowns in a group to be automatically filled when there is only one result. The options are:
    • Always, which means that the group will fill in as soon as it finds there is only one result
    • On User Input, which only auto-fills if one of the columns in the group has been filled by user input (eg. if a user sets the data in one of the Local columns by hand)
    • Never, which prevents auto-filling except for mappings that do not have the ‘Filter By’ option selected.

Example

In this example let’s say you have this simple set of data for the Reference EForm, called “Staff”

RecordID First Name Last Name Username
1 John Smith jsmith
2 David Thorne dthorne
3 Barbara Smith bsmith
4 John Smith jsmith01

You want to create a reference group that allows users to search for a particular person’s Staff record.

On the Local EForm, you would need to create some columns for this data to be populated into, and then create a reference group to map the data across:

  • Name: Staff Record Link
  • Referenced EForm: Staff
  • Maintain Referential Integrity: Never
  • Auto-fill behaviour: Always
  • Mappings: (format: Local | Referenced | Display | Filter By)
    • Staff First Name | First Name | | true
    • Staff Last Name | Last Name | | true
    • Staff Username | Username | | true
    • Staff ID | RecordID | | false

After you have created this Reference Group, create a Web Link that uses a formula to set its URL to go to the staff record with RecordID set by ‘Staff ID’. The web link should be invisible if there is no Staff ID set.

The Staff ID is set to not filter as in this case, the Staff RecordID is the goal of the search mechanism.

Behaviour

Reference groups have some interesting behaviours that users should be aware of. This section describes how the system works.

Updates

Whenever a record posts back to the server, it performs a record update and checks for formula changes, reference group changes, etc. If a field in a reference group has had its value changed, that group will run an update and change the available dropdown values for each field, according to the current state of the group’s fields. Using the example above:

  • Filling “John” into the Staff First Name field (by selecting a dropdown, or just typing the value into a textbox) will cause the reference group to update.
    • Current status: Staff First Name “John”, all other fields blank
  • The server will get a list of records which have their First Name column set to “John” from the table of Staff records.
  • The result will be two rows:
    • RecordID 1, First Name “John”, Last Name “Smith”, Username “jsmith”
    • RecordID 4, First Name “John”, Last Name “Smith”, Username “jsmith01”
  • Because there is more than one result from the query, no fields will be auto-filled.
  • Filling ‘jsmith’ into the Staff Username field will cause the Reference Group to update again.
    • Current status: Staff First Name “John”, Staff Username “jsmith”, All other fields blank
  • This time there is only one row in the result:
    • RecordID 1, First Name “John”, Last Name “Smith”, Username “jsmith”
  • Because there is only one result, all fields in the reference group are populated
    • Final status: Staff First Name “John”, Staff Last Name “Smith”, Staff Username “jsmith”, Staff ID “1”

Cascading

If more than one reference group has a mapping to the same column, they are overlapping and can behave in unexpected ways.

For example:

Reference Group A maps from the EForm “Company”

  • Local Column “Company Name” mapped to Referenced Column “Company Name”
  • Local Column “Company Location” mapped to Referenced Column “Company Location”
  • Reference Group set to Always auto-fill

Reference Group B maps from the EForm “Contacts”

  • Local Column “Company Name” mapped to Referenced Column “Contact Company”
  • Local Column “Contact Name” mapped to Referenced Column “Contact Name”
  • Reference Group set to On User Input auto-fill

Some data:

Company Name Company Location
Example Company Exampletown
Pretend Company Pretendsville

 

Contact Company Contact Name
Example Company John Smith
Another Company David Thorne

If the user opens up a record of the Local EForm and sets the “Contact Name” field to “John Smith”, Reference Group B will populate the Contact Company into the “Company Name” field. Reference Group A notices that one of its fields has changed, and then tries to populate. It finds a match and fills “Company Location” in as “Exampletown”.

If the user sets “Company Location” as “Exampletown” then Reference Group A will populate, but because Reference Group B is set to auto-fill on user input, it won’t trigger a fill for Group B. This is because none of the fields in Group B were filled directly by user input.

Cascade auto-filling can be useful but it can also lead to undesirable results, so try to avoid overlapping Local columns, and test thoroughly to ensure that the groups behave as intended.

Changes in 4.10

Previous to 4.10, Reference Groups would auto-fill individual columns if there was a single match for a column. The changes in 4.10 have caused a different behaviour – the reference group will only auto-fill if there is a single result that can fill each empty field. This means that in 4.9 if a person worked through the example section, after filling “John” into the First Name column, “Smith” would have been populated into the Last Name column, even though there were two different database rows it could find.

Another consequence of this change is that if the database returns multiple rows with the same data, it will not auto-fill. For example, if the reference group found the following matches, it would not populate:

RecordID First Name Last Name
1 John Smith
1 John Smith

For this reason it is important to check if you are setting up a Reference Group that might return multiple lines on the same record. If you’re only referencing Header fields, insert a Filter to only show rows with LineNumber = 1. This will be faster and provide the same results, while avoiding any auto-fill issues that might arise from multi-line eforms.

Related Articles