cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Create title based on user's initials and a successive number

I am creating a purchase order app.  I need the title of the P.O. to look like this "LOG-AA-0003"  (AA being the user's initials).  I need the numbers to be successive based on the user.  so if John Doe were to start a P.O., his P.O.'s number would be based off of his last entry, not the latest entry (I hope that makes sense).  At any rate, I don't have a whole ton of users that will be using this so I am okay with having to code everyone in if that's how it must be.  This is the code I have so far: 

"LOG-" & If(User().FullName = "Alexander Salamander", "AS", "NO") & "-" 

I have no idea how to get a succesive number.  I have the data sitting on one excel sheet.  One table has the P.O. information like the date and vendor.  The other table is strictly for items.  I did this because people might have multiple items for one P.O.  My idea, in order to keep them under the same P.O., was to have the first table's P.O. number fill in the "title" spot for each item so the P.O remained the same for the items they order on the second table.  

 

Another user suggested this:

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.
 
However, I have no idea where to put the final batch of code.  So I can't use it. 
3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Create title based on user's initials and a successive number

@Salamander94 

If you're still looking for a solution for this one, you might want to consider this formula for your Default property of your DataCard:

If(yourFormName.Mode=FormMode.New,
  "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)) & "-" &
  Text(
      Value(
          Last(
              Split(
                  First(
                      SortByColumns(
                          Filter(yourDataSource, StartsWith(yourPOcolumn,  "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)))), 
                          "Title", 
                          Descending)
                       ).yourPOcolumn, 
                   "-")).Result
            ) +1, "0000"),
 ThisItem.yourPOcolumn)

This should give you the results you are looking for without having to resort to another list to store the last number.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Highlighted
Community Support
Community Support

Re: Create title based on user's initials and a successive number

Hi @Salamander94 ,

I found that you have posted similar thread in this forum, please check my response within the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/Create-an-quot-in-succession-numberic-title-q...

 

Actually, the second formula I provided within your previous thread should be set within the Default property of the Text Input box within the Title Data card in your Edit form.

On your side, 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,    /* <-- Check if you are open a New Form or a Edit form. The succesive number should be generated when you open a NEW Form */
   "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 )

4.JPG

 

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.

View solution in original post

Highlighted
Super User
Super User

Re: Create title based on user's initials and a successive number

@bloodoff 

As the formula I provided is written, yes there would be a delegation issue.  However, it's actually very easily solved.

The reason there is a delegation issue is because of the computation that goes on in the Filter statement (shown in red below).

If(yourFormName.Mode=FormMode.New,
  "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)) & "-" &
  Text(
      Value(
          Last(
              Split(
                  First(
                      SortByColumns(
                          Filter(yourDataSource, StartsWith(yourPOcolumn,  "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)))), 
                          "Title", 
                          Descending)
                       ).yourPOcolumn, 
                   "-")).Result
            ) +1, "0000"),
 ThisItem.yourPOcolumn)

If that part is removed and calculated into a variable (let's say on the OnVisible):

Set(myLog, "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)))

And the Default property on the datacard modified to this:

If(yourFormName.Mode=FormMode.New, 
   "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)) & "-" & 
   Text( 
         Value( 
              Last( 
                   Split( 
                        First( 
                             SortByColumns( 
                                    Filter(yourDataSource, StartsWith(yourPOcolumn, myLog)), 
                                   "Title", 
                                   Descending) 
                             ).yourPOcolumn, 
                      "-")).Result 
                 ) +1, "0000"), 
  ThisItem.yourPOcolumn
)

The delegation issue goes away.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

11 REPLIES 11
Highlighted
Kudo Collector
Kudo Collector

Re: Create title based on user's initials and a successive number

Here is a link how you can do that with SharePoint lists:

https://365basics.com/powerapps-form-in-sharepoint-create-a-configurable-auto-incrementing-column-wi...

The 1st list would be your purchase app.

The 2nd list would have all counters per each user so that each user gets their own incremented number.

Now I do see you are using an Excel file so my reply is not completely spot on; however, I don't see why it wouldn't work with Excel. Take a look either way.

 

Highlighted
Helper IV
Helper IV

Re: Create title based on user's initials and a successive number

Thank you for that.  Unfortunetly I won't be able to use it for my app, but it has jogged my brain a bit!

Highlighted
Super User
Super User

Re: Create title based on user's initials and a successive number

@Salamander94 

If you're still looking for a solution for this one, you might want to consider this formula for your Default property of your DataCard:

If(yourFormName.Mode=FormMode.New,
  "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)) & "-" &
  Text(
      Value(
          Last(
              Split(
                  First(
                      SortByColumns(
                          Filter(yourDataSource, StartsWith(yourPOcolumn,  "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)))), 
                          "Title", 
                          Descending)
                       ).yourPOcolumn, 
                   "-")).Result
            ) +1, "0000"),
 ThisItem.yourPOcolumn)

This should give you the results you are looking for without having to resort to another list to store the last number.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Highlighted
Community Support
Community Support

Re: Create title based on user's initials and a successive number

Hi @Salamander94 ,

I found that you have posted similar thread in this forum, please check my response within the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/Create-an-quot-in-succession-numberic-title-q...

 

Actually, the second formula I provided within your previous thread should be set within the Default property of the Text Input box within the Title Data card in your Edit form.

On your side, 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,    /* <-- Check if you are open a New Form or a Edit form. The succesive number should be generated when you open a NEW Form */
   "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 )

4.JPG

 

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.

View solution in original post

Highlighted
Kudo Collector
Kudo Collector

Re: Create title based on user's initials and a successive number

@RandyHayes Is it not affected by a Delegation limitation?

Highlighted
Super User
Super User

Re: Create title based on user's initials and a successive number

@bloodoff 

As the formula I provided is written, yes there would be a delegation issue.  However, it's actually very easily solved.

The reason there is a delegation issue is because of the computation that goes on in the Filter statement (shown in red below).

If(yourFormName.Mode=FormMode.New,
  "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)) & "-" &
  Text(
      Value(
          Last(
              Split(
                  First(
                      SortByColumns(
                          Filter(yourDataSource, StartsWith(yourPOcolumn,  "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)))), 
                          "Title", 
                          Descending)
                       ).yourPOcolumn, 
                   "-")).Result
            ) +1, "0000"),
 ThisItem.yourPOcolumn)

If that part is removed and calculated into a variable (let's say on the OnVisible):

Set(myLog, "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)))

And the Default property on the datacard modified to this:

If(yourFormName.Mode=FormMode.New, 
   "LOG-" & Concat(Split(User().FullName, " "), Left(Result,1)) & "-" & 
   Text( 
         Value( 
              Last( 
                   Split( 
                        First( 
                             SortByColumns( 
                                    Filter(yourDataSource, StartsWith(yourPOcolumn, myLog)), 
                                   "Title", 
                                   Descending) 
                             ).yourPOcolumn, 
                      "-")).Result 
                 ) +1, "0000"), 
  ThisItem.yourPOcolumn
)

The delegation issue goes away.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Highlighted
Kudo Collector
Kudo Collector

Re: Create title based on user's initials and a successive number

@RandyHayes Yeah, I know, I agree. I just thought it would be good to know for the topic starter.

Your updated approach will work for sure considering each user will have their own counter/incremented number (per topic starter).

I don't think it's gonna work well if it's a shared for all counter since that myLog variable might get the same value when several people work at once.

Highlighted
Super User
Super User

Re: Create title based on user's initials and a successive number

@bloodoff 

The variable is not shared across apps and users.  It is unique to each user, so there would be no impact from that.  It is completely independent. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Kudo Collector
Kudo Collector

Re: Create title based on user's initials and a successive number

@RandyHayes I meant if you have a unified numeration (e.g. 1,2,3,4,5... or ticket-1,ticket-2,ticket-3...) then that approach wouldn't work as there shouldn't be duplicates. But there would be as the variable is calculated onVisible (not when saved).

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (8,830)