This is not critical, but having an odd behavior when limiting editing of data. It is not critical because I can limit the Data Source SQL and it still works for us, but just keeps a second check off of our Data Source SQL.
I combine two tables in the Data Source by their Primary Keys and filter by one field for a NEW client on one of the tables. In short, we have the main client table with a status that can be NEW, but I also have a NEW client checklist table that is populated when a client is first inserted into MYSQL. The check you'll see in the Data Source is just a redundant check for the NEW value. This is only if somehow a client was not on the checklist table as a new client but showed as NEW in the main client.
Getting to what is odd is that when I limit the editing, the two records assigned to my WP USERID show up when I just use one table, when I use the other, it shows no data. I figure it was the join, but when I keep the two tables in the Data Source, and remove the Limit Editing, all of the 3 New Clients show up, 2 as expected with my WP USER ID and 1 that belongs to another WP User. The USER ID value for me is correct, it is an Integer. Why would the EDITING remove my two records, they show up when editing is not limited in ANY case, and I proved that my USER ID is correct, yet the EDIT limit drops my data from view only when I have the two tables joined.
SELECT ChkLstNewClient.`ClientID`, ChkLstNewClient.`ChkNewAgreement`, ChkLstNewClient.`ChkNewAuth2Rep`, ChkLstNewClient.`FaceSheet`, ChkLstNewClient.`LTC2PASReq`, ChkLstNewClient.`FinPurpleSheet`, ChkLstNewClient.`StmtsRecMIASheet`, ChkLstNewClient.`MoneyTrail`, ChkLstNewClient.`CashFlow`, ChkLstNewClient.`SchedApptMailin`, ChkLstNewClient.`Application`, ChkLstNewClient.`WorkerID`, Clients.`Status` FROM ChkLstNewClient, Clients WHERE Clients.`Status` = "New" AND Clients.`NSS` = ChkLstNewClient.`ClientID`
Attachment with 00 at end shows the SQL above without the Editing limited
Attachment with 01 at end shows the SQL above with the editing by WorkerID and data is not there anymore.
I have had editing data limited by same WorkerID from our database and it always works. Assumption is that the join is freaking it out, but I see the data is there properly with the join so very confusing.
My workaround is to eliminate the second data check for NEW from Clients, but I should not have to if the data does show up properly when EDIT is off. Yet EDIT works on the Worker ID otherwise when we use it or when the join is off.
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.
I had a feeling it was, sorry I did not locate that in the doc. Actually I am only trying to update one table, the other is there to ensure the ID that sits on both tables is valid on both for the said update for the one table to go ahead. Personally the database itself needs to have the referential integrity. I'll obviously stop using the query to support that part.
This is not critical, but having an odd behavior when limiting editing of data. It is not critical because I can limit the Data Source SQL and it still works for us, but just keeps a second check off of our Data Source SQL.
I combine two tables in the Data Source by their Primary Keys and filter by one field for a NEW client on one of the tables. In short, we have the main client table with a status that can be NEW, but I also have a NEW client checklist table that is populated when a client is first inserted into MYSQL. The check you'll see in the Data Source is just a redundant check for the NEW value. This is only if somehow a client was not on the checklist table as a new client but showed as NEW in the main client.
Getting to what is odd is that when I limit the editing, the two records assigned to my WP USERID show up when I just use one table, when I use the other, it shows no data. I figure it was the join, but when I keep the two tables in the Data Source, and remove the Limit Editing, all of the 3 New Clients show up, 2 as expected with my WP USER ID and 1 that belongs to another WP User. The USER ID value for me is correct, it is an Integer. Why would the EDITING remove my two records, they show up when editing is not limited in ANY case, and I proved that my USER ID is correct, yet the EDIT limit drops my data from view only when I have the two tables joined.
SELECT ChkLstNewClient.`ClientID`,
ChkLstNewClient.`ChkNewAgreement`,
ChkLstNewClient.`ChkNewAuth2Rep`,
ChkLstNewClient.`FaceSheet`,
ChkLstNewClient.`LTC2PASReq`,
ChkLstNewClient.`FinPurpleSheet`,
ChkLstNewClient.`StmtsRecMIASheet`,
ChkLstNewClient.`MoneyTrail`,
ChkLstNewClient.`CashFlow`,
ChkLstNewClient.`SchedApptMailin`,
ChkLstNewClient.`Application`,
ChkLstNewClient.`WorkerID`,
Clients.`Status`
FROM ChkLstNewClient, Clients
WHERE Clients.`Status` = "New" AND Clients.`NSS` = ChkLstNewClient.`ClientID`
Attachment with 00 at end shows the SQL above without the Editing limited
Attachment with 01 at end shows the SQL above with the editing by WorkerID and data is not there anymore.
I have had editing data limited by same WorkerID from our database and it always works. Assumption is that the join is freaking it out, but I see the data is there properly with the join so very confusing.
My workaround is to eliminate the second data check for NEW from Clients, but I should not have to if the data does show up properly when EDIT is off. Yet EDIT works on the Worker ID otherwise when we use it or when the join is off.
Alan
Hi Alan,
Thank you for your purchase.
Probably the issue is because of the JOIN.
If you take a look at our documentation you will see this notice
Best regards.
Kind Regards,
Miloš Jovanović
[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 Bogdan, welcome back from vac.
I had a feeling it was, sorry I did not locate that in the doc. Actually I am only trying to update one table, the other is there to ensure the ID that sits on both tables is valid on both for the said update for the one table to go ahead. Personally the database itself needs to have the referential integrity. I'll obviously stop using the query to support that part.
Thanks again for letting me know.
Alan
This can be closed, I do not introduce any more than 1 table per SQL for a table.