Hi Kostas Theodoropoulos,

I will do my best to advise on some major points of your scenario, and we can then go in more details later on on some specific points if needed.

1. When it comes to using Form input to take data from users - then to present this in our Table, you can easily do this.

We have several built-in integrations with form plugins. Please check each of them in more details on the respective hyperlinks.

These form integrations are :

- integration for Forminator Forms, ( a free add-on), 

- Formidable Forms integration;  

 and  Gravity Forms integration.

If you need our Table to be editable ( not just to show the entries from the Form, but also edit them in our Plugin), this is only possible with the Gravity integration.

With Forminator, for example, users can only input their values as submissions through the Form;

but the Table linked in our Plugin from Forminator can not be edited.

2. Filtering :

You can use our advanced table filters with any DataTable.

Please check all our filter options here.

3. Calculations and Formulas ( they are available for all dataTable types) :

First, the most important point, our Formulas are not the same as Excel Formulas.

I will show you some general use-case scenarios and examples that can be made, i recommend first going through these, then you will get a good idea what is possible with our Plugin's Calculations and Formulas.

Regarding calculation functions, here is our Documentation section with examples, how you can use this.

You can also create a formula/calculated column.

In short, in our Formulas, you will be able to use basic math operations and brackets.

You can use columns (values for each cell will be inserted), or number values. 

Only numeric columns allowed (non-numeric will be parsed as 0). 

Basic math operations and brackets are supported. Example: col1*((col2+2)-col3*sin(col4-3)).

Please note the limitations we pointed out about our Formulas on that Page.

1. One formula column cannot be used in another. This limitation can be avoided by using nested calculations.
2. In tables with server-side processing formulas, columns cannot be used for grouping, filtering, and sorting. Basically, formulas are calculated only for the rows that are immediately visible; Therefore, it’s not possible to “know” the values for currently invisible rows. Please note that this also applies to the “Manual” tables, as they also use server-side processing by default. Also, of course, it is not possible to edit the cells generated by formulas in the editable tables.

Here is another example of how you can present percentages, currency and other columns by using our “Cell content prefix” and a “Cell content suffix” options.

4. For example a user can filter a specific date, or date range :

Yes, we have this as our column filters - you will find that above in the answer about filters.

For a "Date column" you can use a "Date range" filter.

5. So i need to create a database with user input data from front end, and a table or many tables linked and queried from this data base, also shown (not necessarily editable) in the front end.

If you use Form integrations, the data is stored only in the Form, you can not make an easy SQL Query from this data.

Our Plugin is only able to render/show the values from the Form in the Table, but if you need to make Queries from form data, that can be complex.

This data is stored in the Form Plugin's WordPress SQL in multiple Tables as serialized JSON format.

Pulling the SQL data from Forms can only be done through custom MySQL query-based tables.

Please note: Using this plugin feature requires at least a basic knowledge of SQL. It is assumed that you can create the table in some MySQL data manager (e.g., PHPMyAdmin, MySQL Workbench), and prepare a SQL query that will return the data you need. 

A good way to achieve this can be experimenting using our WPDB SQL Query Constructor tool.

You can also try this other SQL Query helper Tool,

Create a MySQL-query-based table by querying a database,

that one has a wider range of all tables you can choose from your Database outside of WPDB.

- Please note: this tool is not an ultimate query generator. It simply constructs a suggestion of a query. We are constantly working to improve it, but SQL is such a complicated and flexible language that full automation for constructing queries is next to impossible. 

Consequently, the more complicated the query, the higher is the probability that it will not return exactly what you need. So, you will often need to play around with the resulting query.

Please be advised that writing custom SQL Queries or debugging Queries does not fall under what our support covers.

You can check out this official page of WP codex,  for help with writing custom Queries.

In the end, if you manage to pull an SQL Query based Table from Form data, you also need to unserialize the JSON data.

This falls under advanced/custom MySQL, and unfortunately, it will not be covered by our support.

You will have to search for custom solutions online, for example, such as this one from Stack Overflow. 

i hope it helps.

So it will depend exactly what kind of Tables you wish to build, where the data is stored.

If you simply wish to make editable Tables directly in our Plugin ( without Forms), please check this Documentation.

Let me know if you have any additional questions.

Thank you.

Hi Kostas Theodoropoulos,

If I understood, the ideal use-case for you would be to have a Form from a Form Plugin let users input their Form submissions on front-end, but to also have this data stored on a file online and synchronized, such as a Google Spreadsheet;

while also at the same time being able to pull SQL Queries from the data;

so in a way, to have the source data for the Table stored in the SQL Database to make an easy Query from it, while inserting new rows from a Form Plugin on front-end, while also synchonizing the data with a Google Spreadsheet?

This is a nice idea, but at the moment, there is no built-in way, to have a "three way" synchonization of the data between a Form ( from a Form Plugin) and Google Sheet and SQL Database.

You can either make a table linked from a Form ( one of our Form integrations);

or have the data stored in the SQL database ( either by building a Manual Table from our Plugin or importing data from a Google Sheet/ or manually building an SQL Table in the Database) then you can easily make an SQL Query based Table and have it editable in our Plugin;

or if you need to have data linked to a Google Sheet - you can make a Table synced with the Sheet/but then this table can't be edited in our Plugin.

We don't have a current way to make all that work with one Table/all the data synced as you described between a Form Plugin / Google Sheet/ and SQL Database.

Only if you skip the Form Plugin and for example use our Plugin's SQL based Tables, then you can easily make front-end editable tables.

You can make a development suggestion for the future, but I can't say a realistic ETA when we could be able to have a 'two way sync' to have data linked from a Google Sheet while also being in the SQL Database.

Please feel free to search on our suggestions page, 

 to see if someone may be already suggested this feature. If you can't see it, feel free to add your suggestion there,  and as more people vote, the feature will move higher on the priority list.

You can certainly follow our changeLog page if you'd like ( it is also available in the plugin dashboard), where we state any changes/new features/bug fixes during updates;

and our newsletter, so you're informed about new features, bug fixes, freebies, etc.

Hi Kostas Theodoropoulos,

Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.

-

At this time, there is no way to achieve something close to your use-case.

Right now, here are the options what our Plugin can do. We already went through this in more details, but just to summarize the main points :

1. You can have a solution with our Plugin and Google Sheets, if you need to do some formulas on your Google Sheet and if you need to have this data stored in an SQL Database and editable from our Plugin side.

But in that case, if you need the table to be editable and stored in SQL data, it can not automatically 'sync back' to your Google Sheet.

You can only import the data from your Google Spreadsheet that has formulas, then our Plugin takes this data and makes a new Manual table in the SQL database.

This table can then be edited with our Plugin and front-end editable, but you can not automatically sync it back to Google Sheet.

There are two ways how you can use Google Sheets data in our plugin ;

1. if you Link the table to Google Spreadsheets,

when you modify or add new rows on the source sheet,

 the plugin will show those changes within 15 minutes (since there's cache on Google's side). 

If you don't want to face any issues with Google cache, i recommend to try our Cache and Auto Update Cache Feature, which helps with performance and you can also set up custom update time via Cron Job.

If you need to use Private Sheets, you can try the Google Sheets API method. 

- This table will not be editable from the plugin side,  but it will be synced with editing from the source Sheet.

2. Or, another way, If you import the file, the plugin reads the source file and creates a manual table. 

Once the table is created, it is no longer linked to the source file, so changing data in the table will not show up in Excel or Google Spreadsheet, and vice-versa. 

This table will be editable in our Plugin.

If you need quicker editing, you can either switch to the Excel-like view, 

and then select and copy the range of cells you want to paste from the source file, and paste it in the Excel-like view, 

or you can Update manual tables from source files (CSV, Excel or Google sheet) with three options :

• Replace data within an existing table.
• Or Add data to an existing table.
•  Or  Replace the entire table with new data.

So to summarize, if you need the Google Sheet to be synchronized with the Table > then you won't have front-end editing and this data doesn't get saved in SQL Database.

Or if you import the Data, that makes a fully editable table in our Plugin, is stored in SQL database, but anytime you make a change on the source Sheet, you need to manually update the table from it.

2. If you need to involve any Form Plugin for front-end data input, then you can't have the Google Sheet in the mix.

And you won't have this make an editable SQL table with our Plugin.

But if you use Gravity Form integration, you can have server-side processing on that linked Table and you can have editing for it in our Plugin.

I understood what you need, but as mentioned before and I hope we clarified it further now, it is not possible to combine having an SQL Table and Form Plugin integration.

If you use any Form integration, all that data is stored on the Form and our Plugin only outputs it;

while with the Gravity integration, you can edit the form entries from our Plugin's table.

The Form data is technically stored in SQL, but it is in serialized JSON format in the WordPress Database as part of the Form Plugin's data.

So we don't have an ideal solution, but you can try one of the presented scenarios above and let us know if you encounter any issue or if you have questions.

Thank you.