Hello - I am struggling with the report writer and need some clarification (a long shot, since there is almost 0 documentation on the subject). I am proficient in T-SQL, so am confident in my ability to write queries, but can't get something very simple to work in a calculated field.

It is a pretty simple ledger report that prints you out transactions in a certain account and subaccount. When a transaction has an AP invoice as the source, I would love to be able to pull the vendor name from that invoice. The way we code those transactions apparently is to have a second line in which the ledger account is always 2000 and the subaccount matches the vendor number, IE:

Line 1 - Concrete materials, account 5001 (Materials), subaccount 130 (concrete)

Line 2 - A/P Invoice, account 2000 (Trade accounts payable), subaccount 90 (vendor name)

If the report is ran for account 5001, the second line won't be in the report, thus my resorting to a query in a calculated field. This is what I'm trying to no avail:

select lgrsub.lngnme from lgrsub inner join lgtnln on lgtnln.subact = lgrsub.recnum where lgtnln.lgract = 2000 and lgrtrn.recnum = {lgrtrn.recnum}

this is what I get:

Has anyone successfully used inner joins in a calculated field? It is in the documentation so I know it's possible but... how?