Display SalesRep Name in BAQ Calculated Field

justen · August 15, 2022, 8:46pm

New coder here looking for some help. I’m trying to create a Commissions BAQ for Invoices, many of which have more than one Sales Rep and Commission payout amount on them.

I’m tasked with listing all Sales Reps/Commission amounts for each invoice, which I’ve been able to do. The problem is our Accounting team wants to see the Sales Rep Name instead of the Sales Rep Code.

I’m currently using calculated fields to split the SalesRepList into separate columns by ‘~’.

(case
    when InvcHead.CommissionAmt1_c > 0
        then [Ice].entry(1, InvcHead.SalesRepList, '~')
end)

Is there any code I can add to the calculated fields to display the associated Sales Rep Name for the SalesRepCode pulled from the SalesRepList?

E102016 · August 15, 2022, 9:15pm

Bring the SalesRep table into your query and link on Company and SalesRep

‘~’ + InvcHead.SalesRepList + ‘~’ Like ‘%~’ + SalesRep.SalesRepCode + ‘~%’

justen · August 15, 2022, 9:29pm

Thank you for the reply. I should have mentioned that I already had the SalesRep Table in the Query like this:

I tried removing the existing Table Relation and added only what you suggested, but I received a ‘Bad SQL Statement’ error when analyzing the query.

Other than not providing enough info initially, did I miss something?

E102016 · August 15, 2022, 11:03pm

You need two relationship criteria for InvcHead to SalesRep

Company = Company
Add the second criteria row
Then type this expression into the InvcHead side
‘~’ + InvcHead.SalesRepList + ‘~’

Operation will be “Like”

Type this expression into the SalesRep side
‘%~’ + SalesRep.SalesRepCode + ‘~%’

justen · August 16, 2022, 1:18pm

Thanks again for replying.

Here is what I tried:

When I do that and Analyze, I get the following:

If I Test, I get a server side ‘Bad SQL Statement’ error:

The server side error looks like this:

Ice.Common.EpicorServerException: Incorrect syntax near ‘‘’. —> System.Data.SqlClient.SqlException: Incorrect syntax near ‘‘’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at Ice.Blaq.Execution.QueryExecutor.ExecuteAndFillDataSetInternal(IDbConnection dbconn, QueryInfo queryInfo, DataSet resultDataset, Action2 perfLogger) in C:\_releases\ICE\ICE3.2.700.24\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 118 at Ice.Blaq.Execution.QueryExecutor.<>c__DisplayClass3_0.<ExecuteAndFillDataSet>b__0(IDbConnection dbconn) in C:\_releases\ICE\ICE3.2.700.24\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 37 at Ice.Blaq.Execution.QueryExecutionHelper.DoJobWithObject[TObj](Func2 theJob) in C:_releases\ICE\ICE3.2.700.24\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutionHelper.cs:line 195
— End of inner exception stack trace —

I’m not sure if it helps at all, but here is the full BAQ:

select 
	[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
	[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
	[Customer].[CustID] as [Customer_CustID],
	[Customer].[Name] as [Customer_Name],
	[InvcHead].[OrderNum] as [InvcHead_OrderNum],
	[InvcHead].[EntryPerson] as [InvcHead_EntryPerson],
	[InvcHead].[InvoiceAmt] as [InvcHead_InvoiceAmt],
	[InvcHead].[InvoiceBal] as [InvcHead_InvoiceBal],
	((case
     when InvcHead.CommissionAmt1_c > 0
         then [Ice].entry(1, InvcHead.SalesRepList, '~')
 end)) as [Calculated_SalesRep1],
	[InvcHead].[RepSplit1] as [InvcHead_RepSplit1],
	[InvcHead].[RepRate1] as [InvcHead_RepRate1],
	[InvcHead].[CommissionAmt1_c] as [InvcHead_CommissionAmt1_c],
	[InvcHead].[PaymentNotes1_c] as [InvcHead_PaymentNotes1_c],
	[InvcHead].[CommissionType1_c] as [InvcHead_CommissionType1_c],
	[InvcHead].[CommissionApproved1_c] as [InvcHead_CommissionApproved1_c],
	[InvcHead].[CommissionEnteredBy1_c] as [InvcHead_CommissionEnteredBy1_c],
	[InvcHead].[CommissionEnteredByDate1_c] as [InvcHead_CommissionEnteredByDate1_c],
	((case
     when InvcHead.CommissionAmt2_c > 0
         then [Ice].entry(2, InvcHead.SalesRepList, '~')
 end)) as [Calculated_SalesRep2],
	[InvcHead].[RepSplit2] as [InvcHead_RepSplit2],
	[InvcHead].[RepRate2] as [InvcHead_RepRate2],
	[InvcHead].[CommissionAmt2_c] as [InvcHead_CommissionAmt2_c],
	[InvcHead].[PaymentNotes2_c] as [InvcHead_PaymentNotes2_c],
	[InvcHead].[CommissionType2_c] as [InvcHead_CommissionType2_c],
	[InvcHead].[CommissionApproved2_c] as [InvcHead_CommissionApproved2_c],
	[InvcHead].[CommissionEnteredBy2_c] as [InvcHead_CommissionEnteredBy2_c],
	[InvcHead].[CommissionEnteredByDate2_c] as [InvcHead_CommissionEnteredByDate2_c],
	((case
     when InvcHead.CommissionAmt3_c > 0
         then [Ice].entry(3, InvcHead.SalesRepList, '~')
 end)) as [Calculated_SalesRep3],
	[InvcHead].[RepSplit3] as [InvcHead_RepSplit3],
	[InvcHead].[RepRate3] as [InvcHead_RepRate3],
	[InvcHead].[CommissionAmt3_c] as [InvcHead_CommissionAmt3_c],
	[InvcHead].[PaymentNotes3_c] as [InvcHead_PaymentNotes3_c],
	[InvcHead].[CommissionType3_c] as [InvcHead_CommissionType3_c],
	[InvcHead].[CommissionApproved3_c] as [InvcHead_CommissionApproved3_c],
	[InvcHead].[CommissionEnteredBy3_c] as [InvcHead_CommissionEnteredBy3_c],
	[InvcHead].[CommissionEnteredByDate3_c] as [InvcHead_CommissionEnteredByDate3_c],
	((case
     when InvcHead.CommissionAmt4_c > 0
         then [Ice].entry(4, InvcHead.SalesRepList, '~')
 end)) as [Calculated_SalesRep4],
	[InvcHead].[RepSplit4] as [InvcHead_RepSplit4],
	[InvcHead].[RepRate4] as [InvcHead_RepRate4],
	[InvcHead].[CommissionAmt4_c] as [InvcHead_CommissionAmt4_c],
	[InvcHead].[PaymentNotes4_c] as [InvcHead_PaymentNotes4_c],
	[InvcHead].[CommissionType4_c] as [InvcHead_CommissionType4_c],
	[InvcHead].[CommissionApproved4_c] as [InvcHead_CommissionApproved4_c],
	[InvcHead].[CommissionEnteredBy4_c] as [InvcHead_CommissionEnteredBy4_c],
	[InvcHead].[CommissionEnteredByDate4_c] as [InvcHead_CommissionEnteredByDate4_c],
	[SalesRep].[SalesRepCode] as [SalesRep_SalesRepCode],
	[SalesRep].[Name] as [SalesRep_Name],
	[InvcHead].[SalesRepList] as [InvcHead_SalesRepList]
from Erp.InvcHead as InvcHead
inner join Erp.Customer as Customer on 
	InvcHead.Company = Customer.Company
	and InvcHead.CustNum = Customer.CustNum
left outer join Erp.SalesRep as SalesRep on 
	InvcHead.Company = SalesRep.Company
	and ‘~’ + InvcHead.SalesRepList + ‘~’ Like ‘%~’ + SalesRep.SalesRepCode + ‘~%’
where (InvcHead.CommissionAmt1_c > 0)

justen · August 16, 2022, 3:44pm

UPDATE: I figured out why your suggestion was throwing an error on my side: I was copying your expression and pasting it into the Table Relations fields rather than retyping the expression. I didn’t notice that the ’ character was not the same on the forum.

Now that it’s sorted, I still don’t know how to populate the fields with the SalesRep Name instead of the SalesRepCode. Essentially, I’m getting the exact same results in the BAQ as I was before. Does this now allow me to pull the Name in the calculated field expression?

E102016 · August 16, 2022, 3:53pm

Looks like you copy and pasted the expression? It will not put the correct single quotes around the expression if you did.

justen · August 16, 2022, 3:55pm

Yes, I think our comments crossed paths, haha. I had just figured that out and posted about it right before your comment.

Topic		Replies	Views
BAQ - Link Table to Calculated Field Epicor ERP 10	8	4853	April 22, 2021
Sales rep commission Epicor ERP 9	5	1964	September 11, 2017
Trying to create a BAQ that lists sales by salesperson. Problem is SalesRepList can hold multiple. Help! Epicor ERP 10 e10	11	3563	May 22, 2019
How to make a monthly booking by rep BAQ Epicor ERP 10 baq	53	3721	August 25, 2023
BAQ / SQL Question Epicor ERP 10 sql , baq	3	1136	January 12, 2021

Display SalesRep Name in BAQ Calculated Field

Related Topics