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 logical.name=logical.name 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 logical.name="logical.name in $File". How do i write this for virtual joins? Is this even possible?
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).
Here is a SM 9.21 OOB example of a hard-coded query used by a VJ:
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 logical.name field then nothing will change in the Virtual Join until Format Control is evaluated again (e.g. when performing a Fill or Save).