MIDRANGE dot COM Mailing List Archive



Home » MAPICS-L » October 2000

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


  • Subject: RE: Suggestions for Changing Many Records in a Mapics DB File
  • From: "Shaw, David" <dshaw@xxxxxxxxxxx>
  • Date: Thu, 5 Oct 2000 09:18:54 -0400

fixed

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





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact