Advanced Querying

To perform more complex querying using multiple logical operations at once, use the cdb_AdvancedLocalQuery() function.
We will query for people with the name Tim over the age of 40 in our ‘clients’ database:

put cdb_buildQuery("firstName","=","Tim") into tQuerySetA["A"] 
put cdb_buildQuery("age",">","40") into tQuerySetA["B"] 
put cdb_advancedLocalQuery(tQuerySetA, "A AND B", "clients") into tRecordIDList 

We can take the tRecordIDList variable and get the first names that match the query results:

repeat for each line xLine in tRecordIDList
     put cdb_lookupLocalValue(xLine,"firstName","clients") into tFirstName
     put cdb_lookupLocalValue(xLine,"age","clients") into tAge
     put tFirstName && tAge & lf after fld "data"
end repeat

output:
Tim 50

Lets modify our query and look for people that have either the first name Tim or are above the age of 40. This requires us to modify one value(AND to OR) in the cdb_advancedLocalQuery() call:

put cdb_buildQuery("firstName","=","Tim") into tQuerySetA["A"]
put cdb_buildQuery("age",">","40") into tQuerySetA["B"]
put cdb_advancedLocalQuery(tQuerySetA, "A OR B", "clients") into tRecordIDList
repeat for each line xLine in tRecordIDList
      put cdb_lookupLocalValue(xLine,"firstName","clients") into tFirstName
      put cdb_lookupLocalValue(xLine,"age","clients") into tAge
      put tFirstName && tAge & lf after tData
end repeat
put tData into fld "results"

output:
Tim 50
John 43

We can do even more complex queries. Lets query the following:
income >= 70000 AND age: <= 30
OR
age: income >= 150000 AND >= 50

put cdb_buildQuery("income",">=","70000") into tQuerySetA["A"]
put cdb_buildQuery("age","<=","30") into tQuerySetA["B"]
put cdb_buildQuery("income",">=","150000") into tQuerySetA["C"]
put cdb_buildQuery("age",">=","50") into tQuerySetA["D"]
put cdb_advancedLocalQuery(tQuerySetA, "(A AND B) OR (C AND D)", "clients") into tRecordIDList
repeat for each line xLine in tRecordIDList
     cdb_lookupLocalValue(xLine,"firstName","clients") into tFirstName
     put cdb_lookupLocalValue(xLine,"age","clients") into tAge
     put cdb_lookupLocalValue(xLine,"income","clients") into tIncome
     put tFirstName & ":" && tAge & comma & "$" & tIncome & lf after tData
end repeat
put tData into fld "results"

Now, lets do an XOR operation. We do not want results where both of these conditions will be true.
income > 50000
XOR
age >= 55

put cdb_buildQuery("income",">","50000") into tQuerySetA["A"]
put cdb_buildQuery("age",">=","55") into tQuerySetA["B"]
put cdb_advancedLocalQuery(tQuerySetA, "A XOR B", "basicTests") into tRecordIDList
repeat for each line xLine in tRecordIDList
      put cdb_lookupLocalValue(xLine,"firstName","basicTests") into tFirstName
      put cdb_lookupLocalValue(xLine,"age","basicTests") into tAge
      put cdb_lookupLocalValue(xLine,"income","basicTests") into tIncome
      put tFirstName & ":" && tAge & comma & "$" & tIncome & lf after tData
end repeat
put tData into fld "data"

Over your head? Start with with our Basic Queries tutorial.