Sequencing using letters and numbers

(0) Share

Report

Posted on by Community Power Pla...

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Verified answer

rubin_boer 4,841 Super User 2024 Season 1 on at

Like (0)

Report
hi @Anonymous

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

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:

Hope it helps,

R

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@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?

Was this reply helpful? Yes No
rubin_boer 4,841 Super User 2024 Season 1 on at

Like (1)

Report
hi @Anonymous

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)

Hope it helps,

R

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

Thank you...that works!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
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 .

Was this reply helpful? Yes No
rubin_boer 4,841 Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No