I have a table of properties that uses a secondary table with foreign key to populate street names.There are numerous properties on numerous street so we have a many to many relationship.
Setting up the lookup table for a street name in a new property record is just fine.However the user wants a view to filter by street and to do this it would seem either
1. the street field in the property table contains the street name . This then breaks the referential integrity of the lookup table - ie if the spelling is changed in the look up table , it would not reflect in the property table or
2. the street field holds the street ID and you have to search on the ID number which is not good
Is there something I am missing
Secondly I am having problems creating a view with the main table - say properties pulling data from a street table and property type table .
I tried creating a new SQL search in the editor but my script seemed to get overwritten when I tried to create the table.
Just some meore detail on getting the SQL script for three table. Attached image shows setting for getting a view of Masterindex,strett and Full_name from three tables.
The generated script was
SELECT Progress_Tracker_1.`Masterindex`, Streets.`Street`, Volunteer.`Full_Name` FROM Streets INNER JOIN Volunteer ON Progress_Tracker_1.`VID` = Volunteer.`ID` INNER JOIN Progress_Tracker_1 ON Streets.`ID` = Progress_Tracker_1.`StreetID`
and generated an error as Progress_Tracker_1 was undefined
This did not run but this does
SELECT Progress_Tracker_1.`Masterindex`, Streets.`Street`, Volunteer.`Full_Name` FROM Progress_Tracker_1 INNER JOIN Volunteer ON Progress_Tracker_1.VID = Volunteer.ID INNER JOIN Streets ON Streets.`ID` = Progress_Tracker_1.`StreetID`
If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (happens rarely, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also server-side processing feature adds this part of statement automatically when users triggers the sorting on the front-end, and having it in initial statement may cause the table to crash.
Unfortunately due to our support policy and included support of the plugin , we are not allowed to write a queries for the customers. Included support covers help with bugs and general inquiries for the plugin features, but not writing custom code.
Many thanks for that - I should have thought it through myself !! Old age I guess !.
However I do still have a slight issue. I have created a view from a master table and foreign key tables It imports into WPtables fine however I have a problem with updating.I only wish to update a field from the master table. I can do this with no problem in the excel type editor, ( attached image shows master table fields-grey, foreign fields Yellow and Green, editing Textrec field only) however when I switch to standard editor I get the attached error saying cannot join more than one table when I apply udates. It would seem the two editors generate different scripts !. Does the excel editor just update the cell whereas the standard editor tries to update the row even when there are no changes to fields? This will give me a problem as I would like some users to just update using the web page view. Can I get the excel editor to display on the webpage ? Any ideas??
If you take a look at our documentation you will see this notice
Only one MySQL table can be edited at a time. Queries from multiple tables with joins cannot be used as an editable feature, since SQL UPDATE and INSERT statements are generated automatically, and there currently is no way to update multiple tables – but this problem is being investigated and pursued.
Only MySQL tables or tables created with “Create a table manually” and “Create a table by importing data from data source” options of “Create a Table” pagecan be edited.
Server-side processing MUST be turned on for front-end editing – Note: It will turn on automatically if you forget to enable it.
MySQL Views cannot be edited properly – only one of the underlying tables can be edited if you pass the proper ID.
As for the excel like view you can paste it in the page by choosing to display the table in excel like view ( attachment)
Keep in mind that there are some limitations in the Excel like view - link.
MySQL Views cannot be edited properly – only one of the underlying tables can be edited if you pass the proper ID.
I understood this to mean that although a view came from two tables, one could edit one of them - but that doesn't seem to be the case ! - Had read all the notes but I guess had misunderstood - I had assumed a view would be treated as one table !
Anyway - many thanks and we will work around it somehow. How do you manage an order situation where the order table will require to pull a vendor and an order item type say ? I an sure many of your users would have that type of configuration
Hi
I have a table of properties that uses a secondary table with foreign key to populate street names.There are numerous properties on numerous street so we have a many to many relationship.
Setting up the lookup table for a street name in a new property record is just fine.However the user wants a view to filter by street and to do this it would seem either
1. the street field in the property table contains the street name . This then breaks the referential integrity of the lookup table - ie if the spelling is changed in the look up table , it would not reflect in the property table or
2. the street field holds the street ID and you have to search on the ID number which is not good
Is there something I am missing
Secondly I am having problems creating a view with the main table - say properties pulling data from a street table and property type table .
I tried creating a new SQL search in the editor but my script seemed to get overwritten when I tried to create the table.
Otherwise the plugin is just great
Cheers
Richard
Further to above comment on sql script attached two images - One show the script the second shows the rendered query
Just some meore detail on getting the SQL script for three table. Attached image shows setting for getting a view of Masterindex,strett and Full_name from three tables.
The generated script was
SELECT Progress_Tracker_1.`Masterindex`,
Streets.`Street`,
Volunteer.`Full_Name`
FROM Streets
INNER JOIN Volunteer
ON Progress_Tracker_1.`VID` = Volunteer.`ID`
INNER JOIN Progress_Tracker_1
ON Streets.`ID` = Progress_Tracker_1.`StreetID`
and generated an error as Progress_Tracker_1 was undefined
This did not run but this does
SELECT Progress_Tracker_1.`Masterindex`,
Streets.`Street`,
Volunteer.`Full_Name`
FROM Progress_Tracker_1
INNER JOIN Volunteer
ON Progress_Tracker_1.VID = Volunteer.ID
INNER JOIN Streets
ON Streets.`ID` = Progress_Tracker_1.`StreetID`
Help here would be gratefully appreciated
Cheers
Richard
Apologies - forgot the image !
Hi Richard,
Thank you for your purchase.
If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (happens rarely, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Unfortunately due to our support policy and included support of the plugin , we are not allowed to write a queries for the customers. Included support covers help with bugs and general inquiries for the plugin features, but not writing custom code.
Best regards.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
Try our FREE mapping plugin! MapSVG - easy Google maps, interactive SVG maps and floor plans, choropleth maps and much more - https://wordpress.org/plugins/mapsvg-lite-interactive-vector-maps/
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 Bogdan
Many thanks for that - I should have thought it through myself !! Old age I guess !.
However I do still have a slight issue. I have created a view from a master table and foreign key tables It imports into WPtables fine however I have a problem with updating.I only wish to update a field from the master table. I can do this with no problem in the excel type editor, ( attached image shows master table fields-grey, foreign fields Yellow and Green, editing Textrec field only) however when I switch to standard editor I get the attached error saying cannot join more than one table when I apply udates. It would seem the two editors generate different scripts !. Does the excel editor just update the cell whereas the standard editor tries to update the row even when there are no changes to fields? This will give me a problem as I would like some users to just update using the web page view. Can I get the excel editor to display on the webpage ? Any ideas??
Cheers
Richard
HI Richard,
Probably the issue is because of the JOIN.
If you take a look at our documentation you will see this notice
As for the excel like view you can paste it in the page by choosing to display the table in excel like view ( attachment)
Keep in mind that there are some limitations in the Excel like view - link.
Best regards.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
Try our FREE mapping plugin! MapSVG - easy Google maps, interactive SVG maps and floor plans, choropleth maps and much more - https://wordpress.org/plugins/mapsvg-lite-interactive-vector-maps/
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 Bogdan
I understood this to mean that although a view came from two tables, one could edit one of them - but that doesn't seem to be the case ! - Had read all the notes but I guess had misunderstood - I had assumed a view would be treated as one table !
Anyway - many thanks and we will work around it somehow. How do you manage an order situation where the order table will require to pull a vendor and an order item type say ? I an sure many of your users would have that type of configuration
Thanks again
Richard
Hi aqain Bogdan
I caanot see where to replicate your screen dump. Where do I find that option box?? !
Cheers
Richard
HI Richard,
When you are pasting the shortcode in the page and click on this icon ( attachment 1)
When you click on that icon you can choose table view like from the attachment below in my previous reply.
Best regards.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
Try our FREE mapping plugin! MapSVG - easy Google maps, interactive SVG maps and floor plans, choropleth maps and much more - https://wordpress.org/plugins/mapsvg-lite-interactive-vector-maps/
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
Yeh - You learn something new very day !!!! - Thanks
I am going to have to play about !!
Many thanks
Richard
Hi Richard,
You are welcome.
I hope that you will find something that will good use.
Best regards.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
Try our FREE mapping plugin! MapSVG - easy Google maps, interactive SVG maps and floor plans, choropleth maps and much more - https://wordpress.org/plugins/mapsvg-lite-interactive-vector-maps/
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