With the proper formula syntax, there are endless possibilities for the available functions and operators in Apricot Results Reporting (ARR) to customize your report data. For example, you can build formulas that change the format of a text, numeric, or date value. You can also create variables that isolate first or last responses of a form, concatenate data from multiple fields into a single column, and much more.

To learn how to create a new variable, click here. Continue reading below to learn popular formulas for common Results reporting needs.

This replaces empty or blank cells in the report column with whichever text label your organization may use while showing the actual values for those who have do have responses to this field:

This flag will return a "1" in a column if the chosen Data Object contains data. Then, filter on the column for the value "1" to only show rows that contain data.

Be sure to replace [Date Field] with what you want to define as "most recent" (ex. most recently created, modified, or enrolled):

An example of this formula may look like:

Replace the text between the asterisks to count the number of responses that include that option value either as the only option or among other options within the response:

OR

Note: The CurrentDate function can be replaced if you need to know a participant’s age as of a specific date.

You can change the actual numbers of these ranges as well as the text within the quotation marks to group and label them as you need:

Replace [Time Field_XX] in the formula with the object from your query:

Replace the text between brackets with the number object you want to reformat:

Some functions (such as the Left, Right, or Match functions) need a string of text in order to work as opposed to a date or number. You can add a plus sign and two quotation marks after the object in the formula to convert it to text and let you use any text-related function:

This formula will add a certain number of days to the selected date object. Change the number after the semi-colon to however many days you want to add:

Use either the Month or MonthNumberOfYear functions, depending on how you want it to show. The Month function will show the month fully written as text (ex. "July"). The MonthNumberOfYear function will show the month as a number (ex. "7").

OR

For the example date of 7/11/2020, this formula would show a value of "11".

This formula would display the day of the week, Saturday, for the same date.

This function would return the value "6" for this date, as Saturday is the 6th day of the week:

When working with dates, you may need to change the way the date is formatted, either for aesthetic purposes, or so you can use the date in another formula. Either way, this can be done with the FormatDate function.

This function is set up in the following way:

=Formatdate([Date Object];"Date Format")

The date format is the string of text that will decide how the date is displayed, and will always have quotation marks around it.

Lets say you have the date July 4th, 2021 showing for the object [Date Object]

You could set it up the formula in a few ways:

You can even add the day of the week into the date by formatting the day as dddd dd. For example, =FormatDate([Date Object];"dddd, Mmmm dd, yyyy") would show as Sunday, July 04, 2021

When you are recording dates and times, you may need to see how long it is between two specific dates with times. This can be done using the timebetween function. This function lets you find the time between two dates with times, and choose how you'd like it shown (Minutes, Hours, Days etc).

The formula will be set up as follows:

=timebetween([Date with time 1],[Date with time 2],period/unit of time)

The period/unit of time will be how you want the time to be measured. The options are as follows

For example, lets say you have two date objects with times:

If you wanted to find the amount of hours between these two objects, you would set up a formula like:

=TimeBetween([Datetime A];[Datetime B];HourPeriod)

This shows that there are 27 hours between 12/21/21 at 3:00 PM and 12/22/21 at 6:00 PM.

When creating a report, you may have strings of text you need to cut down. This can be used for things such as turning a name into an initial or showing only the last four digits of a Social Security Number.

This can be done using the "Left" or "Right" functions.

These functions show only a certain number of characters in a string; the "Left" function showing a certain amount of characters starting from the left, and the "Right" function showing a certain amount starting from the right.

The formulas are set up as follows:

Left:

=Left([String of text object];number of characters to show)

For example, if you wanted to get the first Initial in someone's Last Name, you would do the formula =Left([Last Name];1)

Right:

=right([String of text object];number of characters to show)

For example, if you wanted the last 4 digits of a Social Security Number, you would need to first convert the number to a text string*, then do the formula =Right([SSN Text Variable];4)

When reporting, you may need to change the capitalization of a string of text to make all the letters upper-case or lower-case.

To make all letters capital, you can use the upper function.

For example, if the [State] object returns the text "Maryland," you could use the formula =upper([State]) to return the text "MARYLAND," which will have all the letters capitalized.

Conversely, you may want to remove all capitalization. to do this, you could use the lower function.

For example, if the [State] object returns the text "Maryland," you could use the formula =lower([State]) to return the text "maryland," which will make the letter "m" lowercase.

In some situations, you may just want the first letter of a string to be capitalized. This can be done using the Initcap funciton.

For example, if the [Notes] object returns the text "he will see a doctor next week," you could use the formula =initcap([Notes]) to return the text "He will see a doctor next week," which will capitalize the "H" at the beginning of the text string.

Sometimes you need to capitalize the first letter of every word in a string of text. This can be done using the wordcap function.

For example, if the [Family member name] object returns the text "john smith," you could use the formula =wordcap([Family member name]) to return the text "John Smith," which will capitalize the "J" and "S" at the beginning of each word.

When working in Apricot Results, you may want to round a number down or up in order to make your data more succinct. In order to do this, you can use the floor or ceil functions.

The floor function rounds the number down to the nearest whole number. Formulas using this function will be set up as follows:

=floor([Variable with Number])

For example, lets say you have a variable [Age in Years] that is equal to 1.8. Since it is years, you would want to round down to 1. To do this, you would use the formula =floor([Age in Years]).

Alternatively, the ceil function will round up. Formulas using this function will be set up as follows:

=ceil([Variable with Number])

For example, lets say you have a variable [Average Days in Program] that is equal to 30.15. You would want to round up to 31. To do this, you would use the formula =ceil([Average Days in Program]).

One helpful thing to remember is that in floors are the lowest point in a room, and the floor function lowers a number, while ceilings are the highest point in a room, and the ceil function raises a number up.

When working in Apricot Results, you may need to combine or concatenate data. This is helpful for actions such as making new Identifiers, changing name formatting, or even formatting a date differently.

There are a few methods to do this. If you are trying to combine any type of object or number with text strings, you can just put a plus sign (+) between the two (or more) objects or text strings you are trying to concatenate.

For example, let's say you are counting students that went on a trip in a variable called [Students]. You could set up the formula like =[Students]+" Total Students."

If the total number of students is equal to 14, variable would show "14 Total Students" because the number of students in the [Students] variable would be concatenated with the text string "Total Students."

If you need to concatenate two numbers, on the other hand, you cannot use the plus sign. This is because it would just give you a sum of the two numbers. Instead, you could use the Concatenate function. For example, if we're trying to add a Form Identifier and Record ID to make a new identifier, we could make a variable saying =Concatenation([Form ID];Concatenation("_";[Record])). If the Form ID is 635 and the Record ID is 154, this formula would give us "635_154."

More Examples:

If [A] is a number and [A] = 1, Concatenation([A];[A]) returns "2".

If [A] is a string and [A] = 1, Concatenation([A];[A]) returns "11".

If [A] is a string, [B] is a number, [A] = 1 and [B] = 2, Concatenation([A];[B]) returns "12".