Calculated Merge Field Example: Sorted Linked Calendar Appointments

Calculated Merge Fields are a powerful tool to extend the capabilities of your Actionstep system by giving you control over the display, calculations, and formats of information for your document and email templates. This article provides examples and explanations of calculated merge field codes. This can be helpful to understand the fundamental concepts you can apply forward for your purposes.

Multi-Row Data Collection: Sorted Linked Calendar Appointments

This code is designed to sort a list of appointment dates and titles and then output them in date order. It uses two input strings - one containing a list of dates and the other containing a list of appointment titles - and converts the dates to Unix timestamps. The appointments are then associated with their corresponding timestamp and sorted by date using a bubble sort algorithm. The output is a list of appointments, each with its date and title.

Input

• Set the $date_string variable equal to your merge field for the linked calendar appointment list in the multi-row data collection. This will provide the list of dates from the multi-row data collection.
• Set the $title_string variable equal to your merge field for the title of your calendar appointment list in the multi-row data collection. This will provide the list of titles from the multi-row data collection. 
• Set the $sort_direction variable equal to 'asc' to sort the appointment dates oldest to newest. Set to 'desc' for newest to oldest. 
• Set the $date_output_fm variable equal to the PHP Date Format you would like to use for the date output. 

// Example input strings
$date_string = "[[Multi_Appt1List|fm=%Y-%m-%d]]";
$title_string = "[[Multi_TitleList]]";
$sort_direction = 'desc'; // 'asc' or 'desc'
$date_output_fm ="d/m/Y";

// Split the date and title strings into arrays
$date_array = explode(',', str_replace('and', ',', $date_string));
$title_array = explode(',', str_replace('and', ',', $title_string));

// Convert each date string to a timestamp and store it with the corresponding title
$appointments = array();
foreach ($date_array as $key => $date) {
  $title = trim($title_array[$key]);
  $timestamp = strtotime(str_replace(' ', '-', trim($date)));
  $appointment = array();
  $appointment['date'] = $timestamp;
  $appointment['title'] = $title;
  $appointments[] = $appointment;
}

// Sort the appointments by date using bubble sort
for ($i = 0; $i < count($appointments) - 1; $i++) {
  for ($j = 0; $j < count($appointments) - $i - 1; $j++) {
    $a_date = $appointments[$j]['date'];
    $b_date = $appointments[$j+1]['date'];
    if (($sort_direction == 'asc' && $a_date > $b_date) ||
        ($sort_direction == 'desc' && $a_date < $b_date)) {
      $temp = $appointments[$j];
      $appointments[$j] = $appointments[$j+1];
      $appointments[$j+1] = $temp;
    }
  }
}

// Print the appointments with their dates and titles
foreach ($appointments as $appointment) {
  $date = date($date_output_fm, $appointment['date']);
  $title = $appointment['title'];
  echo "$date: $title\n";
}

Output

When the code is executed, it will split the date and title strings into arrays, convert each date to a Unix timestamp, and associate each timestamp with its corresponding appointment title. The appointments will then be sorted by date using a bubble sort algorithm, with the sort direction controlled by the $sort_direction variable. Finally, the sorted appointments will be printed out with the dates and titles.

Example output

03/03/2023: Signing meeting

03/02/2023: Agreement drafting w/Brabara

03/01/2023: Client requirements meeting

Customize Your Code

• Copy the code into the Calculation Code in a new calculated merge field.
• Replace the example input strings in the code with your own date and title strings. These strings should be formatted in a comma-separated list, with the word "and" used to separate the last two items in the list if there are three or more items. For example: “2023-03-21 and 2023-04-01” or “2023-05-13, 2023-06-20, and 2023-7-14.” Using the “List” merge field for your data field takes care of the “comma/and” formatting. Adding the merge field option fm=%Y-%m-%d will format the input dates to YYYY-MM-DD for use with the calculation and sorting code. This helps to avoid errors with unexpected date formats.
• Set the value of the $sort_direction variable to either "asc" (for ascending order) or "desc" (for descending order).
• Set the $date_output_fm to the PHP date format you would like for the date output.
• Save the code.
• Insert the calculated merge field into your document templates to display a list of linked calendar appointments for the matter.

Line 40 echo "$date: $title\n"; can be customized to change the output format. By default, this code returns “03/01/2023: Signing meeting.” One customization on this could be echo "$title on $date\n"; that would return “Signing meeting on 03/03/2023.”

Related Articles

• Working with Calculated Merge Fields
• Using Merge Field Repeat Blocks
• Using Conditional Merge Field Statements