I want my Grid’s SQL to work on a table a user selects in Search (the grid opens with search).
Assume the database is called dbase, I have a search (SELECT type) field called {Table} that has this SQL:
SELECT table_name FROM information_schema.tables WHERE table_schema=‘dbase’
In onValidate of the search I have [tablevar] = {Table}, setting a global variable [tablevar] to the selected table.
This works and the tables in dbase are listed for user to choose from.
In onAppInit, I’ve also set [tablevar] = ‘mytable’ (mytable being the default table if nothing was selected in Search.)
In the SQL tab, I have the SQL with “SELECT …FROM mytable…” and for the 2 fields associated with “Variable for table” I have “mytable” and “tablevar”. My understanding is SC should replace mytable with contents of [tablevar] at runtime.
Well, from the results, if I choose any other table it returns no records found. It only finds contents for the default table. Meanwhile the other tables have entries that match the search criteria when I look at phpmyadmin.
There must be some little configuration I’m missing out: grateful for some help.