Hi,

I'm having a problem with the foreign key look up and i hope you can help.

The drop down is not showing all entries when configured to use a foreign key lookup.

--

After viewing you support documentation i have configured a column to look at another column in another table via the data tab for that column.

at first it seams to work when there is only one row/record in the foreign key table, once i add other rows/records to the foreign key table, in the drop-down for the first table only the first row/record is showing. none of the other rows/records are available for selection.

for reference the config is the following

1) Table one = customer info - Foreign key 

2) Table two = order table - lookup customer name in table one 

3) both tables have a userID column as part of the table setup for ID purposes 

4) both tables have the wdt_id as it's primary key 

- Bootstrap is enabled for both front & back end 

Update, the rows are sowing in pre-defined values but not in the edit or new form 

Screen Shots

Hi Bogdan, Hope your well.

I get the same result either way, with or without user edit enabled 

just to be sure of no conflicts i built a fresh WP install  as a temp test  and I'm getting the same results their too 

i should mention versions 

WP = 4.9.8

WPDataTables = 2.3

Correction 

i just retested without "Users see and edit only own data" and it works.

is there a way to get this to work with this option enabled,

As a suggestion;

Under "Use values from another wpDataTable" once the table has been selected i would provide another drop-down asking for the user_id field so that the foreign key lookup knows what the other table contains and which user has access to which rows. 

But thats not whats happening 

both tables have a user_id column, but when the"Users can see and edit their own data" option is enabled the dropdown on the new & edit forms only display already input entries into the local table, It's not showing all of the entries by that one user from the remote table.  

This may better explain what's happening.

The foreign key lookup isn't pulling all of the values from the remote table it is only showing the value i have input when "Users can see and edit their own data"  is disabled 

PROBLEM STATEMENT

When both tables have the  "Users can see and edit their own data"  option enabled and the User ID column is set, the forms do not show any dropdown options on any or all tables, the user should be able to see/access all relevant data associated with thier user_id.

The foreign key isn't passing/looking for the user_id in the remote table 

As a test - if i remove the values from the table doing the lookup then NO data is displayed in the dropdown from the remote (FOREIGN KEY) table. see the VALUES REMOVED.png 

the foreign key isn't looking up the remote table when the "Users can see and edit their own data"  is enabled

USE CASE

1) user should only be able to see their own data on all tables 

2) User should be able to lookup/pull their own data from other tables (basic relational linking) using the Foreign Key 

3) User should be able to store data from remote tables, to a local table, and have the solution store the values selected.

i.e When the "Users can see and edit their own data"  is enabled on any or all tables, the user should be able to see/access all relevant data associated with their user_id across all rows in all tables that are linked.

SOLUTION

Under "Use values from another wpDataTable"  in the field setting page i would add a user_id field  for the remote table.

i.e. 

- Selected the remote table under "Choose a source wpDataTable"  as it is done now 

- ADD FIELD *** Provide another drop-down asking for the user_id field for the remote table foreign key lookup fuction knows what the other table contains and which user has access to which rows***. 

- Select "Display Value" as it is done now 

- Select "Store Value"  as it is done now 

Please help 

Many Thanks 

Thanks Bogdan

Hi Bogdan 

any news on when this will be fixed and released

Thanks for the update 

Hi Bogdan

i hope your well.

i was wondering what the timeline was likely to be for the foreign key lookup fix.

a rough estimate would be fine, i can't move forward with WPDataTables until the fix is in place