Hi,

Is it possible to do joins using the TCL prompt to retrieve certain records??

For example I am wanting to retreive lines from a job table which have been placed on an invoice.

Any help is much appreciated.

Regards

Troy

Yes it is, simply type SQL followed by your standard SQL statement. The normal AREV way is to predefine joins using "Symbolics" but as you are using SQL terminology we thought it best to respond this way.

The Sprezzatura Group

World Leaders in all things RevSoft

You can use an inline formula. For example:

LIST INVOICE {@ANS=XLATE('JOB_TABLE',{JOB_NO},"JOB_DESCRIPTION","X"}

but if you use the same relationship frequently, it's better to create a symbolic field.

Thank you to you both for your help with this.

Regards

Troy

Another technique we've used a while ago was a very simple user-defined conversion procedure with parameters to indicate the join details.

Thus if the field in the main file being reported is CUSTOMER (being the key in a CUSTOMERS file) one might put in the TCL sentence … CUSTOMER OCONV "JOINER,CUSTOMERS*BALANCE*MD2," … where the subroutine JOINER simply does an XLATE to look up BALANCE in the CUSTOMERS file for the required key(s) and then, if required, does the display conversion.

Needless to say, the field must not be indexed!

However, if the field in the main report file is multivalued and the joined field is also multivalued then it will be necessary to use the Xlate "step-down" option to convert the joined multivalues to subvalues to ensure correct "cascading" of any associated multivalued fields in the report.

However, we decided joining on the fly is a vitally important capability for our users but that the above technique was a little too complex for end users. Our T-List reporter (versions for Arev and OI) now includes a field definition option to joins as part of the reporter, including dealing with combinations of single- and multi-valued fields.

There's a lot more I could say but I hope this helps.

Just few words.

When you use the same "join" often

it is good practise to create one symbolic field gathering all information from the symbolic field / or use a common variables

joinID and joinRec in all symbolic fields used in the same join.

This reduces multiple reads of the same joined record.

View this thread on the forum...