Create Sequential Code for New Activity


Hi Team,

I am trying to create automated code for the new activity I added to a numbered list
The format should be like this:

Department code - Activity Sequential Code (the structure attached for example)


I've tried to make a system module, but the activity code still has duplicates. Is there a way to make automated code?

Please advise,

Best Answers

  • KirillKuznetsov


    Good job, you are very close to the solution!

    It depends on what user experience you are trying to achive.

    If you want user to create items one by one then you have to publish an import which will generate an item by calculated code based on selected parent (department). When parent is selected - system have to find the max code value of the elements which were already created underthis parent element and add 1 to it.

    For example. In tech department (200) you now have 3 elements. 200-001 200-002 and 200-003. Remove the parent's part and change format to number and you get 1 2 and 3 accordingly. The biggest code value is 3 (003) . Add 1 to 3 = 4. Convert back to calculated code. And this is your next code 200-004.


    Here you will have to use Max summary method. The tricky part with it is that when there are no items under a parent - the output of it will be -Infinity. In this cases you have to override the -Infinity value using IF statement to get code "001". Simply IF TEXT(<max value line item>) = "-Infinity" THEN 1 ELSE <max value line item>+1

  • KirillKuznetsov


    "Code +1" line item has to be calculated in a separate module without activity dimension. In this module User will select Department in list-formated line item. Then usin LOOKUP you will have the following formula: 'Code +1' = SYS Activity Detail.Convert Code[LOOKUP:<select department>] +1

    And then you can use this module to calculate the future code.


  • Hi @KirillKuznetsov 

    Thank you for replying, but I'm still getting stuck on it, I can't get the max value for the new activity

    Would you mind explaining more?


    Thank you,


    Screenshot 2022-10-11 085116.png

  • It is possible to create an incremental count when new items are added to a list by using RANK function.
  • @KirillKuznetsov 

    Thank you very much, I got the solution to create automated code for my new activity.
    But for "-infinity" from convert code to number, I used another condition which is almost the same as your suggestion