Dynamic fields are a premium feature that can dynamically display options on one record based off of another record. Depending on the query, it can even display certain options within a field based off of what they choose in a different field (not to be confused with Form Logic which can only Unhide, Require, or Lock fields as a whole). Generally, dynamic fields also create a link between two records that would not usually be related. Dynamic fields can only be a dropdown or a checkbox and do require a query to run (this requires some SQL knowledge). You can purchase consultation time with our Professional Services department to help build queries or help with dynamic fields as they are not supported by Ongoing Support. The scope of this article will be limited.
Enable Dynamic Fields
To see if you have Dynamic Fields enabled navigate to the Administrator tab and then choose Apricot Settings. Once in your Apricot Settings scroll down to the A La Carte Features section. If you have it enabled you will see "Dynamic Fields - Enabled" as picture (and annotated) above.
Types of Dynamic Fields
There are 3 different types of dynamic fields and each require a query to created for them to work properly.
- Dynamic Number: a field which displays a number based off of a different record and can perform extra calculations.
- Dynamic Dropdown: a field which displays data from one record as an option in a dropdown.
- Dynamic Checkboxes: a field which can potentially display data from multiple records as options (multiple options can be selected).
Note: Dynamic Dropdowns and Checkboxes look like their normal field type counterparts (Dropdown field and Checkbox field respectively).
To locate queries within Apricot first click on the Administrator tab and then choose "Queries" in the Navigation Menu.
- The first button in the "Query Actions" palette is "Create New Category" which allows you to create a category. In the screenshot "Queries for Testing" is a category.
- The second button in the "Query Actions" palette is "Create new Query" which allows you to create a new query.
- To see the queries click the triangle next to the category, it will expand and show you the queries. In this screenshot "Volunteer AutoPop" is a query.
- To edit an existing query go to the Actions menu and choose Edit.
For more information on creating or editing queries please contact your Account Manager to purchase consultation time with our Professional Services team.
Add a Dynamic Number to a Form
First we'll want to navigate to the Administrator tab and from the Navigation Menu choose Standard Forms. Then, Edit the form you wish to add the Dynamic Number to.
- In the Field Choices palette expand Numeric
- Then drag Dynamic Number onto the form
- Click the gear icon for the field
- Display Type: choose either Decimal or Numeric. Note: after you publish the form you cannot change your selection
- Query: choose the query that this field should run. Note: after you publish the form you cannot change your selection
- Rerun Query: choose when the query should run. You must choose at least one and you can choose all 3 if needed (If your query includes a dynamic value, there will be a 4th option as well, see below)
- Manually: the query will run only when you click the "Update" button
- When a record is opened: the query will run only when you open a record
- When a record is created: the query will run only when you create a record
- When a dynamic value is changed: the query will run when the dynamic value is changed. A dynamic value is a variable value in the query itself which allows the query to reference other fields within the same form or within it's parent tier 1 form
7. Click Apply when done.
Add a Dynamic Dropdown or Checkbox to a Form
Note: we are going over both dynamic dropdown and checkbox in the same area because they are set up in exactly the same way.
First we'll want to navigate to the Administrator tab and from the Navigation Menu choose Standard Forms. Then, Edit the form you wish to add the Dynamic Dropdown or Checkbox to.
- In the Field Choices palette expand Linking
- Drag either Dynamic Dropdown or Dynamic Checkboxes onto the form
- Click the gear icon for the field
- Choose the form which the query is based on (this is chosen when you create the query)
- Choose which associated query to run
- Choose the Special Properties (all are optional)Show Link: this shows the link on the record
- Show Link: this shows the link on the record after it is saved. This link will display next to the field, in orange, "Go to record" which you can click to go to the record from which the field is pulling data
- Auto Update: this will update the value on the record if the originating record was changed. For example, the dynamic field is displaying "A" from a record of a form entitled "Options." Next, someone changes "A" to "B." This will update the record to show B. Note: This only runs when a record is opened and it will not change Quick View items that reference this field until you re-save the record.
- Allow Previous Value: this will not update the value on the record if the originating record was changed. The exact opposite of Auto Update.
- Note: In most dynamic fields, it is not recommended to enable both Auto Update and Allow Previous Value. This is dependent on the query being referenced by the field.
7. Click Apply when done
Tricks and Tips
- Dynamic (Linking) fields, like Dropdowns and Checkboxes establish a link between the two forms. Do not deactivate the target link on the other form or your field will not work. You may hide it if you don't want users to see the target link.
- Never change the "Select" portion of your query after it has been in use! This will break all links between existing records. You will not be able to report on these records based off of the linking relationship
- You can import into Dynamic Fields (only if it has not been used before i.e., there is no data in the field what-so-ever) but it requires that you import both the "Value" and the "Link." Please note that the value must display exactly as the Dynamic Dropdown would and you'll need the Record ID of the record in which contains the dynamic field plus the Record ID of the record from which the dynamic dropdown pulls its information
- Technical Limitation: While it is possible to import into Dynamic Fields with two imports (1 text and 1 link import) for dynamic fields that hold no data it is not possible to "update" dynamic dropdowns via import. This is because you can't update links via import. If you need to update Dynamic Values you must do so via the standard user interface.
- If you click into a dynamic dropdown and see "Current Value" near the bottom, this means that the link was broken. This may or may not be for a valid reason depending on your setup
- Dynamic fields can reference other dynamic fields, this is what we call a cascading dynamic dropdown (we suggest to limit this to two or three at most)
- Since dynamic fields run queries, the more dynamic fields on a form you have the slower the form might load. It is also true that it can cause slower load times if it is a complex query
- The Ongoing Support team has extremely limited troubleshooting capabilities for dynamic fields and queries. You may need to purchase time with our Professional Services Team.
Real World Example
Let's say we have a Tier 1 form entitled Staff. On the Staff form you track basic demographics about your staff members. Then, we have a Services Tier 2 form that lives under the Client Profile Tier 1 form. We want to track which Staff provided what service. A lookup list could be useful for this but we also know that we need to report our Staff demographics as well. Now we know we at least need a link between Staff and Services. However, we don't want to use the linking interface, we want a dropdown. So, we will use a dynamic dropdown in this scenario.
We create a query that says "Pull "First_Name Last_Name" from Staff where Active is Yes.
We place the dynamic field on the Services form and tell it to reference the Staff form and choose Staff query.