That is an excellent summary, Brian. Let me add that you can get multiple records, too, by not linking tables, or by linking them on a field that is the same for all records in both tables.
Here is an example. Let's say I need a list of sales reps assigned to each order. I add the OrderHed and SalesRep tables to my BAQ. Because of the moronic database design, there is no linking table to establish which sales reps are on the order. So, I link the two tables on the field Company. Because I only have one company, but 20 different sales reps, I get 20 records for each order, no matter how many sales reps are assigned to the order. Now, I can take that information and parse it in my Crystal report. The only problem is that this is an extraordinarily inefficient way to query a database. Depending on what tables are joined in this fashion, queries like this can become so cumbersome and slow that they are virtually impossible to run. Although in practice such things as this may not be characterized as bugs, they are serious design flaws that should have been corrected long ago. These are design flaws at the Epicor Vantage/Vista level, not the Progress database engine level.
Thom Rose
Controller
Electric Mirror LLC
HOTEL LUXURY
"The World Leader in Back-lit Mirrors & Mirror TV Technology"
T 425 776-4946
A 11831 Beverly Park Rd, Bldg D, Everett, WA 98204 USA
www.electricmirror.com<http://www.electricmirror.com>
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Brian W. Spolarich
Sent: Wednesday, September 02, 2009 7:56 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BAQ Data Displayed Multiple Times
Its not a bug, or even a feature, but a consequence of the way relational databases work, and the BAQ designer and underlying 4GL query-to-sql engine is just a veneer really on top of the underlying Progress or MSSQL database.
You really just have to understand how the data is stored in Vantage.
Say you're interested in Order information, so you make a BAQ that includes OrderHed and OrderDtl. Even if you don't include fields from OrderDtl, typically your BAQ will return one row for each matching row (based on your BAQ filtering criteria) in the OrderDtl table. So you'll see duplicates, because at the lowest level of the table joins, there is one row in the result set for each line in the order.
If you don't want the duplicates, don't include the lower-level tables.
Sometimes to get the data aggregation you want, you have to include the lower-level detail tables in the query because there's information you want there. Depending on what you're doing, you can often use the BAQ relationship editor to change the join type from 'each' to either 'first' or 'last', which will bring in the first or last row of the data on the "right-hand" side of the join.
Similarly, you can use the 'outer-join' option in the relationship editor to bring in all rows in the left and right hand side of each table join, instead of just the rows on the right-hand that are matched with rows on the left. Say for example you're interested in Parts and Orders. If you do a default inner-join between Parts and Orders, you'd only see Parts in the result set for which you have Orders. If you wanted to see all Parts, you'd want to do an outer-join.
Sometimes you're joining data in a BAQ that's really not directly related in Vantage based on the conceptual model of the database (i.e. what the data 'means') because of the report you want to make. I've done this in BAQ Reports and then I do sub-reports and other heavy-lifting in Crystal Reports to give me what I want, but the underlying BAQ Result Set has duplicates because I have somewhat-unrelated data in there on purpose.
I'm probably not explaining this very well but there isn't a one-size-fits-all answer.
-bws
--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
bspolarich@...<mailto:bspolarich%40advancedphotonix.com> ~ 734-864-5618 ~ www.advancedphotonix.com
-----Original Message-----
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of kosmoqa
Sent: Wednesday, September 02, 2009 10:27 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] BAQ Data Displayed Multiple Times
Has anyone ever noticed when creating a BAQ that sometimes the data that you want displayed is repeated any number of times? I'm sure this has something to do with the way the tables are linked, but I'm not sure what. I've seen it on a number of BAQ's that I have created. Eventually, I have been able to get the correct information displayed the correct number of times but it was all due to guesswork and trial and error. Does anyone know for certain what causes this?
[Non-text portions of this message have been removed]
Here is an example. Let's say I need a list of sales reps assigned to each order. I add the OrderHed and SalesRep tables to my BAQ. Because of the moronic database design, there is no linking table to establish which sales reps are on the order. So, I link the two tables on the field Company. Because I only have one company, but 20 different sales reps, I get 20 records for each order, no matter how many sales reps are assigned to the order. Now, I can take that information and parse it in my Crystal report. The only problem is that this is an extraordinarily inefficient way to query a database. Depending on what tables are joined in this fashion, queries like this can become so cumbersome and slow that they are virtually impossible to run. Although in practice such things as this may not be characterized as bugs, they are serious design flaws that should have been corrected long ago. These are design flaws at the Epicor Vantage/Vista level, not the Progress database engine level.
Thom Rose
Controller
Electric Mirror LLC
HOTEL LUXURY
"The World Leader in Back-lit Mirrors & Mirror TV Technology"
T 425 776-4946
A 11831 Beverly Park Rd, Bldg D, Everett, WA 98204 USA
www.electricmirror.com<http://www.electricmirror.com>
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Brian W. Spolarich
Sent: Wednesday, September 02, 2009 7:56 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BAQ Data Displayed Multiple Times
Its not a bug, or even a feature, but a consequence of the way relational databases work, and the BAQ designer and underlying 4GL query-to-sql engine is just a veneer really on top of the underlying Progress or MSSQL database.
You really just have to understand how the data is stored in Vantage.
Say you're interested in Order information, so you make a BAQ that includes OrderHed and OrderDtl. Even if you don't include fields from OrderDtl, typically your BAQ will return one row for each matching row (based on your BAQ filtering criteria) in the OrderDtl table. So you'll see duplicates, because at the lowest level of the table joins, there is one row in the result set for each line in the order.
If you don't want the duplicates, don't include the lower-level tables.
Sometimes to get the data aggregation you want, you have to include the lower-level detail tables in the query because there's information you want there. Depending on what you're doing, you can often use the BAQ relationship editor to change the join type from 'each' to either 'first' or 'last', which will bring in the first or last row of the data on the "right-hand" side of the join.
Similarly, you can use the 'outer-join' option in the relationship editor to bring in all rows in the left and right hand side of each table join, instead of just the rows on the right-hand that are matched with rows on the left. Say for example you're interested in Parts and Orders. If you do a default inner-join between Parts and Orders, you'd only see Parts in the result set for which you have Orders. If you wanted to see all Parts, you'd want to do an outer-join.
Sometimes you're joining data in a BAQ that's really not directly related in Vantage based on the conceptual model of the database (i.e. what the data 'means') because of the report you want to make. I've done this in BAQ Reports and then I do sub-reports and other heavy-lifting in Crystal Reports to give me what I want, but the underlying BAQ Result Set has duplicates because I have somewhat-unrelated data in there on purpose.
I'm probably not explaining this very well but there isn't a one-size-fits-all answer.
-bws
--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
bspolarich@...<mailto:bspolarich%40advancedphotonix.com> ~ 734-864-5618 ~ www.advancedphotonix.com
-----Original Message-----
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of kosmoqa
Sent: Wednesday, September 02, 2009 10:27 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] BAQ Data Displayed Multiple Times
Has anyone ever noticed when creating a BAQ that sometimes the data that you want displayed is repeated any number of times? I'm sure this has something to do with the way the tables are linked, but I'm not sure what. I've seen it on a number of BAQ's that I have created. Eventually, I have been able to get the correct information displayed the correct number of times but it was all due to guesswork and trial and error. Does anyone know for certain what causes this?
[Non-text portions of this message have been removed]