Hi, Rory.

Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.

-

Yes, i believe this should be possible with our plugin, we can provide advice and guidance to get you started,

just to note that our support does not cover building tables or database,

but we will do our best to advise with examples.

-

 For the Top School League Table,

you can make an editable table which will have limited/filtered rows for each Teacher to only see the rows that belong to their School,

and for the NationWide League Table, you can have another SQL based table which will load all the rows/without the view limitation,

but on that table you can either disable editing completely, or just allow Admins to edit.

( We have "Editor roles" option available in the table's Editing Tab, where you can limit editing to just specific user roles, everyone else can just view the rows)

For example, you can have one main/master Manual table which will store all the data in the database.

Then, you can check this table's MySQL Table name from the Editing tab in the back-end table settings,

and you will be able to make multiple SQL tables that pulls data from the same "master table",

and any editing you do on all these other SQL tables are going to be stored in the same table in the database,

This will be my example table :

Now, to make a new SQL Table from it,

first, check in Editing Tab/MySQL table name for editing:

in my example, the Manual table name is wp_wpdatatable_39,

so i make a new SQL table that will pull data from it;

and we can choose all columns like :

SELECT * FROM Table_Name

or just specific columns;

we can also have completely different settings/filtering/sorting and limitations for front-end users on each table;

and you can make multiple SQL Query tables that pull the same data from the "main table".

SELECT  column_name1,
        column_name2,
        column_name3
FROM table_name

-

In this example, if i want to limit to only show the "Company" column, and i will leave wdt_ID ( because we got to have row ID)  and userid ( if we want userID for limiting rows per currently logged user ID),

when i hide those columns in the end, the front-end user will only see the "company" column.

SELECT  wdt_ID,     
   userid,   
    company  
     FROM wp_wpdatatable_39

Later, you can make another SQL Table that will show some other columns, have other filters and so on ( depending on what you need to achieve)...

You can see my Video example here https://watch.screencastify.com/v/jOXrsJ9jaspWG5m3VqZH

-

Important to note, these examples are to limit what each user ID can see and edit,

you can see more about this feature here.

If you need to create groups of users that can edit for each School,

and to have the "school" column only load rows for the group of users per school,

there is a more complex example i will show you.

You can add custom User Roles, and for each School, add a Custom Role,

for example for 'Leeds Grammar' you can add a user role something like "Leeds Grammar teacher",

and add this Role to all the Teachers login's through a Membership Plugin,

or the "User Role Editor" Plugin.

-

We don't have an easy built-in feature to achieve filtering per user role,

but there is a custom workaround that we can suggest.

You could add custom user roles, through the Membership, and here is an example of a table.

If the dependency is a user role, for example, we could make it work.

If I have this table:

This is a manual table which has manually entered IDs from wp_users and roles from wp_usermeta:

So, then I go to create an SQL query based table which will display entries based on user roles using this query:

SELECT  new_table.id AS ID,  
      new_table.role AS Role,   
     new_table.product AS Product,   
     new_table.amount AS Amount
FROM wp_wpdatatable_6 AS new_table
JOIN wp_usermeta AS new_table_1
ON new_table_1.user_id = %CURRENT_USER_ID%
AND new_table_1.meta_value LIKE CONCAT('%', new_table.role, '%')

When I'm logged in with user ID = 1, I see this:

When I'm logged in with user ID = 4, I see the same thing. And when I'm logged in as one of the subscribers, I can only see the rows where role = subscriber:

The only difference is that as subscriber I don't have access to back-end, so this confirms it is working on both front and back.

So, we're only left with hiding unnecessary columns, adding names, and so on.

Hope this helps.

For your specific use-case, you would modify our example to have your custom user roles instead of the ones we used, but i hope that this helps to give you an idea of the plugin's capabilities.

And, just wanted to point out that we have a Demo Sandbox site, where you can try out the plugin functionalities, on this link

and there is a 15-day money-back guarantee period, So if you purchase the plugin, you can safely fully test it out, and if it doesn't fit your needs you can request and receive a refund in that period. ( same goes for all plugins and add-ons)

Let us know if you have additional questions.

Hi, Rory.

I just forgot to add,

you can also do an alternate approach, like building an individual Table for each School in your SQL Database,

or through our Manual Tables ( which make a new table in your database with its own set of columns and rows).

For example, it can be one large Master Manual Table that holds data for all schools.

Then you can have one restricted page for editing of each School,

and have an SQL Table on each page, like SQL Query Table 1 can be :

SELECT * FROM AllSchools
WHERE School = 'Leeds Grammar'

and post this on a page 1 that only users with custom User Role "Leeds Grammar editor/teacher" has,

and restrict access for all other users with a Membership Plugin.

Then this SQL table will only load the rows for that specific school,

and on Page 2 you can have another SQL Table with "Burley Park" rows only,  with the shortcode for that SQL table,

which can only be accessed by logged users with another custom Role, and so on...

-

You can also try our built-in foreign Key relations between the School Tables, if you wish.

Foreign key columns be editable as a single value selectbox.

So there could be a couple of different ways.

I hope this helps.