Designing a script that creates a range of Alpha Values

[Graham Wickens]

I want to add a range of Alpha or Number values to a field in my table.
range could be for example "EAA to EAZ" or "RM to RT" or "1 to 9" or even "04 to 12" or even "EA-01 to EA-09"

my setup code is as follows:

DIM SHARED CodeStart as C
DIM SHARED CodeEnd as C
DIM SHARED NumbChar as N
DIM SHARED CodeType as C
DIM SHARED varC_result as C
varC_result = ui_dlg_box("Add Range Of Codes",<<%dlg%
{Background=#204+255+204}
{region}
Start:| [.8CodeStart!CodeStartEvent_*]{sp=2}End:| [.8CodeEnd!CodeEndEvent_*];
{endregion};
{region}
<*15Run!RUN> <15Close!Close>
{endregion};
%dlg%,<<%code%
if left(a_dlg_button,15) = "CodeStartEvent_" then
if a_dlg_button = "CodeStartEvent_killfocus" then
CodeStart = upper(CodeStart)
end if
a_dlg_button = ""
end if
if left(a_dlg_button,13) = "CodeEndEvent_" then
if a_dlg_button ="CodeEndEvent_killfocus" then
CodeEnd = upper(CodeEnd)
end if
a_dlg_button = ""
end if
%code%)
if varC_result = "RUN" then
NumbChar = len(CodeStart)
CodeType = if(isalpha(ut(CodeStart)),"Char","Numb")
showvar("Start="+quote(ut(CodeStart))+" , End="+quote(ut(codeEnd))+" number of characters is "+NumbChar+" and Code Type is "+quote(CodeType,"'"))
end if

Each new value to be written to field "topparent:Code.value"

that is as far as I have got, at a loss as how to do it, any help gratefully taken

[Graham Wickens]

Had a play.

trace.clear()

example one char changing

Start = "1"
End = "9"
prefix = ""
StartValCol = asc(ut(substr(start,1,1)))
EndValCol = asc(ut(substr(end,1,1)))
trace.writeln(prefix+" = "+startValCol+"/"+EndValCol)

for x = StartValCol to EndValCol
trace.writeln(prefix+chr(x))
next

'

example two chars (one changing)

Start = "10"
End = "19"
prefix = left(start,1)
StartValCol = asc(ut(substr(start,2,1)))
EndValCol = asc(ut(substr(end,2,1)))
trace.writeln(prefix+" = "+startValCol+"/"+EndValCol)
for x = StartValCol to EndValCol
trace.writeln(prefix+chr(x))
next

example three chars (One changing)

Start = "EAE"
Prefix = left(start,2)
End = "EAZ"
startValCol = asc(ut(substr(start,3,1)))
endValCol = asc(ut(substr(end,3,1)))
trace.writeln(prefix+" = "+startValCol+"/"+EndValCol)
for x = startValCol to endValCol
trace.writeln(prefix+chr(x))
next x

example five chars (two Changing)

Start = "RQ-01"
Prefix = left(start,3)
End = "RQ-17"
startValCol1 = asc(ut(substr(start,4,1)))
EndValCol1 = asc(ut(substr(end,4,2)))
startValCol2 = asc(ut(substr(start,5,1)))
EndValCol2 = asc(ut(substr(end,5,1)))
trace.writeln(prefix+" = "+startValCol1+"/"+EndValCol1+" and "+startValCol2+"/"+EndValCol2)
for x = startValCol1 to EndValCol1
for y = startValCol2 to EndValCol2
trace.writeln(prefix+chr(x)+chr(y))
next y
next x

example six chars (three changing)
this is where the problems start, only works if values are in alphabetical order

Start = "G-BEER"
Prefix = left(start,3)
End = "G-BFFZ"
startValCol1 = asc(ut(substr(start,4,1)))
EndValCol1 = asc(ut(substr(end,4,1)))
startValCol2 = asc(ut(substr(start,5,1)))
EndValCol2 = asc(ut(substr(end,5,1)))
startValCol3 = asc(ut(substr(start,6,1)))
EndValCol3 = asc(ut(substr(end,6,1)))
trace.writeln(prefix+" = "+startValCol1+"/"+EndValCol1+" and "+startValCol2+"/"+endValCol2+" and "+startValCol3+"/"+endValCol3)
for x = startValCol1 to EndValCol1
for y = startValCol2 to EndValCol2
for z = startValCol3 to EndValCol3
trace.writeln(prefix+chr(x)+chr(y)+chr(z))
next z
next y
next x

the last example should should be G-BEER -> G-BEEZ , G-BFAA ->G-BFFZ, but it doesnt

one example I found online used base 26 not base 10, but it got too mathematical for my old brain (about 50 years since I used logarithms).

[madtowng]

There's a good chance I'm the author of the base 26, at least that sounds like something I use.

[armasoft]

Graham,

You need to reset (in this instance) startValCol2 and startValCol3 to 'A' once you've processed all the records for each prefix otherwise the G-BF records will start with 'ER' same as the G-BE records...

Start = "G-BEER"
Prefix = left(start,3)
End = "G-BFFZ"
startValCol1 = asc(ut(substr(start,4,1)))
EndValCol1 = asc(ut(substr(end,4,1)))
startValCol2 = asc(ut(substr(start,5,1)))
EndValCol2 = asc(ut(substr(end,5,1)))
startValCol3 = asc(ut(substr(start,6,1)))
EndValCol3 = asc(ut(substr(end,6,1)))
trace.writeln(prefix+" = "+startValCol1+"/"+EndValCol1+" and "+startValCol2+"/"+endValCol2+" and "+startValCol3+"/"+endValCol3)
for x = startValCol1 to EndValCol1
for y = startValCol2 to EndValCol2
for z = startValCol3 to EndValCol3
trace.writeln(prefix+chr(x)+chr(y)+chr(z))
next z
[COLOR=#e74c3c]startValCol3 = "A"[/COLOR]
next y
[COLOR=#e74c3c]startValCol2 = "A"[/COLOR]
next x

[Graham Wickens]

Thanks for that worked better with startValCol3 and StartValCol set to 65 (the asc value of "A").

Now I can try and get all those options into a usable function.

[madtowng]

Here's the main one I use now .
I use it for fool proof date sorting.

If you point me to the old code, I'm sure I can probably find my old function.

'Date Created: 25-Jul-2010 01:57:38 AM
'Last Updated: 02-Nov-2021 08:33:50 AM
'Created By : Gregg
'Updated By : installer
FUNCTION dtfcode AS C (dt2code AS D )
dim yearcode as N = if(mod( (year(dt2code)-1900) ,25) >0,mod( (year(dt2code)-1900) ,25),25)
dim fcode as N = ceiling((year(dt2code)-1900)/25)
dim A as N = 64
dtfcode = chr(A+fcode)+chr(A+yearcode)+chr(A+month(dt2code))+time("dd",dt2code)

END FUNCTION

[Graham Wickens]

import static java.lang.Math.*;

private static String getString(int n) {
char[] buf = new char[(int) floor(log(25 * (n + 1)) / log(26))];
for (int i = buf.length - 1; i >= 0; i--) {
n--;
buf[i] = (char) ('A' + n % 26);
n /= 26;
}
return new String(buf);
}