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.
I think @josecgomez just wrestled with this recently.
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.
I think the UDXX way is what I need. Any details you can give would be a definite help!
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
[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
[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 '')
[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 <> '')
We store 1 record per SalesRep in the UD04 table such that
Key1 = InvoiceNum
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
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!
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
Just the basics. How did you split it in the BPM?
foreach(var x in ttInvcHead.Where(r=>r.Added() || r.Updated())
foreach(var sr in x.SalesRepList.Split('~'))
//Add UD record Here
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.