I find that I can correctly generate the function

GetValue ( List ("text1" ; "text2" ; "text3" ; "text4" ; "text5" ; "text6") ; $$i )

(where $$i is an iterator). But when I replace either List (…) itself or the parameters of List (…) with the name of a text field belonging to another table in the same database (whose records contain the same values shown in the list above), the GetValue function is not correctly calculated.

I am entering the name of the field as:

other_table::name_of_field

I have also tried replacing the name of the field with a global variable, but still no go.

I'd be grateful for any suggestions as to how to fix this.

Is it properly related? What does it mean by "not properly calculated"? What kind of results are you getting?

I'm getting a null return, i.e., "".

The two tables are not related. The"other" table (supplying the list) exists only as a way of quickly generating temporary lists of items to be used for searching in the database's main table. The contents of the "other" table are imported at the beginning of the script and deleted at the end.

Thanks for your patient consideration.

In order to test the theory that the two tables need to be related in order to use the contents of the temporary table to search in the main table. I created a dummy field in the main table and set up a relationship to it from the one field in the temporary table. Still no go.

If I understand your scenario, I would set up a Cartesian ("x") relationship between the two tables, and then use GetNthRecord to pull in the data:

GetNthRecord( related field; $$i )

If you don't want a relationship for some reason, then I'd go to the layout that has the "list" values on it and zap them into repetitions of a variable. Then you could simply use $SweetVariableArray[$$i] to retrieve them.

I'm sorry, but I don't know what "SweetVariableArray" is and don't see it on-line (including this site) or in my manuals.

However, the Cartesian Join works like a dream! And when I delete the records in the temporary table and then import new data into it, there don't seem to be any unhappy side-effects.

Thanks! And thanks also to all others who spent time thinking about this. Quite an adventure for me today!

***

Edit: it is, however, very much slower than pasting the search terms directly into the script — I think 25-30 times slower. But at least it's a genuinely automated solution now. Thanks again!

Sorry, that was just a random lighthearted variable name, it has no special significance. My point was that using repetitions of a variable (essentially what "real programmers" call an "array") can be a useful and convenient way to store and retrieve data.

I see. That clarifies a number of things I haven't understood. Thank you for your persistence — it's now time for me to get down to actually using my database for the next 8 hours — but I'll be back on this problem again tomorrow.