Substitution in file interface

You may want to substitute some of the values that are used in M3 PWB internal data representation. Substitutions of certain values can be conducted in the file interface as per the examples below:
  • Substitution Example 1. The customer uses alphanumeric order numbers (that is, mixed characters and digits) and M3 PWB can only accommodate pure numerical numbers. For this situation substitution is used to generate a new sequence number for the orders.
  • Substitution Example 2. To create a link between item numbers and work centers using a co-sorting key you must use a substitution. To define the substitution, start by defining tokens for the file. This is a Tag located at the top of the PMOTRA.FID file (which is the configuration file for the file interface).
File definitions could look like this:
  • FILEDEF(FAM,H:\SOURCE\CUSTOM\FAMCODE.DAT), or
  • FILEDEF(FAM,H:\SOURCE\CUSTOM\FAMCODE.XXX, SKIP(0), RESET)
  • FILEDEF( <alias>, <path and filename> [, <SKIP(0)>] [, <RESET>])
Alias: used to refer to the file in the SUBSTITUTION tags
  • SKIP(v): Optional skip the value v. v is not used in substitutions.
  • RESET: Optional reset (clear) the file. This option is normally used by a new sequence of values are generated. NFA(GEN_NEW_SEQNUM)

The Substitution Tag is placed in the header of the Record where it is displayed. This tag must be placed before the first Record entry. Refer to the example below:

Example of the PMOTRA.FID file

FILEDEF(OPTR1,H:\PSFI\CONTROL\OPTR1.TXT)

 [IMPORT]

   FILEFORMAT=SEPARATED

   CONVERTCHARS=WINDOWS_TO_PMSIM

   ID=#1

!   DELIMITER=;

!   CHARQUOTES=

   [O]

    SUBSTITUTION=(#7,OPTR1.#4),KEYS((#14,OPTR1.#1),(#7,OPTR1.#2)),NFA(SKIP)

    SUBSTITUTION=(#30,OPTR1.#3),KEYS((#14,OPTR1.#1),(#7,OPTR1.#2)),NFA(SKIP)

    TRANSFORM=#7;0:1;

!     FIELD  TYPE    DEFAULT             OFFSET  LENGTH  R  DESCRIPTION

!     -----  ------  ---------------     ------  ------  -  -------------------

      #1    ;CHAR   ;                   ;1029   ;1    ; "O" EXRTYP  RECORD TYPE

      #2    ;CHAR   ;                   ;9      ;12   ;     COHPEG  PEG ID

      #3    ;CHAR   ;NULL:ERROR         ;113    ;25   ;     COHITM  ITEM NUMBER

      #4    ;LONG   ;NULL:ERROR         ;8      ;     ;     MOHMRP  ORDER NUMBER

      #7    ;SHORT  ;                   ;134    ;     ;     IOHPRI  EXTERNAL NETWORK PRIORITY

      #8    ;DATE   ;                   ;9      ;     ;     MOHDDT  MRP ORDER DUE DATE

      #9    ;TIME   ;                   ;9      ;     ;       -"-                   -"-

      #10   ;DATE   ;                   ;10     ;     ;     MOHRDT  MRP ORDER RELEASE DATE

      #11   ;TIME   ;                   ;10     ;     ;       -"-                   -"-

      #12   ;DATE   ;                   ;11     ;     ;     MOHCDT  END ITEM PEG RECEIPT DATE

      #13   ;TIME   ;                   ;11     ;     ;       -"-                   -"-

      #14   ;SHORT  ;NULL:ERROR         ;136    ;     ;     IOHSTA  SHOP ORDER STATUS

      #15   ;FLOAT  ;NULL:ERROR         ;13     ;     ;     ROHPLQ  PLANNED QUANTITY

      #16   ;FLOAT  ;                   ;14     ;     ;     ROHFDQ  FINISHED QUANTITY

      #17   ;CHAR   ;                   ;138    ;25   ;     COHRON  DESCRIPTION

      #18   ;CHAR   ;                   ;163    ;10   ;     COHSON  SHOP ORDER NUMBER

&..

&..

SUBSTITUTION=(#7,OPTR.#4),KEYS((#14,OPTR.#1),(#7,OPTR.#2)),NFA(SKIP)

SUBSTITUTION=( <RecordEntryId>, <SubstituteWith>), <EXPRESS>(<key>[,<key>]), NFA(<nfaToken>)[,RESET]
  • RecordEntryId: ID for the record entry in which substitution if in effect.
  • SubstituteWith: Column ID in a file used for substitution, defined by a FILEDEF Tag. You can also use another RecordEntryId from the same record if it is the same type (CHAR, SHORT, LONG) as the Substituted RecordEntryId.

EXPRESS: KEY | KEYS. KEY is used for a single expression.

Key: Part of the expression that must be true.

[NOT]( <RecordEntryId>,<Column in substitution file>)
  • For example, (#14,OPTR.#1) means that the value of Entry 14 is compared to all the values in column 1 in the substitution file defined by alias OPTR.
  • If there are more than one key in a substitution, they must all be correct in order for any of the substitutions to take place.
  • You can also have a key like this NOT(#3,OPTR.#2). This key is true for each entry in OPTR column 2 that does not match record entry 3.
  • NfaToken: This token defines what happens if the expression is not evaluated as true.
  • RESET.Reset the substitution file when this substitution is performed. (This is only used with NFA(GEN_NEW_SEQNUM.)
NfaToken Description
SKIP Do nothing
WARNING Make a warning that the substitution was not found (error code 77).
ERROR Make an error. The import/export terminates (error code 76).
GEN_NEW_SEQNUM Generate a new sequence number if a substitution was not found and a new line in the substitution file is created. The new line is used during export.