If you're using a BAQ in a report or a dashboard it pays to take some extra time and make sure that the BAQ is a good query.
What I mean by a "good query" is one that gets in and gets out as quickly as possible by utilizing the indexes that Epicor provides. Now I can create a query that displays all open jobs -- add in the JobHead table and add a condition of JobComplete = false. This is a perfectly good query, it works and it returns the correct results.
But, it doesn't use an index and if it doesn't use an index it will take longer to run, utilize more resources and may even cause inadvertent problems if used in a dashboard. An index is the database's way of filing records so that it can quickly get to your data. You can think of an index as a filing cabinet. People typically file things alphabetically. The database has the ability to files things by multiple pieces of information: i.e. company, job-number, job-complete, job-close. In order to use an index you have to use all of the parts of the index. Each table in the Epicor database has at least one index -- but some tables can have upwards of 40 indexes. To efficiently look up data you have to use 1 of those indexes.
If you go into the data dictionary viewer in Epicor and put in the table JobHead you can see that it has many to choose from:
IndexName IndexFields
CallNum Company,CallNum,CallLine
Candidates Company,JobClosed,JobComplete,Candidate,JobNum
ClsdCmpStart Company,JobClosed,JobComplete,WIStartDate,WIStartHour,JobNum
CmplJobNum Company,JobClosed,JobComplete,JobNum
CmpPart Company,JobComplete,PartNum,JobCompletionDate <D>
CmpProdYld Company,JobClosed,JobComplete,JobEngineered,JobReleased,ProductionYield
EngJobNum Company,JobEngineered
EquipClsDt Company,EquipID,ClosedDate
HDCaseNum Company,HDCaseNum,JobNum
JobClosed Company,JobClosed,ReqDueDate
JobDue Company,JobClosed,DueDate,JobNum
JobNum Company,JobNum
JobProdYld Company,JobNum,JobClosed,JobComplete,JobEngineered,JobReleased,ProductionYield
JobStart Company,JobClosed,StartDate,JobNum
OpenJob Company,JobClosed,JobNum
PartNum Company,JobClosed,PartNum,JobNum
PlantCallNum Company,Plant,CallNum,CallLine
PlantCandidates Company,Plant,JobClosed,JobComplete,Candidate,JobNum
PlantJobClosed Company,Plant,JobClosed,ReqDueDate,JobNum
PlantJobDue Company,Plant,JobClosed,DueDate,JobNum
PlantJobNum Company,Plant,JobNum
PlantJobStart Company,Plant,JobClosed,StartDate,JobNum
PlantOpenJob Company,Plant,JobClosed,JobNum
PlantPartNum Company,Plant,JobClosed,PartNum,JobNum
PlantProject Company,Plant,ProjectID,JobNum
PlantTypeTemplate Company,Plant,JobType,InCopyList,JobNum
Project Company,ProjectID,JobNum
ProjPhase Company,ProjectID,PhaseID,JobNum
RCutScheduled Company,RoughCutScheduled
RelJobNum Company,JobReleased
StatusReadyToPrint Company,Plant,StatusReadyToPrint,JobNum
SysIndex SysRowID
TravelerReadyToPrint Company,Plant,TravelerReadyToPrint,JobNum
WhatIf Company,WIName
WIPCleared Company,WIPCleared,JobNum
WordIssueTopics IssueTopics
WordPartDesc PartDescription
WordResTopics ResTopics
You need to look at these indexes and figure out which one comes closest to what you're trying to search for and which would be the easiest to implement. In our case the indexes that use JobComplete are Candidates: ClsdCmpStart, CmplJobNum, CmpPart, CmpProdYld, JobProdYld and PlantCandidates. I'll use CmplJobNum because it uses 4 fields.
In order to use the CmplJobNum index (company, jobnum, jobcomplete, jobclosed) in the above query you would actually have to compare all of the fields used by the index. "But I don't care about Job Closed (if it's open then it can't be closed) and I don't know the Job Number and why should I use company when BAQ's are already company specific?". It sounds a bit crazy, having to compare a field to itself and actually specify a company in a single company database, but if you want a query that returns quickly then you have to follow the database rules.
In your BAQ you'd click on the JobHead table. Add a condition of JobClosed=False(i know it's redundant but bear with me). Add another condition of JobComplete=false . And add a condition of JobNum = JobNum. This last one's a bit weird. Your essentially saying that the job number has to equal itself. Again, a bit redundant but the goal is to use an index so we have to include some comparison and since we can't say JobNum = <anything> the next best thing is to say JobNum = JobNum. To accomplish this In Epicor9 you'd add a new comparison row, select the JobNum field, choose the "=" in the Compare Op column, and then click on the "Specified Table Field value" in the Filter Value column. Click on "Specified", choose your table in the drop-down and then select the field by checking the checkbox. The company can be either Company="<company Identifier>" or you could also do this with the Company comparison, have JobHead.Company = JobHead.Company -- like we just did for JobNum.
If you don't use an index you're essentially asking the database to go row-by-row to see if it meets your criteria. On small tables this isn't as big a deal but if you are utilizing a table like PartTran that can have millions of rows then it becomes crucial that you use an index. And just because Epicor creates the link when you bring in another table doesn't mean it's utilizing an index.
What I mean by a "good query" is one that gets in and gets out as quickly as possible by utilizing the indexes that Epicor provides. Now I can create a query that displays all open jobs -- add in the JobHead table and add a condition of JobComplete = false. This is a perfectly good query, it works and it returns the correct results.
But, it doesn't use an index and if it doesn't use an index it will take longer to run, utilize more resources and may even cause inadvertent problems if used in a dashboard. An index is the database's way of filing records so that it can quickly get to your data. You can think of an index as a filing cabinet. People typically file things alphabetically. The database has the ability to files things by multiple pieces of information: i.e. company, job-number, job-complete, job-close. In order to use an index you have to use all of the parts of the index. Each table in the Epicor database has at least one index -- but some tables can have upwards of 40 indexes. To efficiently look up data you have to use 1 of those indexes.
If you go into the data dictionary viewer in Epicor and put in the table JobHead you can see that it has many to choose from:
IndexName IndexFields
CallNum Company,CallNum,CallLine
Candidates Company,JobClosed,JobComplete,Candidate,JobNum
ClsdCmpStart Company,JobClosed,JobComplete,WIStartDate,WIStartHour,JobNum
CmplJobNum Company,JobClosed,JobComplete,JobNum
CmpPart Company,JobComplete,PartNum,JobCompletionDate <D>
CmpProdYld Company,JobClosed,JobComplete,JobEngineered,JobReleased,ProductionYield
EngJobNum Company,JobEngineered
EquipClsDt Company,EquipID,ClosedDate
HDCaseNum Company,HDCaseNum,JobNum
JobClosed Company,JobClosed,ReqDueDate
JobDue Company,JobClosed,DueDate,JobNum
JobNum Company,JobNum
JobProdYld Company,JobNum,JobClosed,JobComplete,JobEngineered,JobReleased,ProductionYield
JobStart Company,JobClosed,StartDate,JobNum
OpenJob Company,JobClosed,JobNum
PartNum Company,JobClosed,PartNum,JobNum
PlantCallNum Company,Plant,CallNum,CallLine
PlantCandidates Company,Plant,JobClosed,JobComplete,Candidate,JobNum
PlantJobClosed Company,Plant,JobClosed,ReqDueDate,JobNum
PlantJobDue Company,Plant,JobClosed,DueDate,JobNum
PlantJobNum Company,Plant,JobNum
PlantJobStart Company,Plant,JobClosed,StartDate,JobNum
PlantOpenJob Company,Plant,JobClosed,JobNum
PlantPartNum Company,Plant,JobClosed,PartNum,JobNum
PlantProject Company,Plant,ProjectID,JobNum
PlantTypeTemplate Company,Plant,JobType,InCopyList,JobNum
Project Company,ProjectID,JobNum
ProjPhase Company,ProjectID,PhaseID,JobNum
RCutScheduled Company,RoughCutScheduled
RelJobNum Company,JobReleased
StatusReadyToPrint Company,Plant,StatusReadyToPrint,JobNum
SysIndex SysRowID
TravelerReadyToPrint Company,Plant,TravelerReadyToPrint,JobNum
WhatIf Company,WIName
WIPCleared Company,WIPCleared,JobNum
WordIssueTopics IssueTopics
WordPartDesc PartDescription
WordResTopics ResTopics
You need to look at these indexes and figure out which one comes closest to what you're trying to search for and which would be the easiest to implement. In our case the indexes that use JobComplete are Candidates: ClsdCmpStart, CmplJobNum, CmpPart, CmpProdYld, JobProdYld and PlantCandidates. I'll use CmplJobNum because it uses 4 fields.
In order to use the CmplJobNum index (company, jobnum, jobcomplete, jobclosed) in the above query you would actually have to compare all of the fields used by the index. "But I don't care about Job Closed (if it's open then it can't be closed) and I don't know the Job Number and why should I use company when BAQ's are already company specific?". It sounds a bit crazy, having to compare a field to itself and actually specify a company in a single company database, but if you want a query that returns quickly then you have to follow the database rules.
In your BAQ you'd click on the JobHead table. Add a condition of JobClosed=False(i know it's redundant but bear with me). Add another condition of JobComplete=false . And add a condition of JobNum = JobNum. This last one's a bit weird. Your essentially saying that the job number has to equal itself. Again, a bit redundant but the goal is to use an index so we have to include some comparison and since we can't say JobNum = <anything> the next best thing is to say JobNum = JobNum. To accomplish this In Epicor9 you'd add a new comparison row, select the JobNum field, choose the "=" in the Compare Op column, and then click on the "Specified Table Field value" in the Filter Value column. Click on "Specified", choose your table in the drop-down and then select the field by checking the checkbox. The company can be either Company="<company Identifier>" or you could also do this with the Company comparison, have JobHead.Company = JobHead.Company -- like we just did for JobNum.
If you don't use an index you're essentially asking the database to go row-by-row to see if it meets your criteria. On small tables this isn't as big a deal but if you are utilizing a table like PartTran that can have millions of rows then it becomes crucial that you use an index. And just because Epicor creates the link when you bring in another table doesn't mean it's utilizing an index.