Linked Calculations

Linked calculations allow for data collected in one form to be shown and used in another. This allows for quicker, more efficient data entry, because commonly repeated information doesn't need to be re-entered if it can be drawn from elsewhere in the site. This saves time, but, importantly, also maintains data consistency.

Fields can be linked into a form from other event forms (linked calculations) or profile forms (profile linked calculations). Linking data from multiple forms into a single event form can be useful when you want to summarize data from different areas in one place, allowing this information to be combined and compared directly. For example, a GPS session event form can also display the athletes' rating of perceived exertion (RPE) value from an RPE survey event form, and be used in conjunction with the session duration or another GPS metric to calculate the session load.

Linked calculations can be used to link fields from one event form to another event form. Profile linked calculations can be used to link fields from a profile form to an event form or from one profile form to another profile form.

Similarly, an availability event form can display an athlete's current injury status and any training restrictions from an injury event form, so coaches can see the athlete's training capabilities without having full access to medical data.

Linking profile information into an event form helps people understand more about the person they're looking at, without having to review the specific profile forms where the data originated from. These calculations can also be useful for filtering in reports and dashboards.

Some examples of fields that could be linked from profile forms include nationality, contact information and, in the case of athletes, sport specific information such as sport type, level, team and position.

Types of linked calculations

You can link values, options, dates, times (from event forms only) and text with linked calculations. The linked calculations that are available to link fields from an event form are:

• Linked value: display numeric values entered by a user in answer to a field in another event form.
• Linked text: display text entered by a user in answer to a field in another event form.
• Linked option: display an option entered by a user in answer to a field in another event form.
• Linked date: display a date entered by a user in answer to a field in another event form.
• Linked time: display a time entered by a user in answer to a field in another event form.

The profile linked calculations that are available to link fields from a profile form are:

• Profile linked value: display a value entered by a user in answer to a field in a profile form.
• Profile linked text: display text entered by a user in answer to a field in a profile form.
• Profile linked option: display an option entered by a user in answer to a field in a profile form. 
• Profile linked date: display a date entered by a user in answer to a field in a profile form.

A date field can be referenced by a Linked value calculation to return the date and time (in milliseconds) of the linked event form; however, the index number that results will default to 12 pm of the relevant date. If the event date is referenced, then the result will include information about the time as well.

Controlling linked data

When you link information from one form to another, you should choose the type of linked calculation based on the data type you want to display. To control which value should appear, there are several methods you can use, depending on when the data was entered in the linked field, the value of the linked data relative to other records of the source form, or the value of the linked data relative to the data in the current record.

You can achieve this by:

• Setting the advanced properties for linked calculations.
• Selecting which value to link from the source form, such as the newest, oldest or largest.
• Setting a date range for the data source.
• Specifying the number of records.
• Filtering the data with the Group By and Match To settings.

Setting the advanced property for the linked calculation

The Linked update range advanced property can be modified for all linked calculation types to specify which data is shown. The option you select determines the cut-off point for data that can be displayed by the calculation. Setting this property carefully can help ensure that the correct result is returned, particularly in cases where the event form is filled out multiple times in one day.

The options that are available are:

• Start of previous day: only variables that are entered before the start of the previous day will be pulled through.
• End of previous day: only variables that are entered before the end of the previous day will be pulled through.
• Start of current day: only variables that are entered before the start of the current day will be pulled through.
• End of current day: only variables that are entered before the end of the current day will be pulled through.
• Start of current event: only variables that are entered before the start of the current event start time will be pulled through.
• End of current event: only variables that are entered before the end of the current event start time will be pulled through.

If no option is selected, the most recent variable that matches the date of entry will be pulled through.

Even though End of current day and End of current event may include future records, the linked calculation result will not automatically update if a new record is added. The result will only be updated if the record is resaved.

Profile linked values, text, options and dates have no association with a date or time and will display in all records regardless of whether the profile information was saved before or after the record being viewed.

Setting the Manual update advanced property to True will show the event form you are linking data from as a button at the top of the current event form during data entry. Selecting the button will open a new record for the event form. This may be useful if you want to updated the linked calculation result, but in most cases it should be set to False.

In some instances, the linked calculation may refer to a record that does not contain a value for the selected field. Setting the Ignore blanks advanced property to True will ignore any blank records and return the next record that contains a value and matches the linked calculation criteria.

Selecting which value to link from the source form

You can use this setting to specify which value you’d like to link from the source form. There are several options to choose from:

• Newest: returns the value from the newest (most recent) record of the linked form.
• Maximum: returns the maximum (highest) value from the linked form.
• Minimum: returns the minimum (lowest) value from the linked form.
• Oldest: returns the value from the oldest (least recent) record of the linked form.
• Current state: will return a value based on the worst state of the person. To use this option, the field referenced in the linked calculation must be enabled as a Status indicator.
• Current poor state: similar to Current state, this setting will return a value based on the worst state of the person but you can limit the worst status to specific statuses, rather than including all status options. This may be useful if you wanted to link a value (e.g. injury diagnosis) from records where the person's status is Unavailable or Modified but not Available. To use this option, the field referenced in the linked calculation must be enabled as a Status indicator.
• Dynamic date: returns the value from a specific date based on a date (date field, date calculation or table date calculation) in the destination form. The exact date recorded within this field will determine which date the value is able to be pulled from.

Linked calculations to determine availability status

Linked calculations can be used to summarize availability status from multiple sources and return the most up-to-date status for a person. This is similar to how status indicators work in Smartabase Online but unlike status indicators, linked calculations can be referenced in other event forms and in dashboards.

The configuration of your availability workflow will determine which value to link from the source form. For example, if status is indicated in an Injury Record event form which is updated as the patient recovers, then the worst status from all injury records would indicate their availability. To link this availability status into another form, a linked calculation should use the Current state option to return the worst value.

Alternatively, if status is indicated in a Daily Availability event form which is filled out by a staff member every day, the latest status would indicate their availability. In this case, the linked calculation should use the Newest option to return the latest value.

If you have a scenario where you only want to include some of the status options to calculate availability, you could use the Current poor state option to selectively include those options. For example, if the status options are Unavailable, Modified and Available but you only want to include records where the status is Available or Modified, you could use this option. If there are no injuries with an Unavailable or Modified status, the linked calculation field will be blank.

You can use the Current state and Current poor state options for other fields in the form - they are not limited to status fields. For example, you could use one linked calculation with Current state selected to pull through the worst current status and then use a second linked calculation with Current state selected to pull through the injury associated with that status. If there are multiple injuries with the worst status, only the newest injury will be shown.

To determine the worst current status for a person, it is important that the status fields are configured correctly in the original form (e.g. the Injury Record or Daily Availability forms in the examples above). All status options must have a unique score with the worst status (e.g. Unavailable) scored as 1 and the best status scored highest, and the advanced field property Best value must have the best status option selected (e.g. Available).

Applying a date restriction to the data source

Time ranges can be specified using the Date restriction section of the Calculation tab. The calculation will be restricted to linking a value from records within the time range that you specify.

The options available are:

• All history: includes all past records.
• All (history and future): includes all past records. Linked calculations cannot reference linked records with an event date and time after the current record, so while this option is included (as it is in all date restriction areas of Smartabase, like the performance history), it works the same way as All history.
• Date range: limits the records to be included by setting a start date and an end date for the linked calculation.
• Last: allows the builder to choose a number of days, weeks, months or years to include in the linked calculation. All counts start from the day the record is saved.
  • Days: using last with seven days on a Monday will not go past the previous Tuesday.
  • Months: takes the number of days in the previous month and use that as the range. For example, if a calculation is set to last one month and the event is saved in February, it would be the same as using the last 31 days because January has 31 days. If an event is saved in March with the same settings, it will be the same as using the last 28 days because February has 28 days. This means that the calendar date of the start date is the same as the current date – February 12 to March 12, for example.
• All upcoming: will not include any records. Linked calculations cannot reference records from after the current record, so while this option is included, it is not relevant to linked calculations.
• Last custom: allows the builder to control where the cumulative week or month starts and ends. If a calculation uses the option Last custom 1 week, Monday-Sunday, it will reset every Monday morning. This means that if you are linking the most recent value from the linked form on a Wednesday, it will only go back until Monday (two days) because that’s when the week starts. The same applies for months, except that it is possible to specify on which day the month starts. For example, if set to Last custom 1 month and the 10^th day is specified, the calculation will reset on the 10^th, so if a record is saved on the 12^th, it will only go back two days.
• Today: include all records entered with the same date as the current record.

Specifying the number of records to include in the data source

The number of records to include can also be specified using the Date restriction part of the Calculation tab. The options here are:

• Include all
• Include the most recent record
• 2 most recent records → 9 most recent records

These options can be used in conjunction with date restrictions. For example, you could choose to return a value from the six most recent records of the last four weeks.

Filtering the data with group and match settings

You can also link data from another event form based on a specific matching criteria using the Group by and Match to settings. When these settings are used, the linked calculation will only display information from a record where the Group by value (in the linked form) exactly matches the Match to value (in the destination form). If you aren't using this setting, you can leave the Group by and Match to fields blank.

To enable this setting, go to the Calculation settings in the sidebar and set up the form and field for the linked calculation. Search the Group by dropdown menu for the field in your linked form that you want to group the data by, then search the Match to dropdown menu for the field in the current form that you want to match with the linked form. You can only use this setting with option fields or option calculations, so you will only see these field types in the dropdown menus.

In this example, we're going to look at a GPS session event form. For this workflow, the athletes will participate in the session, which will be recorded by a third-party GPS unit, and integrated into Smartabase automatically.

Directly after the completion of the session (before the data is uploaded into the GPS platform), each player will log in and record their RPE for the session in a form called RPE. They also specify that the RPE value is for the day's field session, which will come into play later.

Next, we want to display the RPE value in our GPS session form to help calculate the session load. However, because an athlete may have more than one training session per day, we need to make sure we retrieve the RPE value recorded for the field session. To do this, we've added an option calculation to the GPS session form called Session Type, which simply returns Field Session.

Once this is set up we can create a new linked value calculation, which we've called Linked RPE Score. This calculation references the RPE Score field from the RPE form. In the advanced properties, the linked update range is set to End of current day so any data up until the end of that day can be linked into the form.

Importantly, we've chosen the Session Type field from the RPE form as the Group by, and the Session Type field from the GPS Session form as the Match to. This means the field will only link data from an RPE record where Session Type is equal to Field Session. Finally, we instruct the calculation to return the newest value and restrict it to today's data only. This means if an athlete didn't fill out their RPE for the session, the calculation won't pull through a value from a field session on a previous day.

Now that the RPE value is correctly linked into the GPS Session event form, we can calculate the Session Load as the RPE multiplied by the Session Duration.

Steps to create a linked calculation

1. Log into the builder interface.
2. Select the Event forms tool.
3. Create an event form or open an existing event form.
4. Select the type of linked calculation needed on the Add question tab and add this field. If applicable, set up the options in the Options tab.
5. In the Advanced properties tab, set the linked updated range.
6. In the Calculation tab:
   • Choose the form that you are linking data from and the specific field to link.
   • Select which value to link from the source form.
   • Set a date range and number of records to include in the data source.
   • Filter the data with the Group by and Match to settings if applicable.
7. Continue working on the form or save the form.