Fixed - BAQ Report with Sales Rep Name

Yahoo Archive
1
Yes it can be done.
Â
You have to start out the BAQ with the SaleRep table.
Next link the InvcHead table using only Customer.
then add other tables of your report.
If there is only one rep then FILTER InvcHead where InvcHead.SalesRepList BEGINS SalesRep.SalesRepCode
Â
If you are using more than one rep then use CALC fields for the different SalesRepCodes and then filter it in Crystal.
Â
See query phrase below
If you want to add a calc field to the BAQ for the SalesRepCode then use this formula.
If Num-Entries(InvcHead.SalesRepList, "~~") >= 1 then Entry(1,
InvcHead.SalesRepList, "~~") else ""

Â
Â
for each SalesRep no-lock , each InvcHead where (Â InvcHead.SalesRepList BEGINS SalesRep.SalesRepCode) no-lock , each Customer where (InvcHead.Company = Customer.Company and InvcHead.CustNum = Customer.CustNum) no-lock , each InvcDtl where (InvcHead.Company = InvcDtl.Company and InvcHead.InvoiceNum = InvcDtl.InvoiceNum) no-lock , each ShipTo where (InvcDtl.Company = ShipTo.Company and InvcDtl.CustNum = ShipTo.CustNum and InvcDtl.ShipToNum = ShipTo.ShipToNum) no-lock , each ProdGrup where (InvcDtl.Company = ProdGrup.Company and InvcDtl.ProdCode = ProdGrup.ProdCode) no-lock .

--- On Wed, 3/4/09, DD <ddavis4569@...> wrote:

From: DD <ddavis4569@...>
Subject: [Vantage] RE: BAQ Report with Sales Rep Name
To: vantage@yahoogroups.com
Date: Wednesday, March 4, 2009, 4:21 PM







I am trying to create a BAQ report of Invoice Commissions with the Sales Rep Name on the report.
Â
InvcHead.SalesRepLi st has the sales rep code in the 1st entry.
I can do a Calc field to pull the Sales Rep Code from the list but I can not use it to link the SalesRep Table to the InvcHead Table.
If I join the tables with just the company then the BAQ will crash.
Â
Anyone know how to link the InvcHead table to the SalesRep Table in a BAQ?
Â
Thanks
Doug Davis



















[Non-text portions of this message have been removed]
2
I am trying to create a BAQ report of Invoice Commissions with the Sales Rep Name on the report.
Â
InvcHead.SalesRepList has the sales rep code in the 1st entry.
I can do a Calc field to pull the Sales Rep Code from the list but I can not use it to link the SalesRep Table to the InvcHead Table.
If I join the tables with just the company then the BAQ will crash.
Â
Anyone know how to link the InvcHead table to the SalesRep Table in a BAQ?
Â
Thanks
Doug Davis
3
I had the same predicament a few months ago. Consensus from the group was that there's no way to get the sales rep name.

Reggie Acosta

--- In vantage@yahoogroups.com, DD <ddavis4569@...> wrote:
>
>
> I am trying to create a BAQ report of Invoice Commissions with the Sales Rep Name on the report.
> Â
> InvcHead.SalesRepList has the sales rep code in the 1st entry.
> I can do a Calc field to pull the Sales Rep Code from the list but I can not use it to link the SalesRep Table to the InvcHead Table.
> If I join the tables with just the company then the BAQ will crash.
> Â
> Anyone know how to link the InvcHead table to the SalesRep Table in a BAQ?
> Â
> Thanks
> Doug Davis
>
4
Do you have only one rep on the invoice? If so, you may have partial success linking InvcHead.SalesRepList with SalesRep.SalesRepCode. If you have more than one rep on an invoice, this will not work. Also, I have seen Vantage/Vista drop tildes into the SalesRepList field on the invoice header, even if there is only one rep. This will prevent those headers from linking with the SalesRep table. For all my commissions calculations, I pull copies of those tables (and some others) into an Access database. There, I parse out the individual sales rep codes from the invoice header sales rep list, and then link those to the Sales Rep table.

FWIW, someone at Epicor had his head stuck way up where the sun don't shine when he structured this aspect of the database. It is an utter joke. Even a database novice should know better than to create the structure they created for sales reps.

Thom Rose
Controller
Electric Mirror LLC
HOTEL LUXURY

"The World Leader in Back-lit Mirrors & Mirror TV Technology"

T 425 776-4946
F 425 491-8200
A 11831 Beverly Park Rd, Bldg D, Everett, WA 98204 USA
www.electricmirror.com<http://www.electricmirror.com>

Note: The information contained in the e-mail, including any attachments, is legally privileged and confidential. If you are not the intended recipient you are hereby notified that any reading, use or dissemination of this message is strictly prohibited. If you have received this message in error, please immediately notify us by telephone at 425-776-4946 and delete this message from your system. Even though this e-mail and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free, and no responsibility is accepted by Electric Mirror LLC for any loss or damage arising in any way from its use


From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of DD
Sent: Wednesday, March 04, 2009 2:22 PM
To: vantage@yahoogroups.com
Subject: [Vantage] RE: BAQ Report with Sales Rep Name


I am trying to create a BAQ report of Invoice Commissions with the Sales Rep Name on the report.

InvcHead.SalesRepList has the sales rep code in the 1st entry.
I can do a Calc field to pull the Sales Rep Code from the list but I can not use it to link the SalesRep Table to the InvcHead Table.
If I join the tables with just the company then the BAQ will crash.

Anyone know how to link the InvcHead table to the SalesRep Table in a BAQ?

Thanks
Doug Davis



[Non-text portions of this message have been removed]