SOLVED

Unique ID issues calculation looses [] every time page is refreshed

%3CLINGO-SUB%20id%3D%22lingo-sub-1433056%22%20slang%3D%22en-US%22%3EUnique%20ID%20issues%20calculation%20looses%20%5B%5D%20every%20time%20page%20is%20refreshed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1433056%22%20slang%3D%22en-US%22%3E%3CP%3EMorning%2C%20Hope%20someone%20can%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20a%20UID%20field%20using%20the%20concatenate%20function%20with%20a%20prefix%20and%20then%20the%20ID%20field.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCONCATENATE(%22HUB%22%2C%5BID%5D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20is%20the%20%5B%5D%20round%20ID%20disappear%20each%20time%20the%20page%20is%20refreshed%20and%20I%20get%20a%20return%20of%20HUB%20for%20each%20new%20entry%20until%20I%20manually%20reinsert%20the%20square%20brackets%20round%20ID.%26nbsp%3B%20I%20remember%20years%20ago%20there%20was%20something%20around%20the%20use%20of%20%5B%5D%20and%20()%20depending%20on%20where%20you%20were%20based%20but%20I%20thought%20that%20was%20sorted.%26nbsp%3B%20When%20I%20use%20the%20()%20it%20doesn't%20apply%20the%20calculation%20to%20new%20records%2C%20but%20keeps%20the%20old%20records.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20other%20issues%20that%20stem%20from%20this%20the%20flow%20that%20emails%20customers%20with%20their%20UID%20will%20just%20return%20them%20HUB.%26nbsp%3B%20Any%20suggestions%20on%20what%20I%20could%20try%3F%20Or%20where%20I'm%20doing%20wrong%20with%20the%20concatenate%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1433056%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMicrosoft%20Lists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1435989%22%20slang%3D%22en-US%22%3ERe%3A%20Unique%20ID%20issues%20calculation%20looses%20%5B%5D%20every%20time%20page%20is%20refreshed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1435989%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239814%22%20target%3D%22_blank%22%3E%40John%20Dorrian%3C%2FA%3E%26nbsp%3B%3A%20Yes%2C%20ID%20column%20is%20there%20in%20the%20calculated%20column%20selection%20field%2C%20check%20column%20setting%20screenshot%2C%20where%20inside%20insert%20column%20box%20does%20not%20have%20ID%20column%2C%26nbsp%3B%3CSPAN%3EID%20is%20not%20supported%20in%20calculated%20columns%20and%20always%20disappear.%20When%20you%20open%20list%20settings%2C%20check%20cal.%20column%20settings%20or%20change%20something%20in%20formula%20and%20hit%20OK%2C%20will%20be%20visible%20(for%20a%20while)%2C%20but%20later%20when%20you%20add%20new%20item%2C%20new%20calculated%20value%20field%20will%20be%20blank%20again.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E1)%20ID%20is%20not%20valid%20column%20to%20use%20in%20Calculated%20Formulas%2C%20it%20is%20not%20displayed%20in%20the%20available%20columns%20for%20a%20reason.%3C%2FP%3E%3CP%3E2)%20It%20is%200%20for%20newly%20created%20items%20as%20it%20get%20its%20value%20from%20the%20database%20after%20all%20Formulas%20are%20evaluated%3C%2FP%3E%3CP%3E3)%26nbsp%3B%20You%20get%20the%20correct%20values%20(in%20all%20existing%20items)%20if%20you%20re-edit%20the%20Formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENote%3A%3C%2FSTRONG%3E%20Workaround%20is%20to%20use%20workflow%20or%20JavaScript%2FjQuery.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436026%22%20slang%3D%22en-US%22%3ERe%3A%20Unique%20ID%20issues%20calculation%20looses%20%5B%5D%20every%20time%20page%20is%20refreshed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436026%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F679665%22%20target%3D%22_blank%22%3E%40Ashish_Kohale%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20info%2C%20and%20at%20least%20I%20know%20not%20to%20use%20%5BID%5D%20for%20calculated%20fields.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20as%20a%20workaround%20I%20could%20do%20just%20use%20the%20ID%20field%20as%20is%20(sequential%20number)%20within%20the%20list%20but%20for%20the%20Flows%20and%20automatic%20emails%20add%20the%20list%20prefix%20(in%20this%20case%20HUB)%20before%20the%20ID%20field%20so%20when%20queries%20come%20in%20to%20the%20service%20desk%20they%20know%20which%20list%20and%20record%20to%20look%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20look%20into%20java%20and%20jquery%20when%20I%20have%20a%20bit%20of%20free%20time.%26nbsp%3B%20Thanks%20again%20for%20the%20info%2C%20every%20day%20is%20a%20school%20day%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436050%22%20slang%3D%22en-US%22%3ERe%3A%20Unique%20ID%20issues%20calculation%20looses%20%5B%5D%20every%20time%20page%20is%20refreshed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239814%22%20target%3D%22_blank%22%3E%40John%20Dorrian%3C%2FA%3E%26nbsp%3B%3A%20Thanks%20John%20!...%26nbsp%3B%20You%20need%20to%20create%20a%20separate%20number%20column%2C%20e.g.%20called%20%5BUID%5D.%20Then%20you%20need%20to%20create%20a%20flow%20that%20will%20fill%20in%20this%20column%20with%20the%20actual%20ID%20value%20like%20HUB123.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreating%20flow%20will%20be%20easy%20and%20fast%20solution%20in%20this%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20I%20have%20answered%20your%20question%2C%20please%20mark%20your%20post%20as%20Solved.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EIf%20you%20like%20my%20response%2C%20please%20give%20it%20a%20Thumbs%20Up.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EAshish%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Morning, Hope someone can help.

 

I created a UID field using the concatenate function with a prefix and then the ID field.

 

=CONCATENATE("HUB",[ID])

 

The issue is the [] round ID disappear each time the page is refreshed and I get a return of HUB for each new entry until I manually reinsert the square brackets round ID.  I remember years ago there was something around the use of [] and () depending on where you were based but I thought that was sorted.  When I use the () it doesn't apply the calculation to new records, but keeps the old records.

 

The other issues that stem from this the flow that emails customers with their UID will just return them HUB.  Any suggestions on what I could try? Or where I'm doing wrong with the concatenate function?

 

Kind Regards

 

John

 

3 Replies
Highlighted

@John Dorrian : Yes, ID column is there in the calculated column selection field, check column setting screenshot, where inside insert column box does not have ID column, ID is not supported in calculated columns and always disappear. When you open list settings, check cal. column settings or change something in formula and hit OK, will be visible (for a while), but later when you add new item, new calculated value field will be blank again.

1) ID is not valid column to use in Calculated Formulas, it is not displayed in the available columns for a reason.

2) It is 0 for newly created items as it get its value from the database after all Formulas are evaluated

3)  You get the correct values (in all existing items) if you re-edit the Formula.

 

Note: Workaround is to use workflow or JavaScript/jQuery.

Highlighted

@Ashish_Kohale 

 

Thanks for the info, and at least I know not to use [ID] for calculated fields.  

 

I think as a workaround I could do just use the ID field as is (sequential number) within the list but for the Flows and automatic emails add the list prefix (in this case HUB) before the ID field so when queries come in to the service desk they know which list and record to look up.

 

I'll look into java and jquery when I have a bit of free time.  Thanks again for the info, every day is a school day :)

 

Kind Regards

 

John

Highlighted
Best Response confirmed by John Dorrian (New Contributor)
Solution

@John Dorrian : Thanks John !...  You need to create a separate number column, e.g. called [UID]. Then you need to create a flow that will fill in this column with the actual ID value like HUB123. 

 

Creating flow will be easy and fast solution in this case.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

 

Regards,

Ashish