Mirror formidable forms entries in mysql table

By: Telema Westwood | Asked: 07/24/2022
ForumsCategory: Paid Help WantedMirror formidable forms entries in mysql table
Telema Westwood asked 2 years ago
Hello, I need to mirror formidables entries in an externl mysql database table so I can visualize the data with an engine that lets me create charts and graphs that have date filters on the frontend. This external table should also update when an entry is updated in formidable forms.
ByteCode LabsByteCode Labs replied 2 years ago

Hi, good day.

It would help to understand a little bit more about the requirements. Just curious why the need to copy formidable data to another database table. This will raise maintenance overheads.

Did you consider the Formidable/Wordpress graph plugins already.

Our group ByteCode Labs (https://www.bytecodelabs.in) can help with the solution design. Let us know.

Thank you.

Victor Font replied 2 years ago

Many Formidable projects have reporting requirements that are not performant with the 2-table content schema design. For example, I had a University that required an audit trail of Formidable data changes and a data warehouse star schema for reporting purposes. These requirement can't be accomplished without a normalized flat table schema design. It's all possible with Formidable though. We also use SQL views when building some queries instead of a massive list of LEFT JOINS. There are many reasons to use flat tables or SQL views with Formidable.

ByteCode LabsByteCode Labs replied 2 years ago

Hi Victor, good day.

Thank you for the feedback.

However, replicating data has many disadvantages -
1. Increased maintenance overhead.
2. No single point of truth.
3. Sync error may lead to incorrect analytics.

Believe it is best to understand if the existing out of the box options have been explored before taking a custom route.

Feel just because something can be done does not mean it should be done, as use cases vary.

Best.

Victor Font replied 2 years ago

This isn’t the place for philosophical discussions. It’s a community forum to directly help people solve problems. As volunteers without any affiliation to Strategy 11, we provide answers that we know work because they are solutions that have been built for real clients to fulfill requirements where systems need to be constructed with normalized data structures.

Based on experience, none of your 3 points are valid for enterprise solutions that require robust ODS reporting. For small systems, yes, you do not need a normalized structure.

I’d love to hear how you would build an audit trail to track and compare every change to Formidable data and report on that data using Formidable's schema. How would you fulfill that requirement if it was your project when Formidable does not keep historical data, but only current entry?

Using SQL views or flat tables to provide ODS reporting and audit trails with data from authoritative sources does not change the single source of truth concept. Single source of truth means there is one system that owns and maintains the data. It doesn't mean that the data can't be used for purposes beyond the parent system's capability. No matter how the data is used and reported on, it is still owned and maintained by the parent system, which is in this case Formidable.

Star schemas for data warehousing are standards for enterprise reporting. When I architected and managed enterprise systems in my corporate life, the designs were Oracle based. The truth sources are disparate enterprise systems such as HR, supply chain, or accounting data. Yet, they still needed to have reports generated that are combined and performant. Just because a business reporting need is robust, it doesn't change the fact that there isn't a single source of truth for each data element.

Since this is the first time I can recall that you’ve ever responded to a post in any of the Formidable related forums over the last 11 years, may I suggest that instead of directly soliciting posters to hire you to provide a paid solution, that you provide a link to your project application to trigger your intake process if someone is interested in working with you based on the relevancy of your answers.

If you’d like to continue the philosophical discussion, then I’d like to invite you to join the <a href="https://www.facebook.com/groups/formidablemasterminds">Formidable Masterminds Facebook Group</a> where I permit these type of threads as long as they’re kept civil.

You can also promote your Formidable Forms experience in the <a href="https://formidable-masterminds.com/developers-directory/">Formidable Masterminds Developers Directory</a>.

ByteCode LabsByteCode Labs replied 2 years ago

Hi Victor, good day.

Many thanks for sharing your style of work.

Our (as in ByteCode Labs) method is slightly different. We start by listening to our clients' journey and their pain points before discussing the solution path. This is exactly what we are trying to do here as well.

And if you have noticed, the requirement is that of data visualization/reporting and not audit trail.

Lastly, out of respect for the author, let's not hijack the communication thread. Our (as in ByeCode Lab's) original post was intended for the author of this thread.

Best regards and have a great day.

3 Answers
Best Answer
Victor Font answered 2 years ago
For this, I would create a SQL View and use Chart JS on the front end to display the charts. SQL Views update automatically each time you query them. You can also make charts interactive. For inspiration, see https://formidable-masterminds.com/create-interactive-graphs-with-chart-js-and-formidable/
Luis De PauLuis De Pau replied 2 years ago

Amazing results!

Luis De PauLuis De Pau answered 2 years ago

Hello!

If the table is in the same database, you can use this:
https://formidableforms.com/knowledgebase/frm_after_create_entry/#kb-insert-form-data-into-second-database-tableOr you could use integromat/zapier/pabbly/integrately to do it. There's also this:https://wpdebuglog.com/downloads/formidable-forms-mysql/ But I haven't use it Good luck!

James Sambadoo replied 9 months ago

Hello, Thank for the link but have an issue with checkbox can you help ? Checkbox value don't save in database

$values = array('select' => $_POST['item_meta'][1]);

"select' is the database column name and 1 is the checkbox id in the form but text field, dropdown and radio is saved well only checkbox

I have tried [1 show=value], [1 show="value"] with Use separate values active in checkbox but still don't work

Bobby Clapp answered 2 years ago
Or this -> https://formidableforms.com/knowledgebase/formidable-api/

Making the Best WordPress Plugin even better - Together

Take on bigger projects with confidence knowing you have access to an entire community of Formidable Experts and Professionals who have your back when the going gets tough. You got this!
Join the community
crossarrow-right