Ronnie H asked 2 years ago
Victor Font answered 2 years ago
Since you originally asked about a view and not a graph, to display the counts in a view, you would create your own shortcode. You can pass the form id for which you are counting entries as a parameter and then use the FrmEntry::getRecordCount function to retrieve your result.
Ronnie H
replied 2 years ago <p><a href="https://connect.formidableforms.com/user-tutorials/custom-shortcode-creation-basic/""""" rel="nofollow">https://connect.formidableforms.com/user-tutorials/custom-shortcode-creation-basic/</a></p><p>Found this on a post Rob made.</p>
Victor Font answered 2 years ago
Victor Font
replied 2 years ago Copy the code and add it to your Code Snippets plugin or functions.php.
Ronnie H
replied 2 years ago Awesome that works perfectly thank you. Now is there any way to add this to a specific field [id]? For example in formidable forms/views field "referral made yes or no" has a field id of 13, is there any way to get the count of that specific field id? Thanks again
Victor Font
replied 2 years ago I wouldn't use this shortcode for fields. First, it won't work as designed, and second, adding field as a parameter after the code is written is a great example of project scope creep. Take it from someone that's been developing code for a looong time, project scope creep always costs someone to fix! LOL!
As for this shortcode not working for fields, let me explain why. I wrote it for a specific purpose. Its purpose is to help you solve the problem you first asked about, which is counting entries by form id. After our PM exchanges, I wrote the code because it's a good edition to our public Gist and as a snippet on Formidable Masterminds.
The shortcode is simplistic, nothing more than a wrapper for Formidable's FrmEntry::getRecordCount() function. Creating a wrapper for existing Formidable functions that work well is a fast way to solve simple problems like yours. Counting entries is a simple problem because Formidable already has a way to do it. I don't think it took me more than a few minutes to write the shortcode because I'm depending on functionality that works well in Formidable core code. The shortcode API that enables developers to create shortcodes is pure WordPress core. Shortcodes are written in PHP and execute on the server.
The FrmEntry::getRecordCount() public function constructs a SQL query that returns the record count for the value passed to the query's WHERE clause. As a wrapper, the shortcode enables you to pass a form id to the FrmEntry::getRecordCount() function that then applies the value passed as the parameter to the SQL query's WHERE clause. The function's source code found in the formidable/classes/models/FrmEntry.php source indicates the parameter can be a single form id or an array. I haven't drilled into that function more deeply because all I needed to learn was how to make it work for your shortcode, which is single form id. If you use form key as a parameter, the shortcode uses the key to get the id before passing to FrmEntry::getRecordCount().
Formidable is building the SQL query "on-the-fly" based on parameters passed to the function. There are different execution paths for single and array parameters. The base SQL used regardless of the parameter type, has a specific command called "LEFT OUTER JOIN" that is hard coded in the function. Hard coded means it cannot be changed. This command is used to join or link two tables together to facilitate the count and return a value to calling execution path. The LEFT OUTER JOIN in this SQL query joins the frm_forms and frm_items table to each other.
The key to understanding why this shortcode won't work with fields is discoverable in Formidable's database schema. Formidable follows the WordPress metadata design. This means that for every form entry, the data is stored in two tables, frm_items and frm_item_metas. Metadata design means that every entry has both a header record in frm_items and as many rows as required in frm_item_metas because each field's captured value in a form is stored in a single table row. If your form has 100 fields and every one of those fields has a value returned in an entry, then that single entry will create 100 rows in the frm_item_metas table. Even Formidable's form builder uses two other tables of metadata design. Those tables frm_forms and frm_fields. In this example, frm_forms is the header record and frm_fields is the metadata.
The two tables joined by the LEFT OUTER JOIN, frm_forms and frm_items, are both header tables. There's no metadata available in this function to search.
One thing to note about metadata is that only entry fields that have values are inserted into frm_item_metas. If the user doesn't provide an answer for a field, if that field has no default value, there will be no record for that field in frm_item_metas with the rest of the entry's fields. Going back to the 100 field example, if only 50 fields of the hundred have values, only 50 rows are added to frm_item_metas table.
If you're truly at a point where passing field id as a parameter is a must have, then you need custom code. FrmEntry::getRecordCount() does not join to frm_item_metas and it can't be made to do so. The shortcode can be modified to enable field id(s) to be passed as parameters, but doing so requires an entirely different SQL query, one that joins to frm_item_metas or count from frm_item_metas alone without joining to another table. If you want to search by multiple fields, the custom needs to build multiple LEFT JOINS on the fly. Will you ever have a need to count by two or more fields or consolidate multiple forms into a single count? These are the questions you need to define before working with any developer. The cost of the project will be determined by it's complexity, but even though I can't see custom work like this taking more than a couple of hours.
To learn more about Formidable's database schema, please see: https://formidableforms.com/knowledgebase/database-schema/
If you need help developing this code, you'll find very competent developers and familiar names in the Formidable Masterminds Developers Directory, https://formidable-masterminds.com/developers-directory/.
Ronnie H
replied 2 years ago Ok so how about this; I just created a custom search form in formidable. Is there any way to get a total number of search results to display. For example searching for Berry returns 8 entries?
Please login or Register to submit your answer
Got it I can just add a graph to my view and insert the highlighted columns into my graph correct?