Superlookup( had known problems in database scanning statements due to problems with fieldvalue( or its shorthand ««»». Those were reportedly fixed in 0.9.003, but either I’m doing something wrong or some problems remain. I provide a simplified example with two databases, To.pandb and From.pandb, created from the respective following texts:
CaseTo,BatchTo
41852,
43882,
42377,
43796,
43004,
44382,
and
CaseFrom,BatchFrom
41852,58
43882,516
42377,202
43796,500
43004,302
44382,644
The Fieldtypes are all text, although their contents happen to be integers. I think this should all work the same with Integer, Floating Point or Date fieldtypes. I don’t know whether keyvalues and keydata can be binary fieldtypes. In this examples the keyvalues and keydata are in the same order, but not sorted. If I select any record in field BatchTo of To.pandb, choose Morph: Fill with Formula… and supply as formula:
lookup("From",CaseFrom,CaseTo,BatchFrom,"")
the correct values appear in each record.
If instead I supply what I think is the corresponding Superlookup formula:
superlookup("From", {CaseFrom=««CaseTo»»}, {BatchFrom},"default","")
it returns an error:
fieldvalue( function: data field "CaseTo" does not exist
If I substitute the explicit field value( for the double chevron shorthand:
superlookup("From", {CaseFrom=fieldvalue("To", CaseTo)}, {BatchFrom},"default","")
it doesn’t return an error, but also doesn’t scan. It returns the value from the current record in some or all of the records, depending on which was the current record.
I have a lot of formulafill lookups I’d like to convert to formulafill superlookups, sort both sides appropriately then try adding the “Wrap” option for speed. I hope I’m somehow writing the superlookups wrongly and can easily be corrected without Jim needing to fix anything. My example databases have simplified field names. Many of those I want to convert have field names with spaces or special characters so any special instructions on quoting field names in the search or current databases would be helpful, eg. if the keydata’s name was written as two words should it be cited as «««Case To»»», ««"Case To"»» or in some other way? If the keyfield’s name were written as two words would "Case From", «Case From», |||Case From||| or would all be correct? How about a two word datafield in the formula?