Variable field name in arraybuild

Panorama X
1

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)

2

This gets easier, if you use an arraybuild( function, instead of the statement. With the function, you are using formulas to build the formulas, so you just need to keep those variables outside the quotes.

let keyDB = “ThisDatabase”
let keyField = “ID”
let keyValue = fieldvalue("",""+keyField)
let searchDB = “AnotherDatabase”
let keysearchField = “ID”
let field_1 = “City”
let field_2 = “State”

local matches
matches = arraybuid(cr(),searchDB,
field_1+{+", "+}+field_2,
keysearchField+{ = }+quoted(keyValue))
3 
let keyDB = “ThisDatabase”
let keyField = “ID”
let keyValue = fieldvalue("",""+keyField)
let searchDB = “AnotherDatabase”
let keysearchField = “ID”
let field_1 = “City”
let field_2 = “State”

local matches
matches = arraybuild(cr(),searchDB,
field_1+{+", "+}+field_2,
keysearchField+{ = }+quoted(keyValue))
message matches

gives me this:

Screen Shot 2021-10-30 at 4.52.43 PM
Screen Shot 2021-10-30 at 4.52.43 PM2093×783 179 KB 

local matches
arraybuild matches ,
    cr() ,
    searchDB, 
    City+chr(0x2c)+" "+State,
    ID = fieldvalue(keyDB,""+keyField)
message matches

gives me this:

Screen Shot 2021-10-30 at 4.56.59 PM
Screen Shot 2021-10-30 at 4.56.59 PM1667×757 173 KB

4

Thank you @dave for the suggestion, but I can’t seem to get your formula to return the data I expect either. For reference, I’m using test data in “ThisDatabase” with the record selected for an unique ID “932847938”, which should correspond to four different entries in “AnotherDatabase” containing addresses. I can’t seem to get any way of notating variables within the formula portion of an arraybuild statement or arraybuild( function.

5

ID appears to be an integer field. Change quoted(keyValue) to str(keyValue)

1 Like
6

Fantastic! Thank you! That returns exactly the values I expected.

7

Better yet, replace quoted(keyValue) with constantvalue(keyValue). That will put quotes around it if it’s text, but leave them off if it’s a number.

8

Thanks for the tip, definitely good to build in that possibility! And it makes the procedure more generally useable without future modifications.