Generate a new drawing number when the user selects "Yes" on the above field

[Matthey]

I have a SharePoint List to track Drawings. When the user wants to add a new entry, one field ask the user if this will be a new drawing. If the user selects "Yes", I want a new drawing number generated based on the date with a unique number at the end of the drawing number (e.g. YY### where the YY is the last 2 digits of the current year and ### is the unique number starting at 001). This drawing number should increment for each additional drawings created and when the next year occurs the number should re-start back to 001. The first drawing number for 2022, the file name generated should be 22001.  

I am fairly new to SharePoint and SharePoint Power Apps. Thanks in advance.

[WarrenBelz · Accepted solution]

@Matthey ,

This is far from straight-forward, but I felt like some code for 2022 - this will allow for up to 1,000 numbers- you will have to replace the List and Field names with your own

With(
   {
      wYear: 
      With(
         {
            wList: 
            Sort(
               YourSPList,
               ID,
               Descending
            )
         },
         LookUp(
            wList,
            Left(
               YourDrawingNo,
               2
            ) = 
            Text(
               Today(),
               "yy"
            )
         ).YourDrawingNo
      )
   },
   Text(
      Today(),
      "yy"
   ) & 
   If(
      IsBlank(wYear),
      "001",
      With(
         {
            wNo: 
            Value(
               Right(
                  wYear,
                  3
               )
            ) + 1
         },
         If(
            wNo < 10,
            "00",
            wNo < 100,
            "0"
         ) & wNo
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Matthey]

Originally my thoughts were going along these lines:

I was going to add the line below to the DataCardValue2 (DataCardValue2 is under the Drawing Number field)

Default=If(DataCardValue3.Selected.Value="Yes", "DataCardValue2.Text,Text(Now())")

DataCardValue3.Selected.Value is the field for “New Drawing” were the users has to select either a “Yes” or “No”

Your Programming above is beyond my skill set.

If you could help me get this line to work that reads "DataCardValue2.Text,Text(Now())") , I was hoping to get this line to just enter the current date and then I could expand this to determine the 001,002,003,etc part of the line. But even the current line I entered does not seem to work, there are errors in this line I can not figure out. 

[WarrenBelz]

Hi @Matthey ,

The easy part is actually to determine if it is a new year (something starting with the current year exists), but if is exists you then have to: -

• Find the latest one.
• Strip the last three characters and turn it into a number (the field is Text).
• Add one to this number.
• Add whatever leading zeros are required (2, 1 or none depending on the number size).
• put the year back in front.

The code I provided works (I tested it on a model) - I will run through what it does: -

• The first With() statement sorts your list with the latest record on top and then looks for the first record with the current year in the first two characters.
• The numbering now starts - first the current year (which applies all the time).
• It then looks for the existence of a record in the With() statement (the result is not blank).
• If it is blank (no record found), the number is "001".
• If found, the second With() statement converts the last three characters of this field (which is the latest number) into a number and adds 1.
• The bottom piece then adds the appropriate number of zeros to the front of the number.

Your post is not clear to me what you actually want to do instead of this.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Matthey]

I grateful for your help @WarrenBelz . I was just wondering since the Field "New Drawing?" is a choice field with either a "Yes" or "No" response don't I need an "If" statement to start this code. I do not want the code to run if the user selects "No". That is why I started with this: Default=If(DataCardValue3.Selected.Value="Yes", "DataCardValue2.Text,Text(Now())") So if the user selects "Yes" to the Field "New Drawing" where do I place you code from above?

[WarrenBelz]

Hi @Matthey ,

Just put the around the outside

If(
   DataCardValue3.Selected.Value="Yes",
   . . .Code in here . . .
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Matthey]

This is what I entered but I am receiving Errors. Is there a way to print the errors or save them in a file?

If(

   DataCardValue3.Selected.Value="Yes",

   With(

   {

      wYear:

      With(

         {

            wList:

            Sort(

               YourSPList,

               ID,

               Descending

            )

         },

         LookUp(

            wList,

            Left(

               YourDrawingNo,

               2

            ) =

            Text(

               Today(),

               "yy"

            )

         ).YourDrawingNo

      )

   },

   Text(

      Today(),

      "yy"

   ) &

   If(

      IsBlank(wYear),

      "001",

      With(

         {

            wNo:

            Value(

               Right(

                  wYear,

                  3

               )

            ) + 1

         },

         If(

            wNo < 10,

            "00",

            wNo < 100,

            "0"

         ) & wNo

      )

   )

)

)

[WarrenBelz]

@Matthey ,

You need to replace anything with Your in front of it with your actual List or field name. I can assure you the code is valid if your references are.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps