Progress Exports (was Sales and Commission Splitting)

Bingo! That's slick. Thanks, William. This whole export process will be much more reliable than my previous method. I like it.

Troy Funte
Liberty Electronics

----- Original Message -----
From: william
To: vantage@yahoogroups.com
Sent: Monday, November 26, 2001 5:28 PM
Subject: Re: [Vantage] Progress Exports (was Sales and Commission Splitting)


Sorry for delay in responding - been out of the office for a few days.

try adding

quit.

after the output close statement.

Troy Funte wrote:
>
> Thanks William. I'm off to a ... start, kind of.
>
> Using the code below, I got the export to work while using the Procedure Editor and F2 to run it. I'm having trouble getting it to run from an icon. When I run it from a .vtg icon (see Edit below), it will do the export first, then prompt me for a userID and Password. Then it finishes with the Procedure Editor screen. How can I get this to run "silently" without any need for additional intervention or screens?
> /* begin export program */
>
> output to c:\vntgwork\table.csv.
>
> for each PORel no-lock:
>
> export delimiter "," PONum POLine PORelNum ReceivedQty.
>
> end.
>
> output close.
>
> /* end of export */
>
> I found a way to run the procedure from a .vtg icon using the following.
>
> [Vantage Startup]
> Executable=E:\epic50\progress\bin\prowin32.exe
> Database=E:\epic50\Vantage\db\vantage
> IniFile=E:\epic50\Vantage\vantage.ini
> ParameterFile=E:\epic50\Vantage\db\Vantage.pf
> ConnectOptions=-N TCP -H localhost -S epic50
> TempDirectory=C:\vntgwork
> StartupProcedure=export.p
> OtherParameters=-rand 2 -q -basekey ini
>
> Troy Funte
> Liberty Electronics
>
> ----- Original Message -----
> From: william
> To: vantage@yahoogroups.com
> Sent: Thursday, November 22, 2001 12:05 AM
> Subject: Re: [Vantage] Progress Exports (was Sales and Commission Splitting)
>
> in its simplest form the following exports a table in comma separated
> format (.csv) that excel, and most other spread sheets, can handle
> easily.
>
> /* begin export program */
>
> output to c:\vntgwrk\table.csv.
>
> for each table no-lock:
> export table delimiter ",".
> end.
> output close.
>
> /* end of export */
>
> You, of course, would replace c:\vntgwrk\table.d with the appropriate
> path and table name. A file of name table.d is created in c:\vntgwrk
> which you can then open with excel.
>
> You can also add a "where" clause to select a subset of records, and
> "by" specifications to determine the sort order. If you only want some
> fields dumped, replace "table" in the example (which would dump all
> fields in the database field order) with a list of field names (field1
> field2 ....) to get only the fields you want in the sequence that you
> want. Note that character fields will be enclosed in double quotes
> (excel expects this), and fields with an unknown value will be dumped as
> ? which excel treats as a character field.
>
> If you really need to get fancy, excel has a coma object interface and
> you can open and populate a spread sheet from within a Progress Program,
> without creating the intermediate file.
>
> HTH.
> Troy Funte wrote:
> >
> > Todd mentions something I have been curious about. How easy is it to write 4GL programs to export tables/fields from Vantage to DBF or .xls files? Is there some sample code available that could be modified by a non-4GL programmer in a .p format (I don't have the 4GL source to compile). Also, is this something that can be run from a batch file nightly or via some auto-scheduler?
> >
> > (Currently I do nightly exports using Vantage Export Utility along with "Ground Control", a macro writing program that basically does the keystrokes for you. This is cumbersome at best, and some time unreliable if Vantage is running slow for some reason. I'd like a better, more reliable way - perhaps with some more programming features.)
> >
> > Thanks.
> >
> > Troy Funte
> > Liberty Electronics
> >
> > ----- Original Message -----
> > From: Todd Anderson
> > To: 'vantage@yahoogroups.com'
> > Sent: Wednesday, November 21, 2001 11:21 AM
> > Subject: RE: [Vantage] Sales and Commission Splitting
> >
> > Todd,
> >
> > Another way to go at this would be to write a few Progress reports that
> > export to a file. ( put "+" signs between fields - or some other odd duck
> > character )
> >
> > Then import the data into MS-Access as "+" delimited fields.
> >
> > >From there you can pretty much do whatever you want.
> >
> > Todd Anderson
> >
> > -----Original Message-----
> > From: Todd Caughey [mailto:caugheyt@...]
> > Sent: Wednesday, November 21, 2001 9:33 AM
> > To: 'vantage@yahoogroups.com'
> > Subject: [Vantage] Sales and Commission Splitting
> >
> > I need some advice for the best way to approach a really thorny issue for
> > us. We need to determine "sales by rep" and "sales by the state where the
> > rep is located" in situations where there are multiple reps. Usually two
> > but sometimes three. Because of the way rep splits are handled in Vantage,
> > via an array within the invoice detail, it is not possible to sort the split
> > sales $ by rep or state. All of the "info" is contained within the one
> > detail record. I have tried linking every which way and using multiple rep
> > tables aliases, etc... and it won't work. If there was a mechanism to pass
> > through all of the data and store it in a temporary file, sort it there and
> > then report on it it would be pretty easy. In my COBOL days this was very a
> > piece of cake.
> >
> > I am thinking of trying a Crystal subreport but have never used subreports.
> > Before I invest in the time to learn how I wanted to see if this appraoch
> > would even work for what I am trying to do. The company President has given
> > me a deadline to get split sales reports working by mid-December so I would
> > like to not take any more false routes.
> >
> > The situation is that we might have a customer in Ohio with a plant in
> > Missouri. Our sales rep for Ohio and the rep for Missouri split the
> > commissions (and thus credit for the sales $) 50/50. The invoice detail for
> > a sale has the split info burried within in it so if I try to sort on rep or
> > state the same invoice detail record can't be in two places in the same
> > report. What I need is for the report to show 50% of the sales $ under one
> > state or rep and the other 50% under the other state/rep. I can easily
> > write a RB report to split the sales and commissions by each invoice and
> > even show the state and rep code for each split. But taking this and
> > re-sorting it is the problem.
> >
> > So Yea or Nea... is Crystal subreports the way to handle hit? Or have RB
> > create a CSV files and use Crystal or Excel to re-sort it? Are Crystal
> > subreports hard to learn how to do? Any subreport "gotchas" waiting to
> > spring up and bite a newbie?
> >
> > Thanks,
> > -Todd Caughey
> > Harvey Vogel Mfg. Co.
> >
> > [Non-text portions of this message have been removed]
> >
> > Yahoo! Groups Sponsor
> >
> > <http://rd.yahoo.com/M=212180.1701098.3252983.1269402/D=egroupweb/S=17050071
> > 83:HM/A=812075/R=0/*http://www.verisign.com/cgi-bin/go.cgi?a=b17834980005700
> > 0>
> >
> > <http://us.adserver.yahoo.com/l?M=212180.1701098.3252983.1269402/D=egroupmai
> > l/S=1705007183:HM/A=812075/rand=923152006>
> >
> > 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]
> >
> > Yahoo! Groups Sponsor
> >
> >
> >
> > 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 the Yahoo! Terms of Service.
> >
> > [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/
>
> --
> /* ================================================================*/
>
> Specialists in Progress Databases and Software since 1986
>
> William E. Colls william@...
> PROComputer Systems Tel 613 591 0079
> 67 Willow Glen Dr. Fax 613 591 3924
> Kanata Ontario Canada www.procomsys.com
> K2M 1T1
>
> Yahoo! Groups Sponsor
>
>
>
> 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 the Yahoo! Terms of Service.
>
> [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/

--
/* ================================================================*/

Specialists in Progress Databases and Software since 1986

William E. Colls william@...
PROComputer Systems Tel 613 591 0079
67 Willow Glen Dr. Fax 613 591 3924
Kanata Ontario Canada www.procomsys.com
K2M 1T1

Yahoo! Groups Sponsor



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 the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
Todd mentions something I have been curious about. How easy is it to write 4GL programs to export tables/fields from Vantage to DBF or .xls files? Is there some sample code available that could be modified by a non-4GL programmer in a .p format (I don't have the 4GL source to compile). Also, is this something that can be run from a batch file nightly or via some auto-scheduler?

(Currently I do nightly exports using Vantage Export Utility along with "Ground Control", a macro writing program that basically does the keystrokes for you. This is cumbersome at best, and some time unreliable if Vantage is running slow for some reason. I'd like a better, more reliable way - perhaps with some more programming features.)

Thanks.

Troy Funte
Liberty Electronics

----- Original Message -----
From: Todd Anderson
To: 'vantage@yahoogroups.com'
Sent: Wednesday, November 21, 2001 11:21 AM
Subject: RE: [Vantage] Sales and Commission Splitting


Todd,

Another way to go at this would be to write a few Progress reports that
export to a file. ( put "+" signs between fields - or some other odd duck
character )

Then import the data into MS-Access as "+" delimited fields.

>From there you can pretty much do whatever you want.

Todd Anderson

-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Wednesday, November 21, 2001 9:33 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Sales and Commission Splitting


I need some advice for the best way to approach a really thorny issue for
us. We need to determine "sales by rep" and "sales by the state where the
rep is located" in situations where there are multiple reps. Usually two
but sometimes three. Because of the way rep splits are handled in Vantage,
via an array within the invoice detail, it is not possible to sort the split
sales $ by rep or state. All of the "info" is contained within the one
detail record. I have tried linking every which way and using multiple rep
tables aliases, etc... and it won't work. If there was a mechanism to pass
through all of the data and store it in a temporary file, sort it there and
then report on it it would be pretty easy. In my COBOL days this was very a
piece of cake.

I am thinking of trying a Crystal subreport but have never used subreports.
Before I invest in the time to learn how I wanted to see if this appraoch
would even work for what I am trying to do. The company President has given
me a deadline to get split sales reports working by mid-December so I would
like to not take any more false routes.

The situation is that we might have a customer in Ohio with a plant in
Missouri. Our sales rep for Ohio and the rep for Missouri split the
commissions (and thus credit for the sales $) 50/50. The invoice detail for
a sale has the split info burried within in it so if I try to sort on rep or
state the same invoice detail record can't be in two places in the same
report. What I need is for the report to show 50% of the sales $ under one
state or rep and the other 50% under the other state/rep. I can easily
write a RB report to split the sales and commissions by each invoice and
even show the state and rep code for each split. But taking this and
re-sorting it is the problem.

So Yea or Nea... is Crystal subreports the way to handle hit? Or have RB
create a CSV files and use Crystal or Excel to re-sort it? Are Crystal
subreports hard to learn how to do? Any subreport "gotchas" waiting to
spring up and bite a newbie?

Thanks,
-Todd Caughey
Harvey Vogel Mfg. Co.






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



Yahoo! Groups Sponsor

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

<http://us.adserver.yahoo.com/l?M=212180.1701098.3252983.1269402/D=egroupmai
l/S=1705007183:HM/A=812075/rand=923152006>

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]


Yahoo! Groups Sponsor



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 the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
in its simplest form the following exports a table in comma separated
format (.csv) that excel, and most other spread sheets, can handle
easily.

/* begin export program */

output to c:\vntgwrk\table.csv.

for each table no-lock:
export table delimiter ",".
end.
output close.

/* end of export */

You, of course, would replace c:\vntgwrk\table.d with the appropriate
path and table name. A file of name table.d is created in c:\vntgwrk
which you can then open with excel.

You can also add a "where" clause to select a subset of records, and
"by" specifications to determine the sort order. If you only want some
fields dumped, replace "table" in the example (which would dump all
fields in the database field order) with a list of field names (field1
field2 ....) to get only the fields you want in the sequence that you
want. Note that character fields will be enclosed in double quotes
(excel expects this), and fields with an unknown value will be dumped as
? which excel treats as a character field.

If you really need to get fancy, excel has a coma object interface and
you can open and populate a spread sheet from within a Progress Program,
without creating the intermediate file.

HTH.
Troy Funte wrote:
>
> Todd mentions something I have been curious about. How easy is it to write 4GL programs to export tables/fields from Vantage to DBF or .xls files? Is there some sample code available that could be modified by a non-4GL programmer in a .p format (I don't have the 4GL source to compile). Also, is this something that can be run from a batch file nightly or via some auto-scheduler?
>
> (Currently I do nightly exports using Vantage Export Utility along with "Ground Control", a macro writing program that basically does the keystrokes for you. This is cumbersome at best, and some time unreliable if Vantage is running slow for some reason. I'd like a better, more reliable way - perhaps with some more programming features.)
>
> Thanks.
>
> Troy Funte
> Liberty Electronics
>
> ----- Original Message -----
> From: Todd Anderson
> To: 'vantage@yahoogroups.com'
> Sent: Wednesday, November 21, 2001 11:21 AM
> Subject: RE: [Vantage] Sales and Commission Splitting
>
> Todd,
>
> Another way to go at this would be to write a few Progress reports that
> export to a file. ( put "+" signs between fields - or some other odd duck
> character )
>
> Then import the data into MS-Access as "+" delimited fields.
>
> >From there you can pretty much do whatever you want.
>
> Todd Anderson
>
> -----Original Message-----
> From: Todd Caughey [mailto:caugheyt@...]
> Sent: Wednesday, November 21, 2001 9:33 AM
> To: 'vantage@yahoogroups.com'
> Subject: [Vantage] Sales and Commission Splitting
>
> I need some advice for the best way to approach a really thorny issue for
> us. We need to determine "sales by rep" and "sales by the state where the
> rep is located" in situations where there are multiple reps. Usually two
> but sometimes three. Because of the way rep splits are handled in Vantage,
> via an array within the invoice detail, it is not possible to sort the split
> sales $ by rep or state. All of the "info" is contained within the one
> detail record. I have tried linking every which way and using multiple rep
> tables aliases, etc... and it won't work. If there was a mechanism to pass
> through all of the data and store it in a temporary file, sort it there and
> then report on it it would be pretty easy. In my COBOL days this was very a
> piece of cake.
>
> I am thinking of trying a Crystal subreport but have never used subreports.
> Before I invest in the time to learn how I wanted to see if this appraoch
> would even work for what I am trying to do. The company President has given
> me a deadline to get split sales reports working by mid-December so I would
> like to not take any more false routes.
>
> The situation is that we might have a customer in Ohio with a plant in
> Missouri. Our sales rep for Ohio and the rep for Missouri split the
> commissions (and thus credit for the sales $) 50/50. The invoice detail for
> a sale has the split info burried within in it so if I try to sort on rep or
> state the same invoice detail record can't be in two places in the same
> report. What I need is for the report to show 50% of the sales $ under one
> state or rep and the other 50% under the other state/rep. I can easily
> write a RB report to split the sales and commissions by each invoice and
> even show the state and rep code for each split. But taking this and
> re-sorting it is the problem.
>
> So Yea or Nea... is Crystal subreports the way to handle hit? Or have RB
> create a CSV files and use Crystal or Excel to re-sort it? Are Crystal
> subreports hard to learn how to do? Any subreport "gotchas" waiting to
> spring up and bite a newbie?
>
> Thanks,
> -Todd Caughey
> Harvey Vogel Mfg. Co.
>
> [Non-text portions of this message have been removed]
>
> Yahoo! Groups Sponsor
>
> <http://rd.yahoo.com/M=212180.1701098.3252983.1269402/D=egroupweb/S=17050071
> 83:HM/A=812075/R=0/*http://www.verisign.com/cgi-bin/go.cgi?a=b17834980005700
> 0>
>
> <http://us.adserver.yahoo.com/l?M=212180.1701098.3252983.1269402/D=egroupmai
> l/S=1705007183:HM/A=812075/rand=923152006>
>
> 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]
>
> Yahoo! Groups Sponsor
>
>
>
> 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 the Yahoo! Terms of Service.
>
> [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/

--
/* ================================================================*/

Specialists in Progress Databases and Software since 1986

William E. Colls william@...
PROComputer Systems Tel 613 591 0079
67 Willow Glen Dr. Fax 613 591 3924
Kanata Ontario Canada www.procomsys.com
K2M 1T1
Thanks William. I'm off to a ... start, kind of.

Using the code below, I got the export to work while using the Procedure Editor and F2 to run it. I'm having trouble getting it to run from an icon. When I run it from a .vtg icon (see Edit below), it will do the export first, then prompt me for a userID and Password. Then it finishes with the Procedure Editor screen. How can I get this to run "silently" without any need for additional intervention or screens?
/* begin export program */

output to c:\vntgwork\table.csv.

for each PORel no-lock:

export delimiter "," PONum POLine PORelNum ReceivedQty.

end.

output close.

/* end of export */

I found a way to run the procedure from a .vtg icon using the following.

[Vantage Startup]
Executable=E:\epic50\progress\bin\prowin32.exe
Database=E:\epic50\Vantage\db\vantage
IniFile=E:\epic50\Vantage\vantage.ini
ParameterFile=E:\epic50\Vantage\db\Vantage.pf
ConnectOptions=-N TCP -H localhost -S epic50
TempDirectory=C:\vntgwork
StartupProcedure=export.p
OtherParameters=-rand 2 -q -basekey ini

Troy Funte
Liberty Electronics

----- Original Message -----
From: william
To: vantage@yahoogroups.com
Sent: Thursday, November 22, 2001 12:05 AM
Subject: Re: [Vantage] Progress Exports (was Sales and Commission Splitting)


in its simplest form the following exports a table in comma separated
format (.csv) that excel, and most other spread sheets, can handle
easily.

/* begin export program */

output to c:\vntgwrk\table.csv.

for each table no-lock:
export table delimiter ",".
end.
output close.

/* end of export */

You, of course, would replace c:\vntgwrk\table.d with the appropriate
path and table name. A file of name table.d is created in c:\vntgwrk
which you can then open with excel.

You can also add a "where" clause to select a subset of records, and
"by" specifications to determine the sort order. If you only want some
fields dumped, replace "table" in the example (which would dump all
fields in the database field order) with a list of field names (field1
field2 ....) to get only the fields you want in the sequence that you
want. Note that character fields will be enclosed in double quotes
(excel expects this), and fields with an unknown value will be dumped as
? which excel treats as a character field.

If you really need to get fancy, excel has a coma object interface and
you can open and populate a spread sheet from within a Progress Program,
without creating the intermediate file.

HTH.
Troy Funte wrote:
>
> Todd mentions something I have been curious about. How easy is it to write 4GL programs to export tables/fields from Vantage to DBF or .xls files? Is there some sample code available that could be modified by a non-4GL programmer in a .p format (I don't have the 4GL source to compile). Also, is this something that can be run from a batch file nightly or via some auto-scheduler?
>
> (Currently I do nightly exports using Vantage Export Utility along with "Ground Control", a macro writing program that basically does the keystrokes for you. This is cumbersome at best, and some time unreliable if Vantage is running slow for some reason. I'd like a better, more reliable way - perhaps with some more programming features.)
>
> Thanks.
>
> Troy Funte
> Liberty Electronics
>
> ----- Original Message -----
> From: Todd Anderson
> To: 'vantage@yahoogroups.com'
> Sent: Wednesday, November 21, 2001 11:21 AM
> Subject: RE: [Vantage] Sales and Commission Splitting
>
> Todd,
>
> Another way to go at this would be to write a few Progress reports that
> export to a file. ( put "+" signs between fields - or some other odd duck
> character )
>
> Then import the data into MS-Access as "+" delimited fields.
>
> >From there you can pretty much do whatever you want.
>
> Todd Anderson
>
> -----Original Message-----
> From: Todd Caughey [mailto:caugheyt@...]
> Sent: Wednesday, November 21, 2001 9:33 AM
> To: 'vantage@yahoogroups.com'
> Subject: [Vantage] Sales and Commission Splitting
>
> I need some advice for the best way to approach a really thorny issue for
> us. We need to determine "sales by rep" and "sales by the state where the
> rep is located" in situations where there are multiple reps. Usually two
> but sometimes three. Because of the way rep splits are handled in Vantage,
> via an array within the invoice detail, it is not possible to sort the split
> sales $ by rep or state. All of the "info" is contained within the one
> detail record. I have tried linking every which way and using multiple rep
> tables aliases, etc... and it won't work. If there was a mechanism to pass
> through all of the data and store it in a temporary file, sort it there and
> then report on it it would be pretty easy. In my COBOL days this was very a
> piece of cake.
>
> I am thinking of trying a Crystal subreport but have never used subreports.
> Before I invest in the time to learn how I wanted to see if this appraoch
> would even work for what I am trying to do. The company President has given
> me a deadline to get split sales reports working by mid-December so I would
> like to not take any more false routes.
>
> The situation is that we might have a customer in Ohio with a plant in
> Missouri. Our sales rep for Ohio and the rep for Missouri split the
> commissions (and thus credit for the sales $) 50/50. The invoice detail for
> a sale has the split info burried within in it so if I try to sort on rep or
> state the same invoice detail record can't be in two places in the same
> report. What I need is for the report to show 50% of the sales $ under one
> state or rep and the other 50% under the other state/rep. I can easily
> write a RB report to split the sales and commissions by each invoice and
> even show the state and rep code for each split. But taking this and
> re-sorting it is the problem.
>
> So Yea or Nea... is Crystal subreports the way to handle hit? Or have RB
> create a CSV files and use Crystal or Excel to re-sort it? Are Crystal
> subreports hard to learn how to do? Any subreport "gotchas" waiting to
> spring up and bite a newbie?
>
> Thanks,
> -Todd Caughey
> Harvey Vogel Mfg. Co.
>
> [Non-text portions of this message have been removed]
>
> Yahoo! Groups Sponsor
>
> <http://rd.yahoo.com/M=212180.1701098.3252983.1269402/D=egroupweb/S=17050071
> 83:HM/A=812075/R=0/*http://www.verisign.com/cgi-bin/go.cgi?a=b17834980005700
> 0>
>
> <http://us.adserver.yahoo.com/l?M=212180.1701098.3252983.1269402/D=egroupmai
> l/S=1705007183:HM/A=812075/rand=923152006>
>
> 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]
>
> Yahoo! Groups Sponsor
>
>
>
> 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 the Yahoo! Terms of Service.
>
> [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/

--
/* ================================================================*/

Specialists in Progress Databases and Software since 1986

William E. Colls william@...
PROComputer Systems Tel 613 591 0079
67 Willow Glen Dr. Fax 613 591 3924
Kanata Ontario Canada www.procomsys.com
K2M 1T1

Yahoo! Groups Sponsor



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 the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
Sorry for delay in responding - been out of the office for a few days.

try adding

quit.

after the output close statement.

Troy Funte wrote:
>
> Thanks William. I'm off to a ... start, kind of.
>
> Using the code below, I got the export to work while using the Procedure Editor and F2 to run it. I'm having trouble getting it to run from an icon. When I run it from a .vtg icon (see Edit below), it will do the export first, then prompt me for a userID and Password. Then it finishes with the Procedure Editor screen. How can I get this to run "silently" without any need for additional intervention or screens?
> /* begin export program */
>
> output to c:\vntgwork\table.csv.
>
> for each PORel no-lock:
>
> export delimiter "," PONum POLine PORelNum ReceivedQty.
>
> end.
>
> output close.
>
> /* end of export */
>
> I found a way to run the procedure from a .vtg icon using the following.
>
> [Vantage Startup]
> Executable=E:\epic50\progress\bin\prowin32.exe
> Database=E:\epic50\Vantage\db\vantage
> IniFile=E:\epic50\Vantage\vantage.ini
> ParameterFile=E:\epic50\Vantage\db\Vantage.pf
> ConnectOptions=-N TCP -H localhost -S epic50
> TempDirectory=C:\vntgwork
> StartupProcedure=export.p
> OtherParameters=-rand 2 -q -basekey ini
>
> Troy Funte
> Liberty Electronics
>
> ----- Original Message -----
> From: william
> To: vantage@yahoogroups.com
> Sent: Thursday, November 22, 2001 12:05 AM
> Subject: Re: [Vantage] Progress Exports (was Sales and Commission Splitting)
>
> in its simplest form the following exports a table in comma separated
> format (.csv) that excel, and most other spread sheets, can handle
> easily.
>
> /* begin export program */
>
> output to c:\vntgwrk\table.csv.
>
> for each table no-lock:
> export table delimiter ",".
> end.
> output close.
>
> /* end of export */
>
> You, of course, would replace c:\vntgwrk\table.d with the appropriate
> path and table name. A file of name table.d is created in c:\vntgwrk
> which you can then open with excel.
>
> You can also add a "where" clause to select a subset of records, and
> "by" specifications to determine the sort order. If you only want some
> fields dumped, replace "table" in the example (which would dump all
> fields in the database field order) with a list of field names (field1
> field2 ....) to get only the fields you want in the sequence that you
> want. Note that character fields will be enclosed in double quotes
> (excel expects this), and fields with an unknown value will be dumped as
> ? which excel treats as a character field.
>
> If you really need to get fancy, excel has a coma object interface and
> you can open and populate a spread sheet from within a Progress Program,
> without creating the intermediate file.
>
> HTH.
> Troy Funte wrote:
> >
> > Todd mentions something I have been curious about. How easy is it to write 4GL programs to export tables/fields from Vantage to DBF or .xls files? Is there some sample code available that could be modified by a non-4GL programmer in a .p format (I don't have the 4GL source to compile). Also, is this something that can be run from a batch file nightly or via some auto-scheduler?
> >
> > (Currently I do nightly exports using Vantage Export Utility along with "Ground Control", a macro writing program that basically does the keystrokes for you. This is cumbersome at best, and some time unreliable if Vantage is running slow for some reason. I'd like a better, more reliable way - perhaps with some more programming features.)
> >
> > Thanks.
> >
> > Troy Funte
> > Liberty Electronics
> >
> > ----- Original Message -----
> > From: Todd Anderson
> > To: 'vantage@yahoogroups.com'
> > Sent: Wednesday, November 21, 2001 11:21 AM
> > Subject: RE: [Vantage] Sales and Commission Splitting
> >
> > Todd,
> >
> > Another way to go at this would be to write a few Progress reports that
> > export to a file. ( put "+" signs between fields - or some other odd duck
> > character )
> >
> > Then import the data into MS-Access as "+" delimited fields.
> >
> > >From there you can pretty much do whatever you want.
> >
> > Todd Anderson
> >
> > -----Original Message-----
> > From: Todd Caughey [mailto:caugheyt@...]
> > Sent: Wednesday, November 21, 2001 9:33 AM
> > To: 'vantage@yahoogroups.com'
> > Subject: [Vantage] Sales and Commission Splitting
> >
> > I need some advice for the best way to approach a really thorny issue for
> > us. We need to determine "sales by rep" and "sales by the state where the
> > rep is located" in situations where there are multiple reps. Usually two
> > but sometimes three. Because of the way rep splits are handled in Vantage,
> > via an array within the invoice detail, it is not possible to sort the split
> > sales $ by rep or state. All of the "info" is contained within the one
> > detail record. I have tried linking every which way and using multiple rep
> > tables aliases, etc... and it won't work. If there was a mechanism to pass
> > through all of the data and store it in a temporary file, sort it there and
> > then report on it it would be pretty easy. In my COBOL days this was very a
> > piece of cake.
> >
> > I am thinking of trying a Crystal subreport but have never used subreports.
> > Before I invest in the time to learn how I wanted to see if this appraoch
> > would even work for what I am trying to do. The company President has given
> > me a deadline to get split sales reports working by mid-December so I would
> > like to not take any more false routes.
> >
> > The situation is that we might have a customer in Ohio with a plant in
> > Missouri. Our sales rep for Ohio and the rep for Missouri split the
> > commissions (and thus credit for the sales $) 50/50. The invoice detail for
> > a sale has the split info burried within in it so if I try to sort on rep or
> > state the same invoice detail record can't be in two places in the same
> > report. What I need is for the report to show 50% of the sales $ under one
> > state or rep and the other 50% under the other state/rep. I can easily
> > write a RB report to split the sales and commissions by each invoice and
> > even show the state and rep code for each split. But taking this and
> > re-sorting it is the problem.
> >
> > So Yea or Nea... is Crystal subreports the way to handle hit? Or have RB
> > create a CSV files and use Crystal or Excel to re-sort it? Are Crystal
> > subreports hard to learn how to do? Any subreport "gotchas" waiting to
> > spring up and bite a newbie?
> >
> > Thanks,
> > -Todd Caughey
> > Harvey Vogel Mfg. Co.
> >
> > [Non-text portions of this message have been removed]
> >
> > Yahoo! Groups Sponsor
> >
> > <http://rd.yahoo.com/M=212180.1701098.3252983.1269402/D=egroupweb/S=17050071
> > 83:HM/A=812075/R=0/*http://www.verisign.com/cgi-bin/go.cgi?a=b17834980005700
> > 0>
> >
> > <http://us.adserver.yahoo.com/l?M=212180.1701098.3252983.1269402/D=egroupmai
> > l/S=1705007183:HM/A=812075/rand=923152006>
> >
> > 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]
> >
> > Yahoo! Groups Sponsor
> >
> >
> >
> > 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 the Yahoo! Terms of Service.
> >
> > [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/
>
> --
> /* ================================================================*/
>
> Specialists in Progress Databases and Software since 1986
>
> William E. Colls william@...
> PROComputer Systems Tel 613 591 0079
> 67 Willow Glen Dr. Fax 613 591 3924
> Kanata Ontario Canada www.procomsys.com
> K2M 1T1
>
> Yahoo! Groups Sponsor
>
>
>
> 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 the Yahoo! Terms of Service.
>
> [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/

--
/* ================================================================*/

Specialists in Progress Databases and Software since 1986

William E. Colls william@...
PROComputer Systems Tel 613 591 0079
67 Willow Glen Dr. Fax 613 591 3924
Kanata Ontario Canada www.procomsys.com
K2M 1T1