Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to autoIncrement a...
Finance | Project Operations, Human Resources, ...
Suggested answer

How to autoIncrement a field in function of other value

(0) ShareShare
ReportReport
Posted on by AymericKer 15

Hello !

i'm creating a form in order to write data in a table and show it in the form . That form only show the data related to a number order and sort by a specific int called ordre. (we first show the line with ordre =1, then ordre =2 ..) . 

So what I want is that when I create a new line in the form,  the value of ordre is automatically calculated and displayed. I put some pictures next to it so you can understand better. I saw some topics about using LineNum but I don't think it's relevant to my case because the number ordre depends on the ordernumber and not on the LineNum.

Here is what i develop for the moment : ( in the methods of the table linked to the form) :

public void initValue()
{
    SLF_ordonnancement SLF_ordonnancement;
    int value;
    
    super();
    
    select firstOnly SLF_ordonnancement order by SLF_ordonnancement.ordre desc
    where SLF_ordonnancement.CodeBarre == SLF_ordonnancement.CodeBarre;
    {
         value = SLF_ordonnancement.ordre   1;
    }
    
    SLF_ordonnancement.ordre = value;
   info(int2str(SLF_ordonnancement.ordre));
}
pastedimage1625746788233v2.png

  • Martin Dráb Profile Picture
    Martin Dráb 231,430 Most Valuable Professional on at
    RE: How to autoIncrement a field in function of other value

    Just note that the check for non-empty ordre field doesn't mean that the field must be editable by users.

    What I had in mind when adding the check were actually cases when the value is set in code in bulk. For example, I may have code generating many lines and I assign the ordre value there. This improves peformance, because the query finding the highest existing value won't be called for each individual line.

  • ergun sahin Profile Picture
    ergun sahin 8,816 Moderator on at
    RE: How to autoIncrement a field in function of other value

    for the first; I think you did not remove the insert code in the code. If you share the final version of the code, we can interpret it more accurately.

    For the second; It works on insert, not validateWrite. This is very normal, you wrote the code there. Martin's goal is to allow the user to enter it manually, only to set it if it's empty.

    No record actually created in the table until the user saves the record. So insert doesn't work until user saves the record.

  • Martin Dráb Profile Picture
    Martin Dráb 231,430 Most Valuable Professional on at
    RE: How to autoIncrement a field in function of other value

    If you get two lines created, you must have fogotten one of the extra calls of insert(). You shouldn't call it from your code at all - it's called automatically when user saves the record.

  • AymericKer Profile Picture
    AymericKer 15 on at
    RE: How to autoIncrement a field in function of other value

    Thank you, I have applied your advice. Only, I have two small problems: the first is that when I add a row there are now 2 rows created in the table (so the order number does not match) and the second is that order is displayed only when the line is validated and not when the line is created.

  • ergun sahin Profile Picture
    ergun sahin 8,816 Moderator on at
    RE: How to autoIncrement a field in function of other value

    When I read what Martin wrote, I understood the problem. You can use martin's structure to fill the ordre. I guess the problem is that the CodeBarre is empty. You want to get it automatically from SalesTable.

    You can use initValue for this.

    But I said form's datasource. I didn't say table.initValue.

    The reason why I said "write in form" is that you get SalesTable from caller/join table(if available), or if you have a parameter while opening the form.

    If you go to debug in your code and look at it, you will see that SalesTable is empty.

    A second issue is that I see you are using insert in your codes. These methods are structures that trigger each other in the structure of the system. Do not use inserts unless you specifically intend to open a second record or insert a record into a different table.

    Note:I have not read the articles in detail, I assume that you are trying to open a record from the form. If you are creating from the code, you have to fill in this field with the other fields. (before table.insert() call)

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 231,430 Most Valuable Professional on at
    RE: How to autoIncrement a field in function of other value

    As we already discussed, initValue() is a wrong place. I'm talking about insert() method, not initValue(). Also, your goal is just to set a value of a field. Calling insert() by yourself isn't related to your goal, therefore it's a bug.

    Let me remind your code from my first reply:

    SLF_ordonnancement ordonnancement;
    
select maxOf(ordre) from ordonnancement
	where ordonnancement.CodeBarre == this.CodeBarre;
	
this.ordre = ordonnancement.ordre   1;

    It does what you want - it find the highest ordre for the current CodeBarre, adds 1 and set the value to ordre field of the current record.

    And I said you can call this logic from insert() method of the table, before super(). Therefore you can simply take the code and put it there:

    public void insert()
{

	SLF_ordonnancement ordonnancement;
		
	select maxOf(ordre) from ordonnancement
		where ordonnancement.CodeBarre == this.CodeBarre;
		
	this.ordre = ordonnancement.ordre   1;
	
	super();
}

    I would just create a separate method for the initialization, to keep code in insert() easier to follow (and making the logic reusable):

    public void insert()
{
	this.initOrdre();
	
	super();
}

public void initOrdre()
{
	SLF_ordonnancement ordonnancement;

	if (this.ordre)
	{
		return;
	}

	select maxOf(ordre) from ordonnancement
		where ordonnancement.CodeBarre == this.CodeBarre;
		
	this.ordre = ordonnancement.ordre   1;
}

  • AymericKer Profile Picture
    AymericKer 15 on at
    RE: How to autoIncrement a field in function of other value

    I tried to follow your suggestion but I didn't quite understand where to write it. Normally I followed your advice :

    public void initValue()
{
    SLF_ordonnancement SLF_ordonnancement;
    SalesTable         SalesTable;
    
    this.CodeBarre = SalesTable.SalesId;
    this.insert();

    super();

    select maxOf(ordre) from SLF_ordonnancement
    where SLF_ordonnancement.CodeBarre == SalesTable.SalesId;

    this.ordre = SLF_ordonnancement.ordre  1;
}

    But I'm not sure the code is in the right place. This one is in the table SLF_ordering which is called when the row is already created. ( so too late). Maybe this one should be placed in a method of the form?

  • ergun sahin Profile Picture
    ergun sahin 8,816 Moderator on at
    RE: How to autoIncrement a field in function of other value

    I did not read the details of what was written, but if you want to give the first value, you can do it in the form datasource initvalue method.

    (Of course, you must have a salesTable record)

    docs.microsoft.com/.../dynamics.ax.application.formdatasource.initvalue

  • Martin Dráb Profile Picture
    Martin Dráb 231,430 Most Valuable Professional on at
    RE: How to autoIncrement a field in function of other value

    init() method of the form is a completely wrong place. The purpose of this method is initializing the form before even opening it - initializing controls, the query for fetching the data and so on. It's not related to your case, no data is loaded at this point and and inserting records here doesn't make much sense.

    Please use the debugger to test your code. For example, you'll immediately see that salesTable variable isn't populared at all (before the form wasn't run yet) and therefore no code depending on it can work.

    But why don't you simply follow my suggestion and don't set the field value in insert() method (before calling super())? Aren't you just making things more complicated for yourself?

  • AymericKer Profile Picture
    AymericKer 15 on at
    RE: How to autoIncrement a field in function of other value

    Here is what i tried : 

    public void init()
{
    super();

    SLF_ordonnancement.CodeBarre = SalesTable.SalesId;
    SLF_ordonnancement.insert();
    info("SalesId : "   SalesTable.SalesId);
}

    I put that code in the methods of the form, so when i'm creating the screen, the form is going to take the SalesId and put it in SLF_ordonnancement before we create a new line.

    But stil does't seems to work, when i info the value of SalesTable.salesId their is nothing showed on the screen.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,430 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans