I’m trying to build a custom procedure that returns an array of data from different columns in another (open) database, and I would like to define these field(s) by a local variable. I have figured out a way to do this with a lookupall( function, but to do so means I have to define exactly how many fields will be used in advance. I was hoping I could use an arraybuild function and set the “formula” by hand each time (for instance in a form with a form text field), which would allow me to combine the data in other ways (and perhaps perform functions on that data). As the help file suggests:
" Tip: The lookupall( function is limited in the ability to format the data from the second database… you can gain a lot more flexibility by using the arraybuild( function"
However, I can’t seem to get good data by using variables to define the fields within the formula of my arraybuild function.
This works perfectly:
let keyDB = “ThisDatabase”
let keyField = “ID”
let searchDB = “AnotherDatabase”
let keysearchField = “ID”
let field_1 = “City”
let field_2 = “State”
local matches arraybuild matches , cr() , searchDB, City+”, "+State, ID = fieldvalue(keyDB,""+keyField) message matches
but “City” and “State” have to by hard-coded into the “formula.” I understand that when defining fields via variable one has to use the structure “”+variable, so that the program understands that the variable name is not itself the field name, but the above code doesn’t work if I replace City with field_1+"" … Likewise the “ID” in the query doesn’t work as a variable in my tests.
However, as a lookupall( function I can use variables to. define the fields and get a perfectly good data array:
local matches matches = lookupall( searchDB, ""+keysearchField, datavalue(""+keyField), ""+field_1, ""+field_2, cr(), chr(0x2c)+" " ) message matches
What I get with the lookupall( formula is the full list of “city, state” records that match the key field. When I try to substitute variables in the arraybuild function for the field names, I get the correct number of matching records, but each line repeats the same “city, state” as the first matching record in the database. My question is: is what I’m trying to do possible? Can I use a variable like field_1 to define the name (or number) of the field that is used in an arraybuild function? If not, is it possible to define the number of datafields in the lookupall( function as a variable within the procedure (to accomodate more or fewer fields being selected)