cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Salamander94
Level: Powered On

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Super User
Super User

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

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-)  

Community Support Team
Community Support Team

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

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:3.JPG

 

4.JPG

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Salamander94
Level: Powered On

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

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.

Community Support Team
Community Support Team

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

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User
Super User

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

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
Level: Powered On

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

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
Level: Powered On

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

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. 

Community Support Team
Community Support Team

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

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 457 members 5,508 guests
Please welcome our newest community members: