Different Results with BAQ & SQL Query

I am trying to determine why when I run the same query via a BAQ and SQL I am missing a few records in the BAQ.
I created a cross-company BAQ selecting all Customers where CustNum = 7
The BAQ returns 5 rows, one missing from one company
When I run the same query (copy/paste) SSMS returns 7 rows.
If I try to pull up the BAQ missing customer in customer entry it comes up fine and there are hundreds of orders, cases, etc for the customer and all base searches work fine.
But say I have a dashboard of all cases, this customer’s cases are missing.
I have compared field by field the missing rows to the rows that come up and I cannot find anything significantly different.
I event rebuilt the Customer table indexes…
I am assuming that the BAQ queries something different or there is some data corruption.
Has anyone else encountered this or found a resolution?

BAQs impose Epicor related security/access rights on the data returned that running the query directly in SQL wouldn’t.

Does your Epicor user account have access to “View All Territories” in work force maintenance?


Remember BAQs respect all access rules, so if the person running the BAQ doesn’t have View All Territories or access to each territory for each customer, they won’t see that customer in the BAQ result. SQL will give you everything.

Mark W.

Thanks Nathan, that was it!

A few details to clarify.
So all the workforces my account was on as an authorized user were set to View All Territories, however, my user account was not a Default User for any Workforce.
By either setting one of my authorized user records to default for the workforce and the records showed up.
The other way to fix this was to remove myself as an authorized user for all workforces.
To correct this for my end user, I just created a Workforce for him with View All Territories enabled and just added his user account as the default user.
This has tripped me up serval times going back to 8.03… when will I learn?