Uh oh! RB Question again

Yahoo Archive
1
Gary,

If you make a calculated field where IIF(SalesRepList Matches
"*os17*","os17",IIF(SalesRepList Matches "*os27*","os27",IIF(SalesRepList
Matches "*os15*","os15",IIF(SalesRepList Matches
"*os25*","os25",SalesRepCode)))). You would have to put in all your
combinations for "os" then use this field to group by and make your
aggregate calculations. It is a lot of work but it should group them by your
outside salesrepcode now.

Jim Frice

> -----Original Message-----
> From: Gary Grenier [mailto:ggrenier@...]
> Sent: Wednesday, August 16, 2000 2:46 PM
> To: vantage@egroups.com
> Subject: [Vantage] Re: Uh oh! RB Question again..
>
>
> A couple of replies inline...
>
> Carol, I think I get the idea here, but wouldn't this only work for one of
> my OS Reps at a time? I want my report to group each OS rep
> separately, but
> all in one report. They are all four characters long, and they
> always start
> (in the SalesRepList) at position 6, 7, 8, or 9. My IS Reps are all four
> characters long, as well, and they always start at position 1, so
> there's no
> problem there. For THAT report, I shortened the SalesRepList
> field to four
> characters. That way, it ignored the rest of the string.
>
> > From: Carol Engen <cengen@...>
>
> > To get around this, I created several functions. (In our case,
> SalesPeople
> > are two characters)
> > * ChosenRep. '31' {where 31 would...
> > * Rep1 SUBSTRING(SalesRepList,1,2)
> > * Rep2 SUBSTRING(SalesRepList,4,2)
> > * Rep3 SUBSTRING(SalesRepList,7,2)
> > * MatchToChosenRep IIF(Rep2Sub = ChosenRep Or Rep3Sub =
> > ChosenRep Or Rep1Sub = ChosenRep,yes,no)
> > My Filter in this report says and MatchToChosenRep = yes
>
> Jim, I've tried this. Unfortunately, because the OS Rep moves around in
> the string, every different position ends up as a different grouping.
> IS03~OS15~~~~, IS03~~OS15~~~, IS03~~~OS15~~, and IS03~~~~OS15~ end up as
> different groupings with separate totals. I want them all
> (because they all
> contain OS15) to be grouped together, with a single total. OS15 can also
> show up in the SalesRepList paired with other IS## reps. I want those in
> the same group as well. In this same report, I'd like to see a
> separate and
> single total for OS27, OS17, etc...
>
> > From: "Jim Frice" <Jim.Frice@...>
>
> > Can you set up a filter using the invchead.salereplist that matches
> "*os*".
> > That should pick up all invoices that have "os" for your outside reps in
> the
> > invchead.salereplist. If I am understanding your coding for
> "os" I believe
> > this will work for you.
> >
> > Jim Frice
2
I received zero responses! Next question: Is it because nobody has any ideas on this one, or is it because the question was hard to understand? If it was hard to understand, I'll try to explain again. The following is the original question:

"I am working on a report that shows total invoicing by sales rep. I'm NOT a big fan of the "SalesRepList" field! We have inside reps and outside reps. Their codes are like the following: IS03, and OS15. Unfortunately, for those two reps, I can see IS03~OS15~~~~, IS03~~OS15~~~, and IS03~~~OS15~~! It's easy enough for me to set up the report if someone wants to know what kind of invoicing can be attributed to IS03. It's also easy to write one that will grab all invoicing related to OS15. What I WANT is to be able to have the report group all invoicing by outside reps in one report. I can't figure out how to do that when the outside reps' codes move around in the string. The above example gives me three separate totals." --for OS15, that is...

I started a call at Eranet to find out why the second rep moves around and to find out if there's a way to make it stationary. Is anybody here using the SalesRepList in reports? If so, do you have multiple reps in that list?

Thanks in advance!

Gary Grenier
Bell Manufacturing Co.
ggrenier@...



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

Send me a copy of the report your working on off list.

Continental Industries Inc. Maker of Solid State Relays.
5456 E. McDowell Rd.
Mesa, Az. 85215
(480)985-7800 Ext. 217


I received zero responses! Next question: Is it because nobody has any
ideas on this one, or is it because the question was hard to understand? If
it was hard to understand, I'll try to explain again. The following is the
original question:

"I am working on a report that shows total invoicing by sales rep. I'm NOT
a big fan of the "SalesRepList" field! We have inside reps and outside
reps. Their codes are like the following: IS03, and OS15. Unfortunately,
for those two reps, I can see IS03~OS15~~~~, IS03~~OS15~~~, and
IS03~~~OS15~~! It's easy enough for me to set up the report if someone
wants to know what kind of invoicing can be attributed to IS03. It's also
easy to write one that will grab all invoicing related to OS15. What I WANT
is to be able to have the report group all invoicing by outside reps in one
report. I can't figure out how to do that when the outside reps' codes move
around in the string. The above example gives me three separate
totals." --for OS15, that is...

I started a call at Eranet to find out why the second rep moves around and
to find out if there's a way to make it stationary. Is anybody here using
the SalesRepList in reports? If so, do you have multiple reps in that list?

Thanks in advance!

Gary Grenier
Bell Manufacturing Co.
ggrenier@...
4
It is very difficult. But not impossible. Primarily, you have to have all
your Salespeople with their identifier the exact same character length. You
can then run reports where the filter is Salesreplist matches "IS015".
Although I have been unable to get this to work (version 3.0)(keeps listing
only the second field position).

To get around this, I created several functions. (In our case, SalesPeople
are two characters)
* ChosenRep. '31' {where 31 would
correspond to your IS015...I have this field in the upper left of the report
and the font is red so that the user can double click on just this field and
the rest of the report flows}


* Rep1 SUBSTRING(SalesRepList,1,2)
* Rep2 SUBSTRING(SalesRepList,4,2)
* Rep3 SUBSTRING(SalesRepList,7,2)
* MatchToChosenRep IIF(Rep2Sub = ChosenRep Or Rep3Sub =
ChosenRep Or Rep1Sub = ChosenRep,yes,no)

My Filter in this report says and MatchToChosenRep = yes




-----Original Message-----
From: Gary Grenier [SMTP:ggrenier@...]
Sent: Wednesday, August 16, 2000 8:00 AM
To: vantage@egroups.com
Subject: [Vantage] Uh oh! RB Question again..

I received zero responses! Next question: Is it because nobody has
any ideas on this one, or is it because the question was hard to understand?
If it was hard to understand, I'll try to explain again. The following is
the original question:

"I am working on a report that shows total invoicing by sales rep.
I'm NOT a big fan of the "SalesRepList" field! We have inside reps and
outside reps. Their codes are like the following: IS03, and OS15.
Unfortunately, for those two reps, I can see IS03~OS15~~~~, IS03~~OS15~~~,
and IS03~~~OS15~~! It's easy enough for me to set up the report if someone
wants to know what kind of invoicing can be attributed to IS03. It's also
easy to write one that will grab all invoicing related to OS15. What I WANT
is to be able to have the report group all invoicing by outside reps in one
report. I can't figure out how to do that when the outside reps' codes move
around in the string. The above example gives me three separate totals."
--for OS15, that is...

I started a call at Eranet to find out why the second rep moves
around and to find out if there's a way to make it stationary. Is anybody
here using the SalesRepList in reports? If so, do you have multiple reps in
that list?

Thanks in advance!

Gary Grenier
Bell Manufacturing Co.
ggrenier@...



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





We no longer allow attachments to files. To access/share Report
Files, please go to the following link:
http://www.egroups.com/files/vantage/
(Note: If this link does not work for you the first time you try
it, go to www.egroups.com, login and be sure to save your password, choose
My Groups, choose Vantage, then choose Files. If you save the password, the
link above will work the next time you try it.)
5
Gary,

Can you set up a filter using the invchead.salereplist that matches "*os*".
That should pick up all invoices that have "os" for your outside reps in the
invchead.salereplist. If I am understanding your coding for "os" I believe
this will work for you.

Jim Frice

> -----Original Message-----
> From: Gary Grenier [mailto:ggrenier@...]
> Sent: Wednesday, August 16, 2000 8:00 AM
> To: vantage@egroups.com
> Subject: [Vantage] Uh oh! RB Question again..
>
>
> I received zero responses! Next question: Is it because nobody
> has any ideas on this one, or is it because the question was hard
> to understand? If it was hard to understand, I'll try to explain
> again. The following is the original question:
>
> "I am working on a report that shows total invoicing by sales
> rep. I'm NOT a big fan of the "SalesRepList" field! We have
> inside reps and outside reps. Their codes are like the
> following: IS03, and OS15. Unfortunately, for those two reps, I
> can see IS03~OS15~~~~, IS03~~OS15~~~, and IS03~~~OS15~~! It's
> easy enough for me to set up the report if someone wants to know
> what kind of invoicing can be attributed to IS03. It's also easy
> to write one that will grab all invoicing related to OS15. What
> I WANT is to be able to have the report group all invoicing by
> outside reps in one report. I can't figure out how to do that
> when the outside reps' codes move around in the string. The
> above example gives me three separate totals." --for OS15, that is...
>
> I started a call at Eranet to find out why the second rep moves
> around and to find out if there's a way to make it stationary.
> Is anybody here using the SalesRepList in reports? If so, do you
> have multiple reps in that list?
>
> Thanks in advance!
>
> Gary Grenier
> Bell Manufacturing Co.
> ggrenier@...
6
Try the SUBSTRING-COUNT function.
IFF(SUBSTRING-COUNT("OS",InvcHead.SalesRepList) > 0,"OutSide","Inside")
Good Luck, why they don't join to a table for SalesRepList is beyond my
thinking.

Patrick J. Winter
Information Services Manager
sSc Specialty Screw Corporation
Vantage 3.00.614, Progress 8.3A

-----Original Message-----
From: Gary Grenier [mailto:ggrenier@...]
Sent: Wednesday, August 16, 2000 08:00 AM
To: vantage@egroups.com
Subject: [Vantage] Uh oh! RB Question again..


I received zero responses! Next question: Is it because nobody has any
ideas on this one, or is it because the question was hard to understand? If
it was hard to understand, I'll try to explain again. The following is the
original question:

"I am working on a report that shows total invoicing by sales rep. I'm NOT
a big fan of the "SalesRepList" field! We have inside reps and outside
reps. Their codes are like the following: IS03, and OS15. Unfortunately,
for those two reps, I can see IS03~OS15~~~~, IS03~~OS15~~~, and
IS03~~~OS15~~! It's easy enough for me to set up the report if someone
wants to know what kind of invoicing can be attributed to IS03. It's also
easy to write one that will grab all invoicing related to OS15. What I WANT
is to be able to have the report group all invoicing by outside reps in one
report. I can't figure out how to do that when the outside reps' codes move
around in the string. The above example gives me three separate totals."
--for OS15, that is...

I started a call at Eranet to find out why the second rep moves around and
to find out if there's a way to make it stationary. Is anybody here using
the SalesRepList in reports? If so, do you have multiple reps in that list?

Thanks in advance!

Gary Grenier
Bell Manufacturing Co.
ggrenier@...



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




We no longer allow attachments to files. To access/share Report Files,
please go to the following link: http://www.egroups.com/files/vantage/
(Note: If this link does not work for you the first time you try it, go to
www.egroups.com, login and be sure to save your password, choose My Groups,
choose Vantage, then choose Files. If you save the password, the link above
will work the next time you try it.)
7
At 08:59 AM 8/16/2000 , you wrote:
>like the following: IS03, and OS15. Unfortunately, for those two reps, I
>can see IS03~OS15~~~~, IS03~~OS15~~~, and IS03~~~OS15~~! It's easy enough for

I've seen this in a bunch of places in Vantage in the 12 months or so we've
had it, and was quite surprised. If any of have been Universe / Unidata /
Pick programmers, it's immediately obvious what they've doing! Those
databases thrive on delimited lists where others would use detail
tables. Big difference is, they have a report generator that can deal with
them somewhat gracefully, and blow the lists apart to look like separate
details. In the Progress R.B. you're pretty much SOL. I think your best
bet is the calculated fields that were previously posted.

Makes me wonder though... aren't some of Epicor / Dataworks' other ERP
packages UniData based? Perhaps they yanked some of the programmers of
those and sat them down with Vantage [and not enough training?] Hmm.

-Wayne Cox
8
A couple of replies inline...

Carol, I think I get the idea here, but wouldn't this only work for one of
my OS Reps at a time? I want my report to group each OS rep separately, but
all in one report. They are all four characters long, and they always start
(in the SalesRepList) at position 6, 7, 8, or 9. My IS Reps are all four
characters long, as well, and they always start at position 1, so there's no
problem there. For THAT report, I shortened the SalesRepList field to four
characters. That way, it ignored the rest of the string.

> From: Carol Engen <cengen@...>

> To get around this, I created several functions. (In our case, SalesPeople
> are two characters)
> * ChosenRep. '31' {where 31 would...
> * Rep1 SUBSTRING(SalesRepList,1,2)
> * Rep2 SUBSTRING(SalesRepList,4,2)
> * Rep3 SUBSTRING(SalesRepList,7,2)
> * MatchToChosenRep IIF(Rep2Sub = ChosenRep Or Rep3Sub =
> ChosenRep Or Rep1Sub = ChosenRep,yes,no)
> My Filter in this report says and MatchToChosenRep = yes

Jim, I've tried this. Unfortunately, because the OS Rep moves around in
the string, every different position ends up as a different grouping.
IS03~OS15~~~~, IS03~~OS15~~~, IS03~~~OS15~~, and IS03~~~~OS15~ end up as
different groupings with separate totals. I want them all (because they all
contain OS15) to be grouped together, with a single total. OS15 can also
show up in the SalesRepList paired with other IS## reps. I want those in
the same group as well. In this same report, I'd like to see a separate and
single total for OS27, OS17, etc...

> From: "Jim Frice" <Jim.Frice@...>

> Can you set up a filter using the invchead.salereplist that matches
"*os*".
> That should pick up all invoices that have "os" for your outside reps in
the
> invchead.salereplist. If I am understanding your coding for "os" I believe
> this will work for you.
>
> Jim Frice
9
Gary, I think the lack of responses is due to the difficulty of the problem.
I looked at it this morning and could not come up with a solution. The
unfortunate reality is that SalesRepList (similar to TaxCodeList in Customer
master) can have zero to five entries and they do NOT have to be in any
order.

Here is the Description of SalesRepList from the Data Dictionary: (I added
the highlight)

"Stores the Sales Rep Codes for the order. Up to five codes can be
established. This field is not directly maitainable. Instead temp widgets
are used for entry of each individual code and then put together as one
comma delimited string field and assigned to this field. This field will
have a Word index, it then will make reporting and data base integrity
checking much easier thru the use of the "contains phrase" when retrieving
records. These codes can be left blank or must be valid in the SalesRep
master. The first one is defaulted from the Customer master if ship to is
blank else from the ShipTo. "

I tried everything I know and am left scratching my head!

Regards
Jim Stetter
-----Original Message-----
From: Gary Grenier [mailto:ggrenier@...]
Sent: Wednesday, August 16, 2000 9:00 AM
To: vantage@egroups.com
Subject: [Vantage] Uh oh! RB Question again..


I received zero responses! Next question: Is it because nobody has any
ideas on this one, or is it because the question was hard to understand? If
it was hard to understand, I'll try to explain again. The following is the
original question:

"I am working on a report that shows total invoicing by sales rep. I'm
NOT a big fan of the "SalesRepList" field! We have inside reps and outside
reps. Their codes are like the following: IS03, and OS15. Unfortunately,
for those two reps, I can see IS03~OS15~~~~, IS03~~OS15~~~, and
IS03~~~OS15~~! It's easy enough for me to set up the report if someone
wants to know what kind of invoicing can be attributed to IS03. It's also
easy to write one that will grab all invoicing related to OS15. What I WANT
is to be able to have the report group all invoicing by outside reps in one
report. I can't figure out how to do that when the outside reps' codes move
around in the string. The above example gives me three separate
totals." --for OS15, that is...

I started a call at Eranet to find out why the second rep moves around and
to find out if there's a way to make it stationary. Is anybody here using
the SalesRepList in reports? If so, do you have multiple reps in that list?

Thanks in advance!

Gary Grenier
Bell Manufacturing Co.
ggrenier@belllabel.


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