You can find out the database-level name of Agiloft tables in Agiloft GUI directly via Table Wizard: Setup > Table > YOUR TABLE > Edit/General > TableName. For example the database name for table Support Case in standard Demo KB is "sw_9991f_case".
However, these names may change during upgrades or if you export/import the knowledgebase onto a new server. A more reliable method is to obtain the physical table name from the Logical Table Name that is provided in the GUI: Navigate to Setup > Table > YOUR TABLE > Edit > General tab. Use the following query to obtain the physical table name:
select a.dbname from swtable a, swproject b where a.name='case' and a.swprojectid=b.swprojectid and b.name='<Your KB Name>';.
You should obtain a result such as:
+----------------+ | dbname | +----------------+ | sw_20288f_case | +----------------+
Please be aware that since Agiloft is a business-level information management system not all data available via GUI in a certain Agiloft table can be directly found in the corresponding database table.
- Some of the data is in a serialized format unsuitable for direct queries or linking, for example multi-value linked fields.
When a column of a record in the target table imports a column of a record in a donor table on the level of database this is expressed with indirect links via a linking table.
EW caches the values of imported fields such as the "Updated By" field in the local table to improve performance when searching or reading records, so the data from such fields is available in the target table using the Field DBName shown in Setup > Table > Edit >Fields tab.
However if the application requires extra data from the source table, obtaining it becomes more difficult. Since the link is defined on the level of meta-data in order to use such relationship to build a true "join" between two tables an external application would have to repeat the logic of linking Agiloft knowledgebase meta-data with the linking table. Instead please apply the following workaround.
Workaround for single-value single- or multiple-column Linked Fields:
There is one feasible way of querying tables linked in Agiloft directly. It will only work on single-value single- or multiple-column linked fields. In the GUI one has to add the ID field to the set of fields imported from the donor table into the target one. This will cause a column to be added to the target table. Though there is no strict relationship on the database level the values in this column can be used to forge links between two tables in the external reporting tool.
Choices values are stored as identifiers in the database. These identifiers may change when the knowledgebase is reconfigured by the administrator. In general there is no explicit guarantee that this internal values will stay the same during the lifetime of the product/knowledgebase.
As such these should be resolved against swchoicelines table bound by the desired value in "choicelinetext" column, linked to swchoices table via "choiceid" column, bound by the name of the search in "name" column and linked to swproject table via swprojectid column, bound by the name of the knowledgebase in the name column. The name of the search can be found in GUI via Setup > System > Manage Choice Lists > Choice Name.
The following query obtains the ID that is used to represent the Yes value in the Demo KB for the yes_no choice list:
select l.choicelineid from swchoicelines l, swchoices c, swproject p where l.choicelinetext='Yes' and l.choiceid=c.choiceid and c.name='yes_no' and c.swprojectid=p.swprojectid and p.name='Demo';