A Data Project consists of one or more data administration and/or static pages. You can use the role attribute to give users access to Data Projects pages.
Use the $$USER$$ or $$USERID$$ environment variables to get access to the current user:
$$USERID$$ > wp_user.user_id
$$USER$$ > wp_user.user_login
In a student administration system for example, you might need a page to inform students about their personal results. Suppose student results are stored in table STUDENT_RESULTS which has a column STUDENT_NAME that holds the WordPress username of the student (or STUDENT_ID that holds the WordPress userid of the student).
To give students access to thier own data only, you can add the following WHERE clause to the student result page:
STUDENT_NAME = $$USER$$ (or STUDENT_ID = $$USERID$$)Students will see their own results only. If you have users who need to see all results, you might need a second student result page available to their role.
NOTES #
- You can set the default value of a table column to the WordPress username using the $$USER$$ environment variable:
Data Projects > Manage Table Options > select your table > set “Default value” to $$USER$$ - If you set the WHERE clause to “your_column_name=$$USER$$” and the default value for your_column_name to $$USER$$, users will be able to see and edit their personal data only.
- If you use the $$USERID$$ environment variable, make sure your column is numeric.
- The $$EMAIL$$ environment variable points to the WordPress user email address.
Demonstration Video #
IMPORTANT Please notice that tab Manage Table Options is no longer available. Table Options was renamed to Project Templates and can now be accessed directly from the plugin menu.
I’ve spent many hours trying to make this work with mostly frustration to show. The directions above were confusing. The term “WordPress username” appears ambiguous. If I use column user_login in wp_users, adding a record does produce the desired user_login value in the data entry form. However, if I include the WHERE clause in my project page, I get no records.
Hi Ben,
I added a short video that shows the steps needed to get your where clause to work with the $$USER$$ environment variable. I hope this helps. Let me know if you need more assistance.
Best regards,
Peter
Hi Peter
I see there is a $$USER$$ variable – is there a $$POST_ID$$ variable? I would like to filter records in a view (that contains the post ID as a field) to show only those associated with the post being viewed.
Thanks,
Toby
Hi Toby,
Sounds like a master detail relationship. What are your requirements? Do you have something like a screen sketch? Maybe you can use a master detail page to select a post from the master table and then show all related posts in the detail table?
Best regards,
Peter
Hi Peter – thanks for the fast response!
I want to use WP Data Access to manage display of lists of articles. I will have 2 Custom Post Types (say A and B) – I am probably using the PODS Framework rather than ACF as the article data can be stored in it’s own table and the Taxonomy data is neater.
One section of Post Type A is a list of Post Type B’s – probably title and some taxonomy data. These lists (a table) will be created in a custom user dashboard as part of a WP Data Access Data Project. So A to B will be a one-to-many and B to A will be a many-to-many relationship ie. Type A posts have only 1 list of Type B but type B posts may appear in multiple type A lists.
I envisaged a table with a row for each Type A id and associated Type B Id. Such that a Type A Post with a list of 7 type B posts will have 7 rows in the table.
A WP Data Access form will be used to allow authorised Type A Authors to select Type B Posts they want to list in their Type A post.
I may not be on the right lines with this idea but currently this is how I see it working…
Thanks,
Toby
Hi Toby,
Do you have a data model? Can you share it? Please use the contactform (just scroll down) if you prefer to send in private.
Best regards,
Peter
Hi Peter
I have just sent a link to Google Sheet which outlines the table data and links I have in mind.
kind regards,
Toby
Hi Peter,
It seems to be not possible to add %%USERID%% as a Default value for a column in the Manage columns for data entry form for table…
Is it?
Hi Toby,
Try the $$USERID$$ instead of %%ISERID%%. That should work! 🙂
Best regards,
Peter
Thanks!
And by the way, please what is the “Less?” feature column?
Hi Toby,
If you have a large number of columns in your master table, you can hide colums when the page is initialized. The plugin will add a “show more” / “show less” button to your parent table to show/hide selected columns. This works only for parent tables on parent/child pages.
Does this help?
Peter
Thanks a lot 😉
Hi Peter,
Thank you for the plugin!
Please without passing thru a list table, like the Edit only option, how can you display a form to edit fields of an existing record, let’s say on wp_users table, using the $$USERID$$ in WHERE Clause or in the Default Value?
Hi,
I would not recommend to make your wp_users table editable this way! 🙂 But suppose you would have a table with a similar structure:
– Add ID = $$USERID$$ to your default where
– Add argument action=edit to your URL
This will show the data entry form for the current user in edit mode (for view mode use: action=view). The user will see a back button to view the list. Other users will not be shown in the list because of the default where clause. If you want to hide or remove the back button checkout action hook wpda_after_list_table:
https://wpdataaccess.com/docs/hooks/wpda_after_list_table/
Good luck,
Peter
Hi Peter,
Thank you for the quick response.
I tried your advice and I got an error!
Let’s say I have the front-end page /my-profile/ with the shortcode [wpdadiehard project_id="1" page_id="1"]
The page_id=1 is set with Mode= edit, and actually showing a table with only the logged username and allows viewing and editing that user, thanks to the WHERE Clause= ID=$$USERID$$.
Great! But I’m looking to show (only and) directly the edit form for that user (without passing thru the table).
When I call this front page like that /my-profile/?action=edit, as I understood, I have the following error message:
ERROR: Wrong arguments [missing key value]
Did I miss something?
Hi,
In the publications advanced tab I am trying to do something like this but it is not working:
WHERE clause: lead_content LIKE %$$USER$$%
I want to use $$USER$$ in a LIKE operator, e.g. LIKE ‘%mystring%’
But it gives an error in the syntax like ‘mystring’%’
Hi David,
Please use the concat function like this:
lead_content LIKE concat($$USER$$, ‘%’)
Good luck,
Peter