Create an "in-succession numberic title" based on user

[Salamander94]

So I'm trying to create a purchase order app.  Ideally my company wants the title to be made from the users initials then a successive number as well as the static department it's for.  As an example if my name is Bob Ross, it should look like LOG-BR-0001.  The next time Bob Ross comes in it should look like LOG-BR-0002.  Here's the catch, if Tom Hanks comes along, his title and succession should be all their own.  So even if Bob Ross is on LOG-BR-0550, Tom Hanks should have his own number succession seperate from Bob Ross' like LOG-BR-0005. 

I hope this made sense and thanks for your time!

[LRVinNC]

I presume Tom Hanks would have his own initials in the id, correct like LOG-TH-0004 vs Bob Ross' LOG-BR-0050.

If it were me, I would just create a simple table in SP, Excel, or whatever data source you are using with Name and last number used.  Then you could just query by name, get the record, increment the number in that file and copy it to use on the PO.  

You could probably also do a query of the data source (syntax will depend on the source) looking for the max value that starts with the combination for your individual (such as LOG-TH-)  

[v-xida-msft]

Hi @Salamander94 ,

Could you please share a bit more about the data source you used in your app? Is it a SP List?

Could you please share a bit more about the data structure of your data source?

Further, do you want to generate the Title field based on the users initials then a successive number?

Based on the needs that you mentioned, I have made a test on my side, please take a try with the following workaround:

Unlock the Title Data card within the Edit form, set the Default property of the Text Input box to following:

"LOG-"&
Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) & "-" &
If(
    Len(Text(Value(Last(Split(
      Last(Filter('20190611_case8', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1))=1,
    "000"&Value(Last(Split(Last(Filter('20190611_case8', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1,
    Len(Text(Value(Last(Split(
      Last(Filter('20190611_case8', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1))=2,
    "00"&Value(Last(Split(Last(Filter('20190611_case8', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1,
    Len(Text(Value(Last(Split(
      Last(Filter('20190611_case8', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1))=3,
   "0"&Value(Last(Split(Last(Filter('20190611_case8', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1,
   Value(Last(Split(Last(Filter('20190611_case8', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1
)

Note: The CurrentUser is a variable in my app, which used to store the Display Name of the current sign in user. The '20190611_case8' represents my SP List data source.

On your side, you should type:

If(
   EditForm1.Mode = FormMode.New,
   "LOG-"& 
   Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) & "-" & 
   If(
      Len(Text(Value(Last(Split(Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 1, 
      "000"&Value(Last(Split(Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Len(Text(Value(Last(Split( Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 2, 
      "00"&Value(Last(Split(Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Len(Text(Value(Last(Split( Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 3, 
     "0"&Value(Last(Split(Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Value(Last(Split(Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1 
   ),
   Parent.Default
)

Note: The Title column represents the Title column in your data source, which used to store the generated title value.

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[Salamander94]

So yes I do want the title to be based on the users initials then a successive number.  The information will be sitting in a sharepoint list.  I am currently trying to figure out how to set them up as a parent child list, but am struggling with that.  At any rate, ideally, the po's will have their own list with date and vendor information.  The child list will involve the actual parts being ordered and costs/quantity.

[v-xida-msft]

Hi @Salamander94 ,

Have you taken a try with the solution I provided above?

Based on the needs that you mentioned, I think the solution I provided could achieve your needs. Please consider take a try with the solution I provided above, then check if the issue is solved.

Best regards,

[PaulD1]

This might be derailing the topic slightly, but...

Using 'meaningful' identifiers such as LOG-BR-0550 can lead to issues later down the line. Often it is better to just use an incrementing (or even random) number for the PO with the other details (person and department) being held in meta-data.

• What if Tom Hanks takes over one or more POs started by Bob Ross? Should you change the existing 'BR' IDs? And if so, what about existing references to that ID if you change it?
• What if the PO starts out for logistics (LOG) but later it is decided it relates to a different department?
• 0550 implies there is a 0549, but what if that PO was cancelled/deleted/never used (record started but not completed)? People may assume 550 POs have been created by BR for LOG but that may not be the case.

[Salamander94]

If(
   EditForm1.Mode = FormMode.New,
   "LOG-"& 
   Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) & "-" & 
   If(
      Len(Text(Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 1, 
      "000"&Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Len(Text(Value(Last(Split( Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 2, 
      "00"&Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Len(Text(Value(Last(Split( Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 3, 
     "0"&Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1 
   ),
   Parent.Default
)

Maybe I didn't completely understand, but this is what I should have for the default text property in my title card?  Also I have moved data to excel.  I'm not sure if this changes anything.

[Salamander94]

Okay so I figured that this is what I should have for the default of my "card: Title"

"LOG-"&
Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) & "-" &
If(
    Len(Text(Value(Last(Split(
      Last(Filter('PO', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1))=1,
    "000"&Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1,
    Len(Text(Value(Last(Split(
      Last(Filter('PO', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1))=2,
    "00"&Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1,
    Len(Text(Value(Last(Split(
      Last(Filter('PO', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1))=3,
   "0"&Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1,
   Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(CurrentUser," ")).Result,1) & Left(Last(Split(CurrentUser," ")).Result,1) in Title)).Title,
      "-"
)).Result)+1
)

And then where excatly does this section go?

If(
   EditForm1.Mode = FormMode.New,
   "LOG-"& 
   Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) & "-" & 
   If(
      Len(Text(Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 1, 
      "000"&Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Len(Text(Value(Last(Split( Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 2, 
      "00"&Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Len(Text(Value(Last(Split( Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 3, 
     "0"&Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Value(Last(Split(Last(Filter('PO', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1 
   ),
   Parent.Default
)

I tried a couple different places but I error out. 

[v-xida-msft · Accepted solution]

Hi @Salamander94 ,

Please set the Default property of the Text Input box within the Title data card in your Edit form to following:

If(
   EditForm1.Mode = FormMode.New,
   "LOG-"& 
   Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) & "-" & 
   If(
      Len(Text(Value(Last(Split(Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 1, 
      "000"&Value(Last(Split(Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Len(Text(Value(Last(Split( Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 2, 
      "00"&Value(Last(Split(Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Len(Text(Value(Last(Split( Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result)+1)) = 3, 
     "0"&Value(Last(Split(Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1, 
      Value(Last(Split(Last(Filter('YourDataSource', "LOG-"&Left(First(Split(User().FullName," ")).Result,1) & Left(Last(Split(User().FullName," ")).Result,1) in Title)).Title, "-" )).Result) + 1 
   ),
   Parent.Default
)

Please consider take a try with above formula, then check if the issue s solved.

Best regards,