You can get to the database schema using SQLTABLES, SQLCOLUMNS and SQLTYPES. I use these commands to load the schema into tables, but you may have to do it a certain way via resident tables if you need to filter them. Something like ...
ODBC CONNECT TO ???;
AllTables: // for investigation into all available tables
//TABLE_QUALIFIER as All.tblQualifier, //no data
TABLE_OWNER as All.tblOwner,
TABLE_NAME as All.tblName,
COLUMN_NAME as All.tblColName,
DATA_TYPE as All.tblColDataType,
//TYPE_NAME as All.tblColTypeName, //no data
PRECISION as All.tblColPrecision,
LENGTH as All.tblColLength,
SCALE as All.tblColScale,
RADIX as All.tblColRadix,
NULLABLE as All.tblColNullble,
//REMARKS as tblColRmrks, //no data
//EXTENDED_ID as tblColExtID, //no data
DATA_SOURCE as All.tblDSource
left join (AllTables)
dtDataType as All.tblColDataType,
dtTypeNameList as All.dtTypeNameList
If you then want to interrogate data within the fields, you can probably build a macro to do SQL interrogation via ADODB based on user selections or maybe the new Direct Discovery can help there. Another way is to just loop through each table in the load script with Qualify (as you stated) and load * (but this might create a very large file).
Okay, understand it now.
Create yourself the following variables ...
objFieldList = Concat($Field,';') -- assumes none of your fields have ';' char in them
objField1 = RangeMaxString(subField(objFieldList,';',1),0) -- or use a space instead of zero
objField2 = RangeMaxString(subField(objFieldList,';',2),0)
... and repeat until enough variables for fields as required. This could easily be created in a loop in the script.
Then, in a chart object create calculated dimensions and a dummy expression (=1) as ...
=$(objField2) .. etc
EDIT: Ignore the RangeMaxString above, you need to wrap some control around the Calculated dimension to hide the column when that variable is not relevant - will work that out.
2. Add a listbox and add the system field $Table
3. Create a search object and select Search in List of Fields and use the expression =GetFieldSelections([$Field])
4. You'll have to use a macro if you want to dynamically create a table box with all the columns of a table.
Set Fields = ActiveDocument.Fields("$Field").GetPossibleValues
Set tb= ActiveDocument.GetSheetObject("TB07")
for i = 0 to tb.getcolumncount -1
for i = 0 to Fields.Count -1
Change TB07 with the ID of your table box.
Thanks for your response.
Points 2 & 4 works perfectly. WHere as point 3 didn't work for me.It says 'No matches found in search'.
Also in Point 4, is there any way I can modify the 'Label' for the table object field. As I have used Qualify *. table names are coming in the field names. I want to replace the tablename. with "".
Thanks in advance for your help.