Relational Database Advice For Queries

Question

I am working with EPICOR and designing BAQ Reports. My basic reports are always fine. Now that I've moved on to building more complex queries, I wondered if there are any tips to help them work. While my syntax is correct, verified by the program, my query still does not populate. Any advice about what to look for? What problems might cause delays?

Answer 1

I work with Epicor905.700c

I have noticed that if you ever have to flip the parent child relationship, the baq will almost never work. So it's best to add the table that you want to be the parent into the designer first. i.e XFileRef is by default a child of XFileAttch, so pull in XFileRef to the designer before you add XFileAttch. This is one thing that I wasted a few hours trying to figure out.

Answer 2

If you are not seeing all of the data in your BAQ (and you have not added restrictive criteria), then it means you have written improper relationships between business objects. I've found that the internal Epicor tools do not typically validate relationships due to the fact that you theoretically could use such a join (for multi-company statistics, of full outer joins to dump many tables for archival, neither of which I recommend)

Some quick tips related to writing BAQs and fixing relationships between business objects that should solve your issue with data populating:

1. Always link on BO.Company = BO.Company, then largest part, then smaller part, etc. For example, OrderHed linked to OrderDtl should be linked on Company, then OrderNum, then OrderLineNum, then OrderRelNum. If your business object is not linked on company, it will not be operating off of an index, and will make the query extremely slow.
2. If a query is experiencing performance issues, make sure your relationships are indexed. You can view this by going to System Management->Data Dictionary Viewer, and searching for the table you're working with. Try to only use the indexes you see here to join two business objects together, or performance will suffer. If you have a progress system, these indexes cannot be updated to my knowledge - so make sure you use them.
3. In general, if you do not have special reasoning behind the relationship direction, always make your base business object the smallest detail unit, and point the parents to the children. For example, if you are making a report that lists information on all of the order releases in a company, add OrderRel, then add OrderDtl and make sure OrderDtl points to OrderRel, then add OrderHed and make sure OrderHed points to OrderDtl. The way that Epicor translates this into the actual query depends on this ordering.
4. To speak specifically to this issue, start with a small number of business objects, adhering to the model set forth in #3, and make sure that your query is returning data. Add business objects one by one until you see less records returned. This means that the data in the BO you added did not satisfy all of those relationship fields, and they will reappear if you click on the relationship line between the two and check the box "outer join". If you do this, you will not see field data from the new BO, because the relationship does not exist for that particular data, but it will not exclude the data from the BOs that were already working. In the example above, if you linked OrderHed with OrderDtl, and you saw some of your order lines go away, this would suggest that there is no order header for the order line that you selected, OR you incorrectly defined the relationship between those objects.
5. As another user has said, do not flip the parent child using the right click->flip parent child, because this functionality is seemingly bugged in all versions of Epicor that I have yet seen. If you experience these problems, delete the business objects and relationships, save, and re-add them. Although Epicor seems to handle the automatic population of relationships more consistently when this is done, I don't actually recommend adding the parent BO first. I would use the method I describe in #4, saving in between each addition.

Follow these rules and it should fix all of your BAQ problems.

I hope this helps!

Source: Epicor BAQ Development for 1.5 years.