The table gets generated like normal and all of the data is outputted.
However, I need a more custom table.
So, I'm looking to create a manual table that also pulls in the values from the columns above.
However, I can't get it to work for the life of me.
For example, when I try to get the 'campaign' values to show up in my manually created table I set the column type as a string, set 'Possible values for column' as 'Use values from another wpDataTable (foreign key), and then Configure the relation as follows:
Foreign Key cannot pull the data into a new table automatically - you have to add it.
So, if you've configured the link in the manual table to display data from "campaign" column, and store data from "affiliate_id" column, the data in that column (in the manual table) needs to match the "affiliate_id" column.
So, if "affiliate_id" = 10, you need to add "10" to the Foreign Key column in the manual table. You can also simply enable editing for the foreign key column, and make sure "Single-value selectbox" is selected in the Editing menu, when you go to column settings, so you'd be able to choose the values manually.
Hmm, well that's not going to work out for me because I need Affiliates to view their own table data on the front-end.
Meaning, manually inputting the actual affiliate_id for each Affiliate isn't going to work when you have potentially 10's of thousands of Affiliates.
Is there anyway that I can pull the data automatically into a manually created table?
Using the SQL Query wizard to create the table doesn't work for my needs because then I can't create an empty column that's value changes the values of other Columns who use the empty column as a part of a formula.
Here's an example:
Affiliate views the table and wants to find the metric, "Cost Per Lead".
He has two columns -- one that displays the 'Total Leads' that he's generated, and another column called 'Ad Spend' that's empty where he can insert any Integer he wants.
When he adds an Integer to the 'Ad Spend' column, it updates a Formula column with a value for Ad Spend / Total Leads.
That value is his 'Cost Per Lead', and can change depending on what he sets his 'Ad Spend' value as.
The 'Total Leads' column, however, is automatically generated by an SQL Query.
=======
In summary, I need 3 Columns where Affiliate's can insert Integers that ultimately affect the values from SQL Query generated Columns.
But, you can't create Manual columns along side SQL Query generated columns it seems.
You can create a view in phpMyAdmin, where you would be able to add another column.
What you're looking for to achieve requires a custom query, and our query constructor can't do that. It is a query constructor helper, not an ultimate generator. You would need to create a query which will pull the affiliate_id based on some other value, so you can use a condition similar to Foreign Keys' "value from" and "store value from" conditions.
When you pull the data using this custom query, you can create a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTable based on a simple query like “SELECT * FROM view1″.
So, basically, you're creating a table in the database which will be pulled into wpDataTables. The issue here, though, can be editing - since views can't be edited properly, so you may need to simply create an empty column in the table where affiliate_id is, and then pull it using a simple query in wpDataTables.
I can't think of any other way to achieve what you wish to achieve.
I've had to do quite a bit to get my own site working, with individual Users needing to see their own data, but also share it with select groups of users if they wish, and insert data which is used in formula columns.
I can't say I understand your requirements fully, but for your information, some of the things I had to resort to included:
- creating new wpdatatables (not views) with SQL joins to pull and match relevant data from different database tables (eg. Affiliate-ID will be linked to data in one table, but User-id may be used for data in another table, hence the need for joins to bring it all together in one new WPdatatable)
- manually adding new empty columns directly to the new table via the database (phpmyadmin), then adding the new column to the SQL query or/or WPdatatable and allowing that column to be front end editable
- a little custom PHP code to alter the page URL where users access their data, in order to use the WPdatatables URL auto-filtering attribute (eg. page-url/?wdt_column_filter[0]="affiliate_id"). The risk here is that anyone can manually edit the URL in their browser, but by allowing the WPdatatables function where users can only edit their own data that can be protected against
It all works for us, but we do have a speed issue (some tables take several seconds to load) which might be caused by some of the above but it doesn't seem so and we're not trying to resolve the issue.
I hope that helps you or others with similar requirements.
OK, so I've been trying to get this Foreign Key feature to work for literally 8 hours... and I can't get it to work at all.
Here's what I'm trying to do:
First, I make a SQL Query generated Table called 'AffiliateWP -- Campaigns' using the GUI provided.
The query selects the 'affiliate_wp_campaigns' table and all of the columns within it.
These columns include the following:
- affiliate_id
- campaign
- visits
- unique_visits
- referrals
- conversion rate
The table gets generated like normal and all of the data is outputted.
However, I need a more custom table.
So, I'm looking to create a manual table that also pulls in the values from the columns above.
However, I can't get it to work for the life of me.
For example, when I try to get the 'campaign' values to show up in my manually created table I set the column type as a string, set 'Possible values for column' as 'Use values from another wpDataTable (foreign key), and then Configure the relation as follows:
Source wpDataTable: 'AffiliateWP -- Campaigns'
Display value: 'campaign'
Store value: 'affiliate_id'
=============================
No matter what I do, I can't get the Campaign names to show up for this manually created table.
I've tried adding the wp_users ID to the SQL Query generated table in order to use that as the Store value... but nope.
What am I missing here!?
Any help is GREATLY appreciated. I've spent an entire day trying to figure out why this foreign key thing won't work. -_-
Hi again Zach
Foreign Key cannot pull the data into a new table automatically - you have to add it.
So, if you've configured the link in the manual table to display data from "campaign" column, and store data from "affiliate_id" column, the data in that column (in the manual table) needs to match the "affiliate_id" column.
So, if "affiliate_id" = 10, you need to add "10" to the Foreign Key column in the manual table. You can also simply enable editing for the foreign key column, and make sure "Single-value selectbox" is selected in the Editing menu, when you go to column settings, so you'd be able to choose the values manually.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Hmm, well that's not going to work out for me because I need Affiliates to view their own table data on the front-end.
Meaning, manually inputting the actual affiliate_id for each Affiliate isn't going to work when you have potentially 10's of thousands of Affiliates.
Is there anyway that I can pull the data automatically into a manually created table?
Using the SQL Query wizard to create the table doesn't work for my needs because then I can't create an empty column that's value changes the values of other Columns who use the empty column as a part of a formula.
Here's an example:
Affiliate views the table and wants to find the metric, "Cost Per Lead".
He has two columns -- one that displays the 'Total Leads' that he's generated, and another column called 'Ad Spend' that's empty where he can insert any Integer he wants.
When he adds an Integer to the 'Ad Spend' column, it updates a Formula column with a value for Ad Spend / Total Leads.
That value is his 'Cost Per Lead', and can change depending on what he sets his 'Ad Spend' value as.
The 'Total Leads' column, however, is automatically generated by an SQL Query.
=======
In summary, I need 3 Columns where Affiliate's can insert Integers that ultimately affect the values from SQL Query generated Columns.
But, you can't create Manual columns along side SQL Query generated columns it seems.
Any idea how to implement this?
You can create a view in phpMyAdmin, where you would be able to add another column.
What you're looking for to achieve requires a custom query, and our query constructor can't do that. It is a query constructor helper, not an ultimate generator. You would need to create a query which will pull the affiliate_id based on some other value, so you can use a condition similar to Foreign Keys' "value from" and "store value from" conditions.
When you pull the data using this custom query, you can create a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTable based on a simple query like “SELECT * FROM view1″.
So, basically, you're creating a table in the database which will be pulled into wpDataTables. The issue here, though, can be editing - since views can't be edited properly, so you may need to simply create an empty column in the table where affiliate_id is, and then pull it using a simple query in wpDataTables.
I can't think of any other way to achieve what you wish to achieve.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Hi Zach - did you get a solution to your issue?
I've had to do quite a bit to get my own site working, with individual Users needing to see their own data, but also share it with select groups of users if they wish, and insert data which is used in formula columns.
I can't say I understand your requirements fully, but for your information, some of the things I had to resort to included:
- creating new wpdatatables (not views) with SQL joins to pull and match relevant data from different database tables
(eg. Affiliate-ID will be linked to data in one table, but User-id may be used for data in another table, hence the need for joins to bring it all together in one new WPdatatable)
- manually adding new empty columns directly to the new table via the database (phpmyadmin), then adding the new column to the SQL query or/or WPdatatable and allowing that column to be front end editable
- a little custom PHP code to alter the page URL where users access their data, in order to use the WPdatatables URL auto-filtering attribute (eg. page-url/?wdt_column_filter[0]="affiliate_id"). The risk here is that anyone can manually edit the URL in their browser, but by allowing the WPdatatables function where users can only edit their own data that can be protected against
It all works for us, but we do have a speed issue (some tables take several seconds to load) which might be caused by some of the above but it doesn't seem so and we're not trying to resolve the issue.
I hope that helps you or others with similar requirements.
Andrew