Trying to create a BAQ that lists sales by salesperson. Problem is SalesRepList can hold multiple. Help!

Epicor ERP 10
1

Hello,

I am trying to create a BAQ to list sales by salesperson. It looks like the OrderHed hold the list of salespeople in 1 column separated by a tilde (~) called SalesRepList. How can I break that up into individual reps for the BAQ? Is there any easier/alternate way to tie sales to individual salespeople?

Any ideas/help is appreciated.

Thanks,

Shawn

2

I think @josecgomez just wrestled with this recently.

3

You can do it using a CTE and it will work . However the performance is dreadful.
You are basically joining the InvcHead table to itself 5 times
So if you have 1 million invoices… that’s 5 million records

But if you want to do it I can definitely walk you through it.
We ended up writing a BPM to split that stupid ~ string into individual UD0XX records and then join that back. The BPM created the UDXX records when the InvcHead is saved and updates it as needed.

4

I think the UDXX way is what I need. Any details you can give would be a definite help!

Thanks @josecgomez!

Shawn

5

Here is a Basic BAQ which does this using a CTE (Give it a shot if you’d like)

RepListFromIvcHead.baq (30 KB) (>10.2.100)

with [SubQuery1] as
(select
    [InvcHead].[Company] as [InvcHead_Company],
    [InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
    [InvcHead].[SalesRepList] as [InvcHead_SalesRepList],
    (2) as [Calculated_num],
    ([Ice].entry(1 ,InvcHead.SalesRepList, '~')) as [Calculated_SalesRe]
from Erp.InvcHead as InvcHead
union all
select
    [SubQuery1].[InvcHead_Company] as [InvcHead_Company],
    [SubQuery1].[InvcHead_InvoiceNum] as [InvcHead_InvoiceNum],
    [SubQuery1].[InvcHead_SalesRepList] as [InvcHead_SalesRepList],
    (SubQuery1.Calculated_num+1) as [Calculated_num2],
    ([Ice].entry(SubQuery1.Calculated_num ,SubQuery1.InvcHead_SalesRepList, '~')) as [Calculated_SalesRep]
from  SubQuery1  as SubQuery1
where SubQuery1.Calculated_num < 5  and SubQuery1.Calculated_SalesRe  '')
 
select
    [SalesRepbyInvoices].[InvcHead_Company] as [InvcHead_Company],
    [SalesRepbyInvoices].[InvcHead_InvoiceNum] as [InvcHead_InvoiceNum],
    [SalesRepbyInvoices].[Calculated_SalesRe] as [Calculated_SalesRe]
from  SubQuery1  as SalesRepbyInvoices
where (SalesRepbyInvoices.Calculated_SalesRe <> '')
6

We store 1 record per SalesRep in the UD04 table such that
Key1 = InvoiceNum
Key2= SalesRepID
Key3 = Sequence (1-5)

We have Post Tran Data Directive which runs every time that the SalesRepList is modified from any to any or there is a RowMod of A. We lookup and delete all the related UD04 records for that Invoice and re-create them when this happens.

Then on BAQ’s its pretty easy to Join UD04 to InvceHead on Key1=InvoiceNum

7

I think I got it but it will be a bit before I can test. Just got pulled into a different project.

If I get stuck or need clarification when I am back on it, I will post a reply here.

Again, Thank you for the quick replies and help @josecgomez!

8

Hey @josecgomez,

Could you post your BPM?

Thanks,

Shawn

9

It won’t do you much good it is very specific to our company. What are you looking for? I can probably get the pieces you need there’s a crap of other logic we are doing that won’t help you

10

Just the basics. How did you split it in the BPM?

11 
foreach(var x in ttInvcHead.Where(r=>r.Added() || r.Updated())
{
      foreach(var sr in x.SalesRepList.Split('~'))
      {
             //Add UD record Here
      }
}
12

We have found that the following code has worked for us, using a BAQ Calculated field to return the first rep in the list. Changing the 1 to 2 would return rep 2.

[Ice].entry(1,OrderHed.SalesRepList,’~’)

13

With MSSQL 2016+ you can cross apply string_split like so (remove ordinal for better perf or not 2022+)

SELECT 
    InvcHead.Company AS InvcHead_Company,
    InvcHead.InvoiceNum AS InvcHead_InvoiceNum,
    s.value AS Split_SalesRep,
    s.ordinal AS SalesRep_Order -- mssql2022+
FROM InvcHead
CROSS APPLY STRING_SPLIT(InvcHead.SalesRepList, '~', 1) s
ORDER BY InvcHead.InvoiceNum, s.ordinal;
InvcHead_Company InvcHead_InvoiceNum Split_SalesRep SalesRep_Order
111111 1001 RepA 1
111111 1001 RepB 2
111111 1001 RepC 3
111111 1002 RepX 1
111111 1002 RepY 2
111111 1003 RepM 1
111111 1003 RepN 2
111111 1003 RepO 3
111111 1003 RepP 4
14

Pretty sure you need 2017+ :slight_smile:

15

Maybe it’s a half-model-year thing. :man_shrugging:

Can say it works on

Microsoft SQL Server 2016 (SP3-OD) (KB5006943) - 13.0.6404.1 (X64)
Oct 18 2021 09:37:01

2017 for sure. Except ordinal argument was added in 2022.

16

NOTE that in the latest version (2025.1) we added a feature to allow you to do this without complications. There is a view available that automatically splits out this data.

17

second time ive seen it mentioned on here with no receipts.

18

OK, how?

19

Check the release notes.

20

this site IS the release notes.