ADDSUM TECHNICAL SUPPORT MEMO
|
| Date: |
April 23, 1997 |
| Subject: |
Replacing or eliminating a portion of the characters of an inventory code for a series of items |
| Product: |
Advanced Accounting 5.0, 5.1 |
| Author: |
Anthony J. Frates (Copyright 1997-8 Addsum Business Software, Inc.) |
BACKGROUND: Normally to change a product code from one value to another, a user would use the "Change Related Rec Vals" (option M under Y-System Maintenance). In this utility all files affecting the replacement of one product code value for another is automatically handled in all of the related files. However, this utility cannot change a range of product codes to either eliminate characters "within" the product code or to replace certain characters within a product code with other characters.
PROCEDURE: Option B (Change/Delete Records) under F-File Utilities can be used to both accommodate a mass change of records as well as an "in string" replacement of a product code. In connection with changing a product code however the user must be extremely careful to (a) make sure they use the correct field names and otherwise repeat the same steps exactly for each modify operation and (b) make sure that all of the related files are updated otherwise information in the system will no longer be linked together and erroneous reporting and other unexpected results could occur.
Before proceeding make sure that a FULL back-up of all data is made. Further ALL OTHER users in a multi-user system must not be in the software while the changes are being made. In changing a product code, certain history files are involved and it could take a while for all of the changes to be made. In a system with large history files this could take an hour or more. For the duration of this period, NO OTHER USERS should be in the software.
To accomplish this change, the key portion of the commands that follow is the "mid" function which allows a piece of a field to be extracted and combined with another piece of the same field.
The steps are:
Select a file name
Select the appropriate company extension (B if the main company)
Choose M to modify values
Highlight the "code" field which in many but not all instances will be the first field in each database and press ENTER
In response to the field modifier expression enter:
mid(fieldname,a,b)+mid(fieldname,c,d)
(where fieldname is the name of the field (e.g. BKIC.PROD.CODE), a is the starting value in the field, b is the number of characters from point a, c is the starting point where we want to continue and d is the remaining characters in the field)
NOTE: we would only need to enter mid(fieldname,a,b) if we were only wanting to eliminate characters from the rightmost or leftmost portion. We would have to use a "concatenated string" however to remove characters from the middle of a string. If we wanted to insert/replace characters we would insert those in between the two mid() strings.
EXAMPLE:
Suppose that we have a series of inventory codes that begin with BBC and we just want these codes to start with B so that code BBC1000 would become B1000, code BBC2000 would become B2000, etc. If we were working on file BKICMSTR, we would type:
mid(bkic.prod.code,1,1)+mid(bkic.prod.code,4,12)
The first "1,1" is saying to start at position one and get one character, in this case "B". The "4,12" is saying to start at position 4 and get all of the remaining characters in the file. (The total length of the product code field is 15 characters: 1+2 (the characters we are eliminating)+12=15.)
To instead replace the 2
nd and 3
rd characters ("BC" in the example) with different characters (for example "CD") one would type:
mid(bkic.prod.code,1,1)+’CD’+mid(bkic.prod.code,4,12)
Press ENTER after typing in the desired string expression and then press ESC.
In response to the "Owner Name" message box, simply press ENTER.
In the "Enter the filter expression" Record Filter box, type in the expression relating to the characters you want to replace, e.g.
mid(bkic.prod.code,2,2)=’BC’ would be looking for all inventory codes that starting at the second position and for that position and the next one are equal to ‘BC’ - every time such a record would be found, it would be replaced by the string entered in the first expression.
These steps then need to be repeated for each file.
Assuming then that we want to eliminate the 2
nd and 3
rd character from a group of product codes that have a ‘BC" in those positions, the following listing shows which files and expressions we would enter (in entering the modifier expression, you must select the same field that is indicated in the mid function):
File: BKICMSTR
Modifier expression: mid(bkic.prod.code,1,1)+mid(bkic.prod.code,4,12)
Record filter: mid(bkic.prode.code,2,2)=’BC’
File: BKICLOC.
Modifier expression: mid(bkic.loc.prod,1,1)+mid(bkic.loc.prod,4,12)
Record filter: mid(bkic.loc.prod,2,2)=’BC’
File: BKICPMAT
Modifier expression: mid(bkic.pmat.pcode,1,1)+mid(bkic.pmat.pcode,4,12)
Record filter: mid(bkic.pmat.pcode,2,2)=’BC’
File: BKARINVL (note: highight the third field down, i.e. bkar.invl.pcode)
Modifier expression: mid(bkar.invl.pcode,1,1)+mid(bkar.invl.pcode,4,12)
Record filter: mid(bkar.invl.pcode,2,2)=’BC’
File: BKARHIVL
Modified expression: same as above
Recordfilter: same as above
File: BKAPPOL
Modifier expression: mid(bkap.pol.pcode,1,1)+mid(bkap.pol.pcode,4,12)
Record filter: mid(bkap.pol.pcode,2,2)=’BC’
File: BKAPHPOL
Modifier expression: same as above.
Record filter: same as above
File: BKARINVI
Modifier expression: mid(bkar.invi.pcode,1,1)+mid(bkar.invi.pcode,4,12)
Record filter: mid(bkar.invi.pcode,2,2)=’BC’
(Note: the next six files can be skipped if you do not use bill of materials)
File: BKBMHIST
Modifier expression: mid(bkbm.hist.prnt,1,1)+mid(bkbm.hist.prnt,4,12)
Record filter: mid(bkbm.hist.prnt,2,2)=’BC’
File: BKBMHSTL
Modifier expression: mid(bkbm.hstl.comp,1,1)+mid(bkbm.hstl.comp,4,12)
Record filter: mid(bkbm.hstl.comp,2,2)=’BC’
File: BKBMHSTL
Modifier expression: mid(bkbm.hstl.prnt,1,1)+mid(bkbm.hstl.prnt,4,12)
Record filter: mid(bkbm.hstl.prnt,2,2)=’BC’
File: BKBMMSTR
Modifier expression: mid(bkbm.parent,1,1)+mid(bkbm.parent,4,12)
Record filter: mid(bkbm.parent,2,2)=’BC’
File: BKBMMSTR
Modifier expression: mid(bkbm.component,1,1)+mid(bkbm.component,4,12)
Record filter: mid(bkbm.component,2,2)=’BC’
File: BKICBOM
Modifier expression: mid(bkic.bom.prod,1,1)+mid(bkic.bom.prod,4,12)
Record filter: mid(bkic.bom.prod,2,2)=’BC’
(Note: the next three files can be skipped if you do not use point of sale)
File: BKPOSLP
Modifier expression: mid(bkpos.lprd.code,1,1)+mid(bkpos.lprd.code,4,12)
Record filter: mid(bkpos.lprd.code,2,2)=’BC’
File: BKPOSNEG
Modifier expression: mid(bkpos.neg.prod,1,1)+mid(bkpos.neg.prod,4,12)
Record filter: mid(bkpos.neg.prod,2,2)=’BC’
File: BKPOSSLL
Modifier expression: mid(bkpos.sll.pcode,1,1)+mid(bkpos.sll.pcode,4,12)
Record filter: mid(bkpos.sll.pcode,2,2)=’BC’
(if you never use the "Notes" feature in connection with inventory items, the next file could be skipped)
File: BKSYNLUP
Modifier expression: mid(bknote.code,1,1)+mid(bknote.code,4,12)
Record filter: mid(bknote.code,2,2)=’BC’ .a. bknote.whr.from=’IC’
(note the additional item that you should type in the record filter for this file)
If we had wanted to change the first three characters to some other three characters, then we would simply adjust the modifier expressions as desired.
As an alternative to the foregoing, a TAS "utility" program could be written that automatically handles all of the above steps.
Copyright ã 1997-8 ADDSUM BUSINESS SOFTWARE, INC. Salt Lake City, Utah
ADDSUM is a registered service mark of Addsum Business Software, Inc.
Advanced Accounting and TAS are trademarks of Business Tools, Inc.
Technical support phone number: 801-277-9240
\homepage\techmemo\ad042397.htm