Displaying Age Groups

Current age:

  1. Pull in the Date of birth column and adjust the properties to display the “age” data style.
  2. Pull in a Number Calc column.
  3. Open the Number Calc column properties, select the data style “Custom Numeric” and select your data of birth column as the first calculation column.
  4. 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:

  1. Drag in the Date of birth and the intake date columns.
  2. Pull in a Date/Time Calc column.
  3. Open the properties, set the calculation type to “Days Between” and select your date of birth and intake date columns as the calculation columns.
  4. Pull in a Number Calc column.
  5. 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.
  6. 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.

  1. 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.
  2. 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.
  3. In your report, pull in the record ID from the Tier 1 records.
  4. Open the properties and change Data Style to “Custom Text”
  5. Use the expression below, replacing the URL with the desired Tier 2 record’s URL created in step 2.

 

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.

 

 

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)

Did this answer your question?