I think I brought a similar issue to your attention once before and wanted to see if anything has been done to address it. It is something that has been bothering me for months now and I seem to have run out of workarounds. The table, which uses foreign keys to match teachers and students (IDs to names) works fine on the back end but is stuck on the loader on the front end. What seems to be the issue is CONCAT, which I use to merge the first and last names in the related tables—works fine if I select either last or first name without concatenating them.
However, what puzzles me is that it works great on the back end but not on the front and since there are multiple entries that share last names, I really need to be able to tell them apart.
My sincerest apology, there was an error on this ticket from my end - I wrote a reply, but it somehow did not get sent properly.
This will not happen again, thank you very much for your patience.
Could you show us more details, how you isolated the corrupted entry in one of the tables which was the root of this issue?
It does seem strange, as you pointed out, why that issue would only show on the front-end and not on the back-end version of the table, but basically, I believe when our developers designed the foreign key relation functionality - it was not intended to be used via a CONCAT made column;
Since the SQL Query based tables, for their foreign key column feature via our Plugin - each column included in the relation, has to be an existing column in the Database;
And when you are doing the CONCAT, of course, that creates a 'custom column' merged from multiple other columns, so it does not exist as such in the source data; That is where our Plugin could struggle and there might be issues, but I am glad to see you managed to make it work in the end.
I will ask our developers about this, if I am right and if they have any additional useful advice to share. As soon as they check all the details, we will report back, but see if you can provide us some additional details - show us how your Query looks; How you isolated that corrupted entry, etc, to provide the devs with a clear picture.
I have a table that records links between students and teachers. Each record pulls data from two different tables—one containing students and the other teachers—based on a unique ID. The table would break whenever I tried to set up foreign key to a table containing a concatenated full name and display it on the front end. Please note that it only broke on the front end, not the back, when I enabled editing (it worked fine as view-only).
The "reference table" contained the column
concat(lname, ' ',fname, ' ',state) as fullname
On how I discovered it, I got lucky after a while. As a temporary fix, I just limited the reference to the teacher's last name. However, my client needs to choose the appropriate teacher from a drop-down menu, which presented a problem in case of namesakes (over 5000 records). After cleaning up trailing spaces from all entries (70+ columns), I tried setting up a concat column directly in WPDT, in phpmyadmin, and even by creating a view (that obviously didn't work) without success.
I needed to come up with a workaround including an additional identifier beyond the last name so I just tried concatenating it with different columns, which worked. This is how I managed to narrow the problem down to a corrupted record in the first name column. From there, it was just a matter of finding anything weird so I ran
SELECT id, fname FROM teachers WHERE fname REGEXP '[^a-zA-Z ]';
at which point I was left with about 100 records and one of them turned out to contain two dots (something like johny..cash). Removing them did it.
Thank you very much for sharing this use-case and the details how you made a working solution in the end; as well as finding those corrupt records. This will be useful to know, not only for other users but perhaps for our developers as well.
We appreciate it.
As you know, if you find anything else we could advise on, please don't hesitate to reach out to us.
Hi there!
I think I brought a similar issue to your attention once before and wanted to see if anything has been done to address it. It is something that has been bothering me for months now and I seem to have run out of workarounds. The table, which uses foreign keys to match teachers and students (IDs to names) works fine on the back end but is stuck on the loader on the front end. What seems to be the issue is CONCAT, which I use to merge the first and last names in the related tables—works fine if I select either last or first name without concatenating them.
However, what puzzles me is that it works great on the back end but not on the front and since there are multiple entries that share last names, I really need to be able to tell them apart.
Thanks for the support,
Rok
Hi there,
So it turns out that what broke the table was a corrupted entry in one of my tables. Still, I wonder why it would only break on the front end.
Best,
Rok
Hi Rok,
My sincerest apology, there was an error on this ticket from my end - I wrote a reply, but it somehow did not get sent properly.
This will not happen again, thank you very much for your patience.
Could you show us more details, how you isolated the corrupted entry in one of the tables which was the root of this issue?
It does seem strange, as you pointed out, why that issue would only show on the front-end and not on the back-end version of the table, but basically, I believe when our developers designed the foreign key relation functionality - it was not intended to be used via a CONCAT made column;
Since the SQL Query based tables, for their foreign key column feature via our Plugin - each column included in the relation, has to be an existing column in the Database;
And when you are doing the CONCAT, of course, that creates a 'custom column' merged from multiple other columns, so it does not exist as such in the source data;
That is where our Plugin could struggle and there might be issues, but I am glad to see you managed to make it work in the end.
I will ask our developers about this, if I am right and if they have any additional useful advice to share.
As soon as they check all the details, we will report back, but see if you can provide us some additional details - show us how your Query looks;
How you isolated that corrupted entry, etc, to provide the devs with a clear picture.
Thank you.
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 Miloš,
I have a table that records links between students and teachers. Each record pulls data from two different tables—one containing students and the other teachers—based on a unique ID. The table would break whenever I tried to set up foreign key to a table containing a concatenated full name and display it on the front end. Please note that it only broke on the front end, not the back, when I enabled editing (it worked fine as view-only).
The "reference table" contained the column
concat(lname, ' ',fname, ' ',state) as fullname
On how I discovered it, I got lucky after a while. As a temporary fix, I just limited the reference to the teacher's last name. However, my client needs to choose the appropriate teacher from a drop-down menu, which presented a problem in case of namesakes (over 5000 records). After cleaning up trailing spaces from all entries (70+ columns), I tried setting up a concat column directly in WPDT, in phpmyadmin, and even by creating a view (that obviously didn't work) without success.
I needed to come up with a workaround including an additional identifier beyond the last name so I just tried concatenating it with different columns, which worked. This is how I managed to narrow the problem down to a corrupted record in the first name column. From there, it was just a matter of finding anything weird so I ran
SELECT id, fname
FROM teachers
WHERE fname REGEXP '[^a-zA-Z ]';
at which point I was left with about 100 records and one of them turned out to contain two dots (something like johny..cash). Removing them did it.
Hope this helps!
Hi Rok,
Thank you very much for sharing this use-case and the details how you made a working solution in the end;
as well as finding those corrupt records. This will be useful to know, not only for other users but perhaps for our developers as well.
We appreciate it.
As you know, if you find anything else we could advise on, please don't hesitate to reach out to us.
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