I am trying to create a simple BAQ to view part information for our scrap GL acccount. I’m linking TranGlc Key 1, Key 2, Key 3 to PartTran Sysdate, SysTime,TranNum respectively per Epicor’s guide. TranGlc is the parent and PartTran is the child in the link. I’m limiting the Segment1Value in TranGLC to just the 50070 account I want to look at and limiting the TranGLC FiscalYear to 2017. When I try and run the query to test, it just sits there indefinately and never returns any information. Can anyone give me an idea on how I can create a query using TranGLC and PartTran that won’t take 24 hours to run?
By putting your criteria ONLY on TranGLC, you are still cycling through EVERY record of the HUGE PartTran table.
Determine what GLcontrol’s you’ve associated to specific PartTran.TranType’s and put your criteria on them within PartTran.
I’d also put a PartTran.SysDate (or TranDate - whichever one is a key indexed field) filter (maybe greater than or equal to ‘today-400’) so you are also heavily restricting what the query must traverse through before.
Below are the keys to the PartTrans from the Data Dictionary Viewer in E10, make sure you include each in your connection. Don’t forget Company!!!
Have you also converted the types in your connection to match the table types?
FieldName
FieldFormat
Mandatory
DataType
Company
x(8)
FALSE
nvarchar
SysDate
{shortyeardate}
TRUE
date
SysTime
9
FALSE
int
TranNum
9
FALSE
int
Index Name
Fields
IX_PartTran
Company,SysDate,SysTime,TranNum
PK_PartTran
SysRowID
IX_PartTran_idxPartDate
Company,PartNum,TranDate,SysDate,SysTime,TranNum
IX_PartTran_IdxPartWhse
Company,PartNum,WareHouseCode,TranDate
IX_PartTran_idxRevert
RevertID
IX_PartTran_IdxWhseActTransUOM
Company,PartNum,WareHouseCode,ActTransUOM,TranDate
IX_PartTran_JobMtlLoans
Company,JobNum,AssemblySeq,JobSeq,JobNum2,AssemblySeq2,JobSeq2,TranType
IX_PartTran_JournalIdx
Company,FiscalYear,FiscalYearSuffix,FiscalPeriod,JournalCode,JournalNum,TranDate,FiscalCalendarID
IX_PartTran_JobAsmSeq
Company,JobNum,AssemblySeq,JobSeqType,JobSeq,TranDate
IX_PartTran_JobNum
Company,JobNum,LotNum,TranDate
IX_PartTran_LegalNumber
Company,LegalNumber
IX_PartTran_NonConfID
Company,NonConfID
IX_PartTran_PartActivity
Company,PartNum,WareHouseCode,BinNum,SysDate,SysTime
IX_PartTran_PORel
Company,PONum,POLine,PORelNum,TranDate
IX_PartTran_OrderNum
Company,OrderNum,OrderLine
IX_PartTran_PartDimDate
Company,PartNum,DimCode,TranDate
IX_PartTran_postSearch
Company,TranDate,TranType,GLTrans,PostedToGL
IX_PartTran_PackNum
Company,PackType,PackNum,PackLine,SysDate,SysTime,TranNum
IX_PartTran_PartLot
Company,PartNum,LotNum,TranDate
IX_PartTran_RefCodeIdx
Company,RefCode
IX_PartTran_PackSlip
Company,PackSlip,PackLine,TranType,SysDate,SysTime
IX_PartTran_PlantTranIdx
Company,PlantTranNum,SysDate,SysTime
IX_PartTran_RefTypeIdx
Company,RefType,RefCode
IX_PartTran_RMA
Company,RMANum,RMALine,RMAReceipt,RMADisp,SysDate,SysTime
IX_PartTran_UserClass
Company,EntryPerson,TranClass,SysDate,SysTime
IX_PartTran_WhsePartBinSysDate
Company,WareHouseCode,PartNum,BinNum,SysDate,SysTime
IX_PartTran_WhseWhseBin
Company,WareHouseCode,BinNum
IX_PartTran_PlantTranDate
Company,PartNum,Plant,TranDate
IX_PartTran_idxABT
ABTUID
IX_PartTran_IdxDatePart
Company,TranDate,PartNum
IX_PartTran_CallNum
Company,CallNum,CallLine,MatNum
IX_PartTran_Costed
Company,Costed,TranDate
IX_PartTran_CustClass
Company,CustNum,TranClass,PartNum,TranDate
IX_PartTran_CycleCountIdx
Company,Plant,WareHouseCode,CCYear,CCMonth,InvAdjSrc,CycleSeq,PartNum
IX_PartTran_DemandReconcileNum
Company,ReconcileNum
IX_PartTran_DMRAction
Company,DMRNum,ActionNum
IX_PartTran_DMRDate
Company,DMRNum,TranDate
IX_PartTran_FIFOCostIdx
Company,PartNum,LotNum,CostID,FIFODate,FIFOSeq,FIFOSubSeq
Damn. That was ugly.
Limiting the query to one day to start with will help fine tune it. Both of
these tables can be very large.
Brad
Robert. BAM!!! That was it. I limited the dates in Part Tran and now it only takes a few seconds to run. I don’t know how I missed that. Thank you so much!
Best regards
Mike Abell
IT Manager
Flexial Corporation
a company of BOA Group
1483 Gould Drive, Cookeville, TN 38506, USA
Phone: +1 931 432 8408
Fax: +1 931 432 1889
Mail: mabell@flexial.com
Website: http://www.flexial.comhttp://www.flexial.com/
http://www.boagroup.comhttp://www.boagroup.com/