I can't seem to make created_by (single line text), and created_on (datetime) columns that automatically fill in with %CURRENT_USER_ID% as a Foreign Key and %CURRENT_DATETIME% to have accountability within the organisation. How do I achieve this within wpdatatables?
You could, however work around this with an SQL Query based table. You can use this (I believe it's a manual table) table as a source, and use JOIN functions to pull the ID from another table (if that's why you need the foreign key).
For the timestamp column, for example, I created a simple manual table:
wdt_ID column is being automatically generated, and is usually hidden, but I've displayed it here for explanation purposes.
When I created a table, and added the first row, I could then modify it. So, in order to set the Datetime column to be auto-populated, I had to open the table in the database. It is being stored as wp_wpdatatable_6, and you can see its database name in Editing tab in the settings above the table:
Then, in PHPMyAdmin, I opened the table, and ran the following query:
After that, again in the Editing tab, above the table, I changed the "ID column for editing" from "wdt_ID" to "datetime" (as you can see in the screenshot above). Then I disabled editing for "wdt_ID" in the column settings:
datetime column, being the "ID column for editing" automatically cannot be edited, but is automatically filled.
You can also choose to hide it from view if you'd like.
Hey Aleksandar, thank you for taking the time. I am in fact only using custom SQL queries.
2. Perfect solution, thank you. I indeed found I have to run a few calculated columns in server. This will work for datetime but sadly not for a created_by column as I can't pull the user from PHPmyAdmin.
1. Is there another way for me to allow my tutors to skip selecting themselves and only have to select the pupils they tutor? Otherwise they'll have to continuously select themselves from a dropdown list which I imagine will become tedious. Or at least have the dropdown search for names starting with whatever is typed in?
(My database structure has all users in one `ST_users` table, and all sessions they input in an `ST_sessions` table. This is to allow user to be both pupil and tutor interchangeably. In `ST_sessions` only the `ST_user.id` is put into the table as `pupil_id` and `tutor_id`. All users are assigned a role through WordPress and the WordPress and ST tables only share WordPress id's.
2. Why wouldn't you be able to pull the user? If you have a good query with a good condition, I believe you could do this.
3. Just like under "2" - you should be able.
I don't understand what "pupil_id" and "tutor_id" are. If they are columns, you could pull the "pupil_id", and then add a condition to pull the name in wpDataTable.
I can't say much without taking a look at the database, though.
Sorry about that, this screenshot will hopefully make it way more clear.
Is there any way to autoselect a dropdown selection (Foreign Key in this case) of the tutor_id if wpDatatables is already set to only show the results where tutor_id = Current_User_ID?
If I turn on "only show own data", the edit field will disappear when trying to input a new entry but the database still requires a tutor_id to be filled in so I get an error.
If "cannot be empty" is set on a wpDatatables column then the error for empty entry will occur Before selecting the tutor_id = current user id. This was a bit counter intuitive for me.
Thanks for the help! And the whole product and such ;)
I can't seem to make created_by (single line text), and created_on (datetime) columns that automatically fill in with %CURRENT_USER_ID% as a Foreign Key and %CURRENT_DATETIME% to have accountability within the organisation. How do I achieve this within wpdatatables?
Hello Marco
Thank you for your purchase.
1. You can't add a placeholder to a Foreign Key column simply because the only editing type for Foreign Key columns is "Single-value selectbox".
2. %CURRENT_DATETIME% can't work with "DateTime" columns because the library we use for the calendar supports only these placeholders:
%TODAY%; %LAST_WEEK%; %THIS_WEEK%; %NEXT_WEEK%; %LAST_30_DAYS%; %LAST_MONTH%; %THIS_MONTH%; %NEXT_MONTH%
You could, however work around this with an SQL Query based table. You can use this (I believe it's a manual table) table as a source, and use JOIN functions to pull the ID from another table (if that's why you need the foreign key).
For the timestamp column, for example, I created a simple manual table:
wdt_ID column is being automatically generated, and is usually hidden, but I've displayed it here for explanation purposes.
When I created a table, and added the first row, I could then modify it. So, in order to set the Datetime column to be auto-populated, I had to open the table in the database. It is being stored as wp_wpdatatable_6, and you can see its database name in Editing tab in the settings above the table:
Then, in PHPMyAdmin, I opened the table, and ran the following query:
After that, again in the Editing tab, above the table, I changed the "ID column for editing" from "wdt_ID" to "datetime" (as you can see in the screenshot above). Then I disabled editing for "wdt_ID" in the column settings:
datetime column, being the "ID column for editing" automatically cannot be edited, but is automatically filled.
You can also choose to hide it from view if you'd like.
I hope this helps.
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
Hey Aleksandar, thank you for taking the time. I am in fact only using custom SQL queries.
2. Perfect solution, thank you. I indeed found I have to run a few calculated columns in server. This will work for datetime but sadly not for a created_by column as I can't pull the user from PHPmyAdmin.
1. Is there another way for me to allow my tutors to skip selecting themselves and only have to select the pupils they tutor? Otherwise they'll have to continuously select themselves from a dropdown list which I imagine will become tedious. Or at least have the dropdown search for names starting with whatever is typed in?
(My database structure has all users in one `ST_users` table, and all sessions they input in an `ST_sessions` table. This is to allow user to be both pupil and tutor interchangeably. In `ST_sessions` only the `ST_user.id` is put into the table as `pupil_id` and `tutor_id`. All users are assigned a role through WordPress and the WordPress and ST tables only share WordPress id's.
Hello Marco
You're welcome.
2. Why wouldn't you be able to pull the user? If you have a good query with a good condition, I believe you could do this.
3. Just like under "2" - you should be able.
I don't understand what "pupil_id" and "tutor_id" are. If they are columns, you could pull the "pupil_id", and then add a condition to pull the name in wpDataTable.
I can't say much without taking a look at the database, though.
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
Sorry about that, this screenshot will hopefully make it way more clear.
Is there any way to autoselect a dropdown selection (Foreign Key in this case) of the tutor_id if wpDatatables is already set to only show the results where tutor_id = Current_User_ID?
Perhaps these will also help.
If I turn on "only show own data", the edit field will disappear when trying to input a new entry but the database still requires a tutor_id to be filled in so I get an error.
Solved it I think:
If "cannot be empty" is set on a wpDatatables column then the error for empty entry will occur Before selecting the tutor_id = current user id. This was a bit counter intuitive for me.
Thanks for the help! And the whole product and such ;)
Hey, great news Marco
Thanks for letting me know you were able to make it work, and good call on "Cannot be empty".
If you have any further questions or issues, please feel free to open a new ticket, and we'll gladly help.
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