Make no mistake about it, Formidable Pro is my favorite form building tool. Why? One of the primary reasons is the unparalleled flexibility provided by its API. Formidable Pro offers developers many hooks that allow you to easily take advantage of built-in core WordPress functionality.
To illustrate, in the exemplar below I am accessing the $wpdb object to populate a Formidable Pro dropdown with custom values pulled from a third party membership management table. The requirement is from a form built for the North Carolina Executive Roundtable (NCER). The North Carolina Executive Roundtable is a professional association of senior-level executives located in Raleigh, NC. Membership is by invitation only. The site's forms require members to submit their name. Of course, there are several ways to do this. We could have members directly enter their name in a text field, which can lead to inconsistencies later on if generating reports, or we could have members select their name from a drop down built from membership details.
Extracting membership details may not be an easy task however. Members come and go and memberships expire if dues aren't paid. As a result, the membership list is dynamic, which means any dropdown built from a membership tables needs to be dynamic. Membership plugins like S2member use the wp_users table to store membership details. Additional attributes other than the core user information is stored in wp_user_meta, which requires a SQL join to extract all appropriate details. Other plugins like WP eMember from Tips and Tricks HQ use their own tables to store member details.
In the former situation, it's appropriate to access the user details with WP User Query. In the latter, the NCER website uses WP eMember as its membership management tool, so it is appropriate to use the $wpdb object. To make this all work with Formidable Pro, we'll be accessing the frm_setup_new_fields_vars and frm_setup_edit_fields_vars hooks.
To get started, copy the code below to your functions.php file. Change the SQL in the line 10 that begins with $the_Result with your own custom query. Since you are using custom code, make sure you've tested it thoroughly in PhpMyAdmin, MySQL Workbench or another of your favorite SQL tools. Make sure you receive the expected results before adding the custom SQL query to your code. This is important because the Formidable Pro support agreement does not permit their excellent support team to help with custom code. So it is incumbent upon you to test, test, test and test again to make sure your custom code is right in every respect.
//populate the member dropdown field for nomination and member-advocate event forms
// data comes from wp_wp_eMember_members_tbl
add_filter(frm_setup_new_fields_vars, frm_populate_member, 20, 2);
add_filter(frm_setup_edit_fields_vars, frm_populate_member, 20, 2); //use this function on edit too
function frm_populate_member($values, $field){
if($field->id == 732 || $field->id == 733){ //replace 732 and 733 with the IDs of the fields to populate
global $wpdb;
//the Query
$the_Result = $wpdb->get_results( "SELECT concat_ws(', ', last_name, first_name) as display_name FROM wp_database.wp_wp_eMember_members_tbl where account_state = 'active' order by last_name, first_name;" );
unset($values['options']); //break the binding of any existing content in the values array
$values['options'] = array(); //remove this line if you are using a checkbox or radio button field
$values['options'][]="";
//the Loop
if(! empty($the_Result)){
foreach($the_Result as $a_user){
$values['options'][]=$a_user->display_name;
$values['use_key'] = false;
}
}
//$values['use_key'] = true; //this will set the field to save the post ID instead of post title
}
return $values;
}
If you run into issues, please feel free to contact me. In most cases, I'll be able to help solve your problem in a few minutes. If it takes longer than a quick fix, my consulting rates are reasonable.
In this example is it possible to pass the value of a prior field to the query here:
Exampe: text field for zipcode. then in the select statement, the where clause includes where zipcode = ‘valueofzipcodefield’.
$the_Result = $wpdb->get_results( “SELECT concat_ws(‘, ‘, last_name, first_name) as display_name FROM wp_database.wp_wp_eMember_members_tbl where account_state = ‘active’ order by last_name, first_name;” );
Thank you for the example, it is very helpful.
Yes, it’s possible. You’d have to retrieve the zip code value from the database first. The two filters used in this example, frm_setup_new_fields_vars and frm_setup_edit_fields_vars, are fired before the form displays in the browser. This is why we use SQL to get the values for the dropdown. If you can get SQL working in PHPMyAdmin where you include a zip code field, then you can add the zip code to the SQL in the code snippet. When writing custom SQL, I always write and test directly against the database first before converting the SQL statement to snippet code. Good luck with this.
Hi Sir,
Thank you for sharing this useful feature in the database applications. I am also using Formidable to build a school management system. I came across a similar situation, so on Googling I came to know about this. And also found your videos on creating advanced views, it was very useful to me. Thank you for that.
In my form, I have two dropdown lists and the second one needs to populate the values from another table like this but depends on the selection of the first dropdown. May I please know, how to do this? Is there any way to get the other field information into this hook.
Thanking you..
Hello Siva,
Thank you for your kind remarks. Yes, you can accomplish what you want to do with Formidable Pro. You don’t have to use a hook either. All you need is two Formidable Pro Dynamic Fields. There’s a lot of information on the Formidable Pro website about dynamic fields. What you want to accomplish would be done in the same manner as the City/State drop downs in their tutorial.
This is great. I’ve just started using Formidable Pro and have had great luck. However, I’ve been searching Formidable’s forum for a dynamic list from the users database without luck. Using Goole search, I found your post and was able to use your code to create a drop-down list from the wp database. Woo-hoo!
I have one teeny problem though. When I choose a name from the list in a form, the result is a number and not the name. For instance, if I choose the 6th name on the list, I get a 6; 12th name on the list = 12. How do I get the result to show the name?
Thank you.
FYI, my company is using WordPress as an internally hosted Intranet.
You could set the value and label differently in the code with something like this:
$values['options'][] = array('label' => $label_val,
'value' => $saved_val
);
And you would need to select “Use separate values” in the field’s options. This way, the displayed value in the field is different from the saved value.
Thanks for the reply. I thought I needed to create a Dynamic Dropdown field which doesn’t have a “Use separate values” in the field’s options. I changed it to a regular dropdown field with the suggested changes and it worked perfectly. Thanks so much.
Hello Victor: Just wanted to let you know that your links are broken on your site. It appears that blog/ is missing from all of your article links. I got to your site from a post regarding this on Formidable Pro but got 404 errors then saw at the bottom of the 404 page Recent Articles and clicked on it and still got 404, after some digging through the structure of your site I noticed the link was missing blog/
Just thought you should know since many that visit will not get to the correct page.
Thank you very much. One of the plugins is overwriting the permalinks settings. I haven’t had time to troubleshoot the issue yet.
Excellent information. I did similar for populating from Post Titles, but this was good to know.
Got here form their User Tricks section you posted at. I’m also liking the Formidable Pro & I wonder why I didn’t shift to this long ago.
I’m having a bottleneck with similar kind of requirement. Would you be able to have a look at my question there? It’s at: formidableforms.com/help-desk/display-matching-name-based-on-number-entered/
Thanks again.