You can get the Client_ID or client name by using foreign keys. You can see an example in the documentation link I provided, and also there is a live example in our sandbox site.
I have already done the single level foreign key, now I want to get data from a third table .
Your example shows a single level relationship. e.g. Parent(one), child(many), and from the child table, I can get the Parent using the foreign key.
But - I want to have 3 levels, GrandParent > Parent > Grandchild , and from Grandchild, I want to get A)Parent B) Grandparent
I gather that this is not possible using the standard editor, so I assume I have to use the query to mysql to achieve this, unfortunately I cannot find the relevant documentation to do this.
I am sorry to disappoint you but what you're trying to achieve is impossible with the plugin's built-in features.
The thing is that when you use a foreign key from a child table it does display the name of the parent, but it gets stored as an integer in the database; so when the grandchild tries to pull the value from the child using a foreign key, it records that integer as it has been stored in the db.
You can try adding conditional formatting for the grandchild, so it changes "1" to "Apple", "2" to "IBM", etc. I believe you have a lot more data you'd need to link, so I doubt this'd help, but unfortunately this is the only work around for this, at the moment.
As I said - this is impossible to achieve using plugin's built in features, but if you know how to generate a query for that in PHPMyAdmin, then that would be considered a workaround. You can create these relations using JOINS or CONCAT in the database, and then create a view from that table our plugin would be able to read.
Something like this could work, but please note that using this 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 an SQL query that will return the data you need. Please be advised that preparing MySQL queries for you is not included in the plugin support.
I'm new to wptables plugin.
I've managed to create 3 tables.
Clients, Jobs, tickets.
I created relationship between Clients(one) and Jobs(many), and I can create a relationship between Jobs(one) and tickets(many), works great !
How do I retrieve the Client name from the Ticket table (job table is in the middle)
example data
clients table -- ------------------
1. client 1 = Apple
2. client 2 = IBM
3. client 3 = Samsung
Jobs table - --------------------
1. Job 1 Build Shed - client is Apple
2. Job 2 Fix Door - client is IBM
3. job 3 fix window - client is IBM
tickets table - ---------------
1. ticket 1 - build foundation - job 1 (build shed) - [[how do i get client name]]
2. ticket 2 - build walls - job 1 (build shed) - [[how do i get client name]]
3. ticket 3 - build roof - job 1 (build shed) - [[how do i get client name]]
4. ticket 4 - demolish door - job 2 (fix door) - [[how do i get client name]]
etc...
Hello Johnny.
Thank You for Your purchase.
You can get the Client_ID or client name by using foreign keys. You can see an example in the documentation link I provided, and also there is a live example in our sandbox site.
Please take a look and let me know if this helps.
Best regards.
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
thanks Aleksandar,
I have already done the single level foreign key, now I want to get data from a third table .
Your example shows a single level relationship. e.g. Parent(one), child(many), and from the child table, I can get the Parent using the foreign key.
But - I want to have 3 levels, GrandParent > Parent > Grandchild , and from Grandchild, I want to get A)Parent B) Grandparent
I gather that this is not possible using the standard editor, so I assume I have to use the query to mysql to achieve this, unfortunately I cannot find the relevant documentation to do this.
Can you point me in the right direction please.
an extra note to add to the last...
From the grandchild, I am able to retrieve the foreign key of the grandparent (from the parent table), but it displays an integer, not the "name"
Hello Johnny.
I am sorry to disappoint you but what you're trying to achieve is impossible with the plugin's built-in features.
The thing is that when you use a foreign key from a child table it does display the name of the parent, but it gets stored as an integer in the database; so when the grandchild tries to pull the value from the child using a foreign key, it records that integer as it has been stored in the db.
You can try adding conditional formatting for the grandchild, so it changes "1" to "Apple", "2" to "IBM", etc. I believe you have a lot more data you'd need to link, so I doubt this'd help, but unfortunately this is the only work around for this, at the moment.
I am sorry for the inconvenience.
Best regards.
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
OK, very disappointing, a great plugin that is missing a very common database feature.
I also find it hard to believe that nobody has ever made some sort of workaround to make this work.
Hello again Johnny.
As I said - this is impossible to achieve using plugin's built in features, but if you know how to generate a query for that in PHPMyAdmin, then that would be considered a workaround. You can create these relations using JOINS or CONCAT in the database, and then create a view from that table our plugin would be able to read.
Something like this could work, but please note that using this 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 an SQL query that will return the data you need. Please be advised that preparing MySQL queries for you is not included in the plugin support.
Best regards.
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