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

Epicor ERP 10
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 <> '')
3 Likes