Hi Victor,

I am sorry, but if you use an SQL JOIN or similar to merge data from multiple tables, you will probably have issues in editing of that table.

When you are using JOINS with more tables please note that in wpdatatables just one table can be editable at the moment and that is the reason why you can't edit fields in table that you made from two (or more) tables.

We noted this under the editing limitations in our Documentation.

Please go through that Documentation Page to see all the important notes, such as :

When you assign the ID column for editing, that must be a different column than "user ID" column if you wish to enable to "Limit each user to only see and edit their own rows" as explained on this Page.

So, I do not entirely understand the full details of your use case.

But, if you need to make a relation between multiple tables and call multiple SQL Tables in one Query;

in that case, you will probably have editing issues;

The only native way in our Plugin to make foreign key relations, is our own foreign key column feature.

It will allow you to have "Single value selectBox" for editing, to choose from available values of the foreign Table's column.

Please check more with our example on that here.

If you are still unable to resolve this, and perhaps I am not fully understanding the use-case;

in that case, please try to clarify your use-case more, what do you mean in the part about automatically assigning the user ID column and the ID column for editing, etc.

The best is if you can add a Video to show more details.

If the Video shows any sensitive data, just send it as a PRIVATE reply;

or if it gets too large, upload to weTransfer and send us a download link.

Thank you.

Hi Victor,

I think I can see what the main limitation here is for your use-case.

If I understood, you made these relations in the Database itself between those SQL tables, and it seems you also added some 'constraints' - but we can not comment about your source data too much, let's focus on our Plugin's functionality and what the said limitation is for your use-case.

For example, when you make an SQL Query based Table in our Plugin, and if you call the table 'phone' in our SQL Table:

Our SQL Query table is not capable to see all these relations you made to other SQL Tables.

If you do a Query in our Plugin like :

SELECT * FROM phone

Our Plugin's SQL Table will only see the data in this specific table, not the relations towards other tables.

Now, in regards to using our Foreign Key Column feature and editing.

I can see what you wish to achieve - basically, you would like our SQL table to 'see' the relation you made and to 'automatically' link the WP user ID and auto-populate a value.

I am sorry to disappoint you, but that is not how our Foreign Key Feature works.

-

When you set a column first to integer, then switch it to String and configure your Foreign relation in our Table;

Then if you edit to make a new row - this row does not have a way to auto-populate the foreign key Column.

As mentioned, the only editing possible for Foreign Key Column is to pick from the Single Value Dropdown - it loads all the possible unique values for you from the Foreign Table and you need to manually choose the editing value for the new row.

-

The only way you can 'pre-populate' a column like this is manually, let's say if you do three rows, and insert integer number 1, 2 and 3.

Now, if you switch this to String and configure the Foreign Key relation to pull from foreign SQL Table;

You first need to make this SQL table in our Plugin - then choose it, choose the column ( store ID and displayed ID).

Then, depending on which integer you had in each row > the foreign displayed value will come up on it.

-

If we had a foreign table with some names for users - and each user has their ID column;

In this example, we had 1, 2, 3 - so the first row will show the displayed name from your Foreign Table associated to ID 1, second row name from ID 2, third row name from ID 3 and so on...

but that only happens if you already edited these rows in this 'current table'.

Our Plugin does not have the capability to 'recognise' a relation which you made between the Database Tables to automatically 'know a value' for editing, if that makes sense.

To summarize, for any new row from a Foreign Key Column - you can only set it as Single Value Selectbox - and you can choose from all the unique values coming from the Foreign Table;

it is not possible to 'auto populate' a column based from another column's value, if that makes sense.

-

Please go through more details on our example here - you can see how we created the "orders' table made by pulling foreign key relation values from other SQL tables such as "employees", "Customers", etc, and see how it works, I hope it might help to clarify what I described a bit more.

Let me know if you have any additional questions.

Hi Victor,

Sorry, I just forgot to add a couple of useful details/points to know, maybe this can help in terms of 'auto populating' some data from the 'currently logged user', if you try a different approach.

1. First, to clarify how our "User can see and edit only own rows" feature works - please check how it works on the Documentation in more details.

When you activate this feature - you need to have an integer column - this can not work for String Columns and set it for the chosen "user ID" Column in the Editing options.

Now, when a logged user adds a new row - this field automatically inserts their current user ID.

I will also give you a basic example with my Manual Table, just to see the main steps needed.

The most important thing is to have an Editable Table for this feature.

Please check here how to make editable tables and how our editing works.

Now, you need to have an integer column which will be used to store User ID rows ( it pulls it from wp_users for the currently logged user id).

In my Table, i just made an integer field named "userid".

This checks with the currently logged user ID  ( this data is stored in wp_users Table in your WP Database for each user).

In my case, my Admin user ID is 1.

So, my user will only see the rows of "userid" column that are 1, he can't see other rows.

( Only Admin can see all rows in the back-end if you enable "Show all rows in back-end", but on front-end not even Admins can see or Edit rows which are not theirs.)

For this to work, you have to make sure to select the right column as the user ID, in the Editing Settings.

Now, any user that makes a new row to this table,

this user ID column will automatically populate the row with their user ID.

You can also hide this column from the table if you wish and it will still work.

You can see here more about hiding/showing columns.

-

The "ID column for editing", this has to be a unique auto increment integer row identifier in the Database table in order for our editing to work, as explained in our Editing Documentation.

2. The only other way how you can auto-populate for the 'currently logged user details' is our Placeholders.

For example, if you use %CURRENT_USER_LOGIN% – this placeholder will be replaced with the currently logged-in username (if the user is logged in).

Please check more about our dynamic placeholders here.

3. But other than that, what you tried to achieve, such as our SQL table to automatically pick up your relations between SQL Tables, that is not possible with our Plugin.

When you are editing, you can only manually choose from the Single value Dropdown - from all the values coming from the foreign Table for the chosen column.

Compared to an SQL JOIN - with the JOIN, you are merging all the data which are 'matched' between "ID" columns as in your case - but if you use a JOIN, that table can't be edited from our Plugin.

I hope that helps to clarify the limitations.

Let me know if you have any questions.

Hi Victor,

You're welcome, I am happy to advise.

Yes, for points #1 and #2, if you manually create an integer Column and insert user ID in each row manually for the users;

For example, if you insert rows like 1, 2, 3 and so on...

And if there is a user from some foreign Table that has username stored in the row with their user ID 1;

Then, you can change this Column to String, and configure your relation, set the 'store value' to pull from the user ID column and 'displayed value' to pull the username - Then the username value will render, like on your screenshot.

If you set to limit the table in the editing to only see and edit their own rows - Then it should work as you wrote, it should only show the rows associated with their user ID.

The foreign Key available values for editing will be limited in the same way - If you set this limit "user can only see and edit own rows" on the Foreign Table;

Then this will limit the 'choices' of unique values for the Single Value Selectbox dropdown for editing in this Table and here, the user can only choose the values associated to their currently logged user ID.

5. Your question : Is it possible to select by default the first element in dropdown?, so the user doesn't need to select the user_id because this is redundant. 

If you wish to auto-populate a Column with the currently logged User ID, you can do so with our Manual table's hidden/dynamic fields.

Please check out more details on this Documentation how you can use these fields.

This is exclusive only for Manual Tables, though, it is not available for other Table types.

Please note, the hidden/dynamic field can not be editable, it only auto-populates the placeholder you choose.

It is hidden by default, but you can show the column in the visibility options if needed.

And, you can not do this on a Foreign Key Column - the Hidden/Dynamic field is a specific type of field, such as shown in the Documentation examples.

So if you make an SQL Query table, you won't see these hidden/dynamic auto-populate fields available.

But this is not possible for Foreign Key columns, if you mean to auto-populate a new row on it.

In a Foreign Key Column - we can not pre-populate it with any placeholder such as for example the :

• %CURRENT_USER_ID% – this placeholder will be replaced with the ID of a currently logged-in user (if the user is logged in).

These placeholders can only be used if the Editor input type is "one line edit" or "multi line edit".

For Foreign Key column you can only use Single Value Selectbox, and that is why it is not possible.

For any new row in Foreign Key Column, you need to manually insert a value via the dropdown.

Or also is it possible to include some javascript logic in frontend so I can select the first element and maybe I would like to include css to hide the user_id field?

If you need to hide any Column from the Editing Modal, you can do CSS like this :

#table_1_edit_dialog > div.row.wdt-edit-dialog-fields-block > div:nth-child(3) {
    display: none !important;
}

In that example, I set a "one line Editor type" on an integer Column;

And I set the placeholder to auto-populate currently logged user ID - then hide it with CSS.

You can add this CSS to the Table's Customize/Custom CSS.

In my case, that 'nth child' selector was the third child element - but on your table, you need to find the right 'nth child number' and experiment until you 'hit' the right selector for your editable field you need to hide.

 I hope this helps to clarify the current capabilities and limitations.