Share via

Calculated column with custom ID

Anonymous
2016-03-03T04:37:32+00:00

I have a list form which contains 3 fields: Company (choice field), NewID (lookup number field) and MyID (number field with min/max values 0000-9999).

On NewID field lookup formula is set:

=IF([External-ID]>999,CONCATENATE("18",[External-ID]),IF([External-ID]>99,CONCATENATE("180",[External-ID]),IF([External-ID]>9,CONCATENATE("1800", [External-ID]),CONCATENATE("18000",[External-ID]))))

Depends of what I will choose on "Company" I need to have different "prefix"+MyID=NewID.

Eg.: When I will choose in "Company" Comp1 (which has prefix "18") I should received NewID as 180001 etc.

How I can set start value for field MyID as "0000" and hide if from Form to be auto-increment (but not based on original ShP ID field).

Best, ZiBi

Microsoft 365 and Office
Microsoft 365 and Office

A comprehensive suite of productivity tools and cloud services that enhance collaboration, communication, and efficiency. Combining classic Office apps with advanced Microsoft 365 features, it supports both personal and business needs

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-03-04T09:58:40+00:00

    You need to :

    a) create the "hidden column" with the default value of 0000

    b) create a calculated column that you will use for your requirement that will concatenate the hidden column AND the ID.

    By doing so the calculated column will auto-increment as the ID autoincrements

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2016-03-04T09:55:16+00:00

    Hi ZiBi,

    For your requirement, some customizations might be needed. We suggest you post in our Technet forum which is a specific support channel for handling customization related requirements. Thanks for your understanding.

    In addition, to create an auto-increment column, you may try using workflow. Here is an article for your reference:

    http://geekswithblogs.net/ThorvaldBoe/archive/2015/01/14/161131.aspx

    Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

    Regards,

    Spike

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-03-04T02:34:42+00:00

    Thanks for answer but ....

    If that new field will have default value 0000 then always entry will be with 0001 but it should be auto-increment. So it should read that previous entry was 000x and add next as 000x+1

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-03-03T04:41:27+00:00

    If I understand you right you can create another field with a default value of 0000. Hide the said field and then use it in the calculations for the concatenation.

    Sorry if I didn't understand your question right.

    Good luck :)

    Was this answer helpful?

    0 comments