RB - Split Commissions & Sales Rep List-My solution

A while back I was asked by our Sales and Marketing Department to
create a report that would group our commissionable invoiced sales by
Sales Rep. calculate the amount of commission per customer within the
sales rep and a total for each sales rep. I worked on the report in
Crystal and ran into the quirkiness of the SalesRepList field in the
InvcHead Table.

My solution to the problem was to create a series of queries in
Access to parse the information out and create one Commissions table
with the following fields.

Rep
Name
InvoiceNum
InvoiceAmt
RepRate
RepSplit
RepSales
RepCom

This table is linked to the InvcHead table in my crystal report by
the InvoiceNum. Prior to running the report the user needs to run an
Access macro, which she has a shortcut for on her Vantage Favorites
bar. The macro runs the queries and updates the information in the
commissions table. The report is then ready to run. Perhaps I could
have used a method similar to what you guys are talking about, but I
decided to create a table of information I new how to deal with. For
better or worse, just another way to skin the cat!

Brian Stenglein
Clow Stamping Company
I'm writing a report to show commission split details for orders being split
between two sales reps. The % of split and the commission rate are easily
had from the order detail but figuring out which sales rep code goes with
each rate is another thing. Two questions about the SalesRepList field on
the Order Header:

1. When I print it the reps are seperated by the tilde character (~). I
finally figured out how to parse out the rep codes but had to create a
string field for CHR(126) rather than use '~' in the ENTRY function. Just
curious why this character can't be used in a direct string (inside quotes
that is). Also the data dictionary says the list is seperated by commas but
this does not seem to be the case.

2. With this big string called SaleRepList is there any way to link to the
rep table to get the name? We have about 40 reps and the way it looks now
is that will have to hard code the names into the report.

It would seem that having the rep code handled the same way as the rep rate
and split would have made it no problem at all to link to 5 alias sales rep
tables and get the respective names. If anyone knows some other way to get
the rep names I would really appreciate hearing it.

-Todd Caughey
Harvey Vogel Mfg. Co.



[Non-text portions of this message have been removed]
Never mind about the link part. Just got that to work. The tilde thing and
the reason they used SalesRepList string are still a curiosity but not a
show stopper any more. Thanks.
-Todd

-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Monday, August 20, 2001 1:05 PM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] RB - Split Commissions & Sales Rep List


I'm writing a report to show commission split details for orders being split
between two sales reps. The % of split and the commission rate are easily
had from the order detail but figuring out which sales rep code goes with
each rate is another thing. Two questions about the SalesRepList field on
the Order Header:

1. When I print it the reps are seperated by the tilde character (~). I
finally figured out how to parse out the rep codes but had to create a
string field for CHR(126) rather than use '~' in the ENTRY function. Just
curious why this character can't be used in a direct string (inside quotes
that is). Also the data dictionary says the list is seperated by commas but
this does not seem to be the case.

2. With this big string called SaleRepList is there any way to link to the
rep table to get the name? We have about 40 reps and the way it looks now
is that will have to hard code the names into the report.

It would seem that having the rep code handled the same way as the rep rate
and split would have made it no problem at all to link to 5 alias sales rep
tables and get the respective names. If anyone knows some other way to get
the rep names I would really appreciate hearing it.

-Todd Caughey
Harvey Vogel Mfg. Co.



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



Yahoo! Groups Sponsor

<http://rd.yahoo.com/M=210949.1523302.3121208.1269402/D=egroupweb/S=17050071
83:HM/A=763786/R=0/*http://altfarm.mediaplex.com/ad/ck/1177-4458-1039-1?mpt=
998331058>

<http://us.adserver.yahoo.com/l?M=210949.1523302.3121208.1269402/D=egroupmai
l/S=1705007183:HM/A=763786/rand=977872727>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]
This problem with the tildes is known by Epicor. I worked with tech
support for a while on this problem. I was eventually told that it would
be "corrected in a future release". Take that as you will. Are you on
version 5? We are on 4 and I gave up trying to separate the reps in the
SalesRepList field. All of our reps have a four character code, but there
was no pattern to the tildes. The number of tildes between the two reps in
the list would vary from one to four.

Gary Grenier
IT Manager
Bell Manufacturing Co.
ggrenier@...

From: Todd Caughey <caugheyt@...>
Subject: RB - Split Commissions & Sales Rep List

...questions about the SalesRepList field on
the Order Header:

1. When I print it the reps are seperated by the tilde character (~). I
finally figured out how to parse out the rep codes but had to create a
string field for CHR(126) rather than use '~' in the ENTRY function. Just
curious why this character can't be used in a direct string (inside quotes
that is). Also the data dictionary says the list is seperated by commas
but
this does not seem to be the case...

-Todd Caughey
Harvey Vogel Mfg. Co.
We are on version 4. So far it is working OK for up to two rep codes (the
most we split so far) of one or two digits each using the ENTRY function and
creating a constant I call Tilde with the CHR function
Tilde=CHR(126) ENTRY(1,SalesRepList,Tilde)

If I have more than two I will keep a close eye out for it in case it can't
resolve which entry number in the list. I wonder if this might be due to
the way the screen temp widgets are being put together in the string saved
as SaleRepList. The link to the SaleRep table is working well for both 1
and two digit codes so I know I am getting them OK. When specifying no
delimiter in
the ENTRY function it returned 1 digit codes as the number plus a tilde
character and this would not link.

Thanks for the heads up on the bug.
-Todd C.

-----Original Message-----
From: ggrenier@... [mailto:ggrenier@...]
Sent: Tuesday, August 21, 2001 9:23 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: RB - Split Commissions & Sales Rep List



This problem with the tildes is known by Epicor. I worked with tech
support for a while on this problem. I was eventually told that it would
be "corrected in a future release". Take that as you will. Are you on
version 5? We are on 4 and I gave up trying to separate the reps in the
SalesRepList field. All of our reps have a four character code, but there
was no pattern to the tildes. The number of tildes between the two reps in
the list would vary from one to four.

Gary Grenier
IT Manager
Bell Manufacturing Co.
ggrenier@...

From: Todd Caughey <caugheyt@...>
Subject: RB - Split Commissions & Sales Rep List

...questions about the SalesRepList field on
the Order Header:

1. When I print it the reps are seperated by the tilde character (~). I
finally figured out how to parse out the rep codes but had to create a
string field for CHR(126) rather than use '~' in the ENTRY function. Just
curious why this character can't be used in a direct string (inside quotes
that is). Also the data dictionary says the list is seperated by commas
but
this does not seem to be the case...

-Todd Caughey
Harvey Vogel Mfg. Co.





Yahoo! Groups Sponsor

<http://rd.yahoo.com/M=207641.1565480.3121573.1269402/D=egroupweb/S=17050071
83:HM/A=765263/R=0/*http://www.verisign.com/cgi-bin/go.cgi?a=b15334027000300
0>

<http://us.adserver.yahoo.com/l?M=207641.1565480.3121573.1269402/D=egroupmai
l/S=1705007183:HM/A=765263/rand=562503292>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]
Nice work Todd,
Now that I can at least strip out the first Sales Rep, I can create a report
builder report which prints a customer Acknowledgement Form that
reacknowledges orders with a lot of releases which the crystal version
doesn't do.

Patrick Winter
sSc Specialty Screw Corporation
Vantage 5.00.317, Progress 9.1a

-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Tuesday, August 21, 2001 9:40 AM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Re: RB - Split Commissions & Sales Rep List


We are on version 4. So far it is working OK for up to two rep codes (the
most we split so far) of one or two digits each using the ENTRY function and
creating a constant I call Tilde with the CHR function
Tilde=CHR(126) ENTRY(1,SalesRepList,Tilde)

If I have more than two I will keep a close eye out for it in case it can't
resolve which entry number in the list. I wonder if this might be due to
the way the screen temp widgets are being put together in the string saved
as SaleRepList. The link to the SaleRep table is working well for both 1
and two digit codes so I know I am getting them OK. When specifying no
delimiter in
the ENTRY function it returned 1 digit codes as the number plus a tilde
character and this would not link.

Thanks for the heads up on the bug.
-Todd C.

-----Original Message-----
From: ggrenier@... [mailto:ggrenier@...]
Sent: Tuesday, August 21, 2001 9:23 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: RB - Split Commissions & Sales Rep List



This problem with the tildes is known by Epicor. I worked with tech
support for a while on this problem. I was eventually told that it would
be "corrected in a future release". Take that as you will. Are you on
version 5? We are on 4 and I gave up trying to separate the reps in the
SalesRepList field. All of our reps have a four character code, but there
was no pattern to the tildes. The number of tildes between the two reps in
the list would vary from one to four.

Gary Grenier
IT Manager
Bell Manufacturing Co.
ggrenier@...

From: Todd Caughey <caugheyt@...>
Subject: RB - Split Commissions & Sales Rep List

...questions about the SalesRepList field on
the Order Header:

1. When I print it the reps are seperated by the tilde character (~). I
finally figured out how to parse out the rep codes but had to create a
string field for CHR(126) rather than use '~' in the ENTRY function. Just
curious why this character can't be used in a direct string (inside quotes
that is). Also the data dictionary says the list is seperated by commas
but
this does not seem to be the case...

-Todd Caughey
Harvey Vogel Mfg. Co.





Yahoo! Groups Sponsor

<http://rd.yahoo.com/M=207641.1565480.3121573.1269402/D=egroupweb/S=17050071
83:HM/A=765263/R=0/*http://www.verisign.com/cgi-bin/go.cgi?a=b15334027000300
0>

<http://us.adserver.yahoo.com/l?M=207641.1565480.3121573.1269402/D=egroupmai
l/S=1705007183:HM/A=765263/rand=562503292>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




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



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Not to beat this to death but in case anyone is interested.

1. It appears the ENTRY function will ignore multiple delimiters (tilde in
this case) and find each occurrence of the non-delimiter strings.

2. In a test database I created an order with five salespersons and a small
testing report I wrote correctly found all five. I also tried the
NUM-ENTRIES function on SalesRepList and it reported 5. The raw
SalesRepList string showed 24-04-38-56-67 (imagine tilde characters for the
dashes). So there was one tilde between each code.

3. I went back to the Salesperson list on the order header and set numbers 2
and 4 to be unused. In the order line commissions screen it correctly
showed only three reps and I had to adjust the split percentages there. I
re-ran the test report and the raw string showed as 24--38--67- with two
tildes between the codes and a dangling one at the end. But the NUM-ENTRIES
was now 3 and the reps and the percentages all showed correctly in the
report. If there are variable numbers of tildes on orders it might possibly
be related to the positions used by the person entering the order header.
If they are skipping around, perhaps to make it easier to read, it might
produce the multiple tildes between codes. Or it still might be a bug.

So I am pretty sure setting a report up with:
Rep1 = ENTRY(1,SalesRepList,Tilde)
Rep2 = ENTRY(2,SalesRepList,Tilde) and so forth thru 5 will get them all
and allow linking to alias SalesRep tables to get all the names.

-Todd C.



-----Original Message-----
From: Winter, Patrick [mailto:pjw@...]
Sent: Tuesday, August 21, 2001 1:01 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Re: RB - Split Commissions & Sales Rep List


Nice work Todd,
Now that I can at least strip out the first Sales Rep, I can create a report
builder report which prints a customer Acknowledgement Form that
reacknowledges orders with a lot of releases which the crystal version
doesn't do.

Patrick Winter
sSc Specialty Screw Corporation
Vantage 5.00.317, Progress 9.1a

-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Tuesday, August 21, 2001 9:40 AM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Re: RB - Split Commissions & Sales Rep List


We are on version 4. So far it is working OK for up to two rep codes (the
most we split so far) of one or two digits each using the ENTRY function and
creating a constant I call Tilde with the CHR function
Tilde=CHR(126) ENTRY(1,SalesRepList,Tilde)

If I have more than two I will keep a close eye out for it in case it can't
resolve which entry number in the list. I wonder if this might be due to
the way the screen temp widgets are being put together in the string saved
as SaleRepList. The link to the SaleRep table is working well for both 1
and two digit codes so I know I am getting them OK. When specifying no
delimiter in
the ENTRY function it returned 1 digit codes as the number plus a tilde
character and this would not link.

Thanks for the heads up on the bug.
-Todd C.

-----Original Message-----
From: ggrenier@... [mailto:ggrenier@...]
Sent: Tuesday, August 21, 2001 9:23 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: RB - Split Commissions & Sales Rep List



This problem with the tildes is known by Epicor. I worked with tech
support for a while on this problem. I was eventually told that it would
be "corrected in a future release". Take that as you will. Are you on
version 5? We are on 4 and I gave up trying to separate the reps in the
SalesRepList field. All of our reps have a four character code, but there
was no pattern to the tildes. The number of tildes between the two reps in
the list would vary from one to four.

Gary Grenier
IT Manager
Bell Manufacturing Co.
ggrenier@...

From: Todd Caughey <caugheyt@...>
Subject: RB - Split Commissions & Sales Rep List

...questions about the SalesRepList field on
the Order Header:

1. When I print it the reps are seperated by the tilde character (~). I
finally figured out how to parse out the rep codes but had to create a
string field for CHR(126) rather than use '~' in the ENTRY function. Just
curious why this character can't be used in a direct string (inside quotes
that is). Also the data dictionary says the list is seperated by commas
but
this does not seem to be the case...

-Todd Caughey
Harvey Vogel Mfg. Co.





Yahoo! Groups Sponsor

<
http://rd.yahoo.com/M=207641.1565480.3121573.1269402/D=egroupweb/S=17050071
<http://rd.yahoo.com/M=207641.1565480.3121573.1269402/D=egroupweb/S=17050071
>
83:HM/A=765263/R=0/*
http://www.verisign.com/cgi-bin/go.cgi?a=b15334027000300
<http://www.verisign.com/cgi-bin/go.cgi?a=b15334027000300>
0>

<
http://us.adserver.yahoo.com/l?M=207641.1565480.3121573.1269402/D=egroupmai
<http://us.adserver.yahoo.com/l?M=207641.1565480.3121573.1269402/D=egroupmai
>
l/S=1705007183:HM/A=765263/rand=562503292>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
< http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.> >
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
< http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages> >
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>
< http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links> >

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
< http://docs.yahoo.com/info/terms/ <http://docs.yahoo.com/info/terms/> > .




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



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
<http://docs.yahoo.com/info/terms/>



Yahoo! Groups Sponsor

<http://rd.yahoo.com/M=207641.1565480.3121573.1269402/D=egroupweb/S=17050071
83:HM/A=765263/R=0/*http://www.verisign.com/cgi-bin/go.cgi?a=b15334027000300
0>

<http://us.adserver.yahoo.com/l?M=207641.1565480.3121573.1269402/D=egroupmai
l/S=1705007183:HM/A=765263/rand=192081763>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




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