I'm not sure if this is a bug or a feature.
But now I'm assuming it has to do with the security that is built into BAQs.
I can reproduce it with a simple BAQ and two tables.
- Invoice table only, returns 20 rows.
Add Customer (open join) and drop 8 rows.
These turned out to be caused when a customer record did not have a territory selected.
- Invoice table only, returns 20 rows.
Add ShipTo table (open join) drop 4 rows.
The ShipTo table doesn't have a record with matching key fields (anymore)? This one is proving a little harder to track down.
I was using the BAQs on large month end summary reports, so when the numbers were off a little it wasn't obvious that I was missing rows.
But now I'm assuming it has to do with the security that is built into BAQs.
I can reproduce it with a simple BAQ and two tables.
- Invoice table only, returns 20 rows.
Add Customer (open join) and drop 8 rows.
These turned out to be caused when a customer record did not have a territory selected.
- Invoice table only, returns 20 rows.
Add ShipTo table (open join) drop 4 rows.
The ShipTo table doesn't have a record with matching key fields (anymore)? This one is proving a little harder to track down.
I was using the BAQs on large month end summary reports, so when the numbers were off a little it wasn't obvious that I was missing rows.
--- In vantage@yahoogroups.com, "Bethany Rye" <brye@...> wrote:
>
> Sometimes this can be because of the links, sometimes it has to do with
> which table is the parent.
>
>
>
> Beth Rye
>
> IT Director
>
> CIGNYS
> 68 Williamson Street
> Saginaw, Michigan 48601
>
> Phone: (989) 753-1411 ext. 1114
>
> Direct Line: (989) 393-0108
>
> Fax: (989) 393-0208
>
> Email: <mailto:brye@...> brye@...
>
>
>
> ***ITAR NOTICE***
>
> This e-mail and/or the attached documents may contain technical data within
> the definition of the International Traffic in Arms regulations, and are
> subject to the export control laws of the US Government. Transfer of this
> data by any means to a foreign person, whether in the US or abroad, without
> an export license or other approval from the US Department of State, is
> prohibited. No portion of this e-mail or its attachment(s) may be reproduced
> without written consent of CIGNYS. If you are not the intended recipient or
> believe that you may have received this document in error, please notify the
> sender and delete this e-mail and any attachments immediately.
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
> cooner_55421
> Sent: Monday, August 01, 2011 3:16 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] BAQ open join vs.ODBC
>
>
>
>
>
> E9.04.506
>
> Hi,
>
> I wonder if anybody is familiar with BAQs sometimes not returning as many
> records as the same query in ODBC?
>
> The first time I noticed was a couple of weeks ago in a BAQ for Invoices.
> When I added the Customer table with an Open join, I started receiving fewer
> records returned. I called Epicor & was told this happens because of
> Territories.
> I made a few changes & thought I had it cleared up but...
>
> Now I have another BAQ that is dropping records.
> This time it is when I add the ShipTo table, even though I'm using an open
> join.
>
> The same query using ODBC returns more the records.
>
> I'd prefer to use BAQ for these if I can clear this up.
> I have a new call with Epicor, no news yet though.
>
> Thanks,
>
>
>
>
>
> [Non-text portions of this message have been removed]
>