Service Manager / Service Center Practitioners Forum

Virtual join query

Trusted Contributor.

Virtual join query

Hi All,


I am trying to do a virtual join to the device table on the incident forms. Everything works fine for a simple virtual join. The issue is how do i make the virtual join query a table depending on values in the form. For example: I need to run a query like in $File. This does not work as it gives me an error Cannot evaluate expression. This is because the query is missing the double quotes. It should be something like" in $File". How do i write this for virtual joins? Is this even possible?







Acclaimed Contributor.

Re: Virtual join query

The query for a Virtual Join is auto-generated based on the Source and Target Fields in the link record. However, it will process hard-coded values in the Query section of the link line (it does not evaluate the Expressions section).

John Stagaman
Acclaimed Contributor.

Re: Virtual join query

Actualy, a VJ can use the expression-based query, but unlike any other link line expression, when defining a link line for a VJ, you need to use the file variable $file, not $File.

Kudos - what, where, how, and why
Want Good Answers? Ask Good Questions...
Acclaimed Contributor.

Re: Virtual join query

Here is a SM 9.21 OOB example of a hard-coded query used by a VJ:

Link: probsummary

Source Field: vj.number.1

Target Field: source

Query: depend.filename="problem" or depend.filename="incidents" or depend.filename="cm3r" or depend.filename="rootcause" or depend.filename="ocmq"


As John noted, the Query will be evaluated but it has to be based on variables/values that pre-exist. The $File variable is not generated (does not exist) when the Virtual Join is evaluated. However, Format Control creates $file (which is a thread variable) that happens to work in the Link record.


Using this variable will affect the Virtual Join when entering the ticket or performing an operation that evaluates Format Control again. In other words, the VJ will consider the existing value when entering the ticket. If the user manually enters a different value in the field then nothing will change in the Virtual Join until Format Control is evaluated again (e.g. when performing a Fill or Save).