cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CT1119
Frequent Visitor

Custom number formatting and auto-incrementing

Hi all, 

 

Was hoping I could get help with a scenario. 

Background:

  • Data is coming from a SharePoint list. 
  • Column getting data from is a "Single line of text" type
  • Current data in the list was all manually entered, so no application formatting previously applied. 

Scenario:

I have Ticket #s in the format of 22-063001, 22-050503, 19-101112, etc. Where it represents the current 2 digit year, dash, 2 digit month, 2 digit day, and finally 2 digit count of tickets for the day - YY-MMDD##. Each day the last 2 digits should start over at 01. The code should also look for the last incremented number and add 1 (i.e. if the last ticket is 22-063005, in the "New ticket" view the ticket # should appear as 22-063006).

 

After many articles, forum posts, and videos I have not been able to answer a couple things. I feel like I am getting close but can't finish it. 

Questions:

  1. How can I get the last 2 digits (the # of tickets in a day) to have a 0 in front when it is 01-09? I am able to get a number to display but I only can seem to get 22-06301. I need the 01. When it gets to 10 and beyond it'll be fine. So, I do NOT want to arbitrarily add a 0 between the day of the month and the # of the day because I do not want to possibly have ticket #s with more digits than the others (getting to 100+ is highly unlikely in my scenario). So i do not want to end up with 22-0630017.
  2. Why can I not get a new number to show up on my "new ticket" form when the code is within the if statement? I can get a number to show up when I take the True expression (see code below) out of the if and have only it running. I get the 22-06301 type number.
  3. What can I add to tell it to start over at 01 (as the total for the day) for each new day of the month? 

Here's the code I've been trying to work with. It is placed in the Default value of the Text Field within the Datacard for that field. 

if(frmNewIntake.Mode-FormMode.New, Text(Now(), "yy")&"-"&Text(Now(), "mmdd")&First(Sort(AddColumns('Ticket Tracker',"NewTicketIncrement", Trim(Substitute('Ticket #',Text(Now(), "yy")&"-"&Text(Now(), "mmdd"),""))*1),NewTicketIncrement, Descending)).NewTicketIncrement+1, Parent.Default)

 Thanks!! 

1 ACCEPTED SOLUTION

Accepted Solutions
Sunil-Narnaware
Solution Supplier
Solution Supplier

@CT1119 

Here is my solution

1. Do Table Reshaping In Order to Get Year, Month, Date & Ticket #.. This is required because ticket# is stored in single line of text and we need to check values

 

 

 

ClearCollect(
    colTicketTracker,
    SortByColumns(
        AddColumns(
            ShowColumns(
                TicketTracker,
                "NewTicketIncrement"
            ),
            "Ticket Month",
            Value(
                Left(
                    Right(
                        NewTicketIncrement,
                        6
                    ),
                    2
                )
            ),
            "Ticket Year",
            Value(
                Left (
                    NewTicketIncrement,
                    2
                )
            ),
            "Ticket Date",
            Value(
                Left(
                    Right(
                        Right(
                            NewTicketIncrement,
                            6
                        ),
                        4
                    ),
                    2
                )
            ),
            "Ticket #",
            Value(
                Right (
                    NewTicketIncrement,
                    2
                )
            )
        ),
        "Ticket Year",
        Ascending,
        "Ticket Month",
        Ascending,
        "Ticket Date",
        Ascending,
        "Ticket #",
        Ascending
    )
)

 

 

 

2. Once the table reshaping is done.. we need to get the ticket #.. now as you said.. each day it should start with 01 and if there are already tickets there for that day.. then we need to get the latest ticket # and add 1 to it.. in below code, I am getting that next/first ticket number

 

 

 

With(
    {
        TodaysDate: Day(Today()),
        CurrentMonth: Month(Today()),
        CurrentYear: Year(Today()),
        LastTwoDigitsOfCurrentYear: Right(
            Year(Today()),
            2
        ),
        nextTicketNumber: Coalesce(
            Sum(
                Last(
                    Filter(
                        colTicketTracker,
                        'Ticket Year' = Value(
                            Right(
                                Year(Today()),
                                2
                            )
                        ),
                        'Ticket Month' = Value(Month(Today())),
                        'Ticket Date' = Value(Day(Today()))
                    )
                ).'Ticket #',
                1
            ),
            Text(
                1,
                "0#"
            )
        )
    },
    Set(
        varNextNumber,
        Concatenate(
            LastTwoDigitsOfCurrentYear,
            "-",
            Text(
                CurrentMonth,
                "0#"
            ),
            Text(
                TodaysDate,
                "0#"
            ),
            Text(
                nextTicketNumber,
                "0#"
            )
        )
    )
)

 

 

 

3. the global variable varNextNumber in above function holds your next/first ticket number.. then you can use this variable while creating the ticket record in sharepoint list.. now make sure once you create a new record, you will have to refresh your collection mentioned in step 1, so after patching the record, you can once again, you can call the step 1 code.. 

Pheww... this was interesting.. try it out.. and let me know.. how it goes..

 

You can further optimize it..
 
Here are screengrabs in succession

SunilNarnaware_0-1656629014447.png

SunilNarnaware_1-1656629068244.png

 


========================
If this helps you resolve your problem, 'Accept it as Solution' so others can also benefit from it. If you like, hit 👍

View solution in original post

15 REPLIES 15
Sunil-Narnaware
Solution Supplier
Solution Supplier

@CT1119 for leading zero for single digit numbers, you can try this Text(8,"0#") replace 8 with appropriate field name

Sunil-Narnaware
Solution Supplier
Solution Supplier

@CT1119 

Here is my solution

1. Do Table Reshaping In Order to Get Year, Month, Date & Ticket #.. This is required because ticket# is stored in single line of text and we need to check values

 

 

 

ClearCollect(
    colTicketTracker,
    SortByColumns(
        AddColumns(
            ShowColumns(
                TicketTracker,
                "NewTicketIncrement"
            ),
            "Ticket Month",
            Value(
                Left(
                    Right(
                        NewTicketIncrement,
                        6
                    ),
                    2
                )
            ),
            "Ticket Year",
            Value(
                Left (
                    NewTicketIncrement,
                    2
                )
            ),
            "Ticket Date",
            Value(
                Left(
                    Right(
                        Right(
                            NewTicketIncrement,
                            6
                        ),
                        4
                    ),
                    2
                )
            ),
            "Ticket #",
            Value(
                Right (
                    NewTicketIncrement,
                    2
                )
            )
        ),
        "Ticket Year",
        Ascending,
        "Ticket Month",
        Ascending,
        "Ticket Date",
        Ascending,
        "Ticket #",
        Ascending
    )
)

 

 

 

2. Once the table reshaping is done.. we need to get the ticket #.. now as you said.. each day it should start with 01 and if there are already tickets there for that day.. then we need to get the latest ticket # and add 1 to it.. in below code, I am getting that next/first ticket number

 

 

 

With(
    {
        TodaysDate: Day(Today()),
        CurrentMonth: Month(Today()),
        CurrentYear: Year(Today()),
        LastTwoDigitsOfCurrentYear: Right(
            Year(Today()),
            2
        ),
        nextTicketNumber: Coalesce(
            Sum(
                Last(
                    Filter(
                        colTicketTracker,
                        'Ticket Year' = Value(
                            Right(
                                Year(Today()),
                                2
                            )
                        ),
                        'Ticket Month' = Value(Month(Today())),
                        'Ticket Date' = Value(Day(Today()))
                    )
                ).'Ticket #',
                1
            ),
            Text(
                1,
                "0#"
            )
        )
    },
    Set(
        varNextNumber,
        Concatenate(
            LastTwoDigitsOfCurrentYear,
            "-",
            Text(
                CurrentMonth,
                "0#"
            ),
            Text(
                TodaysDate,
                "0#"
            ),
            Text(
                nextTicketNumber,
                "0#"
            )
        )
    )
)

 

 

 

3. the global variable varNextNumber in above function holds your next/first ticket number.. then you can use this variable while creating the ticket record in sharepoint list.. now make sure once you create a new record, you will have to refresh your collection mentioned in step 1, so after patching the record, you can once again, you can call the step 1 code.. 

Pheww... this was interesting.. try it out.. and let me know.. how it goes..

 

You can further optimize it..
 
Here are screengrabs in succession

SunilNarnaware_0-1656629014447.png

SunilNarnaware_1-1656629068244.png

 


========================
If this helps you resolve your problem, 'Accept it as Solution' so others can also benefit from it. If you like, hit 👍

CT1119
Frequent Visitor

Thank you so much for responding and the effort! Will give it a try and see what happens. I'm new to PowerApps and trying to wrap my brain around the coding structure so some of the things in the code I'm going to read up on to understand exactly what its doing. Think I get most of it. Will have to figure out the patch bit, but i've seen it used in articles so seems doable. 

I have a couple other requirements related to these ticket #s that I think will be tricky too. But I thought figuring out the unique ticket # was the first step. Just for fun, the other scenarios include:

  1. Each of these ticket numbers relates to a specific type of item. Let's say you're going to Target and you need socks, t-shirt, and sandals. The ticket # as originally worked on represents either the socks, t-shirt, or sandals. Target is just one store. You may have multiple stores to go to. So, in a sense, Target is the group identifier. So I have another ID # already that represents that group of items. And then the ticket # previously worked on represents one of those items in the group. I would like to be able to:
    1. add those additional items to the group - each item would be a new row in the SP list with its ticket # and the same group ID
    2. add them within the same form so that i can see all the information about the group at one time. Each item will have its ticket #, type, and status.
    3. All the other info is just group info like POC and attachments, that would also be copied into each row in the SP list.
    4. Ex. Group ID: 22-063001; Item IDs 22-063001, 22-063002, 22-063003. Next group id is 22-063002.

Once i try out what you helped with for ticket #, i should be able to reuse that for getting new group IDs. 

@Sunil-Narnaware  

Looks like my first challenge is figuring out why ShowColumns doesn't want to recognize my column names. I get an error that my column doesn't exist. The name in your example was something i used when I tried doing AddColumns, so I thought I had to use a column name that is actually in the SP table. I also think I have had issues throughout the app because the column name in SP is Ticket # so I went to SP and changed the column name to TicketID hoping that will overall make the app happier. Seems it didn't like the space or the # a lot. But anyway even after changing the name in SP and going back to the app and updating the code, the ShowColumns function does not think it exists. Almost seems like it doesn't want a Single Line of Text column.  Just to see if it would recognize any column names, I picked another one that is only one word and text but is Multiple Lines of Text type ("Notes") and it didn't throw an error.

 

UPDATE: I got that working by locating the "internal name" of the column... which I thought I had already tried... 

Now working through getting the number to show up in the text field.... Form is in FormMode.New so not sure why it isn't working. Always something. 

If(frmTicketDetails.Mode-FormMode.New, varNextNumber, Parent.Default)
Sunil-Narnaware
Solution Supplier
Solution Supplier

@CT1119 Power Apps works differently when working with SharePoint column names.. even if you change it won't take it.. Refer this guide when it comes to working with SharePoint list The Ten Commandments of SharePoint in PowerApps. - Power Platform Community (microsoft.com)

 

You can find the information about column name and all in above guide..

Sunil-Narnaware
Solution Supplier
Solution Supplier

@CT1119  I see - instead of = while doing comparison.. 

update your if formula..

 

frmTicketDetails.Mode = FormMode.New

 

 

 

 

@Sunil-Narnaware 

Thanks for all your help!!! I marked this as the solution because clearly it is :), I got the numbers displaying and such. The last piece I'm trying to work out is refreshing the collection. I put the collection code from #1 in my submit button, thinking that would refresh it, but when i go to New Ticket i'm seeing a ticket # i know i just entered successfully.  

 

Do I literally have to write a patch? I guess i thought by using the SubmitForm function I was accomplishing the same thing as patch, since I thought patch was used to update content.  I also tried adding a refresh of the data source to the submit button (below). 

 

SubmitForm(frmTicketDetails);
Refresh('Ticket Tracker');
ClearCollect(
    colEvalTracker,
    SortByColumns(
        AddColumns(
            ShowColumns(
               'Ticket Tracker',
               "Ticket_x0020__x0023_"
            ),
            "Ticket Month",
            Value(
                Left(
                    Right(
                        Ticket_x0020__x0023_,
                        6
                    ),
                    2
                )
            ),
            "Ticket Year",
            Value(
                Left (
                    Ticket_x0020__x0023_,
                    2
                )
            ),
            "Ticket Date",
            Value(
                Left(
                    Right(
                        Right(
                            Ticket_x0020__x0023_,
                            6
                        ),
                        4
                    ),
                    2
                )
            ),
            "Ticket ID",
            Value(
                Right (
                    Ticket_x0020__x0023_,
                    2
                )
            )
        ),
        "Ticket Year",
        Ascending,
        "Ticket Month",
        Ascending,
        "Ticket Date",
        Ascending,
        "Ticket ID",
        Ascending
    )
)

 

 

Sunil-Narnaware
Solution Supplier
Solution Supplier

@CT1119 Well refreshing collection is necessary because we need to do table reshaping on latest ticket numbers.. which you got correct as well.. 

 

You can use submit Form.. my recommendation would be put this refresh collection code under OnSuccess property of Form.. so that whenever the ticket # is submitted successfully.. the collection would be refreshed..

 

and by the way you don't need to call Refresh(Ticket Tracker).. because when you call Submit Form(in the back end it is nothing but Patch only.. you can check this using Monitor Tool)..it automatically refreshes the data source in your Power App.. so no need to call explicitly..

 

And about Patch.. you can use it create new records too.. the middle parameter when set to Defaults (Data Source) tells data source that we are creating a new Record.. if it has Lookup or  ThisRecord then it knows we are updating..

 

Try the refresh collection method OnSuccess Property and let me know how it goes..

I am glad that I could help you..🙂🙂

@Sunil-Narnaware 

There must be something I am not catching on to. I do want to understand it. I moved the ClearCollect portion to the OnSuccess. I created a 'new ticket' and then went back to the new ticket form again and same end number was visible. The YY-MMDD part is working but the last 2 digits didn't change. Is that because I didn't add a Patch to the OnSuccess? 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,324)