Dear Experts, We have added the custom UD field (InvoiceRef_c) in InvcHead table. How to add this custom field in AR Aging Report.
I have added the two tables in report data definition
- InvcHead 2. InvcHead_UD
Created the relationship between them. After this created the new relationship between InvcHead and ARDtls tables.
=“SELECT T1.Calc_CustName as ARDtls_Calc_CustName,T2.Calc_LegalNumber,T1.Calc_MulChildren as ARDtls_Calc_MulChildren,T1.Calc_ParentCustID as ARDtls_Calc_ParentCustID,T1.Calc_RptTitle2,T1.Calc_Summary, T2.InvoiceDate,T2.InvoiceNum,T2.PONum,T2.Calc_AgeBaseAll,T2.Calc_AgeCurAll,T2.Calc_AgeInvAmt1,T2.Calc_AgeInvAmt2,T2.Calc_AgeInvAmt3,T2.Calc_AgeInvAmt4,T2.Calc_AgeInvAmt5,T2.Calc_AgeInvAmt6,T2.Calc_AgeLbl1,T2.Calc_AgeLbl2,T2.Calc_AgeLbl3,T2.Calc_AgeLbl4,T2.Calc_AgeLbl5,T2.Calc_AgeLbl6,T2.Calc_BlockedFinChrg,T2.Calc_BlockedRemLetters,T2.Calc_Company,T2.Calc_ContPer,T2.Calc_ContPh,T2.Calc_crMemo,T2.Calc_curDesc,T2.Calc_CurDueDate,T2.Calc_CurrCode,T2.Calc_CustID,T2.Calc_CustName as Calc_CustName,CAST( T2.Calc_Index as nvarchar ) as Calc_Index,CAST( T2.Calc_MulChildren as nvarchar ) as Calc_MulChildren,T2.Calc_ParentCustID as Calc_ParentCustID,T2.Calc_PoDNNbr,T2.Calc_RptARAcctID,T2.Calc_RptUserID,T2.Calc_TierLevelNum,T2.Calc_TopCustID,CAST( T2.Calc_BasCurrency as nvarchar ) as Calc_BasCurrency, T3.TierLevelNum,T3.Calc_ARTotal1,T3.Calc_ARTotal2,T3.Calc_ARTotal3,T3.Calc_ARTotal4,T3.Calc_ARTotal5,T3.Calc_ARTotal6,T3.Calc_LevelCustID,T3.Calc_LevelDesc,T4.Calc_DecimalsGeneral
, T1.[Company], T1.[Calc_ARTot1], T1.[Calc_ARTot2], T1.[Calc_ARTot3], T1.[Calc_ARTot4], T1.[Calc_ARTot5], T1.[Calc_ARTot6], T1.[Calc_RptTitle1], T1.[Calc_RptTitle3], T1.[Calc_SubTitle1], T1.[Calc_Territory], T4.[APBOECheck], T4.[COSequenceCert], T4.[DeepCopy], T4.[DeepCopyDupOrRevEst], T4.[MapURL], T4.[MXMunicipio], T4.[Name], T4.[SendToFSA], T4.[WIApplication], T4.[WIAutoCreateJob], T4.[WIGetDetails], T4.[WIRelease], T4.[WISchedule], T4.[WIShippingCosts], T4.[Calc_BaseCurDesc], T4.[Calc_BaseCurrency], T4.[Calc_CurrName], T4.[Calc_CurrSymbol], T3.[Calc_SInvcNum], T2.[AgeBaseAll], T2.[AgeCurAll], T2.[AgeCurTotal1], T2.[AgeCurTotal2], T2.[AgeCurTotal3], T2.[AgeCurTotal4], T2.[AgeCurTotal5], T2.[AgeCurTotal6], T2.[AgeInvAmt1], T2.[AgeInvAmt2], T2.[AgeInvAmt3], T2.[AgeInvAmt4], T2.[AgeInvAmt5], T2.[AgeInvAmt6], T2.[AgeLbl1], T2.[AgeLbl2], T2.[AgeLbl3], T2.[AgeLbl4], T2.[AgeLbl5], T2.[AgeLbl6], T2.[AgeParentAll], T2.[AgeParentTotal1], T2.[AgeParentTotal2], T2.[AgeParentTotal3], T2.[AgeParentTotal4], T2.[AgeParentTotal5], T2.[AgeParentTotal6], T2.[ContPer], T2.[ContPh], T2.[CreditMemo], T2.[crMemo], T2.[curDesc], T2.[CurDueDate], T2.[CurrCode], T2.[CustID], T2.[CustName], T2.[DispCurTot], T2.[ParentCustID], T2.[PoDnNbr], T2.[RptARAcctID], T2.[RptUserID], T2.[SInvcNum], T2.[Calc_AgeCurTotal1], T2.[Calc_AgeCurTotal2], T2.[Calc_AgeCurTotal3], T2.[Calc_AgeCurTotal4], T2.[Calc_AgeCurTotal5], T2.[Calc_AgeCurTotal6], T2.[Calc_AgeParentAll], T2.[Calc_AgeParentTotal1], T2.[Calc_AgeParentTotal2], T2.[Calc_AgeParentTotal3], T2.[Calc_AgeParentTotal4], T2.[Calc_AgeParentTotal5], T2.[Calc_AgeParentTotal6], T2.[Calc_AltCust], T2.[Calc_AltCustID], T2.[Calc_AltCustName], T2.[Calc_ApplyDate], T2.[Calc_DispCurTot], T5.[InvoiceRef_c], T6.[ForeignSysRowID]
FROM ARPrnt_” +Parameters!TableGuid.Value + " T1
join Company_" +Parameters!TableGuid.Value + " T4
on T1.Calc_Company=T4.Company
LEFT OUTER JOIN RlsHead_" +Parameters!TableGuid.Value + " T3
ON T1.Calc_Company = T3.Calc_Company AND T1.Calc_CustID = T3.Calc_CustID
LEFT OUTER JOIN ARDtls_" +Parameters!TableGuid.Value + " T2
ON T1.Calc_Company = T2.Calc_Company AND T1.Calc_ParentCustID = T2.Calc_ParentCustID AND T1.Calc_CustID = T2.Calc_CustID and T1.Calc_RptARAcctID = T2.Calc_RptARAcctID
LEFT OUTER JOIN InvcHead_" + Parameters!TableGuid.Value + " T5 ON T2.[Calc_Company] = T5.[Company] AND T2.[InvoiceNum] = T5.[InvoiceNum]
AND T2.Calc_CustID = T5.Calc_CustID LEFT OUTER JOIN InvcHead_UD_" + Parameters!TableGuid.Value + " T6 ON T5.[SysRowID] = T6.[ForeignSysRowID]
"
But custom field data is not populating in the report. Please suggest me how to resolve this issue.
You don’t need to add InvcHead_UD in the Report Data Definition. Your custom field should be available in the Exclusions tab under the regular InvcHead table – Epicor will reference the view that includes the UD columns. Make sure the field is not excluded and then add it to the RDL query.
1 Like
Perfect - Tyler. Your assistance is appreciated.
Thank you.
@Hari_Dutt I have been trying to connect the InvcHead table to this report so that I can pull the original invoice amount (InvoiceAmt) however when I join InvcHead table to this report the same way you are showing here and its not printing the InvoiceAmt on the report. I made sure that I added the the field to the RDL Query and that the field is unchecked under excluded fields and yet it is still printing the amount. Any ideas would be greatly appreciated.