I want to add the LegalNumber of an invoice to the Cash Receipt Edit List report

Kinetic ERP
, ,
1

I want to add the LegelNumber of an invoice to the Cash Receipt Edit List report.
The existing system doesn’t have a CashDtl table. I’ve added and linked the table, but I can’t output the report. I’m getting the error shown in the Epicor program.

Is there a solution?

="SELECT T1.CheckRef,T1.OrderNum,T1.TranAmt,T1.DocTranAmt,T1.Rpt1TranAmt,T1.Rpt2TranAmt,T1.Rpt3TranAmt,T1.TranDate,T1.TranType,T1.HeadNum,T1.UnAppliedAmt,T1.DocUnAppliedAmt,T1.Rpt1UnAppliedAmt,T1.Rpt2UnAppliedAmt,T1.Rpt3UnAppliedAmt,T1.CurrencyCode,T1.ExchangeRate,T1.BankAcctID_BankName,T1.Calc_CustName,T1.Reference,T1.Calc_CashDtlCounter,T1.Calc_Rpt1BookExRate,T1.Calc_Rpt2BookExRate,T1.Calc_Rpt3BookExRate,T1.Calc_DocDecimalsGeneral,T2.InvNum,T2.TranAmt Dtl_TranAmt,T2.DocTranAmt Dtl_DocTranAmt,T2.Rpt1TranAmt Dtl_Rpt1TranAmt,T2.Rpt2TranAmt Dtl_Rpt2TranAmt,T2.Rpt3TranAmt Dtl_Rpt3TranAmt,T2.Discount,T2.DocDiscount,T2.Rpt1Discount,T2.Rpt2Discount,T2.Rpt3Discount,T3.BookID,T3.Description,T3.CurrencyCode BookCurrencyCode,T3.Calc_CurrDesc,T3.Calc_DecimalsGeneral,T3.Calc_ReportCurrPos,T2.WriteOffAmount,T2.DocWriteOffAmount,T2.Rpt1WriteOffAmount,T2.Rpt2WriteOffAmount,T2.Rpt3WriteOffAmount
,
T4.LegalNumber as Cashd_LegalNumber,T1.LegalNumber

FROM CashHead_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN CashDtlTemp_" + Parameters!TableGuid.Value + " T2
 ON T1.Company = T2.Company and T1.HeadNum = T2.HeadNum
 LEFT OUTER JOIN GLBook_" + Parameters!TableGuid.Value + " T3
 ON T1.Company = T3.Company
LEFT OUTER JOIN CashDtl_" + Parameters!TableGuid.Value + " T4
 ON T1.Company = T4.Company 
AND T1.GroupID = T4.GroupID AND T1.HeadNum = T4.HeadNum
"

image
image1920×1020 179 KB

image
image1858×680 121 KB

image
image1846×739 120 KB

image
image1843×601 89.8 KB

image
image1737×747 88.9 KB

2

Your Third picture, did you include the columns that you are joining on? In the RDD, you need to include (uncheck in the the Exclusions grid) the fields you want AND all the fields you are using to join that table to the existing table.

1 Like
3

I have done it but it still doesn’t work.

image
image1836×875 142 KB

1 Like
4

Did you add the field on the dataset in the report as well?

1 Like
5

Yes, I added it.

="SELECT T1.CheckRef,T1.OrderNum,T1.TranAmt,T1.DocTranAmt,T1.Rpt1TranAmt,T1.Rpt2TranAmt,T1.Rpt3TranAmt,T1.TranDate,T1.TranType,T1.HeadNum,T1.UnAppliedAmt,T1.DocUnAppliedAmt,T1.Rpt1UnAppliedAmt,T1.Rpt2UnAppliedAmt,T1.Rpt3UnAppliedAmt,T1.CurrencyCode,T1.ExchangeRate,T1.BankAcctID_BankName,T1.Calc_CustName,T1.Reference,T1.Calc_CashDtlCounter,T1.Calc_Rpt1BookExRate,T1.Calc_Rpt2BookExRate,T1.Calc_Rpt3BookExRate,T1.Calc_DocDecimalsGeneral,T2.InvNum,T2.TranAmt Dtl_TranAmt,T2.DocTranAmt Dtl_DocTranAmt,T2.Rpt1TranAmt Dtl_Rpt1TranAmt,T2.Rpt2TranAmt Dtl_Rpt2TranAmt,T2.Rpt3TranAmt Dtl_Rpt3TranAmt,T2.Discount,T2.DocDiscount,T2.Rpt1Discount,T2.Rpt2Discount,T2.Rpt3Discount,T3.BookID,T3.Description,T3.CurrencyCode BookCurrencyCode,T3.Calc_CurrDesc,T3.Calc_DecimalsGeneral,T3.Calc_ReportCurrPos,T2.WriteOffAmount,T2.DocWriteOffAmount,T2.Rpt1WriteOffAmount,T2.Rpt2WriteOffAmount,T2.Rpt3WriteOffAmount
,
T4.LegalNumber as Cashd_LegalNumber,T1.LegalNumber,T4.Company,T4.GroupID,T4.HeadNum




FROM CashHead_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN CashDtlTemp_" + Parameters!TableGuid.Value + " T2
 ON T1.Company = T2.Company and T1.HeadNum = T2.HeadNum
 LEFT OUTER JOIN GLBook_" + Parameters!TableGuid.Value + " T3
 ON T1.Company = T3.Company
 LEFT OUTER JOIN CashDtl_" + Parameters!TableGuid.Value + " T4
 ON T1.Company = T4.Company 
AND T1.GroupID = T4.GroupID AND T1.HeadNum = T4.HeadNum
"
6

I meant on the Dataset properties window > fields

image
image891×932 23.1 KB

7

Also can you send the entire error message you got? When I read the part at the beginning, "Incorrect Syntax near “T1”, it makes me think the query is wrong somehow.

8

Can it be because you have T1.HeadNum and T4.HeadNum? I think in SQL it’s like you’re calling 2 fields the same name. Maybe try aliasing T4.HeadNum as CashDtl_HeadNum?