I recently had a very good discussion with the Director of Operations at
Epicor, and he highly recommended using Progress 4GL for our more complex
export and reporting needs, and for integration with our other applications.
In 4GL, its just a matter of learning the language, and you can pretty much
do everything Vantage itself does. However, out of the box, the Progress
license does not give you the ability to write code that would modify the
database. The execution of Progress 4GL queries can be strictly controlled
through vantage menu security and NT permissions.
I don't believe 4GL programming is for everyone, but for some of you who are
creating complex reports that are exposing the limitations of reportbuilder,
and for anyone who has experience with Visual Basic / C++ programming,
Progress 4GL is a viable, safe tool for creating reports, or at least
exports for crystal, or a visual basic program to use. I've already
downloaded some free source code for exporting progress data to DBF files.
In our case, 4GL eliminates the need for an ODBC connection. When we need a
Crystal report to access more fields than are available in one of the
standard DBF exports, we can write launch 4GL code from a VantageBasic
window that will export the fields we need to a DBF, that Crystal can then
access.
If security isn't as big an issue, ODBC is by far the most convenient way to
go. With an ODBC connection, you can pull Vantage records straight from
Excell, with no programming at all. However, the only true secure solution
using ODBC is a solution where the computer with the odbc connection is
essentially filtering the database information that it gives to users. This
is true for the Crystal Web system, and for my original idea of creating a
standalone workstation that exports data on a timed or on-demand basis using
visual basic program with an ODBC connection.
However with 4GL, there is no need for a web server, or any sort of
macro-automated exports on a standalone workstation. You simply tell the
database what you want from it, and you get it. 4GL can also automate
Office and CAD applications, just as Visual Basic can. All the mass
printing and file auditing I wanted to do with visual basic/ODBC, will be
done with 4GL.
Well, I don't know if this knowledge helps anyone else, but learning that
4GL can do just about everything I wanted to do through visual basic, and do
so in a safe and secure manner, without requiring ODBC, convinced me that it
is the solution for us.
$.02 - Thad
>The following is my conversation with Doug Williams, Epicor's Director of
Development.
-----Original Message-----
From: Doug Williams [mailto:
dwilliams@...]
Sent: Tuesday, June 26, 2001 2:18 PM
To: 'Thad Jacobs'
Subject: RE: Indented BOM RB Report V3 / P8.3A
In 5.00 we started offering a SQL Server database option. The Vantage code
is still written in the Progress 4GL - it is just compiled with some
different options to work against SQL Server. There is only one version of
Vantage. There are no plans to drop support for the Progress database.
From the 4GL you can reference COM objects and OLE Automation servers, etc.
I don't know the interface that SolidWorks uses, but I would imagine that it
would be along those lines if you are able to hook into it from VB. As for
integrating into Vantage, obviously a 4GL program will be a lot easier and
appear more seamless, etc. It can be added directly onto the menu, for
example. To create/maintain complicated screens (possibly with ActiveX
controls) you would need to purchase a copy of ProVision. You can still
maintain absolute safety, however, if you always compile your code for
deployment from a box with only a Query license installed - you would be
guaranteed that nothing you wrote would be able to modify anything in the
database. If you always keep your programs in a separate directory
structure you won't have to worry much about patches. Nothing, however,
really saves you when we make wholesale changes to the database structure
for new releases, etc.
That being said, we do continue to look at new and emerging technologies so
it may be possible that in the future things will be different. My goal/job
is to use the best tool(s) for enterprise-level application development that
I can find. So far that has been Progress.
Doug Williams
Director of Development
eManufacturing - Vantage
Epicor Software Corporation
-----Original Message-----
From: Thad Jacobs [mailto:
tjacobs@...]
Sent: Tuesday, June 26, 2001 1:59 PM
To: 'Doug Williams'
Subject: RE: Indented BOM RB Report V3 / P8.3A
Doug,
I thought I may have heard from someone in support that the next version of
eManufacturing would be SQL server based, not Progress Based. (I may have
misunderstood, but that's the impression I got.)
If that is the case, then it seems 4GL code we write now may have to be
rewritten in a different language upon a future release of Vantage.
If that isn't the case, 4GL looks likes it could be the most efficient
choice for our BOM report, or at least a 4GL export for Crystal to read.
How does 4GL do as far as automating other applications? Automating Office
applications is fairly simple in visual basic. We would like to take all
the part numbers for a particular BOM, and automate SolidWorks to print the
corresponding files. I've already written code to do this in Excel VBA,
using a list of parts that I extracted from the text file created in
c:\vntgwrk when I forced a print error while printing the progress BOM
report.
If this could be done in 4GL, that would be awesome.
I think from a security standpoint, we'd like to see the need for an ODBC
connection eliminated. However, if there are a great deal of internal
changes anticipated in future releases that would affect the implementation
of 4GL programs, we may want to bite the bullet now and go with ODBC/Visual
Basic/Crystal Web Enterprise.
All in all, Vantage is a very useful product for our company, and it suits
most of our needs. There are just tasks that we'd like to automate, such as
moving and printing files for a particular JOB. Some of our machines have
over a thousand parts, and the procedure of printing and indented BOM,
grabbing the old highlighter, and going down the list of partnumbers and
printing the files seems somewhat inefficient.
I know Visual Basic and C++ already. I've taken the advanced Crystal class,
and am becoming quite familiar with report builder. I'm beginning to
research SQL ODBC.
I like Crystal over Report Builder, because it is just plain more powerful.
For some of our reports, however, such as the 5 level indented BOM, it looks
like we're going to need the ability to write a query from scratch, in a
core programming language.
I know 4GL would integrate most tightly with the database. However, we
don't want to integrate so tightly that we're having to edit or rewrite a
bunch of source code when it comes time to upgrade. We also don't want to
step on the toes of the installation programs for new versions. We know
that table links and field names are going to change at times, and we can
live with that. However programming structure issues and whether or not 4GL
is going to be supported in future releases of vantage is my main concern.
So the question, is, in your opinion,
With our needs for custom automation, and in anticipating future upgrades,
would it be better to go with a programming environment external to Vantage
that would read data through an ODBC connection, or would 4GL be the
language of choice?
Thank you very much for your time,
Thaddeus Jacobs
Assistant LAN Technologist / Vantage Support
Kinematic Automation, Inc
mailto:
tjacobs@...
-----Original Message-----
From: Doug Williams [mailto:
dwilliams@...]
Sent: Tuesday, June 26, 2001 11:59 AM
To: 'Thad Jacobs'
Subject: RE: Indented BOM RB Report V3 / P8.3A
Just a comment on the 4GL coding topic: With a Progress Query license (the
license normally shipped with Vantage) you cannot compile programs that
update the database. Since you cannot write/run any programs that change
any data you don't have to worry about damaging anything and it being
billable support, etc. It is a very nice safety feature (and doesn't cost
anything extra, either). The Progress 4GL is also fairly easy to learn
(compared to other languages that I have worked with). Progress offers
several beginning/intermediate classes on 4GL development throughout the
country/year, but depending upon your experience you might be able to pick
up enough reading the manuals.
Doug Williams
Director of Development
eManufacturing - Vantage
Epicor Software Corporation
-----Original Message-----
From: Thad Jacobs [mailto:
tjacobs@...]
Sent: Tuesday, June 26, 2001 11:30 AM
To: 'Doug Williams'
Subject: RE: Indented BOM RB Report V3 / P8.3A
Thanks Doug. I'm not suprised Standard Development stays away from
reportbuilder....
Vantage 3 does have the ability to export to DBF, but access to the export
utility can't be restricted to certain exports. The user with access to
export the BOM tables has the ability to export the Job, Labor and
everything else tables. We need the ability for Document Control to enter a
BOM, then print the indented BOM right away, without having to wait for a
manager to hit the export button.
However, we probably will use one workstation with an ODBC connection to
automatically export various DBF files to NT secured directories on the
network, and have the Visual Basic BOM program running against the export,
so each individual user doesn't need ODBC connection. This is inefficient,
as it creates concurent copies of the database, but it puts the burden of
table security on NT, which is far easier to manage than Progress.
I was going to explore writing my own Progress BOM report, but we are
staying away from 4GL, as we don't want to do anything to the Database that
would make all future related support billable.....
Well, have a nice day, and thanks for your help.
Thaddeus
-----Original Message-----
From: Doug Williams [mailto:
dwilliams@...]
Sent: Tuesday, June 26, 2001 11:11 AM
To: 'Thad Jacobs'
Subject: RE: Indented BOM RB Report V3 / P8.3A
Unfortunately, I am not a Report Builder expert so I really can't answer
your question. The standard development group has not used the Report
Builder product much because we write our reports in either Progress or
Crystal Reports. The report that you are trying to create is certainly not
an easy one.
I'd like to suggest using the Export Utility to dump out the data into a
.dbf, but I don't think that this feature is available until 4.00.
I would suggest contacting either your account manager or the phone support
group to see if they can get you some further information on building this
report.
Sorry that I couldn't be of more assistance,
Doug Williams
Director of Development
eManufacturing - Vantage
Epicor Software Corporation
-----Original Message-----
From: Thad Jacobs [mailto:
tjacobs@...]
Sent: Tuesday, June 26, 2001 11:05 AM
To: '
dwilliams@...'
Subject: Indented BOM RB Report V3 / P8.3A
Doug (or any ReportBuilder expert),
We are running Vantage 3, Progress 8.3A, No ODBC.
I am trying to construct an indented BOM Report Builder Report to fit our
specific requirements. Basically, what I am doing is creating mulitiple
aliases of the PartMtl table, by linking PartMtl.MtlPartnum to
L2PartMtl.Partnum, and L2PartMtl.MtlPartNum to L3PartMtl.PartNum..... all
the way to level 5, which is the deepest we need to go as of now. Within
each level of PartMtl, I am linking to an alias of PartRev and Part, to pull
the description, RevShortDescription, and ECO.
I realize this creates a bunch of duplicate entries, but I am only
displaying the headers, and if I can find a way to sort 5 levels of MtlSeq,
and by Descending PartRev.EffectiveDate within each level of Mtlseq, I
believe I'm good to go.
However, I know I am very much taxing the abilities of RB, and by doing all
these embedded joins, I am creating one ineficcient report. I already
exceeded my maximum statement length by joining 8 levels of parmtl, part,
and partev.
Am I anywhere close to being on track? I'm grouping by MtlSeq at the
various levels. I'm currently trying to sort the thing so the PartMtl
records with the latest rev appear first, without much success.
My supervisor, Ford Garrat, mentioned that Mark Pladson said that an
Indented BOM report is possible, but that we'd have to attend the advanced
RB class. As far as I know, the RB class isn't scheduled for six months,
and we need to create this revised indented BOM much sooner if possible.
Once we upgrade to V5, and implement ODBC, I'll probably write an Excel
Visual Basic program to take care of this report, because most customers
like the BOM's in Excel Format. Our target date for upgrade is September,
and that isn't soon enough for this report. I'm sure after getting this one
down in ReportBuilder, Crystal should be just as easy, and I'll have a much
better idea of how to pull the records into the Excel workbook via ODBC.
We are filtering the report by company, partmtl.partnum (Level 1) , and
partrev.revisionnum (Level 2)
Please let me know if I'm on the right track, and or if I'm just barking up
the wrong table.....
Best Regards,
Thaddeus Jacobs
Assistant LAN Technologist / Vantage Support
Kinematic Automation, Inc
Site ID 92196
mailto:
tjacobs@...