The dreaded SalesRepList field

Yahoo Archive
1
Nice! Gonna file this one away.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of pbparker
Sent: Monday, March 05, 2012 3:34 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: The dreaded SalesRepList field





Got it working.

Replace the line:
{Bpm/Bpm.i &OBJECT_NAME=UD14 }

with:
{bo/UD14/UD14_ds.i}

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "pbparker" <scrumbus@...> wrote:
>
> I verified all the method calls and none have changed either in name or input/output methods.
>
> Wish it would kick back a bit clearer of an error than 4GL stop condition with nothing further.
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , Joe Rojas <jrojas@> wrote:
> >
> > I was the one who created this .p file.
> >
> > It’s a good heads up to know if won’t work in 9.05 because we are upgrading soon.
> >
> >
> >
> > Off the top of my head, the only reason I can think of that it would be bombing out is because Epicor changed the method calls I’m using. I would use the method directives screen to see if either the method names changed or the number or type of parameters changed.
> >
> >
> >
> > Something else to try is to work with the tables directly as opposed to using the BO. I used the BO because I was practicing how to do it. From within the BPM you can use the CREATE and DELETE statements to add and remove rows.
> >
> >
> >
> > From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf Of pbparker
> > Sent: Monday, March 05, 2012 2:59 PM
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > Subject: [Vantage] Re: The dreaded SalesRepList field
> >
> >
> >
> >
> >
> > Looks like someone created a BPM .p file in the Yahoo files to do exactly what I'm asking for, namely separate the sales reps out into a flattened UD table.
> >
> > Only problem is it doesn't work in 9.05. Anyone willing to look at the file in the group files/BPMs section named CallBOFromABL.zip and see if they can see what's wrong with it offhand?
> >
> > I modified his initial method to call the .p directly and hard coded his input variables to see if that was it, but the .p file throws a 4GL error trying to validate it, so it's in there no doubt.
> >
> > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> , "pbparker" <scrumbus@> wrote:
> > >
> > > Yeah, it's the same way in E 9.05.607B and sadly, yes we're on Progress as well now. We're looking to convert to SQL, just can't get a price quoted from Epicor on what this will cost, so we'll just look at it later if/when we implement AFR.
> > >
> > > I tried already doing some linked servers but ran into a problem on my 64bit server with the 64bit ODBC drivers and SQL 2008 so I washed my hands on that for now.
> > >
> > > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> , "Vic Drecchio" <vic.drecchio@> wrote:
> > > >
> > > > I’ve done it before using SQL Views with Selects with the UNION clause. And then join the View to the SalesRep table.
> > > >
> > > > Assuming you’re not a SQL user you’re pretty much screwed. (sorry)
> > > >
> > > > I understand why they may have architected that field like that, but still, it’s an un-joinable field to a master table (Sales Reps). The schema really needs to change; is it like that in E9, too?
> > > >
> > > > If you DO NOT have a lot of Sales Reps (<= baker’s dozen), I’ve used suppression and grouping tricks in Crystal before that get you close to what you want via a scheduled BAQ dump to CSV/XML. You just need a detail section and shared variables for each Rep to make it work. It’s a PAIN to develop and modify...
> > > >
> > > > Finally, if you are a lowly Progress customer like myself, one other â€Å"out-of-the-box” thought would be to install the free version of MS SQL Server, set up your live Progress DB as a linked server and do a â€Å"SELECT INTO...” and have static data cubes (tables) in MSSQL. You could update it nightly if that works for your company. Once you â€Å"suck in” the Order tables and SalesRep table into SQL, *then* you can create some magic and either: Create a sexy ASP web dashboard from the data in SQL and/or use Crystal Reports and hit SQL instead of the BAQ or Progress.
> > > >
> > > >
> > > >
> > > > Vic
> > > >
> > > >
> > > > From: pbparker
> > > > Sent: Friday, March 02, 2012 11:47 AM
> > > > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
> > > > Subject: [Vantage] The dreaded SalesRepList field
> > > >
> > > >
> > > > I had to go and mention it recently in a post here and sadly I've awoken the karma god.
> > > >
> > > > So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.
> > > >
> > > > I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.
> > > >
> > > > Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?
> > > >
> > > > I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.
> > > >
> > > > Anyone come up with anything else for a solution? (I really hate this field).
> > > >
> > > >
> > > >
> > > >
> > > > No virus found in this message.
> > > > Checked by AVG - www.avg.com
> > > > Version: 2012.0.1913 / Virus Database: 2114/4846 - Release Date: 03/02/12
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Joe Rojas | Director of Information Technology | Mats Inc
> > dir: 781-573-0291 | cell: 781-408-9278 | fax: 781-232-5191
> > jrojas@ | www.matsinc.com Ask us about our clean, green and beautiful matting and flooring
> >
> >
> > This message is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company.
> >
> >
> > [Non-text portions of this message have been removed]
> >
>












Joe Rojas | Director of Information Technology | Mats Inc
dir: 781-573-0291 | cell: 781-408-9278 | fax: 781-232-5191
jrojas@... | www.matsinc.com Ask us about our clean, green and beautiful matting and flooring


This message is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company.


[Non-text portions of this message have been removed]
2
I had to go and mention it recently in a post here and sadly I've awoken the karma god.

So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.

I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.

Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?

I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.

Anyone come up with anything else for a solution? (I really hate this field).
3
At least the license is free! Im curious how you split the field out.. could you do it through the default rep in the customer table?

Rob Bucek
Production Control Manager
PH: (715) 284-5376 ext 311
Mobile: (715)896-0590
FAX: (715)284-4084
[Description: cid:1.234354861@...]<http://www.dsmfg.com/>
(Click the logo to view our site)<http://www.dsmfg.com/>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of pbparker
Sent: Friday, March 02, 2012 10:47 AM
To: vantage@yahoogroups.com
Subject: [Vantage] The dreaded SalesRepList field



I had to go and mention it recently in a post here and sadly I've awoken the karma god.

So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.

I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.

Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?

I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.

Anyone come up with anything else for a solution? (I really hate this field).



[Non-text portions of this message have been removed]
4
I'm not sure if this will cover what you need but
I remembered this thread when I read your question:

http://tech.groups.yahoo.com/group/vantage/message/103467


--- In vantage@yahoogroups.com, "pbparker" <scrumbus@...> wrote:
>
> I had to go and mention it recently in a post here and sadly I've awoken the karma god.
>
> So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.
>
> I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.
>
> Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?
>
> I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.
>
> Anyone come up with anything else for a solution? (I really hate this field).
>
5
I’ve done it before using SQL Views with Selects with the UNION clause. And then join the View to the SalesRep table.

Assuming you’re not a SQL user you’re pretty much screwed. (sorry)

I understand why they may have architected that field like that, but still, it’s an un-joinable field to a master table (Sales Reps). The schema really needs to change; is it like that in E9, too?

If you DO NOT have a lot of Sales Reps (<= baker’s dozen), I’ve used suppression and grouping tricks in Crystal before that get you close to what you want via a scheduled BAQ dump to CSV/XML. You just need a detail section and shared variables for each Rep to make it work. It’s a PAIN to develop and modify...

Finally, if you are a lowly Progress customer like myself, one other “out-of-the-box” thought would be to install the free version of MS SQL Server, set up your live Progress DB as a linked server and do a “SELECT INTO...” and have static data cubes (tables) in MSSQL. You could update it nightly if that works for your company. Once you “suck in” the Order tables and SalesRep table into SQL, *then* you can create some magic and either: Create a sexy ASP web dashboard from the data in SQL and/or use Crystal Reports and hit SQL instead of the BAQ or Progress.



Vic


From: pbparker
Sent: Friday, March 02, 2012 11:47 AM
To: vantage@yahoogroups.com
Subject: [Vantage] The dreaded SalesRepList field


I had to go and mention it recently in a post here and sadly I've awoken the karma god.

So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.

I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.

Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?

I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.

Anyone come up with anything else for a solution? (I really hate this field).




No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2114/4846 - Release Date: 03/02/12


[Non-text portions of this message have been removed]
6
I already whipped up some rough code based on what someone said like a year ago when they used it for a calculated field, I simply modified it to be ABL code more or less.

Testing it in a BPM on SalesOrder.Update it fires back all my rep values, so I know the code works.. I just need now to have the ability to insert/delete in progress to maintain my own table of proper data.

Here's the ABL code I did.

DEFINE VARIABLE varRepName as character.
FIND FIRST ttOrderHed NO-LOCK NO-ERROR.

IF AVAILABLE ttOrderHEd THEN DO:

IF (Num-entries(ttOrderHed.SalesRepList,"~~")>=1) THEN DO:
varRepName = (Entry(1,ttOrderHed.SalesRepList,"~~")).
{lib/PublishInfoMsg.i &InfoMsg = varRepName}
END.
IF (Num-entries(ttOrderHed.SalesRepList,"~~")>=2) THEN DO:
varRepName = (Entry(2,ttOrderHed.SalesRepList,"~~")).
{lib/PublishInfoMsg.i &InfoMsg = varRepName}
END.
IF (Num-entries(ttOrderHed.SalesRepList,"~~")>=3) THEN DO:
varRepName = (Entry(3,ttOrderHed.SalesRepList,"~~")).
{lib/PublishInfoMsg.i &InfoMsg = varRepName}
END.
END.

--- In vantage@yahoogroups.com, "b_ordway" <cooner_55421@...> wrote:
>
> I'm not sure if this will cover what you need but
> I remembered this thread when I read your question:
>
> http://tech.groups.yahoo.com/group/vantage/message/103467
>
>
> --- In vantage@yahoogroups.com, "pbparker" <scrumbus@> wrote:
> >
> > I had to go and mention it recently in a post here and sadly I've awoken the karma god.
> >
> > So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.
> >
> > I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.
> >
> > Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?
> >
> > I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.
> >
> > Anyone come up with anything else for a solution? (I really hate this field).
> >
>
7
Yeah, it's the same way in E 9.05.607B and sadly, yes we're on Progress as well now. We're looking to convert to SQL, just can't get a price quoted from Epicor on what this will cost, so we'll just look at it later if/when we implement AFR.

I tried already doing some linked servers but ran into a problem on my 64bit server with the 64bit ODBC drivers and SQL 2008 so I washed my hands on that for now.

--- In vantage@yahoogroups.com, "Vic Drecchio" <vic.drecchio@...> wrote:
>
> I’ve done it before using SQL Views with Selects with the UNION clause. And then join the View to the SalesRep table.
>
> Assuming you’re not a SQL user you’re pretty much screwed. (sorry)
>
> I understand why they may have architected that field like that, but still, it’s an un-joinable field to a master table (Sales Reps). The schema really needs to change; is it like that in E9, too?
>
> If you DO NOT have a lot of Sales Reps (<= baker’s dozen), I’ve used suppression and grouping tricks in Crystal before that get you close to what you want via a scheduled BAQ dump to CSV/XML. You just need a detail section and shared variables for each Rep to make it work. It’s a PAIN to develop and modify...
>
> Finally, if you are a lowly Progress customer like myself, one other “out-of-the-box” thought would be to install the free version of MS SQL Server, set up your live Progress DB as a linked server and do a “SELECT INTO...” and have static data cubes (tables) in MSSQL. You could update it nightly if that works for your company. Once you “suck in” the Order tables and SalesRep table into SQL, *then* you can create some magic and either: Create a sexy ASP web dashboard from the data in SQL and/or use Crystal Reports and hit SQL instead of the BAQ or Progress.
>
>
>
> Vic
>
>
> From: pbparker
> Sent: Friday, March 02, 2012 11:47 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] The dreaded SalesRepList field
>
>
> I had to go and mention it recently in a post here and sadly I've awoken the karma god.
>
> So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.
>
> I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.
>
> Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?
>
> I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.
>
> Anyone come up with anything else for a solution? (I really hate this field).
>
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.1913 / Virus Database: 2114/4846 - Release Date: 03/02/12
>
>
> [Non-text portions of this message have been removed]
>
8
You can call the BO methods to add records for the UD table from a BPM. I usually put the routine in a .p file and have the BPM call it. You could pass the sales order number and the salerep field to the .p file. It would break sales rep down and add as many records as there are sales rep to the UD table.

If you need some examples of calling BOs via .p files let me know and I will dig up some.

Jim Kinneman
Encompass Solutions, Inc

--- In vantage@yahoogroups.com, "pbparker" <scrumbus@...> wrote:
>
> I already whipped up some rough code based on what someone said like a year ago when they used it for a calculated field, I simply modified it to be ABL code more or less.
>
> Testing it in a BPM on SalesOrder.Update it fires back all my rep values, so I know the code works.. I just need now to have the ability to insert/delete in progress to maintain my own table of proper data.
>
> Here's the ABL code I did.
>
> DEFINE VARIABLE varRepName as character.
> FIND FIRST ttOrderHed NO-LOCK NO-ERROR.
>
> IF AVAILABLE ttOrderHEd THEN DO:
>
> IF (Num-entries(ttOrderHed.SalesRepList,"~~")>=1) THEN DO:
> varRepName = (Entry(1,ttOrderHed.SalesRepList,"~~")).
> {lib/PublishInfoMsg.i &InfoMsg = varRepName}
> END.
> IF (Num-entries(ttOrderHed.SalesRepList,"~~")>=2) THEN DO:
> varRepName = (Entry(2,ttOrderHed.SalesRepList,"~~")).
> {lib/PublishInfoMsg.i &InfoMsg = varRepName}
> END.
> IF (Num-entries(ttOrderHed.SalesRepList,"~~")>=3) THEN DO:
> varRepName = (Entry(3,ttOrderHed.SalesRepList,"~~")).
> {lib/PublishInfoMsg.i &InfoMsg = varRepName}
> END.
> END.
>
> --- In vantage@yahoogroups.com, "b_ordway" <cooner_55421@> wrote:
> >
> > I'm not sure if this will cover what you need but
> > I remembered this thread when I read your question:
> >
> > http://tech.groups.yahoo.com/group/vantage/message/103467
> >
> >
> > --- In vantage@yahoogroups.com, "pbparker" <scrumbus@> wrote:
> > >
> > > I had to go and mention it recently in a post here and sadly I've awoken the karma god.
> > >
> > > So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.
> > >
> > > I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.
> > >
> > > Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?
> > >
> > > I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.
> > >
> > > Anyone come up with anything else for a solution? (I really hate this field).
> > >
> >
>
9
We have only one rep on any sales order and I wrote a BPM that puts the rep into a UD field on order entry.
That way I've got it later without the "list" issue

[Non-text portions of this message have been removed]
10
Looks like someone created a BPM .p file in the Yahoo files to do exactly what I'm asking for, namely separate the sales reps out into a flattened UD table.

Only problem is it doesn't work in 9.05. Anyone willing to look at the file in the group files/BPMs section named CallBOFromABL.zip and see if they can see what's wrong with it offhand?

I modified his initial method to call the .p directly and hard coded his input variables to see if that was it, but the .p file throws a 4GL error trying to validate it, so it's in there no doubt.

--- In vantage@yahoogroups.com, "pbparker" <scrumbus@...> wrote:
>
> Yeah, it's the same way in E 9.05.607B and sadly, yes we're on Progress as well now. We're looking to convert to SQL, just can't get a price quoted from Epicor on what this will cost, so we'll just look at it later if/when we implement AFR.
>
> I tried already doing some linked servers but ran into a problem on my 64bit server with the 64bit ODBC drivers and SQL 2008 so I washed my hands on that for now.
>
> --- In vantage@yahoogroups.com, "Vic Drecchio" <vic.drecchio@> wrote:
> >
> > I’ve done it before using SQL Views with Selects with the UNION clause. And then join the View to the SalesRep table.
> >
> > Assuming you’re not a SQL user you’re pretty much screwed. (sorry)
> >
> > I understand why they may have architected that field like that, but still, it’s an un-joinable field to a master table (Sales Reps). The schema really needs to change; is it like that in E9, too?
> >
> > If you DO NOT have a lot of Sales Reps (<= baker’s dozen), I’ve used suppression and grouping tricks in Crystal before that get you close to what you want via a scheduled BAQ dump to CSV/XML. You just need a detail section and shared variables for each Rep to make it work. It’s a PAIN to develop and modify...
> >
> > Finally, if you are a lowly Progress customer like myself, one other “out-of-the-box” thought would be to install the free version of MS SQL Server, set up your live Progress DB as a linked server and do a “SELECT INTO...” and have static data cubes (tables) in MSSQL. You could update it nightly if that works for your company. Once you “suck in” the Order tables and SalesRep table into SQL, *then* you can create some magic and either: Create a sexy ASP web dashboard from the data in SQL and/or use Crystal Reports and hit SQL instead of the BAQ or Progress.
> >
> >
> >
> > Vic
> >
> >
> > From: pbparker
> > Sent: Friday, March 02, 2012 11:47 AM
> > To: vantage@yahoogroups.com
> > Subject: [Vantage] The dreaded SalesRepList field
> >
> >
> > I had to go and mention it recently in a post here and sadly I've awoken the karma god.
> >
> > So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.
> >
> > I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.
> >
> > Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?
> >
> > I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.
> >
> > Anyone come up with anything else for a solution? (I really hate this field).
> >
> >
> >
> >
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.1913 / Virus Database: 2114/4846 - Release Date: 03/02/12
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
11
I was the one who created this .p file.

It’s a good heads up to know if won’t work in 9.05 because we are upgrading soon.



Off the top of my head, the only reason I can think of that it would be bombing out is because Epicor changed the method calls I’m using. I would use the method directives screen to see if either the method names changed or the number or type of parameters changed.



Something else to try is to work with the tables directly as opposed to using the BO. I used the BO because I was practicing how to do it. From within the BPM you can use the CREATE and DELETE statements to add and remove rows.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of pbparker
Sent: Monday, March 05, 2012 2:59 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: The dreaded SalesRepList field





Looks like someone created a BPM .p file in the Yahoo files to do exactly what I'm asking for, namely separate the sales reps out into a flattened UD table.

Only problem is it doesn't work in 9.05. Anyone willing to look at the file in the group files/BPMs section named CallBOFromABL.zip and see if they can see what's wrong with it offhand?

I modified his initial method to call the .p directly and hard coded his input variables to see if that was it, but the .p file throws a 4GL error trying to validate it, so it's in there no doubt.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "pbparker" <scrumbus@...> wrote:
>
> Yeah, it's the same way in E 9.05.607B and sadly, yes we're on Progress as well now. We're looking to convert to SQL, just can't get a price quoted from Epicor on what this will cost, so we'll just look at it later if/when we implement AFR.
>
> I tried already doing some linked servers but ran into a problem on my 64bit server with the 64bit ODBC drivers and SQL 2008 so I washed my hands on that for now.
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Vic Drecchio" <vic.drecchio@> wrote:
> >
> > I’ve done it before using SQL Views with Selects with the UNION clause. And then join the View to the SalesRep table.
> >
> > Assuming you’re not a SQL user you’re pretty much screwed. (sorry)
> >
> > I understand why they may have architected that field like that, but still, it’s an un-joinable field to a master table (Sales Reps). The schema really needs to change; is it like that in E9, too?
> >
> > If you DO NOT have a lot of Sales Reps (<= baker’s dozen), I’ve used suppression and grouping tricks in Crystal before that get you close to what you want via a scheduled BAQ dump to CSV/XML. You just need a detail section and shared variables for each Rep to make it work. It’s a PAIN to develop and modify...
> >
> > Finally, if you are a lowly Progress customer like myself, one other “out-of-the-box” thought would be to install the free version of MS SQL Server, set up your live Progress DB as a linked server and do a “SELECT INTO...” and have static data cubes (tables) in MSSQL. You could update it nightly if that works for your company. Once you “suck in” the Order tables and SalesRep table into SQL, *then* you can create some magic and either: Create a sexy ASP web dashboard from the data in SQL and/or use Crystal Reports and hit SQL instead of the BAQ or Progress.
> >
> >
> >
> > Vic
> >
> >
> > From: pbparker
> > Sent: Friday, March 02, 2012 11:47 AM
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > Subject: [Vantage] The dreaded SalesRepList field
> >
> >
> > I had to go and mention it recently in a post here and sadly I've awoken the karma god.
> >
> > So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.
> >
> > I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.
> >
> > Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?
> >
> > I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.
> >
> > Anyone come up with anything else for a solution? (I really hate this field).
> >
> >
> >
> >
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.1913 / Virus Database: 2114/4846 - Release Date: 03/02/12
> >
> >
> > [Non-text portions of this message have been removed]
> >
>












Joe Rojas | Director of Information Technology | Mats Inc
dir: 781-573-0291 | cell: 781-408-9278 | fax: 781-232-5191
jrojas@... | www.matsinc.com Ask us about our clean, green and beautiful matting and flooring


This message is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company.


[Non-text portions of this message have been removed]
12
I verified all the method calls and none have changed either in name or input/output methods.

Wish it would kick back a bit clearer of an error than 4GL stop condition with nothing further.

--- In vantage@yahoogroups.com, Joe Rojas <jrojas@...> wrote:
>
> I was the one who created this .p file.
>
> It’s a good heads up to know if won’t work in 9.05 because we are upgrading soon.
>
>
>
> Off the top of my head, the only reason I can think of that it would be bombing out is because Epicor changed the method calls I’m using. I would use the method directives screen to see if either the method names changed or the number or type of parameters changed.
>
>
>
> Something else to try is to work with the tables directly as opposed to using the BO. I used the BO because I was practicing how to do it. From within the BPM you can use the CREATE and DELETE statements to add and remove rows.
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of pbparker
> Sent: Monday, March 05, 2012 2:59 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: The dreaded SalesRepList field
>
>
>
>
>
> Looks like someone created a BPM .p file in the Yahoo files to do exactly what I'm asking for, namely separate the sales reps out into a flattened UD table.
>
> Only problem is it doesn't work in 9.05. Anyone willing to look at the file in the group files/BPMs section named CallBOFromABL.zip and see if they can see what's wrong with it offhand?
>
> I modified his initial method to call the .p directly and hard coded his input variables to see if that was it, but the .p file throws a 4GL error trying to validate it, so it's in there no doubt.
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "pbparker" <scrumbus@> wrote:
> >
> > Yeah, it's the same way in E 9.05.607B and sadly, yes we're on Progress as well now. We're looking to convert to SQL, just can't get a price quoted from Epicor on what this will cost, so we'll just look at it later if/when we implement AFR.
> >
> > I tried already doing some linked servers but ran into a problem on my 64bit server with the 64bit ODBC drivers and SQL 2008 so I washed my hands on that for now.
> >
> > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Vic Drecchio" <vic.drecchio@> wrote:
> > >
> > > I’ve done it before using SQL Views with Selects with the UNION clause. And then join the View to the SalesRep table.
> > >
> > > Assuming you’re not a SQL user you’re pretty much screwed. (sorry)
> > >
> > > I understand why they may have architected that field like that, but still, it’s an un-joinable field to a master table (Sales Reps). The schema really needs to change; is it like that in E9, too?
> > >
> > > If you DO NOT have a lot of Sales Reps (<= baker’s dozen), I’ve used suppression and grouping tricks in Crystal before that get you close to what you want via a scheduled BAQ dump to CSV/XML. You just need a detail section and shared variables for each Rep to make it work. It’s a PAIN to develop and modify...
> > >
> > > Finally, if you are a lowly Progress customer like myself, one other â€Å"out-of-the-box” thought would be to install the free version of MS SQL Server, set up your live Progress DB as a linked server and do a â€Å"SELECT INTO...” and have static data cubes (tables) in MSSQL. You could update it nightly if that works for your company. Once you â€Å"suck in” the Order tables and SalesRep table into SQL, *then* you can create some magic and either: Create a sexy ASP web dashboard from the data in SQL and/or use Crystal Reports and hit SQL instead of the BAQ or Progress.
> > >
> > >
> > >
> > > Vic
> > >
> > >
> > > From: pbparker
> > > Sent: Friday, March 02, 2012 11:47 AM
> > > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > > Subject: [Vantage] The dreaded SalesRepList field
> > >
> > >
> > > I had to go and mention it recently in a post here and sadly I've awoken the karma god.
> > >
> > > So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.
> > >
> > > I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.
> > >
> > > Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?
> > >
> > > I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.
> > >
> > > Anyone come up with anything else for a solution? (I really hate this field).
> > >
> > >
> > >
> > >
> > > No virus found in this message.
> > > Checked by AVG - www.avg.com
> > > Version: 2012.0.1913 / Virus Database: 2114/4846 - Release Date: 03/02/12
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
>
>
>
>
>
>
>
>
>
>
>
>
> Joe Rojas | Director of Information Technology | Mats Inc
> dir: 781-573-0291 | cell: 781-408-9278 | fax: 781-232-5191
> jrojas@... | www.matsinc.com Ask us about our clean, green and beautiful matting and flooring
>
>
> This message is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company.
>
>
> [Non-text portions of this message have been removed]
>
13
Got it working.

Replace the line:
{Bpm/Bpm.i &OBJECT_NAME=UD14 }

with:
{bo/UD14/UD14_ds.i}

--- In vantage@yahoogroups.com, "pbparker" <scrumbus@...> wrote:
>
> I verified all the method calls and none have changed either in name or input/output methods.
>
> Wish it would kick back a bit clearer of an error than 4GL stop condition with nothing further.
>
> --- In vantage@yahoogroups.com, Joe Rojas <jrojas@> wrote:
> >
> > I was the one who created this .p file.
> >
> > It’s a good heads up to know if won’t work in 9.05 because we are upgrading soon.
> >
> >
> >
> > Off the top of my head, the only reason I can think of that it would be bombing out is because Epicor changed the method calls I’m using. I would use the method directives screen to see if either the method names changed or the number or type of parameters changed.
> >
> >
> >
> > Something else to try is to work with the tables directly as opposed to using the BO. I used the BO because I was practicing how to do it. From within the BPM you can use the CREATE and DELETE statements to add and remove rows.
> >
> >
> >
> > From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of pbparker
> > Sent: Monday, March 05, 2012 2:59 PM
> > To: vantage@yahoogroups.com
> > Subject: [Vantage] Re: The dreaded SalesRepList field
> >
> >
> >
> >
> >
> > Looks like someone created a BPM .p file in the Yahoo files to do exactly what I'm asking for, namely separate the sales reps out into a flattened UD table.
> >
> > Only problem is it doesn't work in 9.05. Anyone willing to look at the file in the group files/BPMs section named CallBOFromABL.zip and see if they can see what's wrong with it offhand?
> >
> > I modified his initial method to call the .p directly and hard coded his input variables to see if that was it, but the .p file throws a 4GL error trying to validate it, so it's in there no doubt.
> >
> > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "pbparker" <scrumbus@> wrote:
> > >
> > > Yeah, it's the same way in E 9.05.607B and sadly, yes we're on Progress as well now. We're looking to convert to SQL, just can't get a price quoted from Epicor on what this will cost, so we'll just look at it later if/when we implement AFR.
> > >
> > > I tried already doing some linked servers but ran into a problem on my 64bit server with the 64bit ODBC drivers and SQL 2008 so I washed my hands on that for now.
> > >
> > > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Vic Drecchio" <vic.drecchio@> wrote:
> > > >
> > > > I’ve done it before using SQL Views with Selects with the UNION clause. And then join the View to the SalesRep table.
> > > >
> > > > Assuming you’re not a SQL user you’re pretty much screwed. (sorry)
> > > >
> > > > I understand why they may have architected that field like that, but still, it’s an un-joinable field to a master table (Sales Reps). The schema really needs to change; is it like that in E9, too?
> > > >
> > > > If you DO NOT have a lot of Sales Reps (<= baker’s dozen), I’ve used suppression and grouping tricks in Crystal before that get you close to what you want via a scheduled BAQ dump to CSV/XML. You just need a detail section and shared variables for each Rep to make it work. It’s a PAIN to develop and modify...
> > > >
> > > > Finally, if you are a lowly Progress customer like myself, one other â€Å"out-of-the-box” thought would be to install the free version of MS SQL Server, set up your live Progress DB as a linked server and do a â€Å"SELECT INTO...” and have static data cubes (tables) in MSSQL. You could update it nightly if that works for your company. Once you â€Å"suck in” the Order tables and SalesRep table into SQL, *then* you can create some magic and either: Create a sexy ASP web dashboard from the data in SQL and/or use Crystal Reports and hit SQL instead of the BAQ or Progress.
> > > >
> > > >
> > > >
> > > > Vic
> > > >
> > > >
> > > > From: pbparker
> > > > Sent: Friday, March 02, 2012 11:47 AM
> > > > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > > > Subject: [Vantage] The dreaded SalesRepList field
> > > >
> > > >
> > > > I had to go and mention it recently in a post here and sadly I've awoken the karma god.
> > > >
> > > > So, now our sales manager wants a BAQ that's similar to what I did for a quote dashboard, namely show orders grouped by sales rep and industry.
> > > >
> > > > I can split the field out into calculated fields, but that doesn't get me my grouping really because rep order isn't good.
> > > >
> > > > Has anyone come up with a way to join the SalesRep table to OrderHed with that mess of a field?
> > > >
> > > > I'm thinking the only feasible solution is to create a BPM that more or less populates a UD table with the sales rep values split. This would then require me to get special license from Progress to be able to insert rows into tables from ABL.
> > > >
> > > > Anyone come up with anything else for a solution? (I really hate this field).
> > > >
> > > >
> > > >
> > > >
> > > > No virus found in this message.
> > > > Checked by AVG - www.avg.com
> > > > Version: 2012.0.1913 / Virus Database: 2114/4846 - Release Date: 03/02/12
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Joe Rojas | Director of Information Technology | Mats Inc
> > dir: 781-573-0291 | cell: 781-408-9278 | fax: 781-232-5191
> > jrojas@ | www.matsinc.com Ask us about our clean, green and beautiful matting and flooring
> >
> >
> > This message is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company.
> >
> >
> > [Non-text portions of this message have been removed]
> >
>