I am fairly new to epicor in general, and I have little SQL experince, which I think might be causing me to struggle with this.
In Business Activity Query Desinger I am using the Warehosue table, and a subquery for the Customer table, I would like to know if there is a way to limit what is pulled from the Customer table so that it only pulls data when Warehouse pulls data.
Example of what I mean, is when I Analyze and test with only the warehouse table, I only get 40 row’s of data, but when I add a field from the customer table and then test it, epicor will forcibily limit what is shown to 10,000. Is there a way to limit what is shown by the customer table so that it also only shows 40?
The best way we can help is by having you explain the problem you’re trying to solve. That way, others can chime in with different approaches. Explain, in English, what you want this query to do.
Just curious how are you joining the Warehouse and Customer tables?
P.S.
What I suspected… by joining on Company you’re pulling in all warehouses all customers AND in effect would be multiplying each warehouse be the number of customers.
Yes… these two tables will probably not join “naturally”.
Have you looked thru the Help - there are some good examples there.
I also think it might help if you could look at the menu items in E10 for “Datasets Relationship” and/or “Data Dictionary Viewer” ( especially the indexes ).
Or maybe downloading one of the CHM files from this site might give you some ideas of tables and their relationships? Here is a link to one of the “CHM” topics:
What I would like to do is have a separate table I’ve connected in the Query Designer to only pull in the field data releated to the to Table 1, so when I am anylazing I don’t have repeating data due to underlying connections.
(This is probably a bad example, because Warehouse, and customer table only have compnay as a table releation field)
this photo shows me only pulling in data for the warehouse, no repeating data only shows
the Warehouse, Description, and Name rows, seem to repeat as long as the name column has a new name. What i would like to know if it’s possible is to keep them all updating while still having them connected.
I think me trying to use warehouse, and customer tables are a bad example for what I’m trying to do, but I have connected them only by company, under table relations.
What you created there is called a cross join. Every match on the first table, to the second table brings in a row. And since every warehouse record in the company matches every customer in the company you can all kinds of duplicated rows. If you have 5 rows in the first table and 10 rows in the second table, and join them by nothing, or something that they all have in common, then you get 5 x 10 = 50 rows. You need to be able to join on enough fields to get a 1-1 relationship (if that’s what you are looking for)
Thank you very much, I’ll look through the Help, and the link you’ve given me. I was under the assumption that the order and number top left of the tables would be a trickle down system. Thank you again
