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

Any ways to simplify the code?

Hi, 

 

I am a newbie in PowerApps

 

I would like to generate a new reference number with format

  • 1st ref number of a day> "DC-<YYYY/MM/DD>" & 01. e.g. DC-2019092301
  • 2nd ref number of a day> "DC-<YYYY/MM/DD>" & 02. e.g. DC-2019092302
  • 3rd ref number of a day> "DC-<YYYY/MM/DD>" & 03. e.g. DC-2019092303

and so on...

 

My code logic is as follow:

for example, if today is 20190923

  1. Firstly, create today's string DC-20190923
  2. Secondly, create reference number of today

IF today's referecne number "20190923" is less than 10 THEN
   add a prfix "0" & (select the largest reference number "20190923" from current data source, then + 1)
ELSE
   (select the largest reference number "20190923" from current data source, then + 1)

 

Below is the code, but some codes are being used repeatly.

UpdateContext({Status:"Pending for applicant info",ML_x0020_reference_x0020_number:Text("DC-"&Year(Today())&If(Month(Today())<10,"0")&Month(Today())&If(Day(Today())<10,"0")&Day(Today())&If(Right(First(SortByColumns(Search(Table2,Text("DC-"&Year(Today())&If(Month(Today())<10,"0")&Month(Today())&If(Day(Today())<10,"0")&Day(Today())),"ML_x0020_reference_x0020_number"),"ML_x0020_reference_x0020_number",Descending)).'ML reference number',2)+1<10,"0"&Right(First(SortByColumns(Search(Table2,Text("DC-"&Year(Today())&If(Month(Today())<10,"0")&Month(Today())&If(Day(Today())<10,"0")&Day(Today())),"ML_x0020_reference_x0020_number"),"ML_x0020_reference_x0020_number",Descending)).'ML reference number',2)+1,Right(First(SortByColumns(Search(Table2,Text("DC-"&Year(Today())&If(Month(Today())<10,"0")&Month(Today())&If(Day(Today())<10,"0")&Day(Today())),"ML_x0020_reference_x0020_number"),"ML_x0020_reference_x0020_number",Descending)).'ML reference number',2)+1))})

 

  • Example #1. It has been used for 4 times
    Year(Today())&If(Month(Today())<10,"0")&Month(Today())&If(Day(Today())<10,"0")&Day(Today())
  • Example #2. It has been used for 3 times
    Right(First(SortByColumns(Search(Table2,Text("DC-"&Year(Today())&If(Month(Today())<10,"0")&Month(Today())&If(Day(Today())<10,"0")&Day(Today())),"ML_x0020_reference_x0020_number"),"ML_x0020_reference_x0020_number",Descending)

Q3a.PNG

 

In traditional programming, I can create a variable to store the STRING "DC-20190923". Then, recall the variable if required

 

Therefore, anybody knows any way to make it simple?

Thanks in advance

 

Sam

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft RusselThomas
Microsoft

Re: Any ways to simplify the code?

Hi @samsam ,

 

Firstly, let me say I think there are better ways of keeping references and unique ID's Smiley Wink
If you explain a bit more about what you're using the reference for and how, we can perhaps explore other options, but for now I'm going to assume you need this particular format for some reason and try and answer the actual question - with the caveat that "simpler" is a relative term Smiley LOL

  • You can use the Text() function to avoid having to figure out all the leading 0's....and in case you end up with over 100 entries you may want to add another 0 just to be safe (or two or three if you're really worried about the number of references in a day you might get).
  • Because the reference index is part of the actual reference text, you're still stuck with some string manipulation to get the last reference (changing this would be part of exploring other options)

By way of example, add a button to the screen, and set it's OnSelect() property as follows;

Collect(collectRefs, {
    ref: "DC-" & Text(Today(), "[$-en-US]yyymmdd") & //The first part of our ref - the following two options set up the index
        If(IsEmpty(Filter(collectRefs, StartsWith(ref, "DC-" & Text(Today(), "[$-en-US]yyymmdd")))), "001", //First for the day
        Text(Value(Right(Last(SortByColumns(Filter(collectRefs, StartsWith(ref, "DC-" & Text(Today(), "[$-en-US]yyymmdd"))), "ref", Ascending)).ref, 3) + 1), "[$-en-US]000"))}) //all the rest

You can pop in a gallery showing collectrefs with the ref column in a label to see what's happening as you do it.  if relevant, you may also want to pop a delete icon into the gallery to make sure it behaves the way you expect if references get deleted.  (This method will always add 1 to the highest reference, regardless of whether references 'in between' have been removed)

Hope this helps,


RT

 

View solution in original post

2 REPLIES 2
Microsoft RusselThomas
Microsoft

Re: Any ways to simplify the code?

Hi @samsam ,

 

Firstly, let me say I think there are better ways of keeping references and unique ID's Smiley Wink
If you explain a bit more about what you're using the reference for and how, we can perhaps explore other options, but for now I'm going to assume you need this particular format for some reason and try and answer the actual question - with the caveat that "simpler" is a relative term Smiley LOL

  • You can use the Text() function to avoid having to figure out all the leading 0's....and in case you end up with over 100 entries you may want to add another 0 just to be safe (or two or three if you're really worried about the number of references in a day you might get).
  • Because the reference index is part of the actual reference text, you're still stuck with some string manipulation to get the last reference (changing this would be part of exploring other options)

By way of example, add a button to the screen, and set it's OnSelect() property as follows;

Collect(collectRefs, {
    ref: "DC-" & Text(Today(), "[$-en-US]yyymmdd") & //The first part of our ref - the following two options set up the index
        If(IsEmpty(Filter(collectRefs, StartsWith(ref, "DC-" & Text(Today(), "[$-en-US]yyymmdd")))), "001", //First for the day
        Text(Value(Right(Last(SortByColumns(Filter(collectRefs, StartsWith(ref, "DC-" & Text(Today(), "[$-en-US]yyymmdd"))), "ref", Ascending)).ref, 3) + 1), "[$-en-US]000"))}) //all the rest

You can pop in a gallery showing collectrefs with the ref column in a label to see what's happening as you do it.  if relevant, you may also want to pop a delete icon into the gallery to make sure it behaves the way you expect if references get deleted.  (This method will always add 1 to the highest reference, regardless of whether references 'in between' have been removed)

Hope this helps,


RT

 

View solution in original post

samsam
Level: Powered On

Re: Any ways to simplify the code?

Hi RT, 


Thanks for the reply. I have re-written the code, that is based on your concept. 

The code is shorter now. Smiley Very Happy Lesson learnt on today is >>> Text() function, and Value() function

 

UpdateContext({Status:"Pending for approval",ML_x0020_reference_x0020_number:Text("DC-")&Text(Today(),"[$-en-US]yyymmdd")& Text(Value(Right(First(SortByColumns(Search(Table2,Text("DC-")&Text(Today(),"[$-en-US]yyymmdd"),"ML_x0020_reference_x0020_number"),"ML_x0020_reference_x0020_number",Descending)).'ML reference number',2)+1),"[$-en-US]00")})

 

Thanks a lot 🙂

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

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