Update not updating all records in select forupdate

All - I’m using a Job to update the TaxToDate value in the TaxData table. If I update the value to today(), the code runs just fine. If I update the value using a literal date (e.g. 23\2\2012) or calculated date (e.g. today()+3) the code will update only the first 21 records.

Does anyone have a fix and/or explanation?

static void rjv_00299_UpdateTaxToDate(Args _args)
{
TaxData taxData;
str zip;
;

ttsbegin;
while select forupdate TaxToDate from taxData where taxData.TaxCode >= ‘90001’
&& taxData.TaxCode <= ‘96162’
&& taxData.TaxFromDate == 1\10\2011
{
taxData.TaxToDate = today(); //WORKS AS EXPECTED
//taxData.TaxToDate = 23\2\2012; //ONLY UPDATES THE FIRST 21 RECORDS (out of ~3000)

taxData.update();

zip = taxData.TaxCode;
info(zip);
}
ttscommit;

}

There is nothing obvious that stands out to me as why this wouldn’t work. I’d try two things:

Use the str2date function to convert your literal to a date variable.

date taxToDate = str2date(‘02/23/2012’, 213);

Change the “while select forupdate TaxToDate from taxData” to:

“while select forupdate taxData”

Both of these things are just guesses to eliminate some apparent oddities. Perhaps it doesn’t like that you’re selecting one field for update or the date literal isn’t actually formatted correctly? Just guesses…seems strange.

No joy. Using str2date and NOT specifying the TaxToDate field yielded the same results. If I comment out the update (//taxData.update():wink: it does cycle through all 2,660 records. It just doesn’t update them (for obvious reasons).

Do Jobs work on cached recordsets that don’t contain all records or is there some additional compiling that needs to be done somewhere? Anyone? Anyone?

Thanks for the help.

static void rjv_00299_Temp(Args _args)
{
TaxData taxData;
str zip;
int i;
;

ttsbegin;
while select forupdate taxData where taxData.TaxCode >= ‘90001’
&& taxData.TaxCode <= ‘96162’
&& taxData.TaxFromDate == 1\10\2011
{
taxData.TaxToDate = 15\2\2012; //ONLY UPDATES THE FIRST 21 RECORDS (out of 2660)
taxData.update();

zip = taxData.TaxCode;
info(zip);
i = i + 1;
}

ttscommit;
info(Num2Str(i,0,1,1,3));

}

Hi,

Have you considered just using update_recordset like this -

update_recordset taxData
setting taxToDate =
where taxData.taxcode <= ‘96162’
&& taxData.taxFromData ==

Harish - Using update_recordset ended up being the solution. For consistency I was trying to make select forupdate work but, in the end, had to favor “code that worked” over “code that I wanted to work”.

Thanks for the reply!

I was going to suggest update_recordset, but like you, I’d rather figure out why the one method isn’t working. Jobs don’t work on cached recordsets. Do you have any code in the Tables\TaxData\Update method? Is your client’s version the same as your AOS?

Have you tried this on another AX instance with a different database…perhaps another dev or test box?

Your copied code in your most recent reply doesn’t have the suggested change to use a taxToDate variable. I meant to imply to change the from date in the select too. Try the job below with one important additional change of using “.doUpdate” instead of “.update”.

static void rjv_00299_Temp(Args _args)
{
TaxData taxData;
str zip;
int i;

date taxToDate = str2date(‘23/02/2012’, 123); // TODO - Fix this? I’m not sure how your dates are formatted.

date taxFromDate = str2date(‘01/10/2011’ 123); // TODO - Fix this? I’m not sure how your dates are formatted
;

ttsbegin;
while select forupdate taxData where taxData.TaxCode >= ‘90001’
&& taxData.TaxCode <= ‘96162’
&& taxData.TaxFromDate == taxFromDate
{
taxData.TaxToDate = taxToDate; //ONLY UPDATES THE FIRST 21 RECORDS (out of 2660)
taxData.doUpdate();

zip = taxData.TaxCode;
info(zip);
i = i + 1;
}

ttscommit;
info(Num2Str(i,0,1,1,3));

}

WE HAVE A WINNER!!! Though it only started working consistently AFTER I had run the equivalent update_recordset Job one last time. What seems to be the key in your code is the date variable being assigned to the TaxToDate field (taxData.TaxToDate = taxToDate;). Assigning this value any other way produced the same 21 record error I had started with. Also, using either .Update or .doUpdate worked just fine.

Code in the Tables\TaxData\Update method = Nope. No code.

Client version the same as your AOS = Yes (our VAR suspected this as well)

Tried this on another AX instance with a different database = Yes (DEV and TEST)

Thanks for the help!

Glad it’s resolved! I knew after seeing the way you were assigning the date that it would probably cause some issues. I was going to further suggest you bounce the AOS, clear cache, etc. Next steps would have been to do a SQL trace probably. Starts to get into a lot of work at this point lol.