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] <18, 'Minor', IF([column] >17.99 and [column]<65, 'Adult','Senior'))
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] <18, 'Minor', IF([column] >17.99 and [column]<65, 'Adult','Senior'))
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])
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('<b><font color="#FF0000">',[column],'</font></b>'), IF([column]='Attended',CONCAT('<font color="#009933">',[column],'</font>'), IF([column]='Partial Attendance',CONCAT('<font color="#FF9900">',[column],'</font>'),[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)
