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

PDM+ can take care of mass updates to ITEMBL

> -----Original Message-----
> From: McCready, Joan [SMTP:Joan.McCready@metaltekint.com]
> Sent: Thursday, October 05, 2000 10:05 AM
> To:   'MAPICS-L@midrange.com'
> Subject:      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
> +---
+---
| 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
+---