System command, dreport and data transfer

[Dan Snyder]

Mark,

Here's a description of the problem.
I need to find all parts sold to a customer in the past year (I can get this
from file1-jobs no problem), for each of those parts get a list of the
materials required to build them (file2-bill_of_materials), copy the
materials needed data to file3 (whatif) for manipulation and reporting.


> Ewww, that's ugly.  My opinion, but ouch.
Agreed, IT IS UGLY.  I'm dealing with legacy code here and don't have the
time or desire to re-write everything from scratch.  File2
(bill_of_materials) has 623 fields in it and if designed properly should
have been broken out into at least 6 different files/tables.  Breaking the
file apart is not easy since data types were never configured properly or
enforced, so people just typed information in whatever field they wanted to
see it in (For example: I want to see a comment on this screen and report,
so that's I'll just type it across the screen into 5 different fields until
I'm done).  

I chose the approach I described for the following reasons.
1. I'm only a beginner with filePro (3 years, but filePro is only 10% of my
job so still a newbie), therefore I tend to use what's worked before.
2. I have existing code in file2 that does exactly what I need to do, so why
rewrite all of that.  The code "towhatif" loops through fields in file2 that
correspond to 18 material line items in a bill of materials, for each of
those line items it does some sanity checks on values in 8 fields to see if
they look like real data or a comment, then for stuff that looks like real
data transfers those 8 fields into an individual record in file3.  Once in
file3, it's structured much better and is far easier to run reports on.
Eventually that "towhatif" code may be used to help break file2 down into a
better structure, unfortunately that only address one of the 6+ tables it
should be broken down into.  Which is actually why I posted the message.  I
could have a user manually transfer each record using a report and be done
with this, but I was hoping to develop code to assist with future data
cleanup.
3. It's not something the users will do, only I will run this.
Additionally, it will only be run a few times, so I didn't want to spend too
much time developing it.


> I can confirm that SYSTEM() works in *report.  I've never heard of any
exceptions.
So you're saying that my approach should work when called from "D-Request
Output"?  That way I can use it on a group of records simply by selecting
those records like I was running a report (rather than writing code to
select and loop through records)?  My code works fine if I manually enter
data from one record on the command line simulating the "system" call or if
I attach the code to a key in the input processing table.  My code just
doesn't work when I try to call if from @wbrk1 when subtotaling on a part
using "D-Request Output" which is "dreport" I think.  I was asking to if a
"SYSTEM" call to "dreport" should work from "dreport".


Jay,
I think the above may clarify a little for you.  I didn't want it to happen
from an active record in dclerk, I wanted to select records at runtime using
"D-Request Output".


Ken,
> See also "CALL NOAUTO".
Actually, I tried using "CALL" from dreport for something similar last week
and it gave me an error something like "can't nest calls".  If you're saying
I should be able to use CALL instead of SYSTEM from dreport in the same
basic flow I've described I'll take another look at that.


Thank you,

Dan Snyder
IT Manager
Mercury Electronics
717-428-0222 ext.224



-----Original Message-----
From: filepro-list-bounces+dan=macdatatech.com at lists.celestial.com
[mailto:filepro-list-bounces+dan=macdatatech.com at lists.celestial.com] On
Behalf Of Fairlight
Sent: Tuesday, January 30, 2007 1:11 PM
To: filepro-list at lists.celestial.com
Subject: Re: System command, dreport and data transfer

> Can anyone confirm that the system command does or doesn't work in 
> output processing?

I can confirm that SYSTEM() works in *report.  I've never heard of any
exceptions.

> Does anyone have a suggestion on how I could accomplish my objective 
> listed above?

I would do this entirely differently if I were up against the same task.
Keep in mind this is just how I'd be initially looking at solving the
problem, and it's not necessarily the best way.

1) Create a two-field table that you simply use to track part numbers as
   you encounter them for the first time.  It should have one field that's 
   the same length as the part number field in your other tables, and
another
   field identifying a "session" (date+time would probably suffice, maybe
   adding in @id if necessary).  The session field will be used later to
wipe
   all records used in any particular run.

2) Start your report on file1
     A) Grab part number from record.
     B) Lookup to file from step #1 to see if part number has been
processed.
        If it has (ie., a record is present for it), move on to the next 
        record with an END or other appropriate pre-END processing and
        we should restart at step #2A.  If it hasn't, move on to step #2C.
     C) Create new record in file from step #1 with the part number and
        session ID, via free record lookup.
     D) Now that you're on a part number you have never handled in this
        run, do a lookup on file2 for that part number.  Use GETNEXT
        to go through it until your part number in the lookup is not
        the same as you're looking for or the lookup fails entirely (ie.,
        "not lookupname").
     E) For each record found in step #2D, create the appropriate record(s)
        in file3 as necessary via free record lookup.

3) At the end of the run (I'm assuming from the docs that @DONE is perfect
   as the event trigger for this), simply perform a lookup on the table
   from step #1 and delete any records whose session ID is the same as
   the current session ID.

You never have to use SYSTEM() at all, nor do you have to chain-call filePro
executables, eating unnecessary license seats, or increasing project
overhead.  One processing table, all results.

Just one person's suggestion...

mark->
_______________________________________________
Filepro-list mailing list
Filepro-list at lists.celestial.com
http://mailman.celestial.com/mailman/listinfo/filepro-list