Help designing a complex search and then display in specified order

[Graham Wickens]

Need your your help again gentlemen.

In my table the three relevant fields are:

Reg (one value ) (Char,16) i.e. "FZ-12" or 1234
Old_id (none or more comma separated) (Char,72) i.e. "51-10100(N),FZ-12(OO),110100(OY)"
Origin_id (one value) (Char,10) i.e. "51010100"

I define a range of values .

I want to be able to select all records that are in the search value range and in that value order.

for example currently researching NATO F-84G aircraft from the early 1950s, serials/identities for Belgium are FZ-1 through to FZ-213.

so I would want my resulting query listing them in their serial order FZ-1 to FZ-213 (or whatever order I specify) without having to jump about the table as with a full text index.



Any suggestions as to the best way to do this?

many thx

[Graham Wickens]

Was able to think clearly today, I got a script to do what I wanted as follows

DIM SHARED vPrefix as C
dim shared ResultArray[0] as P
DIM SHARED vTypeCode as C
DIM SHARED varC_result as C
DIM vTypeCode_def as C = "k=aircraft_type,{keylist_build(\"H=.05,1:25\",''+Type_Code-\"|\"-Sub,left(''+aircraft_type,25))}{.t. .and. unique_key_value()}"
varC_result = ui_dlg_box("Serial Range Extraction and Order",<<%dlg%
{Background=#204+254+204}
{region}
Serial Range Prefix:| [.12vPrefix];
Aircraft Type:| [%@vTypeCode_def%.40vTypeCode];
{endregion};
{line=1,0};
{region}
<*15Run!RUN> <15Close!CLOSE>
{endregion};
%dlg%)
if varC_result = "run" then
q_filter = quote(vPrefix)+" $ alltext() .and. (type_code="+quote(word(vTypeCode,1,"|"))+" .and. sub="+quote(word(vTypeCode,2,"|"))+")"
showvar(q_filter)
q_order = "recno()"
q_cols = <<%list%
Reg
unique
Old_Id
Origin_id
%list%
ResultArray.initialize_from_table("Logging",q_filter,q_order,q_cols)
rec_count = ResultArray.size()
for x = 1 to rec_count
if vPrefix $ ResultArray[x].reg then
res = alltrim(word(ResultArray[x].reg,2,"-"))
else
res = extract_string(ResultArray[x].old_id,vPrefix,")",1,.f.)
end if
ResultArray[x].Recorder = padl(word(res,1,"("),8,"0")
next x
ResultArray.sort("A","recOrder")
showvar(ResultArray.dump_properties("RecOrder Unique Origin_id reg old_id"),"result")
end if

Now I have to adapt it into something useful.