How to use MessageNumber and MessageSetNumber with ExecuteEdits

 The ExecuteEdits method in Record Object executes the standard system edits on every field in the record. standard system edits are;

• Reasonable Date Range (Is the date contained within the specified reasonable date range?)
• Required fields are present
• Validates all 1/0 fields contain only a 1 or a 0
• Validates all translate fields have a valid value
• Validates all YesNo fields contain a Y or an N
• Validates all prompt edit fields have a valid value

If any of the edits fail, which means an error has found for any field, the status of the property IsEditError is set to True for the record. Then, we can use Field class properties EditError to find out which field is in error, and MessageSetNumber and MessageNumber field class properties to find the error message set number and error message number.
This kind of validation is useful in Application Engine programs or if you executing Component Interfaces (CI) via PeopleCode.

One way of doing this is before you assign values to the real record (SQL record) you can create a copy of the real record as a derived/work record. Then you can invoke ExecuteEdits method with the derived record to check any standard system edits.  

As an example;

Function executePromptTableEdit() Returns boolean;
 Local number &msgnum, &msgset;
   Local integer &i;
   Local string &msgtxt;
   &WorkRec = CreateRecord(Record.MY_DERIVED_RECORD);
   &WorkRec.SetDefault();
   
   /* Copying like-named field values from SQL record to the derived record */
   &REC = GetRecord(RECORD.MY_SQL_RECORD);
   &REC.CopyFieldsTo(&WorkRec);
   
   /*  all system edits are executed */
   &WorkRec.ExecuteEdits();
   
   /* If edit error found */
    If &WorkRec.IsEditError Then
     For &i = 1 To &WorkRec.FieldCount
       If &WorkRec.GetField(&i).EditError Then
        &msgnum = &WorkRec.GetField(&i).MessageNumber;
        &msgset = &WorkRec.GetField(&i).MessageSetNumber;
        &msgtxt = MsgGetText(&msgset, &msgnum, "Message not found", "");
        /* here you can push this informaton to an Array and later you can use this array to populate error information to a Message Record for further lookup*/
       End-If;
     End-for;
     Return False;
    end-If; 
  Return True;  
end-function;

This way, you can use ExecuteEdit method to get the field edit error information for your next application.

%ExecuteEdits

Description

Use the %ExecuteEdits function to apply data dictionary edits in batch. The %ExecuteEdits function is Application Engine-only meta-SQL. You cannot use it in COBOL, SQR, or PeopleCode, not even in Application Engine PeopleCode.

Notes About %ExecuteEdits

Note the following points about the %ExecuteEdits function:

• Consider performance carefully when using this function.

  Prompt table and Translate table edits have a significant effect because they involve correlated subqueries. Run a SQL trace at runtime so that you can view the SQL generated by %ExecuteEdits. Look for opportunities to optimize it.

• In general, %ExecuteEdits is best used on a temporary table.

  If you must run it against a real application table, you should provide Where clause conditions to limit the number of rows to include only those that the program is currently processing. Process the rows in the current set at one time rather than row by row.

• With %ExecuteEdits, you cannot use work records in a batch, set-based operation.

  All higher-order key fields used by prompt table edits must exist in the record that your code intends to edit, and the field names must match exactly. For example,

  %ExecuteEdits(%Edit_PromptTable, MY_DATA_TMP)  

  The record MY_DATA_TMP contains the field STATE with a prompt table edit against PS_REGION_VW, which has key fields COUNTRY and REGION. The REGION field corresponds to STATE, and COUNTRY is the higher-order key. For %ExecuteEdits to work correctly, the MY_DATA_TMP record must contain a field called COUNTRY. The edited field (STATE) can use a different name because Application Engine always references the last key field (ignoring EFFDT).

• In Application Engine, %ExecuteEdits uses the system date when performing comparisons with effective date (EFFDT); however, in some cases this date is not appropriate (Journal Edit, Journal Import, and so on). In these situations, use Journal Date when comparing with EFFDT. To override the use of the default system date with a selected field from a joined table, use %AsOfDateOvr. For example,

  %ExecuteEdits(%AsOfDateOvr(alias.fieldname), %Bind(...)...)

• Restrict the number and type of edits to the minimum required.

  Do not edit fields that are known to be valid or that are given default values later in the process. Also consider using a separate record with edits defined specifically for batch or provide a list of fields to be edited.

Parameters

type	Specify any combination of the following (added together): %Edit_Required %Edit_YesNo %Edit_DateRange %Edit_PromptTable %Edit_TranslateTable
recordname	Specify the record used to obtain the data dictionary edits.
field1, field2, ...	Specify a subset of the fields of the record to which edits apply.

Example

Suppose you want to insert rows with missing or invalid values in three specific fields, selecting data from a temporary table but using edits defined on the original application table. Notice the use of an alias, or correlation name, inside the meta-SQL:

INSERT INTO PS_JRNL_LINE_ERROR (...)
SELECT ... FROM PS_JRNL_LINE_TMP A
WHERE A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)
  AND %EXECUTEEDITS(%Edit_Required + %Edit_PromptTable,?
 JRNL_LINE A, BUSINESS_UNIT, JOURNAL_ID, ACCOUNTING_DT)