Hello everyone,
I am creating a report on sales orders in BAQ. I need to import the salesperson from SO into this report, but I cannot import it because it is not in the DB field. I am using OrderHed, OrderDtl, OrderRel.
How to create this field?
Thanks in advance!
The Sales Person Code from each of those 5 dropdowns is stored as a tilde delimited string in the field OrderHed.SalesRepList. If you’re happy showing that on your BAQ, job done.
If you want the full Name from the SalesRep table:
If you’ve only got one entry in that list, you can join between OrderHed and SalesRep using Company=Company and SalesRepList = SalesRepCode.
If you want to future proof things and code for the fact that up to 5 sales people might be defined against the order, then you had to answer the question of whether you want to end up with the Order row being duplicated 5 times (one for each row from the SalesRep table, or whether you want to do some sort of delimited string of the full names.
There is a field already in the OrderHed table called Calculated_SalesPerson. I suspect this is the name of the primary sales rep on the order (not the code). As you work with reports, you get lucky sometimes and Epicor has already built in those code to description/name conversions in the calculated fields. As @markdamen pointed out, though, if you’re interested in showing all 5 names, you’d have to do a little more.
If you want them listed separately in your BAQ (and resulting report)… you can break them out of the SalesRepList into calculated fields… this will result in only one row per sales order instead of one row per salesrep.
Your BAQ join will need this kind of table relation:
(ignore the (2’s) in the below image… this is snipped from a Union query BAQ where I had joined the tables in multiple queries):
But your table relation will be:
OrderHed: ‘~’+OrderHed.SalesRepList+‘~’
Operaion: Like
SalesRep: ‘%~’+SalesRep.SalesRepCode+‘~%’
This separates the SalesRepList delimited by “~” and compares them to SalesRepCodes
You can then create calculated fields in your BAQ:
For SORep1 (again, you won’t need the (2) after OrderHed)… we want the first “entry” in the OrderHed.SalesRepList:
For SORep2 we want the second entry:
wash-rinse-repeat for SOReps 3 thru 5 if desired.
What I needed was a primary salesperson. So I joined the SaleRep you guys mentioned and completed my report.
Thank you all.
