Invoice SalesRep NOT pulling correctly in BAQ report

Kinetic ERP
1

I’ve been asked to create a modified version of the Sales Analysis report. Include shipto information. No problem.

BAQ report is built.
My Issue is with the SalesRep field.

The SalesRep field has initials in it and they want to see the full name.

I tried the InvcHead table.
Field is called SalesRepList

I then created a calculated field
Field is called FormattedSalesRep

case statement says
when [Customer].[SalesRepCode] = ‘JD’ then ‘Jane Doe’

when I run the BAQ, JD might = Default instead of Jane Doe.

image
image802×1001 192 KB

I don’t know why but I feel like I’m using the wrong table / field?
I don’t understand why my case statement is not returning correct values in the calculated field.

Hopefully this makes sense.

Any help is much appreciated!

2

There is a SalesRep table. Can you create a join in your BAQ to this table based on the SO Sales Rep ID? That will then pull all of the sales rep information that you need.

3

I have tried the SalesRep table.

The sales rep in the SalesRepTable appears to be DIFFERENT from the sales rep in the InvoiceHeadTable.

image
image499×724 35.1 KB

4

Can you post the code for your BAQ?

5

Yes, thank you.

select distinct
[InvcHead].[SalesRepList] as [InvcHead_SalesRepList],
( (case
when [Customer].[SalesRepCode] = ‘JD’ then ‘Jane Doe’
end)) as [Calculated_FormattedSalesRep],
[InvcHead].[CreditMemo] as [InvcHead_CreditMemo],
[InvcHead].[OpenInvoice] as [InvcHead_OpenInvoice],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
[InvcDtl].[InvoiceLine] as [InvcDtl_InvoiceLine],
[InvcDtl].[PartNum] as [InvcDtl_PartNum],
[InvcDtl].[RevisionNum] as [InvcDtl_RevisionNum],
[InvcDtl].[LineDesc] as [InvcDtl_LineDesc],
[InvcDtl].[OurShipQty] as [InvcDtl_OurShipQty],
[InvcDtl].[IUM] as [InvcDtl_IUM],
[InvcDtl].[DocUnitPrice] as [InvcDtl_DocUnitPrice],
[InvcDtl].[DocExtPrice] as [InvcDtl_DocExtPrice],
[ShipTo].[Name] as [ShipTo_Name],
[ShipTo].[Address1] as [ShipTo_Address1],
[ShipTo].[Address2] as [ShipTo_Address2],
[ShipTo].[City] as [ShipTo_City],
[ShipTo].[State] as [ShipTo_State],
[ShipTo].[ZIP] as [ShipTo_ZIP],
[InvcHead].[DocInvoiceAmt] as [InvcHead_DocInvoiceAmt],
[InvcDtl].[TotalMiscChrg] as [InvcDtl_TotalMiscChrg],
[InvcHead].[OrderNum] as [InvcHead_OrderNum]

from Erp.InvcHead as [InvcHead]
left outer join Erp.InvcDtl as [InvcDtl] on
InvcHead.Company = InvcDtl.Company
and InvcHead.InvoiceNum = InvcDtl.InvoiceNum
left outer join Erp.Customer as [Customer] on
InvcDtl.Company = Customer.Company
and InvcDtl.CustNum = Customer.CustNum
inner join Erp.ShipTo as [ShipTo] on
Customer.Company = ShipTo.Company
and Customer.CustNum = ShipTo.CustNum
and Customer.ShipToNum = ShipTo.ShipToNum
left outer join Erp.SalesRep as [SalesRep] on
ShipTo.Company = SalesRep.Company
and ShipTo.SalesRepCode = SalesRep.SalesRepCode
left outer join Erp.OrderHed as [OrderHed] on
ShipTo.Company = OrderHed.Company
and ShipTo.CustNum = OrderHed.CustNum
and ShipTo.ShipToNum = OrderHed.ShipToNum

6

join InvcHead.SalesRepList to SalesRep.SalesRepCode

Then display SalesRep.Name

1 Like
7

Hi Linda,

Noticed you are displaying the sales rep from the customer table.

But in your BAQ, you are joining the ShipTo.SalesRepCode to the SalesRep table.

Be aware that the sales rep in the customer table can be different to the sales rep in the ship to entry.

This might explain why are you are getting weird results.

8

Sharing this link for anyone else struggling to understand how to grab the right sales rep when there is more than 1.

EpicorSalesRepListHowToShowAllFiveNames - YouTube

This video solved my issue!

I needed to pull the sales rep list from the invoichead table
then create a subquery to bring in the salesrep table, the sales rep code
tie the subquery to the invoichead table
connected the two on Company alone
then added a table criteria to the subquery to bring in the name of the sales rep.
I had to use the function called ‘ranges’

image
image1795×1086 140 KB

1 Like