Finance | Project Operations, Human Resources, ...

auto generate field by code

(0) Share

Report

Posted on by junior AX

1,552

Hi,

i want to auto generate a number by code.

let's say when a journal of type "X" gets created, Ex: Journal number is "123"
i want to create a new field to the journal table that gets auto generated by the following

journalNumber Date Number
123 20230411 0001

The number is filled by checking how many journals of type X are there on this day (if no one found then 0001, if one found then 0001, if 2 found 0002,etcc)

So if we have this

JournalNum JournalType createdDateTime NewField
123 X 2023-04-11 123202304110001
124 X 2023-04-11 124202304110002
125 Y 2023-04-11 125202304110001
126 X 2023-04-12 126202304120001

So when the journal is getting created, i will need to check how many journals got created in the same date with the same type other than the one getting created now:

if no one found
journalNum+date+0001

if 1 found
journalId+date+0002

what's the best way to write code for the 0001 part?

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

Mohit Rampal 12,565 Moderator on at

Like (1)

Report

Hi, You can create a variable to assign value 0001, find the journal created on the current date, order by createdDateTime, if no journal found then assign 0001, else increment it by 1.

Concatenate this variable with journal number and date and map to your new field.

Was this reply helpful? Yes No
André Arnaud de Cal... 300,904 Super User 2025 Season 2 on at

Like (1)

Report
Hi junior AX,

In addition to the reply from Mohit, you can check the code which is used for creating sub projects. The standard coding has an example how to increment the string part of a number.

To get the last used number, you can use a select statement like this:

LedgerJournalTable lastJournalTable; date d = DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()); select firstonly NewField from lastJournalTable order by NewField desc where lastJournalTable.JournalNum == currentRecord.JournalNum && lastJournalTable.JournalType == currentRecord.JournalType && lastJournalTable.createdDateTime == d;

Then you can check if the NewField has a value or not to determine if the number should start with 1 or should be incremented.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report
Hi Mohit,

Why order by createdDateTime

Hi Andre,

What's the difference between DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()) and DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone()) -- which one to use

i can't say in the where condition createdDateTime == date (because this one is utc and the other one is date) so how can i make createdDateTime as date -- as i want to get all journals of type X created in the same day

I think you got my question wrongly because i don't want to get the last journal

Both, here's what i tried as a start -- i know it's still wrong, but how can increment the string here?
str todayDate = date2Str(DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone()),321,DateDay::Digits2,0,DateMonth::Digits2,0,DateYear::Digits2); str custom; JournalTable journalTableCount; ttsbegin; JournalTable journalTable = JournalTable::find(_currentJournalTable.JournalId, true); select count(RecId) from journalTableCount where journalTableCount.JournalType == JournalType::X && journalTableCount.RecId != _currentJournalTable.RecId && journalTableCount.CreatedDateTime == DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone()); if(journalTableCount.RecId > 1) { custom = '000' int2Str(journalTableCount.RecId); } else { custom = '0001'; } journalTable.CustomField = _currentJournalTable.JournalId todayDate custom; journalTable.update(); } ttscommit;

Was this reply helpful? Yes No
Mohit Rampal 12,565 Moderator on at

Like (0)

Report

Hi, You can use either CreatedDateTime or new field in Order by Desc to get last journal created on that date so you can increment it by 1.

Your code will not work as you are comparing createdDateTime with date field, compiler will throw error.

Moreover you need to use string functions StrScan, SubStr to get the custom number like 0001 for the field. Also, you don't need to compare RecId if you write your code before insert, just check if there is any existing record created on that particular day for that category.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Mohit,

But I don't want to increment last journal by one.

As you can see i selected count to see how many journals of type X got created this day, to increment one of the parts in the custom field (the 0001)

So if count is 0, it will be 0001, if count is 1 it will be 0002 etc..

So no need to order by here right?

As for thr substr can you please help me more in this?

And what about the createdDateTime and Date

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 300,904 Super User 2025 Season 2 on at

Like (1)

Report
Hi Junior AX,

You can read on my blog how the session date is different from the today's date: Session date in Microsoft Dynamics AX - Dynamicspedia. The session date can be changed by a user. If you will use the session date in the selection, you will get incorrect results as the createddatetime will be filled with the todays date, not with a possible changed session date.

My bad about the datetime == date issue. I will share a new approach below which should work.

You mentioned that I got your question wrong, but that is not the case. When reading your code, you are counting the records, my suggestion was about selecting the last NewField value for a given journal type and date. I noticed that I added the journal number in the select statement by accident. Either you can do the count as you did or get the last record.

Let me share the coding again with a fix for the datetime field and exclusion of the journal number.

LedgerJournalTable lastJournalTable; StartDateTime d = DateTimeUtil::newDateTime(DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()),0); select firstonly NewField from lastJournalTable order by NewField desc where lastJournalTable.JournalType == currentRecord.JournalType && lastJournalTable.createdDateTime >= d && lastJournalTable.createdDateTime < DateTimeUtil::addDays(d, 1);

If you prefer the count as per your attempt, you can at least copy the date selection part. The method generateNextSubProjectId on the table ProjTable has logic to retrieve the last sub project and increment the number, also taking care of a format (e.g. ####). You can look into this coding and reuse parts to fill the custom part with a number and fill it with leading zeros. In the coding you shared, the string will be too long if the count would be 10 or higher.

Let us know if you can continue with the date selection and setting the custom part.

Was this reply helpful? Yes No
Mohit Rampal 12,565 Moderator on at

Like (0)

Report

Hi, As you mentioned that if journal getting created for first time during a day then new field will have 0001, if second time then 0002, in that case either you can order by Desc on created datetime or New field and increment by 1. Or you can find no of journals created and just add 1 like you are doing (in that case order by field is not required).

For createdDateTime, you can use Andre's code and also refer to standard method he suggested. If still any issues, please let us know.

Was this reply helpful? Yes No
ergun sahin 8,826 Moderator on at

Like (1)

Report

If you want to get a unique value, it makes more sense to add one to the highest. Otherwise, if a record is deleted for any reason, you will have two of the same value.

Count will work if your purpose is to just count records but It seems like this is not what you want

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report
Hi Andrea and Mohit,

I tried this way and it works:
[ExtensionOf(tableStr(JournalTable))] final class JournalTable_Extension { public static str newFieldLastCreated() { str newFieldLastCreated = ''; StartDateTime todayDate = DateTimeUtil::newDateTime(DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()),0); newFieldLastCreated = (select reverse NewField from JournalTable order by newFieldLastCreated where (JournalTable.createdDateTime >= todayDate && JournalTable.createdDateTime < DateTimeUtil::addDays(todayDate, 1)) && JournalTable.Posted == 1 && JournalTable.JournalType == JournalType::X).NewField; return newFieldLastCreated; } }

str todayDate = date2Str(DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()),321,DateDay::Digits2,0,DateMonth::Digits2,0,DateYear::Digits2); int sonNum; str newFieldNumberSeqFormat; str newFieldNext; str newFieldLastCreated = JournalTable::newFieldLastCreated(); if(newFieldLastCreated) { newFieldNumberSeqFormat = subStr(newFieldLastCreated, (strLen(newFieldLastCreated)-3), 4 ); sonNum = NumberSeq::numRemoveFormatV2(newFieldNumberSeqFormat, "####"); if (sonNum < 0) { throw error("@XX:NewFieldNumberSeqFormatExceedsIntegerLimit"); } str partial = subStr(newFieldLastCreated, 1, (strLen(newFieldLastCreated)-4) ); newFieldNext = partial NumberSeq::numInsertFormat(sonNum 1, "####"); } else { sonNum = 0; newFieldNext = journalTable.JournalId todayDate NumberSeq::numInsertFormat(sonNum 1, "####"); } journalTable.NewField = newFieldNext;

When last created is empty, the output is 1234562304160001

when last created is 1234562304160001 the output is 1234562304160002

However, I have few concerns, can i amend the code in a better way?

is it ok that i defined ####

is it ok that i had to get the value for both "partial" and "newFieldNumberSeqFormat" -- could it have done it better?

is it ok that i always sepcify -3 and -4 in the first subStr and and -4 in the 2nd subStr?

I feel if i did the count way, logic would have been simpler, as after selecting the count, all i have to do is this ( no need to use subStr twice or get the last journal):
if(journalTableCount.RecId >= 1) { newFieldNumberSeqFormat = NumberSeq::numInsertFormat(journalTableCount.RecId 1, "####"); } else { newFieldNumberSeqFormat = NumberSeq::numInsertFormat(1, "####"); } newFieldNext = journalTable.JournalId todayDate newFieldNumberSeqFormat; journalTable.NewField = newFieldNext;

Hi Ergun,

if a journal got deleted for any reason, i'll have issue in both ways.

using count, will result in 2 journals having the same NewField value
using getLastJournal and increment it by one, will result in having a wrong value saying that we have 3 journals that got created in the same day but they are actually 2 after one got deleted.

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 300,904 Super User 2025 Season 2 on at

Like (1)

Report
Hi Junior AX,

Thanks for the feedback that you got it working. I love your questions if your coding can be improved. That shows passion that you want to learn and improve.

The wat you defined "####" with the double quotes, will let the compiler think this is a label. You will get best practice warnings. Better would be using single quotes. In case the requirement changes to have 3 or 5 positions, you now have to loop through the coding finding all the places where it is defined with 4 positions. You can better declare a constant and use it. Example:

internal const str NumFormat = '####'; if(journalTableCount.RecId >= 1) { newFieldNumberSeqFormat = NumberSeq::numInsertFormat(journalTableCount.RecId 1, NumFormat); } else { newFieldNumberSeqFormat = NumberSeq::numInsertFormat(1, NumFormat); } newFieldNext = journalTable.JournalId todayDate newFieldNumberSeqFormat; journalTable.NewField = newFieldNext;

Having the variables partial and newFieldNumberSeqFormat is OK in my opinion. Of course, you can do it without, but this improves the readability of the coding.

Specifying -3 and -4 can be used, but again, if the requirement changes on the format length, you need to review and adjust again. When you are using the constant, you can use the strLen command to get the string length and use that as dynamic coding.

Was this reply helpful? Yes No