RE: Suggestions for Changing Many Records in a Mapics DB File

David and everyone else:

EPDM can make mass change updates to the ITEMASA file fields, but not to the
ITEMBL file.  Maybe the Integrator module would allow you to place ITEMBL
directly on the Browser and add the mass change capabilities to it....

        --  Joan

Joan McCready, IS Manager         phone:  636-479-4499
The Carondelet Corporation        fax:  636-479-3399
8600 Commercial Blvd              www.metaltekint.com
Pevely, MO  63070                 www.carondeletfoundry.com


-----Original Message-----
From: Shaw, David [mailto:dshaw@spartan.com]
Sent: Thursday, October 05, 2000 8:19 AM
To: 'MAPICS-L@midrange.com'
Subject: RE: Suggestions for Changing Many Records in a Mapics DB File


Al,

Query can be used to do this, but it is a round-about and potentially
dangerous process.  What you have would have to do is make the changes in
one file (let's say Item Master), then create a query that joins Item Master
and Item Balance.  The output of this query should include all the fields
from Item Balance EXCEPT for WHSLC, which would be the one from Item Master.
You would execute the query to an outfile, let's call it TEMPITEMBL.  You
would then copy the records back to your production ITEMBL like this:

CPYF FROMFILE(TEMPITEMBL) TOFILE(ITEMBL) MBROPT(*REPLACE) FMTOPT(*MAP *DROP)

You should do all this with MAPICS in restricted state - no users,
unattached jobs, or scheduled jobs running.  Obviously anytime you replace
the contents of a MAPICS file you're at risk for data loss - make a copy of
ITEMBL beforehand, and check your results carefully to make sure
everything's okay before you let users back on the system.

An RPG program to do this update would be very simple, less than 20 lines of
code, and a bit safer than the Query route.  I could write a sample program
for you at lunch time today, if you'd like - let me know.

Another alternative is SQL, which can do this sort of update if you're on a
new enough release of OS/400 (I think it's V4R3 and newer, but I don't
remember for sure).  An SQL wizard (which I'm not) could talk you through
this pretty quickly.  There are several interfaces to SQL, some included
with the base operating system and others optional in the 5769ST1 product.

There's also a tool called Sequel, from Advanced Systems Concepts, which
provides an even more powerful, simpler interface to SQL, and which can do
this update on any OS/400 release from V2R1M1 on.  I used it for 9 years at
my old job, it's just fantastic for this kind of work.

One warning: Updating WHSLC doesn't cause any other changes in MAPICS,
however there are many fields in MAPICS files which when changed trigger
changes to other fields.  Making changes to such fields from outside of
MAPICS should never be done unless you know everything else that has to be
changed and take care of that, too.

Final thought: The MAPICS client/server modules PDMPlus and EPDM have the
capability to do certain kinds of mass updates.  I don't know if they
support this type in any way or not.  Does anyone else?

Dave Shaw
MAPICS-L Moderator
Spartan International, Inc.
Spartanburg, SC
 
-----Original Message-----
From: aldg [mailto:aldg@mail.rvi.net]

Hi Group:

My last letter (sent just a while ago) delt with the WHSLC field in both the
ITEMBL and ITEMASA Mapics files.

In a somewhat related matter, is there an easy way to change the WHSLC
fields in "many" Items No records with a minimal amount of keystrokes?

The methods I'm aware of (using "two" AS/400 sessions, and the Copy and
Paste computer functions) are:

1. Use Inventory Management's File Maintenance, the changes can be made to
one file and copied and pasted to the second file; one record at a time.

2. Similarly, using AS/400 utilities such as Data Base Utility (DBU) or Data
File Utility (DFU), the the changes can be made to one file and copied and
pasted to the second file; one record at a time.

Is there a way to write a Query (using Query/400) that can make the WHSLC
changes to a List of Item Numbers in each file?  I don't believe this can be
done but I'd like some input on this question.

What about Report Program Generator (RPG)?  I'm not trained in it so I'm not
aware of its capabilities.  This is probably the tool to use to make changes
to a group of records in a large DB file.

I look forward to your suggestions and commments.

Regards....



--
Above message before copy enclosure (if any) was sent by:

Al Gershen
Grants Pass, OR 97526 
aldg@mail.rvi.net & aldg@webtv.net;
and thru ICQ # 12342782
+---
| This is the MAPICS Mailing List!
| To submit a new message, send your mail to MAPICS-L@midrange.com.
| To subscribe to this list send email to MAPICS-L-SUB@midrange.com.
| To unsubscribe from this list send email to MAPICS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: dshaw@spartan.com
+---
+---
| This is the MAPICS Mailing List!
| To submit a new message, send your mail to MAPICS-L@midrange.com.
| To subscribe to this list send email to MAPICS-L-SUB@midrange.com.
| To unsubscribe from this list send email to MAPICS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: dshaw@spartan.com
+---