System command, dreport and data transfer

[Fairlight]

The honourable and venerable Dan Snyder spoke thus:
> I need help doing the following...
> ----------------------------------
> 1. Lookup a group of records in a file (file1-jobs).  This file could have
> multiple records with the same part number.
> 2. For each unique part number in file1, lookup related records in another
> file (file2-bill_of_materials).  File2 may have multiple records for each
> part # which I would need to loop through. 
> 3. For each record found in file2, create new records in a third file
> (file3-temp_working_file).  Multiple records will be created in file3 for
> each record in file2.  File3 is used for "whatif" calculations and
> reporting.

> I tried the following...
> ------------------------
> 1. "D-Request Output" on file1, select the records I want (customer & date
> range), using a report that subtotals by part number and calls the following
> in the subtotal processing...

Ewww, that's ugly.  My opinion, but ouch.

> 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->