Autocomplete lookup on unique field

Hi Everyone,

We have a long and complex form defined where the very first field is unique. This creates a challenge when, after entering all the subsequent data, you receive a "duplicate value" error. In order to eliminate the problem, we attached a jQuery autocomplete to the input field, added a PHP function to be called via AJAX to retrieve the suggestions, and added a call to the record's edit link when the user selects on of the selections.

Before you start

1. PLEASE, PLEASE, PLEASE make a backup of your site and DB.
2. Decide if you want to put your code into a child theme or a snippet plug-in. We like Code Snippets by Shea Bunge.
3. Our needs are for multi-site so this example code is multi-site ready.

Step by step

1. Use your browser's inspect feature to see what the input field's ID actually is when displaying the edit or input form. As of 2018-03-15, it will be "field_" & [form field name]. For example if you have a form field named "company_name", then it will be "field_company_name". Make a note of the ID as we will be using it in the jQuery Autocomplete and OnSelect section later.
2. You have to enqueue the jQuery UI or autocomplete modules depending on your needs. You can download whatever version you like from jQuery.com or Google hosted libraries and load if from your own server or you can reference the online libraries directly. Either way, be sure to match the CSS and JS modules or you could end up chasing down non-existent bugs. See the Enqueue jQuery UI section below for the example code.
3. Add a PHP function to retrieve the autocomplete suggestions along with the ID of the suggested record. See Get Suggestions for the code.
4. Add an HTML field to the top your Formidable form. I don't think, strictly speaking, that you have to put it at the top, but it servers as a reminder that you're doing something unusual in the form, especially if another developer comes in behind you.
5. Add the code from the jQuery Autocomplete and OnSelect into the HTML field making sure to surround it with script tags.
6. Save your Formidable form and open or refresh your edit page.
7. You should now be able to type in a partial value and have a list show up under your edit field. If you select a suggestion, either by clicking or using the keyboard arrows and pressing enter, you will go to the suggestion's edit page. Otherwise, you can enter a new unique value and complete the entry assured that your value is unique.

There's a lot of room for improvement, but this will get you going!

I hope you find this code useful.

Enqueue jQuery UI

function load_jquery_ui() {
wp_enqueue_script('jquery-ui', 'https://code.jquery.com/ui/1.12.1/jquery-ui.js', array('jquery'));
wp_enqueue_style('jquery-ui.css', 'https://code.jquery.com/ui/1.12.1/themes/ui-lightness/jquery-ui.css');
}
add_action('wp_enqueue_scripts', 'load_jquery_ui');

Get Suggestions

Important: The nopriv action could be a different routine if the functionality must be different for logged in v. not logged in users. Optionally, you could also leave it undefined if you want unauthenticated users to get no results at all. Also, please remember to replace [FORMIDABLE FORM FIELD NAME] with the name of your field as shown in the Formidable form editor.


add_action('wp_ajax_nopriv_get_field_suggestions_list', 'get_field_suggestions_list');
add_action('wp_ajax_get_field_suggestions_list', 'get_field_suggestions_list');
//
function get_field_suggestions_list() {
global $wpdb;
$frm_item_metas = $wpdb->prefix . 'frm_item_metas'; //Formidable records
$frm_fields = $wpdb->prefix . 'frm_fields'; //Formidable fields
//
//Convert the search value to something safe for a query
$find_search_value = "%" . $wpdb->esc_like( stripslashes( $_REQUEST['search_value'] ) ) . "%";
$suggestions = array();
//
//If there is a value in the Name parameter then get the suggestions otherwise leave
if ( strlen( $find_search_value ) > 0 ) {
$sql = "select item_id, meta_value from {$frm_item_metas} where field_id = ( select id from {$frm_fields} where field_key = '[FORMIDABLE FORM FIELD NAME]' limit 1) and meta_value like '%s' order by meta_value";
$prepared_sql = $wpdb->prepare( $sql, $find_search_value );
$results = $wpdb->get_results( $prepared_sql );
foreach( $results as $result ) {
$suggestion = array();
$suggestion['item_id'] = $result->item_id;
$suggestion['meta_value'] = $result->meta_value;
$suggestions[] = $suggestion;
}
}
echo json_encode( $suggestions );
die();
}

jQuery Autocomplete and OnSelect

Important: Please enclose the code in the HTML field with script tags, replace [ID FROM STEP 1] with the field ID you retrieved in step 1, and replace [PAGE SLUG] with the value of your edit page's slug.

Here are some clarifications for the code below:
label: Shows on the drop down list
value: Is left in the field on blur or selection
id: Hidden but used for the edit link
delay: Amount of ms to wait before initiating the AJAX call
url: WordPress funnels the AJAX processing through admin-ajax.php for very good reasons, if you need more information look here.
item.reference_names must match what's put in the JSON as the index for the data

jQuery( document ).ready(function( $ ) {
$( '#[ID FROM STEP 1]' ).autocomplete( {
source: function( search_value, response ) {
$.ajax({
type: 'POST',
dataType: 'json',
delay: 200,
url: '/wp-admin/admin-ajax.php',
data: 'action=get_field_suggestions_list&search_value='+search_value.term,
success: function( data ) {
response( $.map ( data, function( item ) {
return {
label: item.meta_value + ' (' + item.item_id + ')',
value: item.meta_value,
id: item.item_id
};
}));
}
});
},
select: function( event, ui ) {
window.open( window.location.href + "[PAGE SLUG]/?frm_action=edit&entry=" + ui.item.id, '_self');
}
});
});