Displaying Age Groups

Current age:

Pull in the Date of birth column and adjust the properties to display the “age” data style.
Pull in a Number Calc column.
Open the Number Calc column properties, select the data style “Custom Numeric” and select your data of birth column as the first calculation column.
In the Number Calc column properties, use the expression below and alter as needed.

IF([column] between 1 and 18, "under 18", IF([column] between 18 and 65, "18 to 65", IF([column] >65, "Over 65", null)))
IF([column] between 1 and 18, "Minor", IF([column] between 18 and 65, "Adult", IF([column] >65, "Senior", null)))

Age at intake:

Drag in the Date of birth and the intake date columns.
Pull in a Date/Time Calc column.
Open the properties, set the calculation type to “Days Between” and select your date of birth and intake date columns as the calculation columns.
Pull in a Number Calc column.
Open the Number Calc column properties, set the calculation type to divide, use your Date/Time Calc column in the first calculation column dropdown and the static value “365” in the second.
In the Number Calc column properties, use the expression below and alter as needed.

IF([column] between 1 and 18, "under 18", IF([column] between 18 and 65, "18 to 65", IF([column] >65, "Over 65", null)))
IF([column] between 1 and 18, "Minor", IF([column] between 18 and 65, "Adult", IF([column] >65, "Senior", null)))

Creating T2s from a report

This will display a link to create a new Tier 2 record for each Tier 1 folder.

Go to the record view for a new Tier 2 record and copy the URL. In this example, we used an attendance record with the URL https://apricot.socialsolutions.com/document/edit/form_id/28/parent_id/1806/id/new
In the URL replace the parent ID, in this case 1806, with ',[column],' giving us the URL https://apricot.socialsolutions.com/document/edit/form_id/28/parent_id/',[column],'/id/new
In your report, pull in the record ID from the Tier 1 records.
Open the properties and change Data Style to “Custom Text”
Use the expression below, replacing the URL with the desired Tier 2 record’s URL created in step 2.

CONCAT('<a href="https://apricot.socialsolutions.com/document/edit/form_id/28/parent_id/',[column],'/id/new">Click Here to Create New Attendance Record</a>')

Displaying current date and time

To display the time and date a report was run within the report table you can use the expression below as a custom text expression.

Date_format(Now(),'%m/%d/%Y %r')

Displaying time since created or last modified

Use the expression below in either a Creation Date or Modification Date field to display the amount of time since the record was created or modified.

TimeDiff(Now(),[column])

Converting a Creation Date to Time and then converting those times to "Early", "On Time", or "Late"

IF(ADDTIME(TIME([column]), "1:00:00") < "12:29:59" , "Early", IF(ADDTIME(TIME([column]), "1:00:00") BETWEEN ("12:30:00") and ("12:30:59"), "On Time", "Late"))

Pulling First or Last initial

The expression below will pull the first character from the column to the left.

Left([column],1)

Displaying column length

If you need to know the number of characters being pulled into a column, you can use the expression below.

Length([column])

Creating a link to the document folder

Clicking into the report cells will direct you to the record but, if you’d like to provide a link to the document folder you can use the expression below in the Tier 1 record ID column.

concat('<a href="https://apricot.socialsolutions.com/document/docfolder/id/',[column],'" target="_blank"> Folder </a>')

Conditional formatting

The expression below is an example of how conditional formatting can be used in apricot reports. Color-Hex Color Codes can be used in these expressions.

IF([column]='Unexcused',CONCAT('',[column],''), IF([column]='Attended',CONCAT('',[column],''), IF([column]='Partial Attendance',CONCAT('',[column],''),[column])))

Phone Number Formatting

Apricot formats phone numbers with "." symbols instead of "-" symbols. If you prefer to see 555-555-5555 instead of 555.555.5555., this custom expression will resolve the issue. Please keep in mind it will also cut off the 3 extension digits for these values, so it is a trade-off.

LEFT(replace([column], '.','-'), 12)

Excluding Tier 1 Records When One of Many Tier 2 Records Matches a Filter

In some cases, users will want to exclude an entire Tier 1 record from the result set if at least one of many of its Tier 2 records meets a certain criteria. For example, in the image below, we want to exclude all Tier 1 Participant Profile records from the results if it has at least one Tier 2 Evaluation record that has an Evaluation Progress equal to Program Completed.

However, if we create a filter that excludes any Evaluation records with an Evaluation Progress equal to Program Completed, we still see Participant Profiles with other Evaluation records in the results regardless of whether or not they have already completed the program.

In order to exclude the entire Participant Profile from the results, we'll need to use a combination of Custom Expressions and Group Filtering.

Open the Column Properties of the field to filter by. In this example, Evaluation Progress.
Select Custom Numeric under Data Style and write:
```
IF ([column] = "Program Completed", 1, 0)
```
**Replace "Program Completed" with the criteria you're looking to filter out.

In our example, this will set any Evaluation Progress that is equal to "Program Completed" to 1 and everything else to 0.
Change Summary to Total (You may need to refresh your options first).
Click Apply.
Open the Column Properties of a Tier 1 field used to identify the record like the Record ID.
Under Group By This Column, select Yes.
We will need to group by some identifying column so that we can apply a group filter.
Click Apply.
Create a Group Filter and set the Filter dropdown to your Tier 2 column that you've applied a Total summary to. In this example, Evaluation Progress.
Set the comparison to Equals 0.
This will exclude any Participant Profile whose Total Evaluation Progress is not equal to 0, therefore excluding Participant Profiles who have at least one Evaluation Progress equal to Program Completed.
Click Apply

Once you've published the report, you'll notice that any Participant Profile that had at least one Evaluation Record with an Evaluation Progress equal to Program Completed will not be included in the results.

Errors in Custom Expressions

If there are any problems with your syntax in a custom expression Apricot will display an error indicating which column and section contains the error and which line the error occurs within, along with a hint. The error message will show you only the first error that it finds, so if there are multiple errors you will need to resolve each one at a time with the aid of this pop up.

If you close out of this pop up and need to view it again, you can click the button on the right hand side of the report to review these details.