I have a custom post type with an Advanced Custom Fields repeater field called 'Change requests'.

One of the fields within the repeater is 'status'. The field is a select box that can be set to 'New', 'Accepted' or 'Declined'.

I've set up a custom column in the posts screen which displays the number of change requests with a status of 'New', by using a function that loops through the change requests for each post and increments a value whenever it finds a row with a status of 'New', and I've made this column sortable. Here's my code:

function rrm_records_custom_columns( $column, $post_id ) {
    if ($column == 'new_change_requests'){
      $changeCount = 0;
      $change_requests = get_field('change_requests',$post_id);
      foreach ($change_requests as $change_request) {
        if ($change_request[status]=='New'){
          $changeCount++;
        }
      }
      echo $changeCount;
    }
}
add_action( 'manage_record_posts_custom_column' , 'rrm_records_custom_columns', 10, 2 );

function rrm_records_add_custom_columns( $columns ) {
    return array_merge( $columns,
        array(
          'new_change_requests' => __( 'New Change Requests', 'wordpress' ),
         ) );
}
add_filter( 'manage_record_posts_columns' , 'rrm_records_add_custom_columns' );

function rrm_records_set_sortable_columns( $columns ) {
  $columns['new_change_requests'] = 'new_change_requests';

  return $columns;
}
add_filter( 'manage_edit-record_sortable_columns', 'rrm_records_set_sortable_columns' );

The issue I'm having is that it doesn't sort by the value shown in the column ($changeCount). I've tried to get this working using functions like the one below, but obviously $changeCount isn't a meta value, so this isn't working.

function rrm_records_custom_orderby( $query ) {
  $orderby = $query->get('orderby');

  if ( 'new_change_requests' == $orderby ) {
        $changeCount = 0;
    $change_requests = get_field('change_requests',$post_id);
    foreach ($change_requests as $change_request) {
      if ($change_request[status]=='New'){
        $changeCount++;
      }
    }
    $query->set( 'meta_key', $changeCount );
    $query->set( 'orderby', 'meta_value_num' );
  }
}
add_action( 'pre_get_posts', 'rrm_records_custom_orderby' );

Any ideas how I can get the column to sort by the actual value in the column?