Multiple Lookups

[Graham Wickens]

In my "LOGFILE" dbf I have 4 fields that I need to search for a inputted target value. All are Character Fields, IDENTITY, FALSE_ID and AS contain single values whilst OLD_ID contains many from 0 upwards.
I have scripts written that will quite happily find values one-at-a-time. If I use the A5 query option I can search for multiple entries, but have to input my list ov values 4 times, once for each field.
Is it possible to write a user script to allow multiple lookups?
Answers on a post card please!!

TIA

[Tom Cone Jr]

RE: Multiple Lookups

Begin postcard:
Yes, if you are referring to a query against multiple fields. This can be as simple as getting the search string from the user, and presenting a checkbox list of searchable fields. Depending on which boxes are checked, the query expression built by the script will vary from search to search. By the way, calling these queries 'lookups' will cause confusion, since the table lookup field rule is apt to be mistaken for what you are talking about.
End postcard.

[Graham Wickens]

RE: Multiple Query Values

The trouble is the user will not know which field the target value is in, so a query on the 4 fields mentioned will have to be standard. Say for example the user enters 15 target values into a search table, how do I then get the query to look for ALL 15 values in one search of the LOGFILE table using the above search table?

the query filter I have been using for SINGLE target values is:

query.filter="identity=target.id .OR. as=target.id .OR. false_Id=target_ID .OR. containsi(Old_Id,target.Id)"

[Mike Konoff]

RE: Multiple Query Values

I once had a database in Q&A for tracking production. In the database there were 6 fields for employee's that may have worked on the production being recorded. Today employee #67 could be in the #1 field, tomorrow, #67 could be in the #3 spot. To search for all production that employee #67 worked on I would have to search all 6 fields in all records, which would mean I would have to type the search spec in all 6 fields.

I solved this by adding one field, a calculated field that would be a character string of all employee number fields. So if employee in the first spot was #67, the second spot was #112, and the third spot was #84, then the added calculated field would read 67-112-84-. In A5 the search for employee number 67 would be *67*, it would only be needed in the one field, and it would return all records that employee #67 worked on.

Additionally, I have come to find that A5 has another neat feature. It can perform Query's on Calculated Display Fields. So in all reality, you don't even need to add a field to your table to do this. Simply create a calculated display field on your form such as what I described above and you'll be able to search all records for what you're looking for.

If you do this, be sure to unclick the "disable" button on the setup tab in the calc display's field properties box.

Let me know if this works for you.

Mike

[Tom Cone Jr]

RE: Multiple Query Values

Graham, please clarify. If the user enters 15 search keys, do you want the search to fail unless all 15 are found in a single field?

Do you want the search to succeed if any of the 15 keys are found in any of the 4 available fields?

-- tom

[Graham Wickens]

RE: Multiple Query Values

to clarify, search to succed if it finds value in ANY of the fields.

[Tom Cone Jr]

RE: Multiple Query Values

Sorry to be dense. But what does

if it finds value in ANY of the fields


mean?


Are you trying to search 4 fields for any of 15 discrete values?

Are you running 15 searches, each of which must run against 4 fields?

Are you trying to search 4 fields for any field that contain (by itself) all 15 values?


-- tom

[Graham Wickens]

RE: Multiple Query Values

I am running 15 searches, each of which must run against the 4 fields?.

[Graham Wickens]

RE: Multiple Query Values

I think I may have been confusing myself and you!
The Parent form is "Logfile Prod"
on the forms right-click-menu is option "Multiple Record query", this invokes form "Multiple Targets".
This form has 2 buttons, 1) "Close" and 2) "Search" which invokes following script:
''XBasic
logfile=table.open("logfile")
multi =table.get("multi_query")
multi.fetch_first()
while .not. multi.fetch_eof()
query.filter="identity="+multi.target
+".or.as="+multi.target
+".or.false_id="
+multi.target
+"containsi(old_id,"+multi.target+")"
idx=logfile.query_create
multi.fetch_next()
end while
:Logfile_Prod.Resynch()
:Logfile_Prod.Activate()

I THINK its creating the query OK now, but I cannot get the "Logfile Prod" form to resynch to the query table.

[Tom Cone Jr]

RE: Multiple Query Values

Graham,

when you use a script like this to run successive (consecutive) queries I believe the expression in 'query.filter' is replaced in each cycle. I don't think you can walk through a series of consecutive xbasic queries gradually winnowing down the result set if you overwrite the value of 'query.filter' on each pass. Instead, assuming your filter expression is ok, this script should return the last query's result set. If there are 15 entries in the log file, this script would return the result set of the 15th item only. -- tom

[Tom Cone Jr]

RE: Multiple Query Values

Maybe this will help.

(Note: I've used an incorrect line continuation character in this script,
but did so for clarity on this board.)

' assume table with 2 fields called field1 and field2
' assume you want to query the table to find each record
' where any of three possible char string values are present in either
' field1 or field2

' here's how the query would be expressed in xbasic:

tbl = table.open(tablename)
query.filter = "(field1 = 'value1' .or. _
field1 = 'value2' .or. field1 = 'value3' .or. _
field2 = 'value1' .or. _
field2 = 'value2' .or. field2 = 'value3'"
idx = tbl.query_create()
recs = idx.records_get()
trace.writeln("Result set has "+ltrim(str(recs))+" records")
idx.drop()
tbl.close()

[Graham Wickens]

RE: Multiple Query Values

You were right, the script did the search in 15 passes! but I then had 15 temporary queries to look through, not the result I need.
so next I tried concatenating the input records into one long query filter and tried this:
''XBasic
logfile=table.open("logfile")
multi =table.get("multi query")
multi.fetch_first()
query_filter=""
while .not. multi.fetch_eof()
if multi.target "" then
query_filter=query_filter+"identity="+chr(34)+ut(multi.target)+chr(34)
query_filter=query_filter+".or.as="+chr(34)+ut(multi.target)+chr(34)
query_filter=query_filter+".or.false_id="+chr(34)+ut(multi.target)+chr(34)
query_filter=query_filter+".or.containsi(old_id,"+chr(34)+ut(multi.target)+chr(34)+")"
query_filter=query_filter+".or."
end if
multi.fetch_next()
end while
query_filter=left(ut(query_filter),len(ut(query_filter))-4)
query.filter=query_filter
idx=logfile.query_create()

This worked but only upto a maximum of 11 records in the multi_query table. Any suggestions

[Tom Cone Jr]

RE: Multiple Query Values

If the target values are unique you might build a long string consisting of the concatenated values of all target strings. Then you might use &Any or $ to see if the current field value is found anywhere within the longer string of concatenated values.

something like
query.filter = "(*ANY(concatenatedtargetsstring,trim(field1)) .or. (*any(concatenantedtargetsstring,trim(field2)) .or. (*ANY(concatenatedtargetsstring,trim(field3)) .or. (*ANY(concatenatedtargetsstring,trim(field4))"

*ANY is case sensitive

-- tom

[Tom Cone Jr]

RE: Multiple Query Values

To avoid problems caused by juxtaposed strings inside the concatenated target string, you would simply pad each value with a hard underscore.

Instead of a string like this
abcdefghijkl

You'd get
abc_de_fgh_jk_l_mno


etc.

[Graham Wickens]

RE: Multiple Query Values

This is my latest effort, but I cannot see the error in the query.filter expression. Can you see if you can spot it?

''XBasic
dim q1 as c
dim q2 as c
dim qf as c
l=table.open("logfile")
m =table.get("multi query")
'' create concatenated fields
m.fetch_first()
while .not. m.fetch_eof()
if m.target "" then
''q1 for the single value fields IDENTITY, AS & FALSE_ID
q1 = q1+ut(m.target)+","
''q2 for the OLD_ID field which contains a history of all
'' Identities associated with this record
'' format being "xxxxxx,YYYYYY,zzzzzz,....."
q2 = q2+"containsi(old_id,"+chr(34)+ut(m.target)+chr(34)+").or."
end if
m.fetch_next()
end while

q1=left(q1,len(q1)-1) '' remove comma at end of field
q2=left(q2,len(q2)-4) '' remove ".or." from end of field

'' set query filter expression

qf="*ANY("+chr(34)+q1+chr(34)+",trim(identity)).or.*any("+chr(34)+q1+chr(34)+",trim(as)).or.*any("+chr(34)+q1+chr(34)+",trim(false_id))"
qf=ut(qf+".or."+q2)
trace.writeln(qf)
query.options="N"
query.filter=qf
idx=l.query_create()
num=idx.records_get()
''
trace.writeln("number of matches = "+ut(str(num)))

TIA