[SOLVED] Create a job to fetch records from table with specific column format and replace with required format

Create a job to fetch records from table with specific column format and replace with required format

I need to create a job in X++ to fetch records from table X with column having id's having format ER5000123440 and replace 5 with -0 in ax. The 5 is the number coming immediate after ER.

after replacing the id will be ER-0000123440.

Select EXPENSEID 
from ms_it_dms_staging 
where EXPENSEID like 'ER4%'
and ms_it_dms_staging.FAILUREREASON=3

this is the code in sql. Can u help me write this in x++

Solution

I don't have AX in front of me, so there may be syntax errors, but this is basically what you'll do:

// Declare variable to hold your new ID temporarily
str myNewId;
// Declare the table buffer variable
ms_it_dms_staging ms_it_dms_staging;

ttsbegin; // Begin a transaction
while select forupdate ms_it_dms_staging 
  where ms_it_dms_staging.EXPENSEID like 'ER5*' &&
        ms_it_dms_staging.FAILUREREASON == 3
{
    // Do your logic here to change the ID. Make sure this works correctly, may need to adjust the start digit.
    // This doesn't need to be in a variable but I'm making it simpler
    myNewId = strfmt("ER-0%1", subStr(ms_it_dms_staging.EXPENSEID, 3, maxInt()));

    ms_it_dms_staging.EXPENSEID = myNewId;
    ms_it_dms_staging.update();
}
ttscommit; // Commit the transaction