cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
calvares
Impactful Individual
Impactful Individual

Sequencing using letters and numbers

I have a requirement to create a form where a job number follows on from the last one in sequential order.

However the job numbers are two letters and a number up to 99 e.g. AB99 would then become AC01.

I know there is a sequence function but I'm not sure it can do anything this complex. Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
rubin_boer
Super User
Super User

hi @calvares 

 

You can play with this (im lazy i think - friday you know):

you have 4 character (string|string|int|int)

the first two can be counted with base 64 for upper case letter ok

the last tow as integers

 

Set(varCounter, 1299)

Add a label and set it to the below

 

With(
    {
        _base: Split(varCounter,"")
    },
    Char(64 + Index(_base,1).Result) & 
    Char(64 + Index(_base,2).Result) & 
    Index(_base,3).Result & 
    Index(_base,4).Result
)

 

response:

Char(64 + Index(_base, 1).Result) = A

Char(64 + Index(_base, 2).Result) = B

Index(_base, 3).Result = 9

Index(_base, 4).Result = 9

rubin_boer_0-1654872662030.png

When you add 1 it becomes AC00 (you can add how to go to AC01 im sure)

and as you add it will just increase.

Example:

Peek 2022-06-10 16-57.gif

 

 

Hope it helps,

R

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

View solution in original post

7 REPLIES 7
rubin_boer
Super User
Super User

hi @calvares 

 

You can play with this (im lazy i think - friday you know):

you have 4 character (string|string|int|int)

the first two can be counted with base 64 for upper case letter ok

the last tow as integers

 

Set(varCounter, 1299)

Add a label and set it to the below

 

With(
    {
        _base: Split(varCounter,"")
    },
    Char(64 + Index(_base,1).Result) & 
    Char(64 + Index(_base,2).Result) & 
    Index(_base,3).Result & 
    Index(_base,4).Result
)

 

response:

Char(64 + Index(_base, 1).Result) = A

Char(64 + Index(_base, 2).Result) = B

Index(_base, 3).Result = 9

Index(_base, 4).Result = 9

rubin_boer_0-1654872662030.png

When you add 1 it becomes AC00 (you can add how to go to AC01 im sure)

and as you add it will just increase.

Example:

Peek 2022-06-10 16-57.gif

 

 

Hope it helps,

R

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
calvares
Impactful Individual
Impactful Individual

This looks amazing @rubin_boer . Not at all lazy...I will have a play and let you know how it turns out. Thank you.

calvares
Impactful Individual
Impactful Individual

@rubin_boer this has been a great starting point thanks. I've familiarised myself with ASCII and have had a play around. However, I haven't worked out how to convert back from my job number e.g. AB99 to 1299.  I can see how to do it using the full 255 character code but not starting from 64. I'll be taking the value from the last job number listed.

Any ideas how to do this?

rubin_boer
Super User
Super User

hi @calvares 

 

here is a way:

 

this code can be used where you wish and in this example i use a button. Coalesce(Index(_getChars, 2).number,Index(_getChars, 1).number) is used for when the two characters are the same in which case the filter will return 1 row

UpdateContext({jobnumber: "AB99"});
UpdateContext({convertedJobnumber: 
    With(
        {
            _getNums: Split(jobnumber,""),
            _getChars: 
                Filter(
                    ForAll(
                    Sequence(26),
                    {
                        number: Value,
                        character: Char(Value + 64)
                    }
                ),
                character in Left(jobnumber,2)
            )
        }   
    ,
        Index(_getChars, 1).number & Coalesce(Index(_getChars, 2).number,Index(_getChars, 1).number) & Index(_getNums, 3).Result & Index(_getNums, 4).Result 
    )
    }
)

 

Result (i used the text input to set that variable jobnumber)

Peek 2022-06-14 16-59.gif

Hope it helps,

R

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
calvares
Impactful Individual
Impactful Individual

Thank you...that works!

calvares
Impactful Individual
Impactful Individual

Right so I had to make a couple of tweaks because the length of varCounter may be 5 or 6 characters depending on the letters e.g. JJ27 would be 101027. This is my adapted code where varNewJob replaces varCounter and varLength is Len(varNewJob):

Switch(varLength, 4,
With(
    {
        _base: Split(varNewJob,"")
    },
    Char(64 + Index(_base,1).Result) & 
    Char(64 + Index(_base,2).Result) & 
    Index(_base,3).Result & 
    Index(_base,4).Result
), 5,
With({_base: varNewJob} ,
Char(64 + Left(_base, 1)) &
    Char(64 + Mid(_base, 2, 2)) &
    Right(_base, 2)
),
6,
With({_base: varNewJob} ,
Char(64 + Left(_base, 2)) &
    Char(64 + Mid(_base, 3, 2)) &
    Right(_base, 2)
)
)

 Also to skip to '01' from '99' I used this code:

If(Right(varConvertedJob, 2) = "99", Set(varNewJob, varConvertedJob +2), Set(varNewJob, varConvertedJob +1))

Seems to be working well. Thanks again @rubin_boer .

rubin_boer
Super User
Super User

sweet stuff, im glad it got you going. i do like the additions 😉

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (5,661)